One of the great features of the Query Store is that it stores query execution plans and runtime performance on-disk and makes it easily accessible through its DMVs and built-in reporting.

Using the information inside the Query Store can make database migrations to higher SQL Server versions far easier, since you can compare query performance before, and after, the upgrade of SQL Server or change of compatibility level. In fact, some of Microsoft’s usage scenarios are addressing the monitoring of query performance after implementing changes to the database, SQL Server Instance or even hardware.

The one thing that has been bothering me in these usage scenarios is the fact that they always focus one the same database. For instance, the scenario regarding measuring the impact of the compatibility mode advises to enable the Query Store on the database with the old compatibility level, measure for a while, change compatibility mode and then look at query performance. Even though we can quickly take corrective actions when queries start behaving strange after the change of compatibility level (for instance plan forcing), we are still looking at the situation after changing the compatibility mode.

So for a while now I was thinking: “How can we leverage the power of the Query Store without having to measure the impact of a change after it occurred?” Well….I think I am getting pretty close to the answer of that question!

Query Store Replay script

So I ended up making a thing that is easy to use, only has minimal-to-no impact on performance and even has some extra functionality. I present to you: Query Store Replay.

In a nutshell: Query Store Replay is a free, open-source, Powershell based script that extracts queries through the Query Store of one database, and replays them to another database, on another server if you so wish.

The main advantage of using the Query Store Replay script is that you can extract and replay your (production) workload on another server before performing a change that can impact your query performance. For instance, say you wanted to test the impact of adding an index to your production database but you don’t want to do this directly on the production database itself. Using the Query Store Replay script you can export the last n hours of your production database queries and directly replay them against your development database where you already added the index. Or, you can change the compatibility level of your database on your development server to a higher version, and replay the production workload that was generated on a lower compatibility level. Using the Query Store Replay script you can directly detect abnormal query behavior and avoid having to force plans on production after you changed the compatibility level.

Ready for a little demonstration?

Query Store Replay demo

For the purpose of showing you one of the scenarios where the Query Store Replay script can help you easily compare query performance, I prepared the script below.

-- Switch to our source database
 USE [DatabaseA]
 GO

-- Clear Query Store
 ALTER DATABASE DatabaseA SET QUERY_STORE CLEAR;

-- Execute a query
 SELECT DISTINCT p.LastName, p.FirstName 
 FROM Person.Person AS p 
 JOIN HumanResources.Employee AS e
     ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN
     (SELECT Bonus
      FROM Sales.SalesPerson AS sp
      WHERE e.BusinessEntityID = sp.BusinessEntityID);

-- Switch to target database
 USE [DatabaseB]
 GO

-- Clear Query Store
 ALTER DATABASE DatabaseB SET QUERY_STORE CLEAR;

On my test machine I have a single SQL Server 2016 Instance with two databases: DatabaseA and DatabaseB. Both databases are a copy of the AdventureWorks database. Both databases have the Query Store feature enabled. DatabaseA has a compatibility mode of 110 (SQL Server 2012) and DatabaseB has a compatibility mode of 130 (SQL Server 2016).

So what the script above does: First it purges the Query Store of DatabaseA. This makes it easier for us to track the query in the Query Store reports for this demo. Secondly, it executes a query against DatabaseA. Then we switch to DatabaseB and purge the Query Store there as well.

Now the Query Store Replay script comes into play. On my machine I executed the script using the parameters shown below:

.\QueryStoreReplay.ps1 -SourceServer localhost -SourceDatabase DatabaseA -TargetServer localhost -TargetDatabase DatabaseB -TimeWindow 1

This tells the Query Store Replay script that we want to retrieve everything in the Query Store that was executed in the last hour against DatabaseA and replay it against DatabaseB.

Now if I look in one of the Query Store reports for DatabaseA I can see the query has been executed and has a duration of 0,22.

If I open the Query Store report for DatabaseB, I can see the same query was executed there as well and is performing slightly faster (0,17).

The reason appears to be a small change in the Index Seek order between the different compatibility modes of DatabaseA and DatabaseB which we can easily identify using the plan comparison tool.

Now this demo only shows a very small example of what the Query Store script can do. Normally you would run the script with many more queries than just one, but I think you get the idea of what’s possible using the scripts.

So how does it work?

I wrote a detailed workflow description of the Query Store Replay script over at its GitHub page, but as a summary:

• Query Store Replay script extracts all the execution plans that are stored in the Query Store of the source database and stores them as .sqlplan files on-disk.
• The Query Store Replay script then extract the query statements from the execution plans as well as any parameters and parameter values.
• From the statements and parameters a .sql file is build. The .sql file includes declaring and setting parameters if they were using during the original query execution.
• The Query Store Replay script replays all the .sql files against the target database.

The last step is optional. A parameter called “ExportOnly” is available to skip the replay and only extract the execution plans and query statements.

How can I get this?

Easy! Head over to the Query Store Replay GitHub page here: https://github.com/Evdlaar/QueryStoreReplay. From there you can directly download the “QueryStoreReplay.ps1” file and start playing with it! The only prerequisites:

• Powershell
• SQL Server Management Objects (SMO) (they are installed when you install SSMS)
• A database with the Query Store enabled that can act as a source database
• SQL Server 2016 or higher

On the GitHub wiki page are some examples you can try yourself and a description of the script in more detail.

The last thing I would like to ask of you: if you run into any bugs or issues, let me know! I am constantly improving this script and your feedback is very valuable! Giving feedback is easy, you can use the GitHub page to create an “issue”, send me a tweet on Twitter (@evdlaar) or add a comment to this article.

Enjoy!