Detailed workflow

Enrico van de Laar edited this page Dec 19, 2016 · 4 revisions

This page describes, in detail, how the Query Store Replay script works. Understanding the scripts behavior can help you get a better idea on use-cases and what is, or isn't, being replayed.

1. Writing the log file

The first thing the Query Store Replay script performs is the creation of a log file. By default this file will be created in your "My Documents" folder. The log file records many of the steps performed by the script and also records the amount of execution plans and statements that were extracted from the source database as well as the amount of statements replayed on the target database. The log file is the first place where you should look if you run into any issues or are interested in how many execution plans, statements and parameters were processed by the Query Store Replay script.

Query Store Replay Log

2. Pre-execution checks

The Query Store Replay script will check of the Source Server is running Microsoft SQL Server 2016 or higher. If a lower version is detected the script will stop execution. After the SQL Server version check another check is performed against the Source Database to detect if the Query Store feature is enabled for this specific database. The Query Store can be in either "Read/write" or "Read-only" mode. If the Query Store is in a different mode, script execution will stop and an error will be recorded in the log file.

3. Folder creation

The Query Store Replay script will create two folders: ExtractedPlans & ReplayQueries. By default these folders are created inside your "My Documents" folder. An alternative location can be configured by setting the FileLocation parameter when you start the script.

The ExtractedPlans folder will hold all of the executions plans that are extracted from the source database's Query Store. The ReplayQueries folder will store query statements inside .sql files that are used for replaying the workload against the replay target database.

Query Store Replay folders

4. Execution plan extraction

In this phase the Query Store Replay script will extract all of the execution plans that have been executed in the time window supplied using the TimeWindow parameter. These execution plans will be stored in the ExtractedPlans folder as .sqlplan files. You can view the extracted plans through SQL Server Management Studio of any other execution plan viewer tool.

Extracted plans

5. Query statement and parameter extraction

After the extraction of the execution plans, the Query Store Replay script will move through all the execution plans one at a time and extract the query statement from the XML. If any parameters were supplied in the query statement the Query Store Replay script will extract those as well.

Note: The parameters that are extracted are those used for the last compilation of the execution plan.

Extracted Query Statements

6. Replay .sql file building.

Using the query statement and parameters, the Query Store Replay script will build a .sql file for every statement. If any parameters were found for a specific statement it will also declare them in the script so everything needed to run the statement is present in one single .sql file.

Example Statement file

If you supplied the parameter ExportOnly when running the script, execution will stop here.

7. Replay the .sql files

The last step of the Query Store Replay script is the execute all of the .sql files against the Target Database you specified. Depending on the amount of query statements this can take some time to complete. Import to remember is that the replay does not keep track of the original execution timing. This means that queries are executed directly after one another. If any errors occurred when replaying queries, they will be recorded in the log file.

After the replay is completed the Query Store Replay script will end.

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.