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

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

Proprietăți

ID articol: 308737 - Ultima examinare: 12 sept. 2008 - Revizie: 1

Feedback