---

In SQL Server, the query optimizer uses statistics to create query plans that improve query performance. A common question among database administrators and developers is whether updating statistics leads to plan recompilation, especially when there have been no changes to the table data. This article dives into this scenario within SQL Server 2022, providing practical examples to illustrate the behavior.

Background

SQL Server uses statistics to estimate the row count and data distribution of tables and indexes. These estimates are crucial for the query optimizer when it decides on the most efficient way to execute a query. When statistics are outdated, SQL Server might generate suboptimal query plans. Thus, keeping statistics up to date is essential for maintaining query performance.

However, frequent updates to statistics can also have a downside. If every statistics update triggered a plan recompilation, it could lead to unnecessary overhead, especially in environments where data changes are minimal or where statistics are updated as a precautionary measure.

Experiment Setup

To explore whether a statistics update triggers a plan recompilation in SQL Server 2022, let’s set up a simple experiment. We’ll create a table, add some data, and then observe the behavior of query execution plans before and after updating statistics without modifying the table data.

-- Create a sample table
CREATE TABLE dbo.SampleData (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DataValue INT
);

-- Insert sample data
INSERT INTO dbo.SampleData (DataValue)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

-- Create an index on DataValue
CREATE INDEX idx_DataValue ON dbo.SampleData(DataValue);

Initial Query Plan

First, let’s execute a query to generate and cache an execution plan:

-- Query to generate and cache an execution plan
SELECT * FROM dbo.SampleData WHERE DataValue > 5;

Update Statistics without Modifying Data

Next, we update the statistics of the dbo.SampleData table without making any changes to the data:

-- Update statistics without changing the data
UPDATE STATISTICS dbo.SampleData WITH FULLSCAN;

Observing Plan Recompilation

To determine if the statistics update caused a plan recompilation, we can use the sys.dm_exec_cached_plans and sys.dm_exec_query_stats dynamic management views (DMVs) to check if the execution plan for our query has been recompiled:

-- Check for plan recompilation
SELECT deqs.execution_count, deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE deqp.objectid = OBJECT_ID('dbo.SampleData');

Results and Interpretation

If you observe that the execution_count for the query remains unchanged and the query plan is the same as before updating the statistics, it indicates that the statistics update did not trigger a plan recompilation. However, if you notice a change in the execution_count or a new query plan, it suggests that SQL Server considered the statistics update significant enough to warrant a recompilation.

Conclusion

In SQL Server 2022, whether a statistics update triggers a plan recompilation depends on several factors, including the nature of the update and the specific query optimization settings. Through our experiment, we’ve demonstrated how to investigate this behavior using practical T-SQL examples.

It’s important for database administrators to understand the impact of statistics updates on query performance and plan recompilation. By carefully managing statistics and observing their effects on query execution plans, you can ensure optimal performance in your SQL Server environment.

Additional Tips

• Use the AUTO_UPDATE_STATISTICS database option to allow SQL Server to automatically update statistics when necessary.
• Monitor query performance and plan cache to understand the impact of statistics updates on your specific environment.
• Consider updating statistics manually in environments with large data volumes and infrequent changes to ensure statistics accurately reflect data distribution.

By maintaining a balance between up-to-date statistics and minimizing unnecessary plan recompilations, you can achieve efficient query performance in SQL Server 2022.

---

This article provides a foundational understanding and practical approach to investigating the impact of statistics updates on plan recompilation in SQL Server 2022. By following the examples and tips provided, you can effectively manage your SQL Server environment for optimal performance.