Getting started with Query Store feature in SQL Server 2016 – Part 3

Introduction

Query Store is a new feature in SQL Server 2016, which once enabled, automatically captures a history of queries, execution plans, and runtime statistics, retaining them for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after server restart or upgrade.

In “Getting Started with the Query Store Feature in SQL Server 2016 – Part 1” I talked about this new feature and its advantages, then explained some of the scenarios where you can use it. Finally, I talked about data capture processing, including what gets captured, and how it is retrieved when you enable Query Store feature. In “Getting started with Query Store feature in SQL Server 2016 – Part 2” I explained query execution flow when using Query Store and how it differs from regular query execution flow, and I talked about typical performance troubleshooting workflow with Query Store and how to get started with it. In this third article of the series, I am going to demonstrate how you can analyze the collected data by Query Store either with T-SQL scripting or with the graphical user interface in SSMS with built-in reports\dashboards.

Query Store – Analyzing Collected Data with T-SQL

With SQL Server 2016 RC0, there are seven new dynamic management catalog views and 6 new extended stored procedures to work with Query Store. To learn more about each of these catalog views refer to the MSDN article, “Query Store Catalog Views (Transact-SQL)” and for stored procedures refer to “Query Store Stored Procedures (Transact-SQL)”.

Query Store

Based on these views, I am providing you some of the pre-written scripts (which of course you can modify based on your specific need) which you can use for some common scenario. For example, the first query gives you the last ten recent queries that were executed in the current database; likewise, the second query gives the count of executions for each query ranked by number of executions in descending order. For each of these queries, I have put comments prior to it so that you can easily identify and make use of it:

--Query last 10 recent queries that were executed 
--in the current database
SELECT
       TOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, 
       p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text   qt 
JOIN sys.query_store_query   q ON qt.query_text_id   = q.query_text_id   
JOIN sys.query_store_plan   p ON q.query_id   = p.query_id 
JOIN sys.query_store_runtime_stats   rs ON p.plan_id   = rs.plan_id
ORDER BY rs.last_execution_time   DESC
 
--Query to get count of executions for each query
--ranked by number of executions in descending
SELECT
       q.query_id, qt.query_text_id, 
       qt.query_sql_text, 
       SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text   qt 
JOIN sys.query_store_query   q ON qt.query_text_id   = q.query_text_id   
JOIN sys.query_store_plan   p ON q.query_id   = p.query_id 
JOIN sys.query_store_runtime_stats   rs ON p.plan_id   = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC
 
--Query to get 10 queries with the longest average 
--execution time within last one hour, 
--ranked by query duration
SELECT
       TOP 10 qt.query_sql_text, 
       q.query_id, qt.query_text_id, p.plan_id, 
       GETUTCDATE() AS CurrentUTCTime, 
       rs.last_execution_time,   rs.avg_duration
FROM sys.query_store_query_text   qt 
JOIN sys.query_store_query   q ON qt.query_text_id   = q.query_text_id   
JOIN sys.query_store_plan   p ON q.query_id   = p.query_id 
JOIN sys.query_store_runtime_stats   rs ON p.plan_id   = rs.plan_id
WHERE   rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.avg_duration desc
 
--Query to get 10 queries that had the biggest 
--average physical IO reads in last 24 hours, 
--ranked by IO cost
SELECT
       TOP 10 qt.query_sql_text, 
       q.query_id, qt.query_text_id, 
       p.plan_id, rs.runtime_stats_id, 
       rsi.start_time, rsi.end_time, 
       rs.avg_physical_io_reads,   
       rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text   qt 
JOIN sys.query_store_query   q ON qt.query_text_id   = q.query_text_id   
JOIN sys.query_store_plan   p ON q.query_id   = p.query_id 
JOIN sys.query_store_runtime_stats   rs ON p.plan_id   = rs.plan_id 
JOIN sys.query_store_runtime_stats_interval   rsi 
ON rsi.runtime_stats_interval_id =   rs.runtime_stats_interval_id
WHERE   rsi.start_time >=   DATEADD(HOUR, -24, GETUTCDATE()) 
ORDER BY rs.avg_physical_io_reads   desc
 
--Query to get queries that recently got regressed in the 
--performance (execution time got doubled within last 48 hours)
SELECT
       qt.query_sql_text, 
       q.query_id, 
       p1.plan_id AS plan1, 
       rs2.avg_duration AS   plan2
FROM sys.query_store_query_text   qt 
JOIN sys.query_store_query   q ON qt.query_text_id   = q.query_text_id   
JOIN sys.query_store_plan   p1 ON q.query_id   = p1.query_id   
JOIN sys.query_store_runtime_stats   rs1 ON p1.plan_id   = rs1.plan_id   
JOIN sys.query_store_runtime_stats_interval   rsi1 ON rsi1.runtime_stats_interval_id   = rs1.runtime_stats_interval_id   
JOIN sys.query_store_plan   p2 ON q.query_id   = p2.query_id   
JOIN sys.query_store_runtime_stats   rs2 ON p2.plan_id   = rs2.plan_id   
JOIN sys.query_store_runtime_stats_interval   rsi2 ON rsi2.runtime_stats_interval_id   = rs2.runtime_stats_interval_id
WHERE   rsi1.start_time >   DATEADD(HOUR, -48, GETUTCDATE()) AND
rsi2.start_time >   rsi1.start_time AND
rs2.avg_duration >   2*rs1.avg_duration
 
