Set Option causes Stored Procedure Recompilation
I was working on a database performance issue and found that SET options can cause stored procedure recompilation. After knowing this fact a question raised in my mind that, If SET options causes recompilation then why we often use SET options in stored procedure.
Then I tried all applicable SET options in stored procedure and run the SQL profiler by checking the SP:Recompile option, to know whether the SET option is causing recompilation or not.
If Profiler catch the SP:Recompile event it means the stored procedure is recompiled.
Lets Try it,
Step 1: Create a Stored procedure
Create Procedure TestRecompilation
SET ARITHABORT OFF
Step 2: Run the profiler and check the SP: Recompile event. To see the SP:Recompile event check the Show all events check box.
Step 3: Execute the Stored Procedure
Step 4: Check the profiler log. If the SP:Recompile event logged against the Stored Procedure that means the stored procedure recompiled.
Note: Procedure will recompile based on SET option and the value. Say for example if I use SET ARITHABORT ON, it won’t recompile the procedure but SET ARITHABORT OFF will cause procedure recompilation.
Refer link http://msdn.microsoft.com/en-us/library/ms190707.aspx to know more on SET option.
For more detail on procedure recompilation refer http://support.microsoft.com/kb/243586
3 Comments »
Leave a Reply