Q1 Is there any way I can tell SQL Server not to precompile a particular stored procedure ?
By "precompiled" do you mean that the procedure has been compiled, and the plan is being retrieved and reused (normally a desirable thing), without a re-compile, and there is a need to prevent such caching and plan reuse???
If so, then:
A1 Yes. Consider adding a with recompile statement to the procedure; that should cause it to be re-compiled each time it is executed.
My question was NOT to recompile. Thanx anyway for the response. -Bheemsen
Q2 Is there any way I can tell SQL Server NOT to recompile a particular stored procedure ?
A2 Indirectly, there may be e.g.(if there is a with recomple statement, remove it).
Some kinds of things that cause recompilation include data changes in rows, schema changes and certain set commands; seperating such things out of existing stored procedures may or may not improve performance by reducing recompiling. For example: If a procedure includes ddl statements; one might consider creating several seperate procedures and calling the "ddl procedures" from other non-ddl stored procedures as required (test alternate designs for performance differences prior to implementation).
Bear in mind that while recompiling may be resource intensive, (in which case reusing a high cost rated plan is generally desirable), not all tsql recompilation incurs high cost rated plans i.e.(it may not always be "cost effective" to keep a smaller infrequently used plan in the Procedure Cache). Sql Server algorithyms effectively weight Procedure Cache space, recompile costs, and recent plan usage statistics.
That said, you may wish to consider running Profiler traces (listing recompile events and statements) and then analyze the column detailing the reason for recompilation. Doing so should both give a reasonable idea of why procedures are being recompiled, as well as some ideas about strategies that may improve performance.
An SP will be compiled at first run.
It will then be recompiled if there are any changes in the objects on which it depends.
This can be if there are changes to the table structures, indexes, views, ... or if it depends on temp tables that are not created within the SP.
Changes in statistics can also cause a plan to be marked as invalid.
Note also that if you have temporary tables (not the new table variable) or User defined Functions (UDFs), SQL may choose to recompile the SP every time. SQL also recompiles when the statistics are out-of-date.