--Query to get queries with multiple plans
WITH   QueryWithMultiplePlans 
AS
(
       SELECT COUNT(*) AS cnt, q.query_id 
       FROM sys.query_store_query_text qt 
       JOIN sys.query_store_query q ON   qt.query_text_id =   q.query_text_id 
       JOIN sys.query_store_plan p ON   p.query_id =   q.query_id 
       GROUP BY q.query_id HAVING COUNT(DISTINCT plan_id) > 1 
)
SELECT   q.query_id, OBJECT_NAME(object_id) AS ContainingObject,   
query_sql_text, plan_id, p.query_plan AS   plan_xml, p.last_compile_start_time, 
p.last_execution_time 
FROM QueryWithMultiplePlans   qm 
JOIN sys.query_store_query   q ON qm.query_id   = q.query_id 
JOIN sys.query_store_plan   p ON q.query_id   = p.query_id 
JOIN sys.query_store_query_text   qt ON qt.query_text_id   = q.query_text_id   
ORDER BY query_id,   plan_id

Query Store – Analyzing Collected Data within SSMS

As mentioned earlier, T-SQL scripting is not the only way to look into data captured by Query Store. SQL Server 2016 includes several inbuilt reports and dashboards to quickly analyze and get insight in to the captured data. These reports and dashboards also allow you take actions like comparing two plans, forcing plans, etc.

To explore the data captured by Query Store, you can expand the database for the Query Store feature that has been enabled in the Object Explorer of SSMS and expand the Query Store node as shown in the figure below:

Expand the Query Store Node

As you can see in the figure above, there are four dashboards/reports available to make use of immediately. For example, the dashboard displayed below (one of the above shown dashboards) shows the last 25 queries that are most expensive in terms of resource consumptions in the last hour. As I mentioned earlier, Query Store captures different metrics (Duration, CPU Time, Logical Reads, Logical Writes, Physical Reads and Memory Consumption) with different statistics (Total, Average, Maximum, Minimum and Standard Deviation) and you refresh the view for each of the metrics in terms of its highest consumers. Also, you can hover your mouse over a query in the top pane with bar chart and get more details about the query ID, average duration, number of execution counts, and number of plans generated, etc.

As you can see in the other dashboard below, it allows you to compare different plans for a query, and allows you to force a plan if you think that plan is more appropriate. You can unforce a plan for the query that has an earlier forced plan (if you want to later allow SQL Server to choose between recent plans then forced ones, you will need to click on the Unforce Plan button to the right of the disabled Force Plan button as shown in the figure below).

TIP: SQL Server Query Optimizer can generate multiple execution plans over time for a given query, which also gets retained in the Query Store (assuming you have enabled it for the database), in that case you can enforce policies to direct the SQL Server Query Processor to use a specific execution plan for that query (you do it normally in case you conclude that a specific plan performs better than others). This is referred to as plan forcing. This was possible earlier also, but you were limited to making use of execution plans available in the current plan cache only; with Query Store these execution plans are persisted and hence you can still get access to it even after a restart.

Top 25 Resource Consumers During the Last Hour

The next dashboard shows overall resource consumption for the last month for the selected database. 

Overall Resources Consumption During the Last Month

There are two more dashboards. The Regressed Queries dashboard shows the top 25 regressed queries in the last hour; the Tracked Queries report allows you to specify a Query ID and find out details of it.

TIP: If SQL Server Query Optimizer generates a new plan for a query, which performs worse than the previous execution plans for the same query, then the query is said to be a “Regressed Query”.

All these dashboards show the result for standard default parameters, but if needed you can click on the Configure button in the top right corner of the dashboard to change different configurations related to metrics, statistics, durations, etc., as shown below and refresh your dashboard by clicking on the Refresh button:

Configure Top Resource Consumers

Note: The feature mentioned and demonstrated in this article is based on SQL Server 2016 Release Candidate 0 and might change when RTM is available or in future releases.

Conclusion

Query Store is a new feature in SQL Server 2016, which once enabled automatically captures a history of queries, execution plans, and runtime statistics, retaining them for your troubleshooting performance problems caused by query plan changes. This new feature greatly simplifies performance troubleshooting by helping you quickly find performance differences, even after a server restart or upgrade.

In this article I demonstrated how you can analyze the collected data by Query Store either with T-SQL scripting or with the graphical user interface in SSMS with built-in reports\dashboards.

Resources

Getting started with Query Store feature in SQL Server 2016 – Part 1

Getting started with Query Store feature in SQL Server 2016 – Part 2

What’s New in Database Engine

Monitoring Performance By Using the Query Store

Best Practice with the Query Store

See all articles by Arshad Ali