Query Store

This blog is an easy-to-read summary of the Query Store functionality available in Azure SQL and other versions of SQL Server.

Query Store was introduced in SQL Server 2016 and it provides insights into plan choices and performance issues.

If enabled, it is available within SQL Server Management Studio via the Query Store folder:

Accessing Query Store

Functionality includes:

  • Maintain query plan versions. This means that queries can be optimised where they are slowed due to execution plan changes.
  • Identifying and Analyzing key metrics
Query Store UI

Key Metrics

The following are the metrics exposed by the Query Store views and can be aggregated (min, max, avg, etc.) over a chosen timescale

CPU TimeCPU time for the query plan in msDurationDuration time for the query plan in ms
Execution CountCount of how many times the query plan has been executedLogical ReadsNumber of pages read from the data cache for the query plan
Logical WritesNumber of pages written to the data cache for the query planMemory ConsumptionMemory grant (reported as the number of 8 KB pages) for the query plan
Physical ReadsNumber of pages read from disk for the query planCLR Time. NET Common Language Runtime engine time for the query plan
Degree of ParallelismDegree of Parallelism (DOP) is the number of processors, that are assigned for the parallel planRow CountNumber of returned rows for the query plan
Log MemoryNumber of bytes in the database log used by the query planTempDB MemoryNumber of pages used in tempdb for the query plan
Wait timesThe wait when a query moves from Runnable to Suspended for the query plan
Metric Definitions

See this page from Microsoft

Views

The following views are available to the support the analysis of the plans and metrics.

Regressed QueriesPinpoint queries for which execution metrics have recently deteriorated
Overall Resource ConsumptionAnalyze the total resource consumption for the database based on any of the metrics.
Top Resource Consuming QueriesChoose an execution metric of interest, and identify queries that had the most extreme values for a provided time interval.
Queries With Forced PlansLists previously forced plans using Query Store.
Queries With High VariationAnalyze queries with a high-execution variation.
Query Wait StatisticsAnalyze wait categories that are most active in a database and which queries contribute most to the selected wait category.
Views

Supporting SQL

The following SQL Statements will be useful when working with Query Store

Query ID

SELECT q.query_id, t.query_sql_text SQL_Text, object_name(q.object_id) AS parent
FROM sys.query_store_query_text t JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
WHERE t.query_sql_text LIKE N’%Load%’

Plan ID

SELECT t.query_sql_text, q.query_id, p.plan_id
FROM sys.query_store_query_text t
JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
WHERE q.query_id IN (SELECT q.query_id FROM sys.query_store_query_text t JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id WHERE t.query_sql_text LIKE N’%Load%’)

Reading the Plan

Now the tricky bit!! When you find a plan in Query Store that you wish to work on, the first thing you need to decide is whether it is going to meet your performance expectations. You effectively need to read the plan. Which is beyond the scope of this post. A good place to start is here

Showplan logical and physical operators reference

Summary

In itself, this is not a solution to performance issues but will help identify them and manage the plans used. It needs to be augmented with a healthy understanding of:

  • Query Plans and what they mean
  • Cost of applying indexes when considering load times. Particularly with large datasets
  • SQL Azure – Performance Recommendations
  • Types of indexes and when to use each one (Clustered, Non-Clustered, Column Store, Text, etc.)
  • Index health (e.g. degree of fragmentation)
  • Maintenance of stats (e.g. are they accurate or not)

Image by StockSnap from Pixabay

Leave a Reply

Your email address will not be published. Required fields are marked *