The 1.2 version of the Query Store Replay script a number of new features are added that return information of the query behavior on the target server. More specifically, in version 1.2 you are able to compare execution plans and query runtime statistics between replay executions on the source and target servers!

Video guide to using the Query Store Replay script

Double-click to open fullscreen.

New parameters

In version 1.2, four parameters are added to the Query Store Replay script that can be set to influence the execution of the script:

• SelectOnly

  The SelectOnly parameter influences the type of queries that will be replayed. If this parameter is set to $true, only SELECT queries will be exported and replayed against the target database. If set to $false all queries will be exported and replayed. By default this value is set to $true.

• PlanConsistency

  The PlanConsistency parameter, when set to $true, performs an extra action to check if the execution plan generated on the target server is identical to the execution plan generated during the replay of the query on the target server. By default this parameter is set to $false. This parameter requires the Query Store to be enabled on the target database.

• ComparePerf

  When the ComparePerf parameter is set to $true the Query Store Replay script will record the duration of the query execution on the source server and on the target server and returns this information in a table at the end of the Query Store Replay script execution. By default the ComparePerf parameter is set to $false.

  This parameter requires the Query Store to be enabled on the target database.

• IncludeStatements

  The IncludeStatements parameter adds the first 100 characters of the query statement to the table returned when the ComparePerf parameter is set to $true. By default this parameter is set to $false.

Using the PlanConsistency and ComparePerf parameters

Personally I believe the PlanConsistancy and ComparePerf features are a great addition to the Query Store Replay script since they provide you with a direct comparison on how your queries behave on a different database/server. Using them is very easy but require that your target database has the Query Store enabled. This also means your target server has to be running SQL Server 2016 or higher.

Let’s look at a specific use-case where these new parameters can be used: testing of your query workload on a database with a different compatibility level.

For this scenario I created two database inside a Microsoft SQL Server 2016 Instance, both are based on a backup of the AdventureWorks database and renamed for easier identification:

• DatabaseA

  Compatibility mode 110 (SQL Server 2012)

• DatabaseB

  Compatibility mode 130 (SQL Server 2016)

Now say I want to test how my query workload for DatabaseA performs when it is executed against DatabaseB that has a higher compatibility level configured. Normally we would have to manually capture the queries performed against DatabaseA and execute them against DatabaseB and then go through each of the queries to see how they behaved against DatabaseB. With the Query Store Replay script we can automate these steps!

To show you how this works I executed the SQL script below. This script purges the Query Store on DatabaseA and DatabaseB and executes some queries against DatabaseA. These queries are the workload we are replaying against DatabaseB. The reason I clear the Query Store on both of the databases is to keep the data returned by the Query Store Replay script nice and small for demonstration purposes.

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

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

-- Execute some queries
 SELECT Name, ProductNumber, ListPrice AS Price
 FROM Production.Product 
 ORDER BY Name ASC;
 GO

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);
 GO

SELECT p.Name AS ProductName, 
 NonDiscountSales = (OrderQty * UnitPrice),
 Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
 FROM Production.Product AS p 
 INNER JOIN Sales.SalesOrderDetail AS sod
 ON p.ProductID = sod.ProductID 
 ORDER BY ProductName DESC;
 GO

-- Switch to target database
 USE [DatabaseB]
 GO

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

Now that I generated a workload for DatabaseA that we want to replay to DatabaseB, let’s run the Query Store Replay script and specify we want to check the execution plan consistency and performance a performance comparison:

.\QueryStoreReplay.ps1 -SourceServer localhost -SourceDatabase DatabaseA -TargetServer localhost -TargetDatabase DatabaseB -TimeWindow 1 -PlanConsistency $true -ComparePerf $true -IncludeStatements $true

After running the Powershell command above, the Query Store Replay script will return the Compare Performance table:

Let’s go through the various columns of the table and describe what they mean.

• SourcePlanID

  This is the ID of the Execution Plan the Query Store assigned for this query on the source database, in this case DatabaseA.

• SourceQueryID

  Represents the ID of the query statement the query store assigned to the query on the source database.

• TargetPlanID

  This is the ID of the Execution Plan on the target database (DatabaseB) inside the Query Store. This ID can (and frequently will be) different to the ID of the SourcePlanID column.

• TargetQueryID

  The ID of the query statement on the target database inside the Query Store. Just like the Execution Plan ID on the target database, this ID can be different to the value inside the SourceQueryID column.

• SourceDB_Duration

  The duration of the query statement (in microseconds) when last executed against the source database.

• TargetDB_Duration

  The duration of the query statement (in microseconds) when the query got replayed against the target database.

• QueryStatement

  Since we set the IncludeStatements parameter to $true when running the Query Store Replay script, the query statements gets added to the table.

As you can see, using the new parameters can give you a quick overview of the performance of the query statements between both databases. One additional thing I would like to point out is the “*” added to the duration of the statement on the target database of the second statement.

The “*” indicates that for this query statement a different execution plan was generated on the target database compared to the source database. For our use-case we can probably conclude that the change in compatibility level on DatabaseB resulted in a different execution plan for this specific query.

If we want to see the full query statement of the query that generated a different plan, we can use the Query Store Replay log file. Inside the log file an info message is logged with the filename of the replay file that generated a different plan on the target database.

Summary

Using the new features available in the Query Store Replay script can easily provide you with information how your query performed when you replay it to a different database. Not only does it return the query execution times on both the source and the target database but it can also show you if a different execution plan was used on the target database.

You can download the Query Store Replay script for free from its GitHub page: https://github.com/Evdlaar/QueryStoreReplay.