대랑 I/O 사용 세션 쿼리

SQL SERVER 2005 이상

1. I/O Pending 조회

-- =====================================
-- I/O pending
-- 어떤 row도 반환되지 않아야 한다.
-- =====================================
select 
    database_id, 
    db_name(database_id) as db_name,
    file_id,
    t2.io_type, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

2. 현재 db의 i/o stall 정보

-- ==========================
-- 현재 db의 i/o _io_virtual_file
-- ==========================
declare @db_name sysname
set @db_name = null
select db_name(database_id), file_id ,io_stall_read_ms ,
    cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms' ,
    num_of_reads ,
    cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' ,
    io_stall_write_ms ,num_of_writes ,
    cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' ,
    io_stall_read_ms + io_stall_write_ms as io_stalls ,
    num_of_reads + num_of_writes as total_io 
from sys.dm_io_virtual_file_stats(DB_ID(@db_name),null) 
order by avg_io_stall_ms desc

3. 대량의 i/o 사용 쿼리

-- ==========================
-- 대량의 i/o기준 상위
-- ==========================
SELECT TOP 10 qt.dbid,
     object_schema_name(qt.objectid,qt.dbid) + '.' + object_name(qt.objectid,qt.dbid) [object_name],
     (qs.total_logical_reads/execution_count) as avg_logical_reads,
     (qs.total_logical_writes/execution_count) as avg_logical_writes,
     (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count as [Avg IO],
    substring (qt.text,qs.statement_start_offset/2, 
    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text, 
    qt.objectid, object_name(qt.objectid) as sp_name
FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt ORDER BY [Avg IO] DESC