Query Store and the Plan Cache Flushing

I’ve had two comments recently on my blog about Query Store causing the plan cache to be flushed. There was a known issue related to the plan cache flushing after Query Store was enabled, but this was fixed in CU2 for SQL Server 2016 SP1. So I did some testing and here is what I think is causing the confusion:

When you enable Query Store, which is done with an ALTER DATABASE SET statement, the plan cache for the database is flushed.

Now, before anyone writes up a UserVoice item, understand that there are several ALTER DATABASE SET commands that cause the plan cache for a database to be flushed. For example, taking a database OFFLINE causes the database plan cache to be flushed. That one seems intuitive, right?  So why is the plan cache cleared when you enable Query Store, or change one of the settings?  To ensure that new Query Store data is not lost.  This relates to the internals of how Query Store works, which aren’t essential to dig into, the point is that this behavior is known by Microsoft and expected.

If you review the ALTER DATABASE SET documentation, and specifically review the Query Store options, you won’t find any mention of the database plan cache clearing.  But you can test it to see that it occurs…

Testing

First, disable Query Store for the WideWorldImporters database, and then free the plan cache:

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = OFF;
GO

DBCC FREEPROCCACHE;
GO

USE [WideWorldImporters];
GO

SELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity
FROM Sales.Orders o
JOIN Sales.OrderLines ol
	ON o.OrderID = ol.OrderID
WHERE o.CustomerID = 972;
GO
SELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity
FROM Sales.Orders o
JOIN Sales.OrderLines ol
	ON o.OrderID = ol.OrderID
WHERE o.CustomerID = 123;
GO

Now query the plan cache to confirm those plans are in cache:


SELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE t.text LIKE '%Sales%';
GO
SQL Server's plan cache after initial query execution
SQL Server’s plan cache after initial query execution

Great, they’re there. Now enable Query Store, then check the plan cache again.


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

SELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE t.text LIKE '%Sales%';
GO
SQL Server's plan cache after enabling Query Store
SQL Server’s plan cache after enabling Query Store (plans have been cleared)

 

 

 

 

 

 

 

The plan cache for the database has been cleared. Note that this only clears the plan cache for that database – plans for other databases still remain in cache. Run a few more queries to add some plans back to the plan cache, and confirm they’re there.


USE [WideWorldImporters];
GO

SELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity
FROM Sales.Orders o
JOIN Sales.OrderLines ol
	ON o.OrderID = ol.OrderID
WHERE o.CustomerID = 972;
GO
SELECT o.OrderDate, o.ContactPersonID, ol.StockItemID, ol.Quantity
FROM Sales.Orders o
JOIN Sales.OrderLines ol
	ON o.OrderID = ol.OrderID
WHERE o.CustomerID = 123;
GO

SELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE t.text LIKE '%Sales%';
GO
SQL Server's plan cache after enabling Query Store AND running some queries
SQL Server’s plan cache after enabling Query Store AND running some queries

This time, change one of the settings for Query Store. It’s already enabled, but perhaps we want to change the INTERVAL_LENGTH_MINUTES setting from the default of 60 minutes to 30 minutes.


USE [master]
GO
ALTER DATABASE [WideWorldImporters] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30)
GO

SELECT t.dbid, t.text, s.creation_time, s.execution_count, p.query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE t.text LIKE '%Sales%';
GO
SQL Server's plan cache after changing a Query Store setting (plans have been cleared)
SQL Server’s plan cache after changing a Query Store setting (plans have been cleared)

In checking the plan cache again, the ALTER DATABASE SET statement cleared the database’s cache.

Summary

As you can see, the database plan cache is cleared after you enable Query Store, or change any settings related to Query Store. This is the same behavior we see with other ALTER DATABASE SET commands (e.g. changing the recovery model).  Unfortunately, this is not documented, nor is anything written to the ERRORLOG.

Of note: I don’t expect that you are changing settings often (if you are, I’d like to understand that thought process, as once you find the right values for space and interval, I expect those settings to be static…and if you’re not sure where to start, feel free to check out my post discussing the different options). I also don’t expect that you are turning Query Store on and off throughout the day; that completely defeats the purpose of the feature. It should be enabled, and left enabled, all the time. You don’t know when a problem might occur, right?

11 thoughts on “Query Store and the Plan Cache Flushing

  1. Nice post. Tiny typo in the summary paragraph where you accidentally a word:

    “As you can see, the database plan cache is after you enable Query Store”

  2. Thanks for the new article Erin. Glad to know that the flush I’d seen was one of those rare occasions that it wasn’t “just me” 😉

  3. Thanks for the new article.

    I tried to change COMPATIBILTY LEVEL for the database in Availability group using:
    ALTER DATABASE “” SET COMPATIBILITY_LEVEL = 120
    I got the below error:

    Msg 1468,
    The operation cannot be performed on database ” ” because it is involved in a database mirroring session or an availability group

    any one help to fix it please

    1. I forgot to mention that I am usnig sql 2014 ALWAYS ON.

      please provide mw with the steps required to fix the above error.
      thanks

    2. I forgot to mention that I am usnig sql 2014 ALWAYS ON.

      please provide me with the steps required to fix the above error.
      thanks

    3. Make sure you are running the ALTER DATABASE statement on the read/write copy of the database (primary node). Based on the error, it looks like you’re trying to run it against a secondary.

  4. HI Erin,

    I have a plan forced through Query store on SQL 2019 CU11. I see that subsequent execution of same query ID is using a different plan ID which is identical to the forced plan ID. The sys.dm_exec_query_stats DMV does indicate that the query has a new cached plan as well at the moment.

    I also looked at sys.query_store_plan DMV to validate that there is “NONE” indicated in plan forcing failure reason and force failure count is also 0.

    Trying to validate if my forced plan is working or there is any other validation that I need to perform.

    1. Chetan-

      To be clear – the plan that is in the plan cache (and thus being used), has a DIFFERENT plan_id than the forced plan, but is identical to the forced plan in terms of shape, operators used, etc? If so, that’s a “morally equivalent” plan, and while the plan_id is not the same, to the engine it’s the same plan. Kendra Little wrote a great blog post about it: https://www.littlekendra.com/2018/03/12/what-is-a-morally-equivalent-execution-plan-and-why-is-it-good/

      Hope that helps,

      Erin

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.