Using Query Store to Monitor Performance

Query Store is a feature available in Azure SQL Database that helps you understand workload performance and identify tuning opportunities. It automatically captures a history of queries, plans, and runtime statistics in your database. This data is then retained for review, helping you see how changes to your queries or database affect overall performance.

What is Query Store?

Query Store records:

  • The text of the query.
  • The query execution plan.
  • Runtime performance statistics.
  • Runtime execution facts.

It also tracks changes in query plans over time, allowing you to identify regressions caused by query plan changes. By analyzing this data, you can pinpoint expensive queries, understand what query plans are being used, and determine where to focus your tuning efforts.

Note: Query Store is enabled by default for new Azure SQL Databases. For existing databases, you may need to enable it manually.

Key Features of Query Store

  • History of Queries: Stores all queries executed against the database.
  • Execution Plan Analysis: Tracks query execution plans and how they change.
  • Performance Metrics: Collects runtime statistics such as CPU time, duration, logical reads, and physical reads.
  • Top Resource Consumers: Identifies queries consuming the most resources.
  • Query Regression Detection: Alerts you to performance degradations.
  • Plan Forcing: Allows you to force a specific query plan if a new plan causes performance issues.

Getting Started with Query Store

Enabling Query Store (if not already enabled)

You can enable Query Store using T-SQL or the Azure portal.

Using T-SQL:
ALTER DATABASE YourDatabaseName
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
Using Azure Portal:

Navigate to your Azure SQL Database in the Azure portal, then go to Performance > Query performance insight. If Query Store is not enabled, you will see an option to enable it.

Monitoring Performance with Query Store

Query Store provides several views to help you analyze your workload:

Top Resource Consuming Queries

This view shows queries that have consumed the most resources (e.g., CPU, duration, logical reads) over a specified period.

Use the following T-SQL query:

SELECT TOP 10
    query_id,
    query_text,
    total_cpu_time / 1000.0 AS total_cpu_time_ms,
    total_duration / 1000.0 AS total_duration_ms,
    total_logical_reads,
    avg_duration / 1000.0 AS avg_duration_ms
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_queries AS q ON rs.query_id = q.query_id
ORDER BY total_cpu_time DESC;

Query Plan Analysis

Understanding the execution plans used for your queries is crucial for performance tuning. Query Store tracks plan changes, helping you identify when a new plan might be causing a performance regression.

You can view execution plans in SQL Server Management Studio (SSMS) or Azure Data Studio by right-clicking on a query in the Query Store reports and selecting "View Execution Plan".

Tip: Regularly review the "Queries with Regressed Plan" report in Query Store to identify potential performance issues caused by plan changes.

Query Plan Forcing

If you identify a query where a recent plan change has negatively impacted performance, and you have a known good plan, you can force Query Store to use that specific plan. This can be a powerful tool for quickly mitigating performance regressions.

-- Find the plan_id to force
SELECT plan_id, query_id
FROM sys.query_store_plan_stats
WHERE query_id = 'your_query_id';

-- Force the plan
ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, GRAPH_DETECTION_INTERVAL = 60 MINUTE, GRAPH_DETECTION_TIMEOUT = 300, GRAPH_DETECTION_AUTO_REBUILD = OFF, GRAPH_DETECTION_AUTO_DROP = OFF, GRAPH_DETECTION_MAX_QUERY_COUNT = 10000, GRAPH_DETECTION_MAX_PLAN_COUNT = 1000, GRAPH_DETECTION_MAX_PLAN_PER_QUERY = 10, GRAPH_DETECTION_MAX_PLAN_PER_QUERY_WITH_REGRESSION = 5, GRAPH_DETECTION_MAX_PLAN_PER_QUERY_WITH_FORCE = 2, GRAPH_DETECTION_MAX_PLAN_PER_QUERY_WITH_REGRESSION_AND_FORCE = 2);
GO

EXEC sp_query_store_force_plan @query_id = your_query_id, @plan_id = plan_id_to_force;

Configuring Query Store

Query Store offers several configuration options to control data retention and capture behavior:

  • DATA_FLUSH_INTERVAL_SECONDS: The interval at which runtime statistics are flushed to disk.
  • MAX_STORAGE_SIZE_MB: The maximum size of the Query Store data.
  • QUERY_CAPTURE_MODE: Controls which queries are captured (e.g., AUTO, ALL, NONE).
  • MAX_PLANS_PER_QUERY: The maximum number of plans to retain per query.

You can modify these settings using the ALTER DATABASE SET QUERY_STORE command.

Next Steps

Explore the Query Store reports in SSMS or Azure Data Studio to identify performance bottlenecks. Use the information gathered to optimize your queries and database configuration.