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

Article translations Article translations
Article ID: 308737 - View products that this article applies to.
This article was previously published under Q308737
Expand all | Collapse all

On This Page

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.

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 How to obtain the latest SQL Server 2000 service pack

Properties

Article ID: 308737 - Last Review: December 23, 2005 - Revision: 4.4
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbhowtomaster KB308737

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com