Understanding SQL Server Recompilations

By: Bhavesh Patel   |   Updated: 2018-07-23   |   Comments   |   Related: > Query Plans

Problem

When an application submits a query to the SQL Server database engine for the first time, a query plan is prepared and complied in order to execute it and then the plan is stored in memory. In subsequent executions of the query, SQL Server will try to re-use this optimized plan from the query plan cache. When SQL Server tries to use this query plan it might find issues or changes that require a recompilation. Due to the recompilation, performance can be impeded. There are a number of reasons why a query recompilation can occur which I will describe in this tip.

Solution

To better understand recompilations, first we will look at the compilation and caching mechanism that SQL Server uses.

SQL Server Query Plan Compilation

At a high level, the query processor goes through multiple phases before producing a query plan in SQL Server. First it parses and normalizes the SQL Server statements then it compiles and optimizes the statements. After that, as a part of the query execution process, the query plan is generated and stored in memory.

When a query plan is cached and there are subsequent executions of the same query, the optimizer decides whether to create a new plan or use the existing plan. There are several reasons why a new plan could be generated such as changes in statistics, cardinality estimation, recompilation, etc.

SQL Server Memory Allocation in Plan Cache

Basically, four types of objects are stored in the Plan Cache and SQL Server decides the appropriate allocation of memory for the Plan Cache from the Buffer Pool.

• Object Plans
  • Stored Procedures, Functions, Triggers
• SQL Plans
  • Cached Plans, Prepared Plans, Auto-Parameterized Plans
• Bound Trees
  • Views, Constraints and Defaults which are internal structures
• Extended Stored Procedures
  • Extended Procedures are system procedures like xp_cmdshell and sp_executesql

Most of the memory allocated to the buffer-pool is used for data caching. Despite how the memory is allocated to the query plan cache, the total memory usage depends on which SQL Server version is used and also the operating system. It might be possible to exceed the memory limits due to internal or external memory pressure and this could cause the cache to be flushed.

As per MSDN, below are the SQL Server version memory cache limits.

SQL Server Recompilation

When a T-SQL/Batch executes, before SQL Server begins executing the query, the database engine fist checks the validity and optimizes the query plan. If any check fails, then the T-SQL statement is compiled again or the corresponding batch will be compiled again and possibly a different query plan can be produced. Such types of compilations are known as recompilations. The Recompilation Threshold (RT) limit also plays a part. Also, the use of temporary tables and the amount of data in temporary tables could cause a recompilation to occur.

Recompilations can be a headache for DBAs, although most recompilations in SQL Server do not impact performance, sometimes recompilations slow down query execution due to frequent recompilations and cause performance bottlenecks.

SQL Server Recompilation Reasons

There are multiple reasons why a recompilation can occur. A recompile of an execution plan could be due to database level configuration changes, schema changes, index changes, etc.

The following query shows the different recompilation reasons.  This was executed in SQL Server 2017 Developer Edition.

We can get information about why a recompile occurred by using the sys.dm_os_performance_counters SQL Server DMV and filter on counter_name for the database.  We can also use Extended Events and Profiler.

Capturing Recompilation Events Using SQL Server Profiler

In SQL Server Profiler, we want to capture events SP:Recompile and SQL:StmtRecompile as shown below.

Using SP:Recompile we can capture procedure level recompilation events and using SQL:StmtRecompile we can capture statement level recompilation events. Here, I have set both options and started a trace.

I have found recompilation events with the reason 3 - Deferred compile. In short, we can capture these events with the help of multiple options, but we need to take action if the server has a large workload, so we don't impact performance while collecting additional data using Profiler.

Recompilations can be beneficial and generate a better query plan that has different parameters, because this can help avoid issues like parameter sniffing or cause plan cache bloat. Developers can also issue a recompile if needed during testing.  Recompilations that occur frequently can cause a performance bottleneck, so I will cover some workaround solutions.

Workaround Solutions for SQL Server Recompilations

Here I will cover some workarounds for SQL Server recompilations.

Auto-parameterized queries and other options

With ad-hoc queries where similar types of queries are executed multiple times; for select, insert, update, delete and merge, if there is not a parameter the query plan may not be prepared well. The text does not match due to case and space sensitivity. We could configure auto-parameterized queries, but we need to make sure the server does have an ad-hoc query workload and queries are running fine from a performance perspective. We can also use a Plan Guide which allows the query optimizer to use a specific plan for the query by using the query level hint Use Plan with the query.

Change execution methods in dynamic T-SQL code

Using stored procedure design methods is a key factor for better behavior than dynamic-SQL. However, if you need to use dynamic T-SQL code, then sp_executeSQL is more efficient than using EXECUTE to execute a SQL statement built as a string when changing parameter values are the only variation. Because the T-SQL statement itself remains constant and the only change is to the parameter values, the optimizer can reuse the query plan which is built on the first execution.

Changing SET options can also affect the plan cache

There are certain behavior changes in the query caching based on how the SET options are applied. For example, when using Showplan_? or No_Exec, the plan is compiled, but not cached. This could also affect statement level caching as well, which means it needs to know the caching behavior when it is used at the query level.

Need to properly use query level recompilation hints

There are a number of ways to use recompilation hints at the stored procedure and query level. There are certain options available, for an example: creating a stored procedure with the recompile option, use exec with the recompile option, or using the hint option(recompile). If you use these options, you should do extensive testing before rolling out to production.

Use of temporary objects in SQL Server

You may find deferred compile frequently when using temporary tables and there are a number of query level hints available to reduce such blocker events caused by the data modification counter, the recompilation threshold or update of statistics.  Some of the options you can use are:

• KEEP PLAN - The keep plan query hint (i.e. OPTION (KEEP PLAN)) changes recompilation thresholds for temporary tables. There is no guarantee to keep the statement in the plan cache when a recompilation event is found while statistics update.
• KEEPFIXED PLAN - To avoid plan recompilation due to plan optimality related reasons (statistics update). Use the OPTION (KEEPFIXED PLAN) hint.
• OPTIMIZED FOR - The Optimized For unknown (or for a specific value) gives the optimizer hint directly to the query optimizer to use the standard algorithms. The optimizer will look at all available data statistics to determine the value of variables used to generate a query plan. This means using this hint could be more intuitive when there are excessive recompilation events.

Server wide and database level configuration

Server and database wide configurations could also impact recompilations. For example, the max server memory configuration is used server wide and the caching mechanism is directly connected with the memory settings. SQL Server memory is used based on the SQL Server version, OS and max server memory value. It is possible to have memory pressure internally or externally on the server.  Due to that pressure, it is possible to flush the stored query plan from SQL Server.  It could affect server wide or database wide changes as well. Trace flags can also impact the caching mechanism for example Trace Flag 8032 reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows the cache to be larger. Query execution configurations such as MAXDOP (parallel execution) and cost threshold for parallelism could also be impacted due to internal caching mechanisms.

Next Steps

• Test these various options on a test or development server prior to rollout in production.
• Read these related articles:
  • Plan caching in SQL Server
  • Troubleshooting Excess Compilations
  • Analyzing the SQL Server Plan Cache
  • SQL Server 2016 memory pressure leads to the plan cache clearing

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips