Review statement logic and consider techniques to reduce frequent statement recompiles.
Some of the main causes of frequent statement recompiles include the following:
- Ad-hoc queries. Ad-hoc queries are SQL statements sent directly to the server without being enclosed in stored procedures. In order to reuse the execution plan for a given ad-hoc query, the entire statement must be identical to the one executed before. Even if there is an extra space or line-break, the query plan will not be reused. Since SQL Server 2005, the ad-hoc query plan can be cached using auto-parameterization feature. In this case,SQL Server can replace the literal value with a parameter and cache the query execution plan. If the same query is executed with a different literal value, the plan can be reused.
- SET options. Changing the value of the following set options is likely to cause SQL Server to not reuse execution plans. It is recommended to change the settings at connection time and ensure they do not change for the duration of the connection:
- Bulk Insert statements. The execution plan of bulk insert statements is never cached so their execution plan is compiled at every execution.
- Literals longer than 8KB. If any query contains a literal longer than 8KB, the execution plan is not cached and it will be recompiled at each execution.
- Unqualified object names. If the object name isn’t qualified, then SQL Server will have to resolve the name to determine the correct database where the object resides and its owner. If Transact-SQL code specifies database name, owner name and object name for each referenced object, then SQL Server is more likely to re-use the query execution plan. Query plans can typically be reused if the server name, database name and owner name for each object referenced by the query are the same from one execution to next.
- CREATE PROCEDURE…WITH RECOMPILE or EXEC PROCEDURE…WITH RECOMPILE. If you use either of these constructs, you advise SQL Server to recompile the execution plan; therefore previously used execution plan will not be reused. In rare cases, recompiling stored procedure execution plan might be beneficial, but as a rule recompiling stored procedures at every execution is a poor idea.
Other reasons include:
- Correctness. Each Transact-SQL batch references one or multiple database objects (tables, views, user-defined functions, etc.). If the schema of any object referenced by the batch has changed (for example - due to adding a column, dropping a constraint, or creating an index) the batch will have to be re-compiled in order to return correct results. Furthermore, some SET options can affect the results of a query. If the batch changes the value of a particular SET option, then the query plan will be recompiled at each execution.
- Plan optimality. If the batch references tables in which data volume has changed significantly since the last execution of this batch then it will have to be re-compiled. As an example, suppose that a query returns 90% of all data in a given table; the query optimizer is likely to use a table scan or clustered index scan for such a query. Next suppose that you added thousands of rows to the table referenced by the query (perhaps through a BULK INSERT statement). Next time you execute the same batch it only return 10% of the table’s data. Query optimizer will compile a new execution plan and use a non-clustered index seek because doing so will be considerably more efficient (as opposed to scanning the entire table).
- Mixing Data Definition Language (DDL) and Data Modification Language (DML) statements within the same batch or stored procedure. For example, if you create a temporary table, populate and read from this table in the same batch you’ll experience recompiles at every execution. Similarly, adding an index to an existing table or view prior to selecting from it will also cause a recompile.
When users send Transact-SQL statements to SQL Server, the query optimizer generates an execution plan. Typically the query optimizer has numerous options to choose from. It can decide to scan tables; seek for values using clustered or non-clustered indexes; use LOOP, MERGE or HASH join algorithms; choose various orders for joining tables and so on. The query optimizer is very efficient and comes up with the best possible execution plan very quickly. Producing the execution plan uses up valuable resources and some time, so it’s best to eliminate this step if at all possible by re-using an existing execution plan. Compiled query execution plans are stored in the procedure cache. SQL Server can often re-use previously generated query plans for stored procedures as well as for Transact-SQL batches by searching the procedure cache for an existing execution plan.
There are many factors that can cause SQL Server to not use an existing plan and recompile the query plan. Knowing these factors can help you adjust your code so that the number of recompiles is minimized. For example, modifying the value of certain SET options (SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS, and others) within a batch will cause its execution plan to recompile every time the batch is called. Instead it is best to modify the necessary SET options when you establish the connection to the server and maintain the same option values for the duration of the connection. A recompile is also often caused by data changes in tables referenced by each query.
Note: As of SQL Server 2005, when a single statement for which the execution plan needs to be recompiled is found, the execution plan will be recompiled only for that statement instead of the all statements in the batch, as in previous versions
When a batch is sent, the query optimizer examines recompilation thresholds for each table referenced by each query within the batch. It also examines statistics defined on each table. A recompilation threshold is determined by the number of rows found in a table. The query optimizer determines the number of rows changed since the last time statistics were updated by examining rowmodctr column of sysindexes table when using SQL Server 2000 or colmodctr (available only internally and not through system tables or views) when using SQL Server 2005. The number of changes is compared to the total number of rows in the table to determine if the recompile is necessary. Note that the recompilation threshold isn’t checked for table variables, so data changes in table variables will never cause a recompile. The recompilation threshold is checked for temporary tables.
To reduce the number of recompiles due to data changes on temporary tables, you can use the query hint “KEEP PLAN”. To eliminate the recompiles due to data changes you can use the query hint “KEEPFIXED PLAN”. Be sure to test your queries thoroughly as using these options might force the query optimizer to choose a suboptimal execution plan.
In order to promote reuse of existing execution plans, you should try to parameterize your queries and use stored procedures as opposed to ad-hoc Transact-SQL statements. Usage of sp_executeSQL stored procedure as well as EXEC command to execute a dynamic SQL query, increase the odds of reusing existing execution plans.
SQL Server provides several options for forcing recompilation of query plans. If the parameters passed to the query vary widely from one execution to the next, recompiling the execution plan at each execution might provide better performance than reusing the same execution plan. You should test the performance of your queries when their execution isn’t found in cache and has to be compiled at execution time. This way you’ll know if the overhead of compiling the execution plan is acceptable. The DBCC FREEPROCCACHE statement removes all execution plans from the procedure cache. The DBCC FLUSHPROCINDB command drops execution plans within a certain database. Any query executed immediately after DBCC FREEPROCCACHE or DBCC FLUSHPROCINDB will have to be compiled. To recompile a stored procedure’s execution plan, execute the procedure using WITH RECOMPILE option. To recompile the execution plan for a query use the query hint, RECOMPILE.