Cleanup SQL Server Query Store Data After Database Restore

By:Carlos Robles |   Last Updated: 2019-01-08   |  |   Related Tips:More > Query Store

Problem

SQL Server Query Store data persists on disk even afterbackup andrestore operations, let's imagine a production database is restored in a QA environment for UAT because testers want to use the latest data from production.  There are circumstances, where the Query Store data from a different environment is not required because servers have different specifications or just because the UAT effort is aimed to test code changes introduced as a new development project.  Query Store data from a different environment could be an inconvenience in this situation, leading to false positive results or confusion when analyzing query performance data.

Solution

Query Store data could be crucial to determine a performance issue of a database after changes are introduced, having data from a different system with different specifications will lead to inaccurate results.  One way to solve this situation is to clear out all the historical Query Store data, that was restored as part of database restore process .

Analyze SQL Server Query Store with T-SQL

First thing, let's find out how much data, for this I will run the following query in SQL Server Management Studio (SSMS). The query will do a count of the number of queries already captured in Query Store's data of production (the backup we used as a source):

USE [OldQueryStoreData]
GO
-- Get number of queries stored in Query Store data
SELECT COUNT(*) NumQueries from sys.query_store_query
GO

In my case this is the result I have from the query above:

As noted, we have 3,749 queries captured already. Now it's time to find out the amount of storage allocated in megabytes (MB). I will run the following query in SSMS to get the Query Store status and actual size in megabytes:

USE [OldQueryStoreData]
GO
-- Get current size of Query Store data
SELECT current_storage_size_mb, actual_state_desc
FROM sys.database_query_store_options
GO

In my case this is the result I have from the query above:

The results are pretty clear, Query Store using 48 MB's of data from the PRIMARY file group, as you may know, the Query Store stores all the query performance information only in this filegroup.

With the help of these two simple queries, I was able to confirm that my restored version of the production database still has old Query Store data including query text, query execution plans and runtime statistics from a completely different server that is very likely to have different specifications (CPU, Memory, Disks) and configuration.

Cleanup SQL Server Query Store

Let's proceed to clean up this old Query Store data using T-SQL, the following query will purge all the query performance data using the "ALTER DATABASE" option combined with "SET QUERY STORE CLEAR" connected to the "MASTER"; it works the same as any other database configuration level change.

USE [MASTER]
GO
-- Cleaning up old Query Store data
ALTER DATABASE [OldQueryStoreData] SET QUERY STORE CLEAR
GO

The Messages window from SSMS confirms the change was successfully completed, now let's run a new query to perform the same verification I ran before.

The following query will return the actual space used by Query Store after the purge and will return the current status which should be "READ-WRITE" because I didn't modify the Query Store operation mode:

USE [OldQueryStoreData]
GO
-- Checking size and status of Query Store
SELECT current_storage_size_mb, actual_state_desc
FROM sys.database_query_store_options
GO

The results grid from SSMS confirms the size of the Query Store data is now 1 MB, if you recall I did the same verification after the restore and the space used was around 48 MB.

Analyze SQL Server Query Store with Management Studio

If you don't like to use T-SQL to check things and are comfortable with SQL Server Management Studio , there is also an option to check the Query Store status and current space utilization.

Open SSMS, expand the Databases node and look for the database which has Query Store enable:

Then just right click and select Properties:

From the Properties window, select "Query Store" in the left panel (last option):

As noted, we can see how much space is used by Query Store using SSMS in shown in the above image, also there is a button called " Purge Query Data ". The problem with this GUI approach is that things have to be done manually.

Let's imagine you have to restore a production database in a QA environment on weekly basis, are you willing to spend time clicking on this button after a restore? Probably not. A better approach is to have anautomated job with the T-SQL command I used in my previous example to purge the data:

USE [MASTER]
GO
-- Cleaning up old Query Store data
ALTER DATABASE [MyDB] SET QUERY STORE CLEAR
GO

With the help of this simple tip you can save yourself some time with a situation where your testing has the wrong query performance results and make sure the Query Store starts tracking query performance information accurately from the start.

Next Steps

• Read more about the basics of Query Store from this tip
• Read more about Automatic Query Tuning from this tip
• Read moreSQL Server 2017 tips

Last Updated: 2019-01-08

About the author