SQL Server 2016 Query Store Queries

Problem

One of the new features of SQL Server 2016 is the Query Store. In our last tips about Query Store we reviewed how to configure and access Query Store with SQL Server Management Studio. We also provided examples of the Query Store usage.  What are some practical applications of using the Query Store?  What queries can be run against the Query Store?  What sort of questions can I get answered?

Solution

We will use the Query Store catalog views and stored procedures for the queries below. Also, we will provide reference to other queries available on the Microsoft MSDN web site.

Useful SQL Server 2016 Query Store Queries

Find the Query Store size

With this query you can find the used size and maximum size of the Query Store:

SELECT current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options 

Find a Query’s ID in the Query Store

Find a query ID based on partial query text or parent object (view, stored procedure, etc.) name:

SELECT q.query_id, t.query_sql_text, object_name(q.object_id) AS parent_object 
  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'%insert %db_store%'
        OR object_name(q.object_id) = 'proc_1'  

Find Plan(s) Associated with a Query

Find plan(s) ID(s) based on query ID (if known), partial query text or parent object (view, stored procedure, etc.) name:

SELECT  t.query_sql_text, q.query_id, p.plan_id, object_name(q.object_id) AS parent_object 
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 = 1 
-- OR t.query_sql_text LIKE  N'%SELECT c1, c2 FROM  dbo.db_store%'
-- OR object_name(q.object_id) = 'proc_1'  

Find TOP 10 Queries with Multiple Plans in the SQL Server Query Store

SELECT TOP 10 t.query_sql_text, q.query_id, 
object_name(q.object_id) AS parent_object, 
COUNT(DISTINCT p.plan_id) AS num_of_plans 
   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 
GROUP BY t.query_sql_text, q.query_id, object_name(q.object_id)
HAVING  COUNT(DISTINCT p.plan_id) > 1
ORDER BY COUNT(DISTINCT p.plan_id) DESC

Find TOP 10 the most Frequently Executed SQL Server Queries in the Query Store

SELECT TOP 10 t.query_sql_text, q.query_id, 
object_name(q.object_id) AS parent_object, 
SUM(s.count_executions) total_executions
 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 
   JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
 WHERE s.count_executions > 1 -- used to make the query faster
GROUP BY  t.query_sql_text, q.query_id, object_name(q.object_id)
ORDER BY SUM(s.count_executions) DESC

Find TOP 10 SQL Server Queries with the largest number of rows affected in the Query Store

This could be useful to check if there are any queries that return large number of rows and probably later investigate if filters could be added to the query.

We had one old application that initially used a small table. Developers used a query that returned all records from this table. When the database and table grew over a couple of years this really affected application performance.

SELECT  top 10 t.query_sql_text, q.query_id, 
object_name(q.object_id) AS parent_object, 
s.plan_id, s.avg_rowcount
 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 
  JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
WHERE s.avg_rowcount > 100
ORDER BY s.avg_rowcount DESC

You may need to filter the query to exclude index operations on large tables.

Find TOP 10 SQL Server Queries with the largest ratio of the compilations per execution in the Query Store

Sometimes query performance could be affected by excessive recompilations. Use this to find the top 10 queries with a high number of compilations:

WITH Query_Stats 
AS 
(
 SELECT plan_id,
 SUM(count_executions) AS total_executions
 FROM sys.query_store_runtime_stats
 GROUP BY plan_id
)
SELECT TOP 10 t.query_sql_text, q.query_id, p.plan_id,
s.total_executions/p.count_compiles avg_compiles_per_plan
  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 
    JOIN Query_Stats s ON p.plan_id = s.plan_id
ORDER BY s.total_executions/p.count_compiles DESC

Read more about recompiling execution plans here.

Cleanup the SQL Server Query Store Data

You can remove a specific plan from the Query Store with the sp_query_store_remove_plan stored procedure (runtime statistics for the plan will be cleaned up as well):

EXEC sp_query_store_remove_plan @plan_id = 1
GO

With the sp_query_store_reset_exec_stats stored procedure you can delete the runtime statistics for the specific plan, but keep the plan itself in the Query Store:

EXEC sp_query_store_reset_exec_stats @plan_id = 1
GGO

You can also remove an entire query from the Query Store with the sp_query_store_remove_query stored procedure. This will remove the related plans and statistics from the Query Store as well:

EXEC sp_query_store_remove_query @query_id = 1

Other Useful Queries

Some other useful queries could be found on the Microsoft MSDN web site here:

• Last n queries executed on the database
• Number of executions for each query
• The number of queries with the longest average execution time within last hour
• The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count
• Queries that recently regressed in performance (comparing different point in time)
• Queries that recently regressed in performance (comparing recent vs. history execution)
• Delete ad-hoc queries.

Other Columns for your Queries

You may find the following columns useful for your own queries:

• is_parallel_plan column in sys.query_store_plan catalog view
• avg_dop column in sys.query_store_runtime_stats catalog view
• query_parameterization_type column in sys.query_store_query catalog view
• is_internal_query column in sys.query_store_query catalog view.

Next Steps

• Write your own queries using the Query Store catalog views.
• Download the latest evaluation version of SQL Server 2016.
• Read SQL Server 2016 Books Online documentation.
• Read how to Monitor Performance By Using the Query Store.
• Read other tips on SQL Server 2016.