|Stored procedure recompiles and SET options|
About me and Nucleus
Articles, tips etc.
Courses and training
The information here applies mainly to SQL Server 2000. There has been many changes in more recent versions of SQL Server (statement level recompile, new SET options etc). So, even though the basic idea in this article still holds, I recommend that you instead use below excellent whitepaper:
Plan Caching in SQL Server 2008
Using stored procedures is generally considered a good thing. One advantage of stored procedures is that they are precompiled. This means that at execution time, SQL Server will fetch the precompiled procedure plan from cache memory (if exists) and execute it. This is generally faster than optimizing and compiling the code for each execution. However, under some circumstances, a procedure needs to be recompiled during execution.
Thanks to SQL Server MVP Frank Kalis, this article is translated to German.
The execution engine can come to a point in the stored procedure where it finds that it needs to recompile the whole procedure. Note that stored procedures are not compiled statement by statement. If a recompile is needed, say, at the end of the procedure, then the whole procedure will be optimized and compiled. Why does this happen? Here are some reasons:
Be aware that a procedure can be recompiled several times during execution. I recommend that you use Profiler and catch the SP:Recompile event to determine whether your procedure is recompiled at execute time. You will get one event for each re-compilation.
Not all SET options causes recompiles. So I conducted a test to find out which does. Below table lists all documented SET options and whether each causes a recompile or not.
I used below SQL code to semi-automate above work: