Home » Query Store » Replay your workload through the Query Store Replay script!
19
Dec 2016

Replay your workload through the Query Store Replay script!

Enrico
Query Store
43 comments

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.

19122016_qsr_blog_01

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).

19122016_qsr_blog_02

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.

19122016_qsr_blog_03

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: //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!

 

43 comments to “Replay your workload through the Query Store Replay script!”

You can leave a reply or Trackback this post.
  1. Brent Ozar says: December 21, 2016 at 00:35

    Neat idea! I haven’t tested this, but I’m just guessing – if I have a stored proc that builds dynamic SQL and executes it, I’m guessing it’s going to execute twice with this method – once inside the stored procedure, and again via the dynamic SQL call, which gets tracked separately in Query Store? Same thing with nested stored procedures, functions, etc?

    Reply
    • Enrico says: December 21, 2016 at 06:31

      Hi Brent,

      It should actually only executed the nested dynamic query once but I am not 100% certain. I haven’t tested that scenario yet, but I will today.

      Reply
    • Enrico says: December 21, 2016 at 08:27

      Hi Brent,

      Just checked, I can confirm it will only execute the dynamic query once.
      The script actually seperatly extracts the execution plan of the dynamic query inside the stored procedure and replays the statement inside that plan.

      This also means that it will not actually replay the execution of the SP (like EXEC sp_***) it will just extract the SQL that was executed by the SP.
      For instance, running “EXEC sp_GetProductsByID @ID=5” will result in a replay file that can contain “SELECT * FROM Products WHERE ID = 5”.

      Does this answer your question or did you have another scenario in mind?

      Reply
  2. Touhid says: August 8, 2018 at 06:23

    Hi Enrico
    I tried with the named instance but it didn’t work for me.
    Instead of localhost, I provided ‘ComputerName\InstanceName’ it throws an error – Could not find a part of the path.
    Any idea how can I fix the error.

    Reply
  3. MQ says: October 3, 2019 at 05:52

    Great idea. I’m upgrading SQL 2014 to 2017. If I get my SQL plan files stored to disk, will this operation complete. I know you don’t have a crystal ball however, are you aware of any road blocks (like SQL plan format changes)?

    Reply
  16. unsplash.com says: March 1, 2022 at 07:04

    I do not even know the way I ended up here, however I believed this submit used to be great.
    I do not understand who you might be however
    definitely you’re going to a well-known blogger in the event you aren’t already.
    Cheers!

    Reply
  18. www.pinterest.com says: March 1, 2022 at 07:50

    I do not know whether it’s just me or if perhaps everybody else experiencing issues with your
    blog. It seems like some of the written text within your posts are running off the screen. Can someone else please comment and let me know if this is happening to them too?
    This could be a problem with my browser because I’ve had this happen before.
    Kudos

    Reply
  19. ask.fm says: March 1, 2022 at 13:01

    First off I would like to say fantastic blog!
    I had a quick question in which I’d like to ask if you do
    not mind. I was interested to find out how you center yourself and clear your thoughts prior
    to writing. I have had a tough time clearing my thoughts in getting my ideas
    out. I do enjoy writing however it just seems like the
    first 10 to 15 minutes are generally lost simply just trying
    to figure out how to begin. Any ideas or tips?
    Thanks!

    Reply
  20. www.pcb.its.dot.gov says: March 1, 2022 at 17:32

    Hey there this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code with HTML.

    I’m starting a blog soon but have no coding experience so I wanted to get guidance from
    someone with experience. Any help would be enormously appreciated!

    Reply
  21. www.pinterest.com says: March 1, 2022 at 22:51

    Hey There. I discovered your blog using msn. This is a really well written article.
    I will make sure to bookmark it and return to read more of your helpful information. Thank you for the
    post. I will certainly return.

    Reply
  22. blip.fm says: March 2, 2022 at 00:15

    Please let me know if you’re looking for a writer for your blog.

    You have some really good articles and I think I would be a
    good asset. If you ever want to take some of the load off,
    I’d really like to write some content for your blog in exchange for a link back to mine.
    Please shoot me an email if interested. Thanks!

    Reply
  25. www.openstreetmap.org says: March 3, 2022 at 03:08

    naturally like your website however you have to take
    a look at the spelling on quite a few of your posts.
    Many of them are rife with spelling problems and I to find it very troublesome to
    inform the truth then again I’ll definitely come again again.

    Reply
  28. linktr.ee says: March 6, 2022 at 14:59

    It’s in fact very complex in this active life to listen news on Television, so I simply use web for that reason, and get the newest
    news.

    Reply
  37. vimeo.com says: March 9, 2022 at 01:04

    What’s up everyone, it’s my first pay a quick visit at this website,
    and piece of writing is in fact fruitful for me, keep up posting these articles.

    Reply

Write a Reply or Comment

Your email address will not be published.