Replay your workload through the Query Store Replay script!
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: //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!”
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?
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.
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?
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.
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)?
It’s remarkable for me to have a site, which is
good in support of my knowledge. thanks admin
I enjoy reading a peice that can get people to think.
Too, many thanks to get allowing for myself to annotate!
It’s remarkable for me to have a website, which is beneficial in support
of my know-how. thanks admin
Wonderful post! We will be linking to this great content on our site.
Keep up the good writing.
I got this web site from my pal who informed me regarding this site
and now this time I am browsing this site and reading very informative content at this time.
Thanks for finally writing about > Replay your workload through
the Query Store Replay script! – DotNine < Loved it!
This piece of writing is in fact a pleasant one it helps new web people, who are wishing for blogging.
This paragraph will help the internet users for building up new web
site or even a blog from start to end.
I am genuinely pleased to read this webpage posts which consists
of lots of valuable information, thanks for providing these kinds
of information.
It’s hard to come by well-informed people on this topic, however,
you sound like you know what you’re talking about! Thanks
If you desire to get much from this article then you have to apply such strategies to your won website.
Hi to every one, as I am actually keen of reading this weblog’s post to
be updated daily. It carries pleasant stuff.
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!
I am regular visitor, how are you everybody? This article posted at this website is in fact pleasant.
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
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!
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!
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.
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!
Very nice write-up. I absolutely appreciate this site.
Keep it up!
Hi to every one, it’s truly a nice for me to pay a quick visit this
website, it contains useful Information.
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.
Thanks for finally writing about > Replay your workload
through the Query Store Replay script! – DotNine < Liked it!
I am genuinely thankful to the owner of this
web site who has shared this impressive article at at this place.
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.
I read this post fully regarding the comparison of newest and previous
technologies, it’s amazing article.
Hi to every single one, it’s genuinely a nice for me to
visit this web site, it consists of precious Information.
Hi to every body, it’s my first pay a quick visit of this weblog; this blog includes amazing and genuinely excellent information in support of readers.
Thanks for finally writing about > Replay your workload through the Query
Store Replay script! – DotNine < Loved it!
Hurrah! Finally I got a web site from where I be capable of genuinely take useful information concerning my study and knowledge.
What’s up, after reading this amazing article i am too happy to share my knowledge here with mates.
If you would like to obtain a great deal from this post then you have to apply such techniques to your won weblog.
Hi friends, how is all, and what you want to say concerning this paragraph, in my view its actually amazing designed for
me.
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.
Superb, what a website it is! This weblog presents valuable information to us, keep
it up.
Pim Roelof Hein 5581 Παναγιώτης Square, Apt. 180, 20055-2686, East Γεώργιοςton, Delaware, Greece