When working with SQL Server, it is important to understand the concept of query recompilations and how they can affect the performance of your SQL queries. In this article, we will explore the conditions under which the query optimizer decides to recompile queries and the impact it has on query performance.

The Query Processing Phases

When a query is submitted to SQL Server, it goes through three essential phases of query processing:

1. Query Parsing: In this phase, the query syntax is checked for correctness and a parse tree is generated.
2. Query Binding (Algebrizer): This phase validates the existence of columns, tables, and other objects in the database, as well as checks the user’s permissions on these objects.
3. Query Optimization: This is the most complex and performance-related phase of query processing. The query optimizer evaluates different query plan candidates and selects the plan with the lowest cost. The selected query plan is then stored in the plan cache for future executions of the same query.

Reasons for Query Recompilations

There are several reasons that can cause SQL Server to recompile queries stored in the plan cache:

• Schema changes
• Statistics changes
• Deferred compile
• Set option change
• Temp table changes
• Remote rowset changes
• 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

To monitor query recompilations, you can use SQL Server Extended Events or SQL Profiler. SQL Server Extended Events allow you to capture and report when a statement-level recompilation occurs. SQL Profiler, although deprecated, can also be used to monitor recompilations by selecting the SQL: StmtRecompile event class.

Example: Schema Changes and Recompilations

Let’s consider a scenario where we need to change the data type of a column in a table. This schema change will cause query recompilations. For example:

ALTER TABLE Production.ProductDescription
ALTER COLUMN Description nvarchar(600);

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;

In this case, the query will be recompiled due to the column data type change operation.

Conclusion

Understanding query recompilations and their impact on query performance is crucial for optimizing SQL Server databases. By monitoring and analyzing the reasons for recompilations, you can identify and resolve performance issues effectively. Whether it’s schema changes, statistics changes, or set option modifications, being aware of the factors that trigger recompilations will help you optimize your SQL queries and improve overall database performance.