SQL statements (Logical reads)

Job details

Parameters

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.