DaveWentzel.com            All Things Data



During peak periods of use, the recompilation of the single compiled plan architecture can result in serialized lockout behavior during recompilation of the query plan. In other words, when recompilation occurs, a compile lock is placed on the shared compiled plan, suspending all concurrent executions until the completion of recompilation. In such cases, excessive recompilation can be undesirable.
Recompilation can be tracked using the SP: events in profiler.  Ensure you monitor EventSubClass to see the cause of the recompilation.  The most common and easy to fix is 5...this is due to temp table manipulations.  This can be fixed by using KEEP PLAN, moving temp table CRUD to a sub proc, or encapsulating temp table CRUD into sp_executesql. 
In 2005 recompilation occurs at the statement level and not at the whole procedure level.  Or so this is documented.  This means you shouldn't have to break up long procedures into smaller chunks just to avoid the recompilation hit. 

Add new comment