Some days ago, I worked on a service request that our customer saw a CPU speaks time to time. This issue was cause a recompilation of a queries and I would like to share my lesson learned here.
Imagine you have an application that frequently queries the database with changing parameter values. Sometimes, due to schema changes, statistics updates, or other reasons, the execution plan that SQL Server has cached for that query might not be optimal for a specific parameter value. Consequently, SQL Server might opt to recompile the query.
Steps:
- Initial Setup:
- Have a SQL Server instance and SSMS ready to use.
- Create a test database and a table:
CREATE DATABASE RecompileLab;
GO
USE RecompileLab;
GO
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(255),
Price DECIMAL(10, 2),
Category NVARCHAR(50)
);
Populate the Table:
Use a loop to insert a lot of records into the table:
begin transaction
DECLARE @i INT = 1;
WHILE @i <= 100000
BEGIN
INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (@i, 'Product ' + CAST(@i AS NVARCHAR), RAND() * 100, CASE WHEN @i % 2 = 0 THEN 'A' ELSE 'B' END);
SET @i = @i + 1;
END
commit transaction
Create a Stored Procedure:
CREATE PROCEDURE FetchProductByCategory @category NVARCHAR(50)
AS
BEGIN
SELECT * FROM Products WHERE Category = @category;
END
GO
Execute the Stored Procedure:
First, execute the stored procedure with one parameter value:
EXEC FetchProductByCategory @category = 'A';
This will generate and cache an execution plan based on that category.
Simulate a Change That Might Cause Recompilation:
A common reason for recompilation is statistics change. Let's update a large number of records, which should change the statistics and potentially cause a recompilation on the next run:
UPDATE Products SET Category = 'C' WHERE ProductID < 50000;
Execute the Stored Procedure Again:
EXEC FetchProductByCategory @category = 'B';
Given that we updated a significant number of records in category 'A', it's likely the statistics have changed enough to cause a recompilation of the query inside the stored procedure when run with a different parameter.
Check for Recompilation:
Use the original query you provided to check for recompilations:
SELECT TOP 25 sql_text.TEXT,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.Dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num > 1
ORDER BY plan_generation_num DESC;
-- If you only the products table query
SELECT TOP 250 sql_text.TEXT,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.Dm_exec_sql_text(sql_handle) AS sql_text
WHERE plan_generation_num > 1
and sql_text.TEXT like '%from products%'
ORDER BY plan_generation_num DESC;
If everything goes as planned, you should see the text of your query from FetchProductByCategory in the results, indicating that it has been recompiled.
This hands-on scenario helps you witness how changes in data can lead to recompilations, especially when the underlying statistics that the database engine uses to generate query plans change significantly.
Additional Information
Lesson Learned #324: Query Recompilation in Azure SQL - Microsoft Community Hub
Lesson Learned #266: Avoiding duplicate statistics - Microsoft Community Hub
Microsoft
