Comparing plan consistency and query performance using the Query Store Replay script
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.
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:
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.
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.
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.
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:
Compatibility mode 110 (SQL Server 2012)
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.
This is the ID of the Execution Plan the Query Store assigned for this query on the source database, in this case DatabaseA.
Represents the ID of the query statement the query store assigned to the query on the source database.
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.
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.
The duration of the query statement (in microseconds) when last executed against the source database.
The duration of the query statement (in microseconds) when the query got replayed against the target database.
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.
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.