Lesson Learned #445:Unlocking the Mystery of Recompilations in SQL Server: A Practical Lab

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:

1. 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 #367: Query Antipattern and Ring Buffer in Azure SQL Database - Microsoft Community Hub

Lesson Learned #266: Avoiding duplicate statistics - Microsoft Community Hub

Lesson Learned #69: Azure SQL Database – How are the statistics updated automatically by SQL engine - Microsoft Community Hub

Continue reading...