The SQL Server Query Store

The SQL Server Query Store is a feature introduced in SQL Server 2016 that helps database administrators troubleshoot query performance issues by providing detailed information about query execution plans and resource usage over time. The Query Store captures query performance metrics, execution plans, and other metadata about each query, and stores this information in a set of system tables in the database. This information can be used to identify queries that are experiencing performance problems, investigate the causes of these problems, and take steps to optimize query performance. The Query Store tracks the following information for each query:

• Execution plans: The Query Store captures the execution plan used for each query and stores it in the system tables, along with other metadata such as query text, query hash, and query plan hash.
  

• Performance metrics: The Query Store captures performance metrics such as CPU usage, I/O usage, and query duration for each query, allowing administrators to identify queries that are consuming excessive resources.
  

• Plan regression: The Query Store can detect when a query's execution plan has changed, and can automatically revert to a previous plan if the new plan performs worse than the old one.
  

• Query tuning: The Query Store provides tools for administrators to identify and tune queries that are experiencing performance issues, such as the ability to compare different execution plans for a query and identify the most efficient one.
  

Overall, the Query Store is a valuable tool for database administrators who need to troubleshoot and optimize query performance in SQL Server, and can help to reduce the time and effort required to identify and fix performance issues.

To enable Query Store in a SQL Server database using T-SQL, you can use the following command:

ALTER DATABASE [database_name] SET QUERY_STORE = ON;

Replace [database_name] with the name of the database that you want to enable Query Store for. This command enables Query Store with the default settings, which includes capturing data for all queries executed in the database.

You can also specify additional options when enabling Query Store using the ALTER DATABASE command, such as setting retention periods for captured data or limiting the maximum amount of disk space used by the Query Store. Here is an example of the ALTER DATABASE command with some additional options:

ALTER DATABASE [database_name] SET QUERY_STORE = ONWITH 
    ( 
        OPERATION_MODE = READ_WRITE, 
        CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 7 ), 
        DATA_FLUSH_INTERVAL_SECONDS = 900,
        MAX_STORAGE_SIZE_MB = 1000
    );

In this example, the OPERATION_MODE is set to READ_WRITE, which means that Query Store will capture data for all queries executed in the database. The CLEANUP_POLICY option sets the threshold for stale queries to 7 days, which means that data for queries that haven't been executed in the past 7 days will be deleted. The DATA_FLUSH_INTERVAL_SECONDS option sets the interval for flushing data to disk to 900 seconds (15 minutes), and the MAX_STORAGE_SIZE_MB option limits the maximum amount of disk space used by Query Store to 1000 MB.

Note that enabling Query Store may have some performance overhead, so you should monitor your database's performance after enabling Query Store and adjust the settings as necessary to minimize any impact on performance.

View the top resource consuming queries in the Query Store:

SELECT TOP 10 q.query_id, q.query_sql_text, rs.avg_cpu_time, rs.avg_logical_io_reads, rs.avg_physical_io_reads, rs.avg_durationFROM sys.query_store_query q
JOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_idORDER BY (rs.avg_cpu_time + rs.avg_logical_io_reads + rs.avg_physical_io_reads + rs.avg_duration) DESC;

This query returns the top 10 queries in the Query Store sorted by the sum of their average CPU time, logical IO reads, physical IO reads, and duration.

View the top plans by execution count in the Query Store:

SELECT TOP 10 p.plan_id, q.query_sql_text, p.query_plan, p.execution_countFROM sys.query_store_plan p
JOIN sys.query_store_query q ON p.query_id = q.query_idORDER BY p.execution_count DESC;

This query returns the top 10 plans in the Query Store sorted by execution count, along with their plan ID, query text, query plan in XML format, and execution count.

View the wait statistics for a specific query in the Query Store:

SELECT w.wait_category_desc, w.wait_time_ms, w.wait_countFROM sys.query_store_query q
JOIN sys.query_store_wait_stats w ON q.query_id = w.query_id
WHERE q.query_id = [query_id];

Replace [query_id] with the ID of the query you want to view the wait statistics for. This query returns the wait category description, wait time in milliseconds, and wait count for the specified query.

View the top wait categories in the Query Store:

SELECT TOP 10 wait_category_desc, SUM(wait_time_ms) AS total_wait_time_ms, SUM(wait_count) AS total_wait_count
FROM sys.query_store_wait_stats
GROUP BY wait_category_desc
ORDER BY total_wait_time_ms DESC;

This query returns the top 10 wait categories in the Query Store sorted by total wait time, along with the total wait time in milliseconds and total wait count for each category.

View the execution context for a specific query in the Query Store:

SELECT execution_type_desc, query_parameterization_type_desc, object_id, object_name, query_parameterization_type_desc
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_query q ON rs.query_id = q.query_id
WHERE q.query_id = [query_id];

Replace [query_id] with the ID of the query you want to view the execution context for. This query returns the execution type, query parameterization type, object ID, object name, and query parameterization type description for the specified query.