Lesson Learned #324: Query Recompilation in Azure SQL

Today, I worked on a service request that our customer asked about frequent query recompilation due to an automatic statistics update.

The recompilation of queries could be good and bad. Sometimes, is better to consider a new compilation for a query instead of reusing the current execution plan, for example, if the data distribution in the table and the statistics have changed. Also, adding a new indexes, constraints or modifying the structure of the table could also produce a new recompilation. In SQL Server and Azure SQL the recompilations are at statement level.

Just wanted to add that using Query Data Store we have a force plan that we need to check - Optimized plan forcing with Query Store - SQL Server | Microsoft Learn

For example, in Azure SQL DB and Azure SQL Managed Instance we could identify the SQL statement that has been recompile using the following Extended Event at database level for Azure SQL DB or for server for Azure SQL Managed Instance.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!!!!'

CREATE DATABASE SCOPED CREDENTIAL [https://azureblobcontainer.blob.core.windows.net/xe-container] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2023-01-12&ss=b&srt=sco&sp=rwdlacx&se=2023-01-28T23:38:22Z&st=2023-09-29T14:38:22Z&spr=https&sig=24TJMAf1hOzE2DDVqw1QRs....'

CREATE EVENT SESSION [Recompile] ON database

ADD EVENT sqlserver.sql_statement_recompile( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.compile_plan_guid,sqlserver.context_info,sqlserver.database_name,sqlserver.query_hash,sqlserver.request_id,sqlserver.sql_text,sqlserver.username))

ADD TARGET package0.asynchronous_file_target(

SET filename='https://azureblobcontainer.blob.core.windows.net/xe-container/Recompile.xel')

ALTER EVENT SESSION [Recompile] ON DATABASE STATE=START

If you need to reproduce the query recompilation we could use the following TSQL:

select top 10 * from Fact.sale

select top 10 * from Fact.sale option(recompile)

Right now, we need to stop the extended event running the following query and review the file generated in our blob storage.

ALTER EVENT SESSION [Recompile] ON DATABASE STATE=STOP

Once we have downloaded the file we could see:

Enjoy!

Continue reading...