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
- 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.