Monitor by using Query Store

The Query Store feature in Microsoft Azure SQL Solutions allows you to monitor the performance of your SQL queries and gain valuable insights into their behavior. By enabling the Query Store, you can capture query execution details and easily analyze their performance over time. In this article, we will explore how you can use the Query Store to monitor and optimize your Azure SQL databases.

Enabling the Query Store

Enabling the Query Store is a straightforward process. You can enable it at the database level using the Azure portal, Transact-SQL, or PowerShell. Once enabled, the Query Store starts collecting query execution statistics and related information. These statistics include metrics like execution time, CPU time, logical reads, physical reads, and more.

Accessing Query Store Information

To access the Query Store information, you can use various built-in views and functions. One such view is the sys.query_store_runtime_stats view, which provides real-time statistics about query performance. You can query this view to gather insights into query execution patterns, identify top resource-consuming queries, and detect performance regressions.

For example, the following Transact-SQL code retrieves the top 10 queries consuming the most CPU time from the Query Store:

SELECT TOP 10 query_text_id, SUM(cpu_time) AS total_cpu_time
FROM sys.query_store_runtime_stats
GROUP BY query_text_id
ORDER BY total_cpu_time DESC;

In addition to the sys.query_store_runtime_stats view, you can also utilize other views like sys.query_store_query_text, sys.query_store_plan, and sys.query_store_query to retrieve detailed information about individual queries and their execution plans.

Analyzing Query Performance over Time

Analyzing query performance over time is another essential aspect of the Query Store. You can compare query performance metrics across different time intervals using the sys.query_store_runtime_stats and sys.query_store_runtime_stats_interval views. These views allow you to track query execution behavior and identify long-term trends such as performance degradation or optimization.

To analyze query performance over time, you can use the following Transact-SQL code to retrieve the average duration of a query in each hour of the day for the last seven days:

SELECT DATEPART(HOUR, runtime_stats_interval_start_time) AS hour_of_day,
AVG(avg_duration) AS average_duration
FROM sys.query_store_runtime_stats_interval
WHERE runtime_stats_interval_start_time > DATEADD(DAY, -7, GETUTCDATE())
GROUP BY DATEPART(HOUR, runtime_stats_interval_start_time)
ORDER BY hour_of_day;

By monitoring query performance trends, you can proactively identify queries that require optimization or troubleshooting.

Forcing a Specific Execution Plan

The Query Store also provides the ability to force a specific execution plan for a query. This feature is beneficial when you encounter queries with suboptimal plans. You can use the sys.sp_query_store_force_plan stored procedure to force a plan chosen by the Query Store for a particular query.

For example, the following Transact-SQL code forces a specific plan for a query identified by its query ID:

EXEC sys.sp_query_store_force_plan @query_id = , @plan_id = ;

By forcing a plan, you can ensure consistent query performance and mitigate any plan regressions.

Conclusion

The Query Store feature in Microsoft Azure SQL Solutions enables you to monitor and optimize the performance of your SQL queries effectively. By leveraging its views and functions, you can gather insights into query execution patterns, track performance over time, and force specific plans when necessary. Utilizing the Query Store empowers you to identify and resolve performance issues, ensuring efficient query processing in your Azure SQL databases.