Query Store

The Query Store is a feature available in SQL Server 2016 and later versions that helps you to monitor and diagnose performance issues in your database. It captures a history of queries, execution plans, and runtime statistics, allowing you to identify performance regressions and optimize query performance over time.

What is Query Store?

Query Store automatically and continuously captures the history of queries, execution plans, and runtime statistics in your database. It maintains this information until explicitly cleared. This historical data is invaluable for:

• Identifying top resource-consuming queries.
• Detecting and diagnosing performance regressions.
• Identifying queries that have changed execution plans.
• Comparing query performance over time.
• Forcing specific execution plans for troubleshooting.

Key Components

Query Store consists of the following main components:

• Query Text: The actual SQL statement executed.
• Execution Plans: The plan generated by the query optimizer to execute a query.
• Runtime Statistics: Metrics such as CPU time, elapsed time, logical reads, and execution count.
• Performance Metrics: Aggregated performance data over configurable time intervals.

Enabling and Configuring Query Store

To enable Query Store for a database, you can use SQL Server Management Studio (SSMS) or Transact-SQL. Here's an example using T-SQL:

ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE);
            

You can also configure Query Store properties such as the maximum storage size and cleanup policy using the database properties in SSMS or the following T-SQL commands:

-- Set storage size
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (MAX_SIZE_MB = 1024);

-- Set cleanup policy
ALTER DATABASE YourDatabaseName
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30, LATENCY_IN_MINUTES = 60));
            

Using Query Store

Once enabled, Query Store collects data automatically. You can access and analyze this data through:

1. SSMS Built-in Reports:

In SSMS, navigate to your database, then to "Database Reports" > "Standard Reports". You'll find several Query Store reports, including:

• Top Resource Consuming Queries
• Queries with Regressed Performance
• Queries with Forced Plans
• Unused Plan Forcing Candidates

2. T-SQL Querying:

Query Store exposes its data through a set of dynamic management views (DMVs). Some commonly used views include:

• sys.query_store_query: Contains information about queries.
• sys.query_store_plan: Contains information about execution plans.
• sys.query_store_runtime_stats: Contains runtime statistics for queries.

For example, to find the top 5 queries by average CPU time:

SELECT
    qsq.query_id,
    qsq.query_sql_text,
    AVG(qsr.avg_cpu_time) AS average_cpu_time_ms
FROM
    sys.query_store_query AS qsq
JOIN
    sys.query_store_runtime_stats AS qsr ON qsq.query_id = qsr.query_id
GROUP BY
    qsq.query_id, qsq.query_sql_text
ORDER BY
    average_cpu_time_ms DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
            

Force Plans

One of the powerful features of Query Store is the ability to "force" a specific query plan. If Query Store identifies a query whose performance has regressed due to a new execution plan, you can use Query Store to tell SQL Server to use a previously known good plan. This is done via the SSMS interface or T-SQL.

-- Example to force a plan (replace placeholders)
EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456;
            

To unforce a plan:

EXEC sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;
            

Considerations

• Query Store adds a slight overhead to query execution and data storage.
• Ensure sufficient disk space is allocated for Query Store data.
• Regularly review Query Store reports to monitor database performance.
• Understand the `OPERATION_MODE` (READ_ONLY, READ_WRITE, OFF) and `CLEANUP_POLICY` for effective management.