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:

1. 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'

2. 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.
3. 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]

4. 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.
5. 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

• 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.

Compilation Related Changes with SQL Server 2005