SQL Query recompilations and query performance effects

Use SQL Profiller to track SQL query recompilations

This article will mention in which conditions the query optimizer decides to recompile to queries and how it affects the SQL query performance.

The Boss: Query optimizer

Query Parsing: In this phase, the correctness of the query syntax is checked. In this phase, a parse tree is generated to send the next phase
Query Binding (Algebrizer): The main duty of this phase is to validate whether the columns, tables, and other objects exist in the database. At the same time, it checks the user has permissions to objects that exist in the query
Query Optimization: This step is the most complicated and query performance-related phase of query processing because the query plan is shaped in this step. SQL Server query optimizer performs a cost-based optimization therefore it evaluates different query plan candidates and decides the query plan that has the lowest cost. After the first execution of a query, the generated query plan is stored in plan cache to use for the next execution of the same query. At this point, different reasons can be caused to recompile the queries that are located in the query plan cache

The result of the following query gives us all possible query recompilations reasons.

SELECT dxmv . name , dxmv . map_key , dxmv . map_value FROM sys . dm_xe_map_values AS dxmv WHERE dxmv . name = N 'statement_recompile_cause' ORDER BY dxmv . map_key

Query recompilations and query performance

Schema changed
Statistics changed
Deferred compile
Set option change
Temp table changed
Remote rowset changed
For browse permissions changed
Query notification environment changed
PartitionView changed
Cursor options changed
Option (recompile) requested
Parameterized plan flushed
Test plan linearization
Plan affecting database version changed
Query Store plan forcing policy changed
Query Store plan forcing failed
Query Store missing the plan

Monitor the query recompilations

SQL Server Extended Event is a system monitoring tool that helps to monitor the database performance metrics and collects different events. So that, we can easily use extended events to resolve the query performance problems. Moving from this idea, using an extended event to monitor the recompilations seems a suitable solution. The sql_statement_recompile event can capture and report when a statement-level recompilation has occurred. The following query will create and start an extended event that helps to report when a query is recompiled.

CREATE EVENT SESSION CaptureQuery_Recompilations ON SERVER ADD EVENT sqlserver . sql_statement_recompile ( ACTION ( sqlserver . database_name , sqlserver . query_hash_signed , sqlserver . query_plan_hash_signed , sqlserver . sql_text , sqlserver . username ) ) ADD TARGET package0 . ring_buffer WITH ( STARTUP_STATE = ON ) ;

On the other hand, SQL Profiler is another tool to monitor the recompilations, and SQL: StmtRecompile event class reports when a recompilation occurs. To enable this event class in SQL Profiler we need to select them in the Trace Properties. So, we can use SQL Profiler to monitor query performances but notice that this SQL is deprecated. In the Adventureworks database, we will execute the following query and it will contain the OPTION(RECOMPILE) query hint. Due to this hint, the executed query will be recompiled by the optimizer.

p . [ ProductID ] , pm . [ Name ] AS [ ProductModel ] , pmx . [ CultureID ] , pd . [ Description ] FROM [ Production ] . [ Product ] p INNER JOIN [ Production ] . [ ProductModel ] pm ON p . [ ProductModelID ] = pm . [ ProductModelID ] INNER JOIN [ Production ] . [ ProductModelProductDescriptionCulture ] pmx ON pm . [ ProductModelID ] = pmx . [ ProductModelID ] INNER JOIN [ Production ] . [ ProductDescription ] pd ON pmx . [ ProductDescriptionID ] = pd . [ ProductDescriptionID ] WHERE p . ProductID = 994 OPTION ( RECOMPILE )

When we look at the created extended event, it will capture the query recompilation and what reason causes the recompilation.

Capture query recompilations with extended event

At the same time, the SQL Profiler shows a report after the execution of the query and it includes query recompilation reason.

Use SQL Profiler for query recompilations

Schema changings and query recompilations

Sometimes, we require to change the design of the tables in the database. Such as we can add new columns or change the data type of an existing column. At the same time, we can create, alter or remove indexes on the tables. These types of changes will cause query recompilations. For example, we will change the Description column data type of the ProductDescription table and then re-execute the query.

