Running the Query Store Replay script

Enrico van de Laar edited this page Dec 30, 2016 · 5 revisions

To use the Query Store Replay script you will need a few prerequisites:

  • Microsoft SQL Server 2016 or higher installed on the SQL Server machine that will be the source of the query replay.
  • SQL Server Management Objects (SMO). More information about SMO and how you can install it can be found here: https://msdn.microsoft.com/en-us/library/ms162189.aspx

Query Store Replay parameters

The Query Store Replay script expects a number of parameters that need to be supplied before execution can start:

  • SourceServer (required): The name of the server from which you want to extract query statements.
  • SourceDatabase (required): The name of the database on the SourceServer from which the script extracts query statements. Keep in mind that the Query Store feature needs to be enabled on this database.
  • TargetServer (optional): The name of the server where we will replay our extracted workload against. This can be the same server as the SourceServer or a different one. SQL Server 2016 is not required for the TargetServer.
  • TargetDatabase (optional): The name of the database on the TargetServer that will be used to replay the query statements we extracted from the SourceDatabase. To make sure the replay of queries works, this database will need to have the same table schema as the SourceDatabase. Enabling the Query Store for this database is not required.
  • TimeWindow (required): This value determines how far back in time we go to retrieve query statements from the Query Store. For instance, setting the TimeWindow to a value of '4' means that the last 4 hours of (unique) execution plans will extracted from the SourceDatabase.
  • FileLocation (optional): Specify a folder where the extracted execution plans, replay scripts and log files are stored on your local machine. By default this location is the My Documents folder.
  • ExportOnly (optional): When you set ExportOnly to $true the Query Store Replay script will only export execution plans and query statement to the export folders and not replay the workload against the TargetDatabase.
  • SelectOnly (optional): If set to $false not only SELECT statements will generate a replay .sql file but also UPDATE, INSERT and DELETE statements. By default the setting is set to $true which means only SELECT queries will be replayed against the TargetDatabase.
  • PlanConsistency (optional): If set to $true an extra check will be ran to detect non-consistent execution plan generation on the TargetServer. This parameter requires the Query Store to be enabled on the TargetDatabase.
  • ComparePerf (optional): If set to $true the last duration metrics of the query execution on the source and target are recorded and returned. This parameter requires the Query Store to be enabled on the TargetDatabase.
  • IncludeStatements (optional): If set to $true the table returned by the ComparePerf parameter also includes the first 100 characters of the query statement.

Examples

Various examples van be found on this Wiki on the "Examples" page.

Getting help

A description of the parameters and function of the script can be retrieved when running Get-Help from a Powershell window against the QueryStoreReplay.ps1 script like so:

Get-Help .\QueryStoreReplay.ps1 -Full

If you run into issues or bugs when using the Query Store Replay script please contact me on Twitter @evdlaar or post a new issue on this GitHub page.

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.