Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Cleanup SQL Server Query Store Data After Database Restore


By:   |   Last Updated: 2019-01-08   |   Comments (2)   |   Related Tips: More > Query Store

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:

query store queries

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:

query store size

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
purge query store results

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
query store size and status

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:

ssms query store

Then just right click and select Properties:

ssms query store

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

ssms query store

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


Last Updated: 2019-01-08


get scripts

next tip button



About the author
MSSQLTips author Carlos Robles Carlos Robles is Data Platform MVP and multi-platform DBA with +10 years of experience in database technologies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 10, 2019 - 6:42:21 PM - Carlos Robles Back To Top

Hi Dejan,

It depends, there are cases where a fresh development effort it's in course and all those forced plans are no longer required.

The tip is oriented to performance data collected by Query Store, the wait stats are a clear example. The wait stats that we are going to see in a fully dedicated system are not going to be the same as a shared environment where the performance is not the concern.

Looking at query performance stats from a different environment (with different specs) would be not be a good baseline, forced plans can be easily identified and forced again but I agree with you in some cases it will not work :)


Thursday, February 21, 2019 - 4:37:06 PM - Dejan Krakovic Back To Top

On the flip side, when a database is restored to the production environment, the customer may already have forced plans through Query Store, which were also part of the backup, and would like to keep those plans forced even in the new environment.

Applying the suggestion to clear Query Store after restore would break this scenario, and the plans would first have to be captured in Query Store (for which there is no guarantee that Query Optimizer would pick them) and then subsequently forced again.

So, clearing Query Store after restore should only be done if the customer is explicitly fine with losing all the previously captured Query Store data and any potential plans that were forced. Otherwise, this is not recommended as a best practice after each database restore.


Learn more about SQL Server tools