When tracing SQL Stored procedure recompile event (SP:Recompile), it may find helpful to know why procedure recompiled. This can be determined through the value in column EventSubClass for SP:Recompile Event. (see capture)
Following sub classes can be found for SP:Recompile event.
SubClass Name | SubClass | Detailed Reason for recompilation |
Schema changed | 1 | Schema, bindings, or permissions changed between compile and execute |
Statistics Changed | 2 | statistics changed |
Deferred compile | 3 | Recompile because of DNR(Deferred Name Resolution). Object not found at compile time, deferred check to run time. |
Set option change | 4 | Set Option changed in batch |
Temp table changed | 5 | Temp table schema, binding, or permission changed |
remove rowset changed | 6 | Remote rowset shcema, binding, or permission changed |
query notification environment changed | 8 | NEW in SQL Server 2005 |
partition view changed | 9 | SQL Server 2005 sometimes adds data-dependent implied predicates to WHERE clauses of queries in some indexed views. If the underlying data changes, such implied predicates become invalid, and the associated cached query plan needs recompilation. (New in SQl Server 2005) |
No comments:
Post a Comment