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

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

Dependencies and Cleanup

Tables and Results Descriptions

Maintenance and Upgrades

Automation Features