See Also: Main_Page - Monitoring & Tuning - Tuning Tools - SQL Server Query Optimizer
Reasons for Recompiling a Query Execution Plan
Query execution plans will be recompiled for one of the following reasons:
- Data in the tables referenced by the query has changed significantly since the query was last executed. Each table has a recompilation threshold associated with it which varies based on the total number of rows in the table. When the query plan is generated, the query optimizer examines the total number of changes in each column against the total number of rows in the table. With SQL Server 2000 the number of changes for each table (or index) can be obtained by querying rowmodctr column of sysindexes system table. In SQL Server 2005 sys.sysindexes is a view that is maintained only for backward compatibility. Instead the query optimizer checks colmodctr value which is maintained for every column in each table. Colmodctr is available only internally and cannot be obtained by querying any catalog views or dynamic management views.
If you experience excessive recompiles and find that this phenomenon is attributed to frequent data changes, you can choose to turn off automatic updating of statistics. Keep in mind, however, that if you turn off automatic updating of statistics you must update statistics manually. Otherwise the query optimizer might come up with poor execution plans for critical queries. It is advisable to use this option sparingly, perhaps by turning off automatic statistics update on a single large table that is being populated heavily throughout the day as opposed to turning off this option at the database level. You can also turn off automatic update of statistics on individual indexes. For example, the following statement turns off automatic updating of statistics on the EnglishProductName_index of dimProduct table:
sp_autostats 'DimProduct', 'OFF', 'EnglishProductName_index'
- The schema of objects affected by the query has changed. For example, if you add or drop columns from the table or change column data types, the query plan will have to be recompiled in order to return correct results.
- You can force SQL Server to recompile the plan of a given routine next time it is executed by running sp_recompile system procedure. For example, the following query will advise SQL Server to recompile the stored procedure CustOrderHist in Northwind database next time it is executed:
EXEC sp_recompile [Northwind.dbo.CustOrderHist]
- Certain operations clear the procedure cache completely thereby causing every consecutive statement to be compiled at first execution. These operations include:
- Detaching the database from server.
- Upgrading a database to the next version of SQL Server.
- Executing the RECONFIGURE statement after changing any configuration setting with sp_configure.
- ALTER DATABASE...MODIFY FILEGROUP command.
- Changing the collation at the database level.
- Executing DBCC FREEPROCCACHE command.
Although typically you should try to minimize the number of recompiles, the DBCC FREEPROCCACHE statement can be quite handy for testing. You should test the performance of your queries (on a non-production server) assuming that the execution plan will need to be generated at execution time because SQL Server won't always find an existing execution plan in the procedure cache. You can use DBCC FREEPROCCACHE, sp_recompile, RECOMPILE query hint or WITH RECOMPILE option to force generating a new execution plan.
- Certain operations remove execution plans from the procedure cache within the context of the current database. Any statement executed the first time after any of these operations will have to have a new execution plan compiled:
- Executing DBCC FLUSHPROCSINDB command. This is equivalent to DBCC FREEPROCCACHE except affects a single (current) database as opposed to all databases.
- ALTER DATABASE...MODIFY NAME command.
- Taking database offline or bringing it back online.
- When auto-close event occurs. The auto-close option should NEVER be used on any database in production environment because it can have detrimental effects on performance. This option should only be used for databases running on SQL Server Express or MSDE.
- Executing DBCC CHECKDB command.
- Creating a view using WITH CHECK option.
Tracking Recompile Events
If you suspect that your application's performance suffers due to excessive recompiles, you can track the recompile events using SQL Server Profiler traces. You should monitor SP:Recompile event under stored procedures as well as Auto-stats event under Performance category (with SQL Server 2000 auto-stats event appears under Objects category) to see if the recompile is due to changing statistics. In addition you should track SP: StmtStarting event to identify the individual statements that caused recompilation.
Other events of interest when troubleshooting recompiles include:
- Cursors: CursorRecompile</b> - shows when cursor execution plan is recompiled due to a schema change. This event is only available with SQL Server 2005.
- Performance: Show Plan All For Query Compile and Performance: Show Plan XML For Query Compile - show batch compilations. Every time the batch is compiled, these events will produce textual query plan much like the one you can obtain in Query Analyzer or SQL Server Management Studio using SET SHOWPLAN_ALL ON. The latter option provides graphical output of the execution plan within the Profiler.
You can also track SQL Compilations / sec and SQL Re-Compilations / sec performance counters under SQL Server:SQL Statistics performance object using Windows perfmon tool.
Compilation Related Changes with SQL Server 2005
If SQL Server 2000 (or earlier versions of software) detected a single statement within a batch or stored procedure that needed to be recompiled, the execution plan was recompiled for the entire routine. SQL Server 2005 introduces statement level recompilation. This means if a single statement needs to be recompiled the plan of the routine can be reused for the rest of the statements. With this in mind we can expect SQL Server 2005 to encounter more recompilation events. For example, if a stored procedure consists of 20 statements and 9 of them need to be recompiled this will result in 9 recompilations, whereas in previous versions this would result in a single recompile of the entire stored procedure plan. On the other hand, the cost of recompiling individual statements will be considerably lower than recompiling the whole routine.
Yet another change with SQL Server 2005 is how recompiles are handled for concurrent executions of the same procedure. With previous versions if the same stored procedure was called simultaneously by two users one of them will have to wait until the execution plan was generated for the other request. The first connection could then try to re-use the stored procedure execution plan generated for the second connection. With SQL Server 2005 both requests can proceed concurrently so a separate plan is generated for both connections at the same time. The plan that is generated last will stay in the procedure cache.