Using Query Store in SQL Server for Performance Analysis

Blogs

Fortify your SQL Server: The Ultimate Guide to Always Encrypted
September 10, 2024
A Beginner’s Guide to Spark Compute in Microsoft Fabric: Starter and Custom Pools
September 11, 2024

Using Query Store in SQL Server for Performance Analysis

SQL Server Query Store is a powerful feature introduced in SQL Server 2016 that helps database administrators and developers track query performance over time. By providing insights into query execution, execution plans, and performance trends, Query Store makes it easier to troubleshoot slow-running queries and optimize database performance.

What is Query Store?

Query Store is often referred to as the “flight recorder” for SQL Server, as it continuously captures a history of query executions, along with their performance metrics and associated execution plans. Unlike dynamic management views (DMVs) that reset after a server restart, Query Store retains its data even after restarts, making it a reliable tool for long-term performance tracking.

Key capabilities include:

  • Tracking query performance over time
  • Storing multiple query execution plans
  • Identifying performance regressions
  • Offering insights into query plan changes

Enabling Query Store

Query Store is disabled by default on SQL Server databases. We can enable query store by running the below command:

ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON;

We can also configure specific settings such as the retention period, data size, and collection intervals. Here’s an example:

ALTER DATABASE [DatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 1000, INTERVAL_LENGTH_MINUTES = 15);

Understanding Query Store Components

  1. Runtime Statistics: This tracks how long queries take to execute, how many times they’ve been run, and other important performance metrics like CPU time and I/O.
  2. Execution Plans: Query Store captures every execution plan a query has used over time, allowing you to compare past and present plans.
  3. Wait Statistics: This shows where queries are spending the most time waiting, which is critical for diagnosing performance bottlenecks.

Analyzing Query Performance

Once Query Store is enabled, we can start leveraging its data to analyze query performance and execution plan behavior.

  1. Query Store Views

SQL Server provides several system views to query Query Store data:

  • sys.query_store_query: Contains information about individual queries.
  • sys.query_store_plan: Provides details about execution plans associated with queries.
  • sys.query_store_runtime_stats: Offers runtime metrics like duration, CPU usage, and I/O for each execution plan.

We can retrieve performance data using the below query:

SELECT

q.query_id,

qsqt.query_sql_text,

rs.avg_duration,

rs.avg_cpu_time,

rs.avg_logical_io_reads

FROM sys.query_store_query AS q

JOIN sys.query_store_plan AS qp ON q.query_id = qp.query_id

JOIN sys.query_store_runtime_stats AS rs ON qp.plan_id = rs.plan_id

JOIN sys.query_store_query_text AS qsqt ON q.query_text_id = qsqt.query_text_id

ORDER BY rs.avg_duration DESC;

This query shows the slowest queries by average duration, along with their execution stats.

  1. Built-in Reports

SQL Server Management Studio (SSMS) provides built-in Query Store reports under the database context:

  • Top Resource Consuming Queries: Identify the queries using the most resources.
  • Regressed Queries: Track queries whose performance has worsened over time.
  • Tracked Query Plans: View queries that have had multiple execution plans and compare their performance across those plans.

Handling Query Plan Regressions

One of the key benefits of Query Store is its ability to capture multiple execution plans for a single query. This helps to identify when SQL Server switches to a less efficient plan.

When we notice a query plan regression, we can force a better-performing plan using Query Store. This prevents SQL Server from using suboptimal plans in the future.

Here’s how we can force a query plan:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 3;

To unforce a plan if needed:

EXEC sp_query_store_unforce_plan @query_id = 1;

Using Query Store for Performance Tuning

Query Store enables proactive performance tuning through the following strategies:

  1. Identify Long-Running Queries: By analyzing runtime statistics, we can easily spot slow queries and work on tuning them.
  2. Monitor Query Plan Changes: Keep track of execution plan changes and ensure that SQL Server is always using the most efficient plan.
  3. Resolve Regressions Quickly: By forcing efficient plans when regressions occur, we can quickly mitigate performance issues without significant downtime.
  4. Historical Trend Analysis: Query Store allows us to look back at performance data over time, making it easier to understand the long-term impact of database changes.

Best Practices for Query Store

To get the most out of Query Store, follow these best practices:

  • Monitor Query Store Size: Set a reasonable size limit for Query Store data (e.g., 1000 MB) to avoid unnecessary storage overhead.
  • Regularly Review Queries: Periodically check for regressed queries and top resource consumers to keep your SQL Server performing optimally.
  • Use Automatic Cleanup: Enable automatic cleanup of old data to prevent Query Store from consuming too much storage:
    • ALTER DATABASE [YourDatabaseName]

    SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

  • Tuning Intervals: Adjust the data collection interval based on your workload to ensure that Query Store captures meaningful data without overwhelming the system.

Conclusion

Query Store is an indispensable tool for analyzing and tuning SQL Server performance. By leveraging its capabilities, you can track query behavior, identify bottlenecks, and optimize query plans, all while maintaining a history of performance data. Whether you are a DBA or a developer, enabling and using Query Store should be a key part of your SQL Server performance monitoring strategy.

 


Pramodh P

Leave a Reply

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