Introduction

In the dynamic realm of database management, monitoring and optimizing query performance is paramount. SQL Server offers the Query Store feature, a powerful tool that captures and stores detailed information about query execution plans and runtime statistics. In this blog post, we’ll delve into leveraging T-SQL to extract valuable insights from the Query Store, focusing on queries executed within the last 7 days.

Query Store Essentials

Before we dive into T-SQL scripts, let’s briefly revisit the Query Store. It acts as a repository for query-related data, allowing database administrators to analyze historical performance metrics and troubleshoot issues efficiently.

Comprehensive Query Analysis in the Last 7 Days

To get a comprehensive view of queries executed in the last 7 days, we can use the following T-SQL script:

-- T-SQL script to analyze queries executed in the last 7 days

SELECT 
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
	ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) total_logical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) total_physical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) total_logical_io_writes,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.first_execution_time BETWEEN (getdate()-7) AND (getdate())
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY count_executions DESC

This script provides a detailed breakdown of query performance, including execution counts, total duration, CPU time, and more, for queries executed in the last 7 days.

Specific Query Search Example

Suppose you want to focus on queries containing a specific keyword within the last 7 days. The following T-SQL script demonstrates how to achieve this:

-- T-SQL script to search for a specific query in the last 7 days

SELECT 
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
	ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) total_logical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) total_physical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) total_logical_io_writes,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.first_execution_time BETWEEN (getdate()-7) AND (getdate())
and qt.query_sql_text LIKE 'DELETE FROM%'
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY count_executions DESC

This example narrows down the search to queries with the keyword “DELETE FROM” executed in the last 7 days.

Conclusion

In conclusion, harnessing the power of T-SQL with the SQL Server Query Store provides a robust way to analyze and optimize query performance. The provided scripts offer a starting point for gaining valuable insights into the queries executed in the last 7 days, enabling proactive database management and performance tuning.

Stay tuned for more in-depth explorations of SQL Server features and best practices!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.