Query Store – identifying regressed queries

To see how Query Store represents regression, you will use a new Query Store node within SSMS. From four integrated reports, you will choose Tracked Queries as shown in the following screenshot:

Tracked Queries report in the Query Store section of SQL Server Management Studio

When you click Tracked Queries, a new window will be opened in SSMS. In the text field, Tracking query, enter 1 and click on the button with the small play icon. As mentioned earlier, assume that the ID of your initial query is 1. You will see a screen similar to the one displayed in the following screenshot:

Tracked Queries report showing the new execution plan (compatibility level 140)

You can see two different colors for bullets, representing two execution plans used for the execution of the query with ID set to 1. The vertical axis shows the average execution time for the plans in milliseconds. It is clear that the yellow plan performs better and that the average execution time for the blue plan is significantly increased. In the bottom pane, you can see the execution plan for the selected circle (in this case this is a plan with the id set to 2 which represents the execution plan under compatibility level 140). The plan uses the Clustered Index Scan and Hash Match operators, which is not efficient for a query that returns no rows.

When you click on the green circle on the screen, you get the window shown in the following screenshot:

Tracked Queries report showing the old execution plan (compatibility level 110)

You can see that, for this case, the old execution plan uses the appropriate and efficient Nested Loops join operator, which explains why the old execution plan is better for this highly selective query (no rows are returned).

Changes in the cardinality estimator introduced in SQL Server 2014 are responsible for new execution plans in this example. The old CE estimates 10 rows, while the new CE expects 3,129 rows to be returned, which leads to an execution plan with the Scan and Hash Match Join operators. Since the query returns no rows, the estimation done by the old CE is more suitable in this case. The same plan (Clustered Index Scan and Hash Match) would be generated under compatibility level 120.

Of course, you can get this info by querying catalog views, too. The following query returns rows from the collected runtime statistics for two plans in the Query Store:

SELECT plan_id, CAST(avg_duration AS INT) AS avg_duration, avg_logical_io_reads FROM sys.query_store_runtime_stats WHERE plan_id = 1 
UNION ALL 
SELECT plan_id, CAST(avg_duration AS INT) AS avg_duration, avg_logical_io_reads FROM sys.query_store_runtime_stats WHERE plan_id = 2;

The output is shown in the following screenshot. You can see that all execution parameters are significantly increased for the second plan:

Comparing multiple runtime statistics for two different execution plans

Table of Contents for Query Store&#xA0;&#x2013; identifying regressed queries

Create new playlist

Sign In

Sign Up

Table of Contents for
Query Store – identifying regressed queries