Cached query statistics

Job details

Parameters

Job Summary for Cached Query Statistics

• Purpose: The purpose of this job is to gather and report performance metrics related to cached query plans in SQL Server versions between 2000 and 2017 where SQL Server is the engine edition specified.
• Why: Monitoring cached query statistics is critical as it helps in identifying performance bottlenecks and optimizing SQL queries. The data collected allows database administrators to analyze trends over time, understand the load and impact on the server resources, and take strategic decisions to improve query performance.
• Manual Checking:
  To manually check cached query statistics similar to what the dbWatch task performs, the following SQL command can be executed:

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

• Various views, functions, and procedures are used to gather and calculate the statistics:
• dbw_cached_query_stat_proc: Main stored procedure to collect data.

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

• The core implementation revolves around creating necessary SQL structures like tables and views, and inserting initial data based on existing cached queries.
• The task consistently updates and cleans up data, ensuring that the latest data is always available for analysis, and that historical data doesn’t grow uncontrollably.

Scheduling and Versions

• Scheduled to run at multiple specific times throughout the day (every few hours), which helps in maintaining a good balance between real-time analysis and resource optimization.
• The job script includes versioning and upgrading capability, ensuring that any move from one version to another is handled seamlessly, maintaining all the necessary database adjustments without losing any historic data.

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.