Query Store in SQL Server 2019 (CTP 3.0)

Friends, CTP 3.0 dropped today, and it includes some changes for Query Store in SQL Server 2019!  I am so excited!!  I’ve downloaded it and have WideWorldImporters installed and have a lot of testing planned, but if you’re impatient, guess what?  The documentation is already updated!  If you check out the ALTER DATABASE SET page you will see that Query Store now has a new option for QUERY_CAPTURE_MODE: CUSTOM.  For those of you with ad hoc workloads, this will help.

Remember that with QUERY_CAPTURE_MODE, the default for SQL Server 2016 and SQL Server 2017 is ALL, and for Azure SQL Database it is AUTO.  I previously recommended that everyone use AUTO, as this means you do not capture queries that are insignificant (thresholds determined by Microsoft and not publicly documented, but it’s at least 3 executions and some amount of of CPU).

The CUSTOM option allows you determine what queries are captured based on:

  • EXECUTION_COUNT
  • TOTAL_COMPILE_CPU_TIME_MS
  • TOTAL_EXECUTION_CPU_TIME_MS

These three options operate in an OR manner.  For example, if I set the values as follows:

  • EXECUTION_COUNT = 100
  • TOTAL_COMPILE_CPU_TIME_MS = 60000
  • TOTAL_EXECUTION_CPU_TIME_MS = 60000

A query would only be captured in Query Store if it executed at least 100 times, if the total compile time for the query was at least 60 seconds, or if the total execution time (for all executions) was at least 60 seconds.

You also control the interval across which those options are tracked via the STALE_CAPTURE_POLICY_THRESHOLD setting.  This can be as low as 1 hour and as high as 7 days.  If I set it to 1 hour, then if the query did not exceed any of the thresholds within the 1 hour time frame, it wouldn’t be captured.  Ultimately, you can control what Query Store captures based on executions, compile time, or duration, which will help keep the size of the Query Store data much more manageable.

In addition to these new options, the default values for two settings changed in SQL Server 2019.  Specifically:

  • MAX_STORAGE_SIZE_MB increased from 100MB to 1000MB
  • QUERY_STORE_CAPTURE_MODE changed from ALL to AUTO

I have a fair bit of testing I want to do to see these settings in action, but I wanted to give you all a heads up if you’re looking to upgrade to 2019 when it’s released, or if you have had a less-than-positive experience with Query Store and wonder if it’s something you’ll ever be able to use.  The answer is yes, and this is a step in the direction to make it happen.  More to come!

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]. 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]. 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.

Important Query Store Fixes – January 2019

The latest CUs for SQL Server 2016 and 2017 contain some important Query Store fixes that I thought worth mentioning for those of you on either version or those of you looking to upgrade.  As of this writing, the current CU for SQL Server 2016 SP2 is CU5, and for SQL Server 2017 it is CU13.  Many times we see fixes that make it into a SQL Server 2017 CU ported back to a SQL Server 2016 build.  Interestingly enough, there are some Query Store fixes in 2016 CUs that are not in 2017 CUs.  I don’t know if that’s because the issues do not exist in 2017, or if it’s just that they have been fixed yet in 2017.  I’m planning to update this post if the fixes are added down the read.  So here we go, in descending CU order…

SQL Server 2017 CU13FIX: A dump file may be generated when you run the DML internal plan on Query Store enabled database in SQL Server 2017

This will occur for anyone using Automatic Plan Correction (which means you will be on Enterprise Edition), as noted by having FORCE_LAST_GOOD_PLAN enabled.  You can read more about Automatic Plan Correction here (I’m a  fan, it’s pretty cool and very helpful for those with small DBA teams and lots of databases, or those DBAs who just have too much on their plate and are constantly putting out fires).

SQL Server 2017 CU11 and SQL Server 2016 SP2 CU5FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017

I’m aware of multiple companies that have run into this issue and I’m glad to see that the fix was ported back to 2016.  I highly recommend getting up to this CU if you can, as the resolution requires a restart (I’ve heard that killing the session_id also works but I haven’t seen that).  Also note that the command referenced in the KB article is ALTER DATABASE <databasename> SET QUERY_STORE_CLEAR, but I have also seen this when trying to change one of the Query Store options (e.g. changing size or retention days).  I am pretty sure it’s any ALTER DATABASE statement that changes Query Store configuration.

SQL Server 2016 SP2 CU5FIX: Query Store enabled database takes long time on startup after you apply cumulative update for a SQL Server version

Note that this is applicable to systems with Availability Groups, and this is a fix that is not listed for any 2017 CU…I’ll keep watching to see if it shows up.

SQL Server 2016 SP2 CU4FIX: Access violation when SQL Server 2016 tries to start Query Store Manager during startup

This is also a fix that is not listed for any 2017 CU…

SQL Server 2017 CU5FIX: Access violation occurs when Query Store collects runtime statistics in SQL Server 2017

I don’t see this fix in any SQL Server 2016 CU, but I’ll keep watching.

SQL Server 2016 SP2 CU2FIX: Slow performance of SQL Server 2016 when Query Store is enabled

There were many performance-related improvements for Query Store in SQL Server 2017, and huge props to the SQL Server team for getting these back-ported to 2016.  At a bare minimum, this is the build you should be running, but I would rather see you on CU5 at this point.

Note: If you installed CU2 for SQL Server 2017 at any point, please read: Query Store Fix in SQL Server 2017 for an explanation of what you need to do when you apply a higher CU and why.

You may look at this list of fixes and be concerned.  Don’t be.  These fixes are a good thing!  They tell me that more and more people are using Query Store (which is great) and while they have run into issues, I believe it’s because these are bigger systems (see the access violation fix which is for systems with 256 logical cores) or they are interesting/edge-case workloads, both of which probably don’t exist in Azure, and may be why these issues haven’t been seen previously.  I remain, as always, a big proponent of Query Store.  If you’re seeing something interesting feel free to comment here or drop me an email.  I’ve had some folks do that and I was able to confirm they were seeing a bug and let them know that it would be fixed soon.  I’ve had one or two other cases where it’s an issue I haven’t seen and I recommended contacting Product Support.  Either way, if you’re unsure about Query Store I have lots of resources to get you started, to get it configured properly, and I answer the question “what about performance?!”.  I hope this helps!