ALTER TABLE Production . ProductDescription ALTER COLUMN Description nvarchar ( 600 ) ; p . [ ProductID ] , pm . [ Name ] AS [ ProductModel ] , pmx . [ CultureID ] , pd . [ Description ] FROM [ Production ] . [ Product ] p INNER JOIN [ Production ] . [ ProductModel ] pm ON p . [ ProductModelID ] = pm . [ ProductModelID ] INNER JOIN [ Production ] . [ ProductModelProductDescriptionCulture ] pmx ON pm . [ ProductModelID ] = pmx . [ ProductModelID ] INNER JOIN [ Production ] . [ ProductDescription ] pd ON pmx . [ ProductDescriptionID ] = pd . [ ProductDescriptionID ] WHERE p . ProductID = 994

As we can see, a query recompilation occurred because of the column data type changing operation.

Schema changings can cause to query recompilations

Index rebuilds and query recompilations

Indexes are very special database objects and they help to retrieve data fastly from the databases. Because of this feature, they increase the query performances. However, data modifications may corrupt the logical orders of the indexes. In this type of fragmentation, the new page isn’t in the same order as the physical order so logical fragmentation occurs. In order to resolve this problem, we can rebuild the logical order of the index pages. Through the following query, we can rebuild all indexes of the Product table.

ALTER INDEX ALL ON Production . Product REBUILD

When we re-execute the sample query after the index rebuilding operations, we will see that the query will be recompiled by the query optimizer due to schema changed reasons.

Index rebuildings can cause query recompilations

SQL Server statistics and query recompilations

SQL Server statistics plays a critical role in query performance because the query optimizer uses statistics to estimate how many rows will return from a query. The query optimizer can create statistics for an individual column during the execution of the query if we enable the Auto Create Statistics option of the database. After creating statistics, the query optimizer recompiles the executed query, assuming there will be more up-to-date statistics data. Now let’s learn this concept more deeply with an example. At first, we will create a table and then execute a very basic query.

CREATE TABLE TestNewProduction ( PID INT PRIMARY KEY IDENTITY ( 1 , 1 ) , PModelID INT , Name VARCHAR ( 50 ) , ProductNumber VARCHAR ( 50 ) , SafetyStockLevel INT , ReorderPoint INT )

After the execution of this query, SQL Server stores its execution plan into plan cache.

SELECT P . Name FROM TestNewProduction P INNER JOIN Production . ProductModel PM ON P . PModelID = PM . ProductModelID WHERE P . Name LIKE 'A%'

Now, we will insert some rows into the TestNewProduction table.

INSERT INTO TestNewProduction ( Name , PModelID , ProductNumber , SafetyStockLevel , ReorderPoint )

SELECT TOP 100 Name , ProductModelID , ProductNumber , SafetyStockLevel , ReorderPoint FROM Production . Product

WHERE ProductModelID IS NOT NULL

As the last step, we will execute the sample query again. In this case, the query optimizer will decide to create new statistics for the Name column and recompile the query.

SELECT P . Name FROM TestNewProduction P INNER JOIN Production . ProductModel PM ON P . PModelID = PM . ProductModelID WHERE P . Name LIKE 'A%'

The extended event captures the recompile event of the query optimizer and it shows the recompile reason.

SQL Server statistics and query performance

On the other hand, SQL Profiler shows all events more clearly and it can provide more data for the query performance metrics.

Use SQL Profiller to track SQL query recompilations

After execution of the query the following steps are performed:

Query optimizer recompile the query due to the statistics changed reason
A new statistic creates for the Name column because this column is used in the where condition
A new statistic creates for the PModelID column because this column is used in the join statement
Finally, the query is completed

As we can see in this scenario, resolving the query performance issues requires understanding the behavioral effects of the query optimizer.

Another point about statistics is that data modifications cause statistics to become stale. SQL Server Statistics stores the data distributions in the histograms but after the data modifications, histogram data will become out of date. Manually or automaticly updating the statistics causes query recompilations. For example, let’s add 500 new rows to the TestNewProduction table and understand how this scenario happens.