Why do stored procedures recompile?

When SQL Server stored procedures recompile, it is not always good news for performance conscious developers and to identify the potential issues, SQL Profiler can be used.

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.

Comments