SET Option that Causes Stored Procedure to Recompile
We often use SET option in stored procedure without realizing if it can cause performance problem. I was working on Critical Database Performance issue recently arguing on SET option causing recompile without realizing which one causes or which one not. Moreover I came across in situation when some experts mentioned that "set isolation level read uncommitted" and "set nocount on" are causing recompilation. Whereas truth is these options DO NOT cause recompilation. So I thought of putting following list available handy for reference whenever we write code or optimize code or find stored procedure recompiling due to "set option changed".
In case you want to examine if your procedure is causing recompilation due to "set option" then you should do followings to identify:
1. Open SQL Server profiler
2. Select SP: Recompile event from list (If using SQL Server 2008/2005 then you may not see it by default then you should check "Show All Events" to see the same:
3. Run SQL Server Profiler
Run Stored Procedure
Look for following in SQL Profiler
If you see SP:Recompile captured in profiler that means Stored Procedure is recompiling on every call so you should try to avoid it as it does contribute in negative performance. you may refer http://support.microsoft.com/kb/308737 for more detail on the same.
Analyze SET option before you use it to avoid any negative performance impact on procedure execution.