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

8 thoughts on “Database Compatibility Level and Query Store

  1. Hi Erin, I’m not having much luck with this. The query uses the Columnstore index by default unless I use a query hint. And after changing to Cardinality 150 there is no entry in the query store tables for another version of the query with that compatibility level – still just a single entry for 110. What might the problem be?

    Regards,

    Gordon.

    1. Gordon-

      If the plan is the same with compat level 150 as it is with 110, and the 110 plan was captured first, then even after you change the compat level to 150 for the database, it does NOT show up as a new plan in Query Store.

      If you look at the runtime stats, you should see that the existing plan is still being used.

      Erin

  2. Hi Erin,

    I am running SQL 2019 and using the Query Store to analyze query performance for db-comp levels 110 and 150.
    I noticed that for some records in [sys].[query_store_plan] the [compatibility_level] value is inconsistent with the [CardinalityEstimationModelVersion] value in the plan XML.

    For example:
    SELECT [plan_id], [compatibility_level], [query_plan]
    FROM [sys].[query_store_plan]
    WHERE [plan_id] IN (8777, 7283747)

    Output:
    ——–

    plan_id: 8777
    compatibility_level: 150
    CardinalityEstimationModelVersion: 70

    plan_id: 7283747
    compatibility_level: 110
    CardinalityEstimationModelVersion: 150

    How is that possible? This looks messed up.

    Thanks,
    Marios

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.