Cached query statistics


Job details

Name: Cached query statistics
Platform: Sqlserver
Category: Performance
Description: This task returns aggregate performance statistics based on cached query plans in SQL Server.
Long description: This task returns aggregate performance statistics based on cached query plans in SQL Server (the sys.dm_exec_query_stats dynamic management view). The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view which might produce inaccurate results if there are many plans removed between sampling of statistics by the dbWatch task.
Version: 1.1
Default schedule: 16 2,5,8,11,14,17,20,23 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & maj_version < ‘2017′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name Default value Description
keep data for 3 Number of days to provide statistics for.

Job Summary for Cached Query Statistics

SELECT qs.sql_handle, qs.execution_count,
qs.total_logical_reads, qs.total_physical_reads,
st.text 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC

Job Components Analysis

SQL Server Version and Edition Restrictions

The job specifically targets instances of Microsoft SQL Server with versions from 2001 up to 2016.

SQL Objects and Dependencies

Data Recorded and Analysis Process

The job takes snapshots of various performance metrics like logical reads, logical writes, physical reads, and CPU time associated with cached queries. The reporting templates provide insights in the form of charts and result sets which include metrics per database, trend analysis across dates, and a snapshot of top consuming queries.

Automated vs Manual Monitoring

Unlike manual queries that provide static snapshots, this automated monitoring job collects, aggregates, and reports data across various metrics periodically without the need for manual intervention, reducing the chance for human error and ensuring a continuous review of database performance.

Implementation and Execution Details

Scheduling and Versions

Benefits and Impact

Monitoring cached query statistics can significantly help in tuning SQL Server’s performance by providing insights into how queries are processed and identifying inefficient queries. Continuous monitoring helps in proactive adjustments to the database, preventing severe performance degradation and potential downtime.

Conclusion

This dbWatch monitoring job is a vital tool in the arsenal of a database administrator looking to enhance the performance and stability of their SQL Server databases through effective query optimization and resource allocation.