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