Milena Petrovic

Frequent query recompilations – a SQL query performance killer – introduction

April 9, 2014 by
When it comes to SQL Server performance tuning, there are many factors that should be checked. One of the top performance killers is a frequent query recompilation. It adds pressure to the processor and degrades performance.

In this article, we will explain what compilations and recompilations are, and give recommendations for creating reusable queries to keep the pressure off your processor.

What is a compilation?

A compilation is the process when a stored procedure’s query execution plan is optimized, based on the current database and database objects state. This query execution plan is then stored in cache and can be quickly accessed.

When a query is executed, it’s sent to the parser first. The parser checks the query syntax and stops the execution if the syntax is incorrect.

If multiple syntax errors exist in the submitted query (in this example both in the first and the third line), the parser will stop when it reaches the first one.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘*’.

When the syntax is correct, the query goes further to the algebrizer. The algebrizer finds all objects used in the query, verifies their names, finds the data types used, checks whether aggregate functions are used, and based on the information collected creates a syntax-based optimization.

A query compilation consists of the processes executed in the parser and algebrizer. A complied plan is saved in cache.

What is recompilation?

A recompilation is the same process as a compilation, just executed again. If the database structure or data change significantly, a recompilation is required to create a new query execution plan that will be optimal for the new database state and ensure better procedure performance.

A recompilation degrades SQL Server performance, as SQL Server is performing the same action multiple times, instead of using its resources for other important actions. If you could use the cached execution plan, instead of recompiling the stored procedure again, that would make query execution faster for the time needed for a recompilation.

Therefore, it’s recommended to have reusable query execution plans.

Recompilation cannot be completely eliminated, but you should watch for a large number of recompilations which indicates that queries are reused, but their query execution plan is not. Also, a large number of compilations should be investigated, as that means that new queries are excessively compiled, and not reused. In some cases, it’s recommended to check the compilation/recompilation ratio.

If the recompilations occur more than expected, find the stored procedures that are frequently recompiled. You can use SQL Profiler, or a SQL Server monitoring tool. Then determine why the stored procedure is frequently recompiled instead of reused from cache, and finally fix the problem.

What is parameterization?

One of the mechanisms that SQL Server uses to provide compiled queries to be reused is using parameterization. For example, we will create a query execution plan for the following query that contains the specific value for the AddressID.

If no parameterization was used, it would mean that each time the same query is executed, but with different values for the AddressID, it would have to be recompiled. As shown in the query execution plan, this is not the case, as the @1 parameter is used instead of the exact value, so the query execution plan can be reused for every AddressID value.

Query cost for DELETE command

When is a query recompiled?

As described above, a compiled query execution plan is stored in cache.

The queries are automatically recompiled when:

A query is executed using a RECOMPILE query hint.

There are three query hints that define query execution plan use. Query hints are executed on the statement level, so they don’t affect the whole stored procedure or query.

RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled. This is an alternative to using the WITH RECOMPILE clause in stored procedures; it is useful if you want to recompile only some of the statements in the stored procedure, not all of them.

For example:

KEEP PLAN – specifies that a query execution plan is not recompiled when it normally is. When the table index column changes due to an INSERT, DELETE, UPDATE, or MERGE statement, the query execution plan is recompiled. The number of changed rows that triggers a recompilation is different for temporary and permanent tables and depends on the number of rows in the table. The threshold value is higher for permanent tables. For example, if a temporary table has less than 6 rows, the plan will be recompiled if 6 new rows are added. For the permanent table with 6 rows, 500 new rows must be added to trigger a recompilation. The KEEP PLAN raises this threshold, so the recompilations occur less frequently.

KEEPFIXED PLAN – specifies that a query execution plan is never recompiled due to index column changes and changes in statistics. When this query hint is used, the plan is recompiled only if the schema of tables used in the query is changed or the sp_recompile stored procedure is executed on these tables. The example above applies here as well; the only difference is OPTION (KEEPFIXED PLAN) instead of OPTION (KEEP PLAN).

A WITH RECOMPILE option us used in a CREATE PROCEDURE statement or in an EXECUTE statement when the procedure is called

The query execution plan is not stored in cache after the stored procedure is executed, so it will be recompiled each time it’s executed

When executing a stored procedure with a WITH RECOMPILE option in the EXECUTE statement, a new query execution plan is created and used for this specific execution, but it’s not stored in cache. If there is already a plan in cache for this specific stored procedure, it’s intact.

The sp_recompile system stored procedure is used

The stored procedure removes an existing query execution plan for a specific stored procedure or query from cache, so they are recompiled the next time they are called.

When the stored procedure is executed, the following message is shown

Object ‘dbo.uspGetBillOfMaterials’ was successfully marked for recompilation.

In this article, we explained what compilations, recompilations, and parameterization are. We showed how to recompile a query using T-SQL query hints, options, and stored procedures. In the next part of this article, we will show how to detect frequently recompiled queries.

Resources
Query Hints (Transact-SQL)
Troubleshooting stored procedure recompilation
Recompile a Stored Procedure

Milena Petrovic

Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

View all posts by Milena "Millie" Petrovic
Milena Petrovic
3,475 Views
  • santhoshnsoft

    THANKS MILLIE, Very useful post