Problem
SQL Server Query Store data persists on disk even after backup and restore 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 an automated 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 more SQL Server 2017 tips
