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?