Microsoft KB Archive/308737

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

Article ID: 308737

Article Last Modified on 12/23/2005

-

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

-



This article was previously published under Q308737



IN THIS TASK

 * SUMMARY
 * Identify the cause of recompilation in an SP:Recompile event
 * REFERENCES



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 &quot;SP:Recompile Event Class&quot; topic in SQL Server 2005 Books Online. back to the top

