SQLcommitted

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

AS

BEGIN

SET ARITHABORT OFF

SELECT ‘SQLCOMMITTED’

END

GO

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

exec TestRecompilation

go

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

January 13, 2012 - Posted by SQLcommitted | Performance Tips and Tricks | database performance, recompilation, set option, Stored Procedure Recompilation