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

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.

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:

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

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