Queries with OPTION (RECOMPILE) and Query Store

 

Last week in our IEPTO2 class I was asked about queries with OPTION (RECOMPILE) and Query Store. Specifically: Do queries that have the OPTION (RECOMPILE) hint go into Query Store, AND do queries in a stored procedure created with the RECOMPILE option go into Query Store? I knew the answer to the first question, and was pretty sure I know the answer to the second one, but I wanted to test to verify. Let’s take a look.

TL;DR In case you’re too busy to keep reading, the answer is yes to both.

Setup

We are using the WideWorldImporters database, which you can download from Github.  I’m running the latest CU for SQL Server 2017, but this is applicable for any version of Query Store (SQL Server 2016 and higher) and Azure SQL Database.  The code below will enable Query Store, set QUERY_CAPTURE_MODE to ALL (to understand the different various and what’s recommended for production, check out my Query Store Settings post), and then clear out anything that’s in Query Store.  I don’t typically recommend that you clear out Query Store, but we’re restoring a demo database, and this is a demo, so I want to make sure we start fresh.  Lastly, we’ll create a stored procedure to use for testing that is created with RECOMPILE and then completely free procedure cache.  Note that adding the RECOMPILE option to a stored procedure is not something I recommend – it means that the entire stored procedure will recompile every time it is executed.  I also don’t recommend freeing procedure cache in production – this is just for demo purposes.


USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR;
GO

DROP PROCEDURE IF EXISTS Sales.usp_GetOrderInfo 
GO

CREATE PROCEDURE Sales.usp_GetOrderInfo
(@OrderID INT)
WITH RECOMPILE
AS
BEGIN
SELECT 
o.OrderID,
o.CustomerID,
o.OrderDate,
ol.Quantity,
ol.UnitPrice
FROM Sales.Orders o
JOIN Sales.OrderLines ol
ON o.OrderID = ol.OrderID
WHERE o.OrderID = @OrderID;
END
GO

DBCC FREEPROCCACHE;
GO

Testing

First, execute an ad hoc query, one that is not part of a stored procedure, that has the OPTION (RECOMIPLE) hint:


SELECT
i.InvoiceID,
i.CustomerID,
i.InvoiceDate,
il.Quantity,
il.UnitPrice
FROM Sales.Invoices i
JOIN Sales.InvoiceLines il
ON i.InvoiceID = il.InvoiceID
WHERE i.InvoiceID = 54983
OPTION (RECOMPILE);
GO 10

If we check the plan cache, you’ll notice that there is no evidence that this query has executed:


SELECT 
qs.execution_count,
st.text, 
qs.creation_time
FROM sys.dm_exec_query_stats AS [qs] 
CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) [st]
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) [p]
WHERE [st].[text][/text] LIKE '%Sales.Invoices%';
GO

Plan cache after ad hoc query with OPTION (RECOMPILE)

 

But if we look in Query Store we do see the query:


SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
[rs].[count_executions],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
[qst].[query_sql_text],
TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML],
[qsp].[query_plan] 
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qst].[query_sql_text] LIKE '%Sales.Invoices%';
GO

Query Store capturing ad hoc query with OPTION (RECOMPILE)

 

If we expand the query_sql_text column (middle text removed for space reasons) you can see that the text includes OPTION (RECOMPILE).  This is pretty cool.

Query with OPTION (RECOMIPLE) stored in Query Store

 

Now let’s execute the stored procedure we created with RECOMPILE and then check the plan cache:


EXEC Sales.usp_GetOrderInfo 57302;
GO 10

SELECT 
qs.execution_count,
st.text, 
qs.creation_time
FROM sys.dm_exec_query_stats AS [qs] 
CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) [st]
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) [p]
WHERE [st].[text][/text] LIKE '%Sales.Orders%';
GO

Plan cache after stored procedure created with OPTION (RECOMPILE)

 

And when we check Query Store we do see the query:


SELECT
[qsq].[query_id], 
[qsp].[plan_id],
[qsq].[object_id],
OBJECT_NAME([qsq].[object_id]) AS ObjectName,
[rs].[count_executions],
[rs].[last_execution_time],
[rs].[avg_duration],
[rs].[avg_logical_io_reads],
[qst].[query_sql_text],
TRY_CONVERT(XML, [qsp].[query_plan]) AS [QueryPlan_XML],
[qsp].[query_plan] /* nvarchar(max) */
FROM [sys].[query_store_query] [qsq] 
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp] 
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs] 
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE OBJECT_NAME([qsq].[object_id]) = 'usp_GetOrderInfo';
GO

Query from stored procedure created with OPTION (RECOMIPLE) stored in Query Store

 

Summary

Regardless of where OPTION (RECOMPILE) is used – at the statement level for an ad hoc query or a statement within a stored procedure – and when the RECOMPILE option is used at the procedure level during creation or execution – the query text, the plan, and the execution statistics still get captured within Query Store.

3 thoughts on “Queries with OPTION (RECOMPILE) and Query Store

  1. This is one of the reasons I love the Query Store so much. Now we just need to have it writable on secondary replicas!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.