Database Compatibility Level and Query Store

A question I’ve gotten a few times when teaching relates to database compatibility level and Query Store. I was talking to a client yesterday about post-upgrade plans and implementing Query Store, and the topic came again. They wanted to know what compatibility level the database needed in order to use Query Store.

The quick answer: it doesn’t matter.

Query Store is an engine feature that was introduced in SQL Server 2016 (and available in Azure SQL Database since Q4 of 2015) and continues to evolve with each release. It is a feature that has to be enabled at the database level, using ALTER DATABASE SET, and it is compatibility level agnostic.

However, Query Store does track both engine version and compatibility level at the plan level in sys.query_store_plan. This allows you to use Query Store data for comparing plan performance for a query before and after upgrades, both at the engine level and database compatibility level.

Query Store Demo Setup

I have a copy of WideWorldImporters restored on a SQL Server 2019 instance, and I’ll mention that it’s not the standard copy you get from GitHib, it’s one I’ve run the DataLoadSimulation.DailyProcessToCreateHistory stored procedure against to add more data and introduce some skew. If you recreate this against a copy of WideWorldImporters that you have, you may not see the same results.

The first step after restoring is to enable Query Store, and then clear out any old QS data. This is something I do only for demos; I don’t recommend it for a production database unless you’re ok with removing all Query Store data.

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON;
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
	OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 10
	);
GO

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR;
GO

Next, set the compatibility level to 110, and set the database to use the legacy Cardinality Estimator, just as if we had upgraded this database from SQL Server 2012 to SQL Server 2019.

USE [master];
GO
ALTER DATABASE [WideWorldImporters] 
	SET COMPATIBILITY_LEVEL = 110;
GO

USE [WideWorldImporters];
GO
ALTER DATABASE SCOPED CONFIGURATION 
	SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

Compatibility Level 120

We have a very simple query that we’ll execute 10 times against WideWorldImporters, and display the actual execution plan.

USE [WideWorldImporters];
GO

SELECT 
	[ol].[StockItemID], 
	[ol].[Description], 
	[ol].[UnitPrice],
	[o].[CustomerID], 
	[o].[SalespersonPersonID],
	[o].[OrderDate]
FROM [Sales].[OrderLines] [ol]
JOIN [Sales].[Orders] [o]
	ON [ol].[OrderID] = [o].[OrderID]
WHERE [ol].[UnitPrice] = 240.00
AND [o].[OrderDate] = '2016-04-16';
GO 10
Execution Plan with Compatibility Level=110

Execution Plan with Compatibility Level = 110

The plan has two nested loops, with a clustered index scan against the Orders table, and an index seek against the FK_Sales_OrdersLines_OrderID nonclustered index.  Within Query Store, the sys.query_store_plan table shows a compatibility_level of 110, and an engine_version of 15.0.1900.25.

Output from sys.query_store_plan

Output from sys.query_store_plan

Compatibility Level 150

Without making any changes to the Query Store configuration, change the compatibility level to 150. The Cardinality Estimator version stays set to Legacy.

USE [master];
GO
ALTER DATABASE [WideWorldImporters] 
	SET COMPATIBILITY_LEVEL = 150;
GO

Next, re-run the same query against Orders and OrderLines and note the plan change:

Execution Plan with Compatibility Level=150

Execution Plan with Compatibility Level = 150

We still have the clustered index scan against Order, but it feeds into a Hash Mash that uses a Columnstore index scan against OrderLines to probe the matching rows – it’s a different plan. We see this in Query Store as a different plan_id (4), with a compatibility_level of 150.

SELECT 
    [qst].[query_text_id],
	[qsq].[query_id],  
	[qst].[query_sql_text], 
	[rs].[count_executions],
	[qsp].[plan_id], 
	[qsp].[compatibility_level],
	[qsp].[engine_version]
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_text_id] = 1; 
GO
Output from sys.query_store_plan after change to compatibility level 150

Output from sys.query_store_plan after change to compatibility level 150

Leveraging Query Store

One of the many ways you can take advantage of the data in Query Store, beyond just identifying problematic queries, is to use it for A/B testing.  This entirely the purpose of the Query Tuning Assistant, which will compare plan performance you, as well as stabilize query performance.  If you are unable to run a representative workload against your database, or just want to investigate specific, critical queries, you have all the data at your disposal in Query Store.  You simply need to be running SQL Server 2016 or higher, or Azure SQL Database and have Query Store enabled with compatibility level set to any supported value (100 or higher).

Query Store Training: SQLBits 2020

I am thrilled to announce that I will be heading back to London in spring 2020 for a day of Query Store training.  The training day schedule for the SQLBits conference has been announced, and I will be presenting a full day pre-conference session on Query Store.  My workshop, Performance Tuning with Query Store in SQL Server and Azure, goes beyond setup and basics for Query Store, and dives into how you can use Query Store to find performance issues and mitigate them.  I got some great ideas from attendees at last month’s PASS Summit workshop, and I am already working on incorporating new demos and examples into what I will cover at SQLBits.

