Skip to content
  • There are no suggestions because the search field is empty.

T SQL Script: Azure SQL Find unused or over-used indexes

T SQL Script: Azure SQL Find unused or over-used indexes

-- Azure SQL Find Top IO Consumers (logical and physical reads)
SELECT TOP 20
    qs.total_logical_reads,
    qs.total_physical_reads,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_physical_reads / qs.execution_count AS AvgPhysicalReads,
    SUBSTRING(st.text, qs.statement_start_offset/2,
        (CASE WHEN qs.statement_end_offset = -1 
              THEN LEN(st.text) * 2 
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS StatementText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_logical_reads DESC;