Thursday, February 3, 2011

SP:Recompile Event - finding out the reason for Stored Procedure Recompilation

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.

statistics changed




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