SQL Server IOの高いクエリを抽出する

作成日 2021.10.21
Sql Server
SQL Server

SQL ServerでIOの高いクエリを抽出する手順を記述してます。

1. 環境
2. クエリを実行

環境

OS windows10 pro
SQL server2017 Version 14.0.3223.3
SSMS 18.7.1

クエリを実行

以下の確認用のクエリを実行します。実行プランも確認できます。

select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no
,       (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1

,       (total_worker_time+0.0)/1000 as total_worker_time
,       total_logical_reads as [LogicalReads]
,       total_logical_writes as [LogicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes as [AggIO]
,       (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO]
,       case when sql_handle IS NULL
                then ' '
                else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1        then len(convert(nvarchar(MAX),st.text))*2      else qs.statement_end_offset    end - qs.statement_start_offset) /2  ))
        end as query
,       db_name(st.dbid) as database_name
,       creation_time
,       last_execution_time
,       st.objectid as object_id
,		qs.sql_handle
,		qp.query_plan
from sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where (total_logical_reads+total_logical_writes ) > 0
order by [AvgIO] desc

実行結果