Select the product you need help with

How to identify the cause of recompilation in an SP:Recompile event

Article ID: 308737 - View products that this article applies to.

This article was previously published under Q308737

SUMMARY

This step-by-step article describes how to identify the cause of recompilation in an SP:Recompile event.

Identify the cause of recompilation in an SP:Recompile event

The SP:Recompile event in the SQL Profiler indicates when SQL Server recompiled a stored procedure or a trigger. In SQL Server 2000 Service Pack 2 (SP2) or in SQL Server 2005, the SP:Recompile event provides more information about why the recompilation occurs, which is helpful when you are troubleshooting recompile issues.

When SQL Server runs a stored procedure or a trigger, SQL Server may have to recompile the stored procedure or the trigger for various reasons. The SP:Recompile event in SQL Profiler shows when a stored procedure or a trigger is being recompiled.

In SQL Server 2000 SP2 or in SQL Server 2005, the SP:Recompile event identifies the reason for the recompilation with an integer code in the EventSubClass column. Here is a list of the integer codes with the reason for recompilation:

SQL Server 2000 SP2

   Integer code   Reason for recompilation
   -----------------------------------------------------------------------
   1              Schema, bindings, or permissions changed between compile
                  or execute.

   2              Statistics changed.

   3              Object not found at compile time, deferred check to 
                  run time.

   4              Set option changed in batch.

   5              Temp table schema, binding, or permission changed.

   6              Remote rowset schema, binding, or permission 
                  changed.

SQL Server 2005

   Integer code   Reason for recompilation
   -----------------------------------------------------------------------
   1  		  Schema changed.
               	  
   2  		  Statistics changed.
      		  
   3  		  Recompile DNR.
      		  
   4  		  Set option changed.
      		  
   5  		  Temp table changed.
      		  
   6  		  Remote rowset changed.
      		  
   7  		  For browse perms changed.
      		  
   8  		  Query notification environment changed.
      		  
   9  		  MPI view changed.
      		  
   10 		  Cursor options changed.
      		  
   11 		  With recompile option.

For more information about the SP:Recompile event in SQL Server 2005, see the "SP:Recompile Event Class" topic in SQL Server 2005 Books Online.

Back to the top | Give Feedback

REFERENCES

For additional information about the latest service pack for Microsoft SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:

290211

(http://support.microsoft.com/kb/290211/ )

How to obtain the latest SQL Server 2000 service pack

Back to the top | Give Feedback