If you have any questions about content I’ll cover during the class, please send me an email or leave a comment below.  I know there are a ton of great options at the conference, and I want to make sure you understand what to expect from a day of training with me (hint: lots of scripts and real-world examples).  I am so excited to be going to back to SQLBits.  It is a fantastic conference and the people are fabulous.  I hope to see you there!

Encrypted Stored Procedures and Query Store

Very often I get interesting questions from the community, and most recently someone asked about encrypted stored procedures and Query Store.  Here’s the question (paraphrased):

I have some encrypted stored procedures in one of my vendor databases.  I have enabled Query Store, and I can see query_id values and runtime statistics, and when a query has multiple plans, I can see some of the execution plans, but not others.  If I force a plan for a query that’s part of an encrypted stored procedure, will it work?

There are two issues here:

  • Why are only some plans showing up in Query Store for a query that’s part of an encrypted stored procedure?
  • Can I force a plan for a query that’s part of an encrypted stored procedure?

When in doubt, test.

Setup

For testing I will use a copy of WideWorldImporters that’s been enlarged, so that there’s some variability in the data.  After it’s restored we will enable Query Store and clear out any old data.

USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
     FROM  DISK = N'C:\Backups\WideWorldImporters_Bits.bak'
     WITH  FILE = 1,
     STATS = 5;
GO

/*
     Enable Query Store with settings we want
     (just for testing, not for production)
*/
USE [master];
GO
ALTER DATABASE [WideWorldImporters]
     SET QUERY_STORE = ON;
GO

ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (
     OPERATION_MODE = READ_WRITE,
     CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
     DATA_FLUSH_INTERVAL_SECONDS = 60,
     INTERVAL_LENGTH_MINUTES = 30,
     MAX_STORAGE_SIZE_MB = 100,
     QUERY_CAPTURE_MODE = ALL,
     SIZE_BASED_CLEANUP_MODE = AUTO,
     MAX_PLANS_PER_QUERY = 200)
GO

/*
     Clear out any old data, just in case
     (not for production either!)
*/
ALTER DATABASE [WideWorldImporters]
     SET QUERY_STORE CLEAR;
GO

With the database restored, we will create our encrypted stored procedure:

USE [WideWorldImporters];
GO

DROP PROCEDURE IF EXISTS [Sales].[usp_CustomerTransactionInfo];
GO

CREATE PROCEDURE [Sales].[usp_CustomerTransactionInfo]
     @CustomerID INT
WITH ENCRYPTION
AS
     SELECT [CustomerID], SUM([AmountExcludingTax])
     FROM [Sales].[CustomerTransactions]
     WHERE [CustomerID] = @CustomerID
     GROUP BY [CustomerID];
GO

Testing

To create two different plans, we will execute the procedure twice, once with a unique value (1092), and once with a non-unique value (401), with a sp_recompile in between:

EXEC [Sales].[usp_CustomerTransactionInfo] 1092;
GO

sp_recompile 'Sales.usp_CustomerTransactionInfo';
GO

EXEC [Sales].[usp_CustomerTransactionInfo] 401;
GO

Note that if you enable the actual query plan option in Management Studio, no plan will appear.  You won’t find a plan in the plan cache (sys.dm_exec_query_plan), nor will you find one if you run Profiler or Extended Events.  But when you use encrypted stored procedures and Query Store, you can get the plan:

SELECT
     [qsq].[query_id],
     [qsp].[plan_id],
     [qsp].[is_forced_plan],
     [qsq].[object_id],
     [rs].[count_executions],
     DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())),
     [qsp].[last_execution_time]) AS [LocalLastExecutionTime],
     [qst].[query_sql_text],
     ConvertedPlan = TRY_CONVERT(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 [qsq].[object_id] = OBJECT_ID(N'Sales.usp_CustomerTransactionInfo')
ORDER BY [qsq].[query_id], [qsp].[plan_id];
GO
Query and Plan Info in Query Store

Query and Plan Info in Query Store

 

Both plans can be viewed, but the query text is not part of the plan, and it is not in Query Store.

Plan for query_id 1

Plan for query_id 1

 

Plan for query_id 2

Plan for query_id 2

We can still force a plan, though:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;
GO

How will we know if a forced plan is used when we run the query?

EXEC [Sales].[usp_CustomerTransactionInfo] 1050;
GO 5

EXEC [Sales].[usp_CustomerTransactionInfo] 401;
GO 5

Typically, I would run the query in SSMS and get the actual execution plan, and check the UsePlan parameter to see if it’s true.  But the plan won’t show up in SSMS, so we have to look in Query Store.  If we re-run the query above again, we see that the execution count has increased, and the last_execution_time has also changed.

Query Store Information After Plan is Forced

Query Store Information After Plan is Forced

Conclusion

Based on the data in Query Store, when you force a plan for a query that’s part of an encrypted stored procedure, it is used.  You will have to verify its use in Query Store, as other troubleshooting tools (plans, DMVs, XE) do not provide data to confirm.

As for why the execution plan for a query does or does not show up in Query Store, I’m not sure.  I couldn’t recreate the behavior where the plan did not appear in Query Store, but I’m hoping the person who sent in the question can help me figure out why.  Stay tuned!