You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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.

back to the top

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
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
back to the top
Properties

Article ID: 308737 - Last Review: 12/23/2005 00:32:09 - Revision: 4.4

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

  • kbhowtomaster KB308737
Feedback
&t=">