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
In a basic manner, when we submit a query to SQL Server it performs 3 essential phases:
- 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.
|
1 2 3 4 5 6 |
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 |
- 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.
|
1 2 3 4 5 6 7 8 |
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); GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT p.[ProductID] ,p.[Name] ,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.
At the same time, the SQL Profiler shows a report after the execution of the query and it includes query recompilation reason.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
ALTER TABLE Production.ProductDescription ALTER COLUMN Description nvarchar(600); GO SELECT p.[ProductID] ,p.[Name] ,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.
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.
|
1 |
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.
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.
|
1 2 3 4 |
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.
|
1 2 3 4 |
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.
|
1 2 3 |
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.
|
1 2 3 4 |
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.
On the other hand, SQL Profiler shows all events more clearly and it can provide more data for the query performance metrics.
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.
|
1 2 3 4 |
INSERT INTO TestNewProduction ( Name,PModelID,ProductNumber,SafetyStockLevel,ReorderPoint) SELECT TOP 100 Name,ProductModelID,ProductNumber,SafetyStockLevel,ReorderPoint FROM Production.Product WHERE ProductModelID IS NOT NULL GO 5 |
To capture the auto-update statistics event, we need to make some modifications in our extended event therefore we can easily observe what is happening behind the scene. The auto_stats event can capture when automatic updating of column statistics events has occurred.
|
1 2 3 4 5 6 7 8 9 |
DROP EVENT SESSION [CaptureQuery_Recompilations] ON SERVER GO CREATE EVENT SESSION [CaptureQuery_Recompilations] ON SERVER ADD EVENT sqlserver.auto_stats, 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) GO |
After recreating the extended event, we will execute our sample query again and check out the captured events.
|
1 2 3 4 |
SELECT P.Name FROM TestNewProduction P INNER JOIN Production.ProductModel PM ON P.PModelID= PM.ProductModelID WHERE P.Name LIKE 'A%' |
As seen in the image, the extended event has captured a query recompilation event as we expect.
The auto_stats indicates statistics-related events. When we click the first one we can see all details about this event.
SET options and query recompilations
The SET options enables us to change the session-level behavior of the SQL Server so that we can modify various options with these options. Another important point about the set option is may cause query recompilation. Such as, SQL Server Management Studio (SSMS) and application connection options can be different and it causes different execution plans. For example, if we change the ARITHABORT and NUMERIC_ROUNDABORT connection options, the query optimizer will decide to recompile the query.
|
1 2 3 4 5 6 |
SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SELECT P.Name FROM TestNewProduction P INNER JOIN Production.ProductModel PM ON P.PModelID= PM.ProductModelID WHERE P.Name LIKE 'A%' |
The extended event shows us the recompile reason and it is clear that set option change can cause query recompilations.
Conclusion
In this article, we have explored which reasons can cause query recompilations and their interaction with query performance. At the same time, we have learned how we can monitor the query recompilation events with different two methods.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023