SQL statements (Logical reads)
Job details
Name: |
SQL statements (Logical reads) |
Platform: |
Sqlserver |
Category: |
Performance |
Description: |
Collects statistics for the SQL statements corresponding to the query plans that have resulted in the most logical reads. |
Long description: |
Collects statistics for the SQL statements corresponding to the query plans that have resulted in the most logical reads. |
Version: |
1.8 |
Default schedule: |
16 6,12,15 * * |
Requires engine install: |
Yes |
Compatibility tag: |
.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ |
engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
Name |
Default value |
Description |
min logical reads |
3000000 |
The minimum number of logical reads, per execution, before statistics for a statement will be inserted into the historic table. |
max history records |
100 |
The maximum number of records kept in the historic table (ordered by last execution time). |
Job Summary
- Purpose: The job is designed to monitor SQL statements based on logical reads across instances running on SQL Server or Azure SQL Managed Instance that are higher than version 2000. It aims to track and document SQL statements and query plans that result in high numbers of logical reads, supporting performance analysis and optimization efforts.
- Why: Monitoring logical reads is crucial because high logical reads can indicate inefficient query performance or indexing issues, potentially leading to reduced application performance and increased resource consumption. By capturing these metrics, database administrators can identify problematic queries and make necessary adjustments.
- Manual checking: To manually check this activity on the database, the following SQL commands can be applied:
SELECT qs.sql_handle,
qs.execution_count,
qs.total_worker_time,
qs.total_elapsed_time,
qs.total_physical_reads,
qs.total_logical_reads,
qs.last_logical_reads,
qs.min_logical_reads,
qs.max_logical_reads,
qs.last_execution_time,
qs.creation_time
FROM sys.dm_exec_query_stats AS qs
WHERE qs.total_logical_reads >= 3000000
ORDER BY qs.total_logical_reads DESC
Implementation Details
- The job queries the dynamic management views of SQL Server to fetch execution statistics focusing on logical reads that exceed a given threshold.
- The results are stored and managed in historical tables named
dbw_sql_logical_reads
and dbw_sql_logical_reads_histr
, ensuring that data concerning SQL statement executions remains available for performance analysis and trend monitoring.
- A cursor-based SQL procedure is employed inherently to cycle through queried data, applying filters on logical reads and comparing entries with historical data to keep or update records accordingly.
- The procedure incorporates error handling and is designed to clean up historical records based on the maximum number of records and order them by the last execution time to maintain a manageable and relevant dataset.
Dependencies and Cleanup
- The job relies on the correct functioning of SQL Server’s dynamic management views, specifically
sys.dm_exec_query_stats
, in addition to internal task dependencies on the historical tables it manipulates.
- In case of failure, specified cleanup operations include the removal of created temporary tables and procedures to avoid clutter or interference with subsequent operations.
Tables and Results Descriptions
- SQL statements (Logical Reads): This main presentation table dynamically reflects the attributes and metrics of query executions, such as the count of executions, total and average time, and logical reads.
- Each line item represents a captured plan’s statistics, emphasizing on high logical read counts, which are key to determining the need for possible query or index optimizations.
Maintenance and Upgrades
- The implemented procedures provide detailed diagnostic messages and error handling mechanisms to ease troubleshooting and ensure consistency of performance data.
- For upgrades from older versions (1.6, 1.7 to 1.8), alterations to the existing procedure include standardized parameter fetching and enhanced error reporting, potentially to align with new analytical requirements or schema changes in
sys.dm_exec_query_stats
.
Automation Features
- The job is scheduled to run at specified times (6 AM, 12 PM, and 3 PM daily), which are adjustable to suit environment-specific needs for monitoring frequency.
- Enhancements such as the express-install characteristic suggest ease in setup with minimal manual configuration, emphasizing initiatives toward operational efficiency and automation in database monitoring environments.