[MSSQL] 특정 Wait 상태와 그와 관련된 Resource 조회

PAGEIOLATCH 리소스 조회 쿼리입니다.

Wait type을 변경하면 다른 리소스에 대한 것들도 조회가 가능합니다.

/*

           resource_description 컬럼

                     database_id:file_id:page_id 로구분되어짐.

*/

 

-- 현재PAGELATCH Wait를구함

select session_id, wait_type, resource_description

from sys.dm_os_waiting_tasks

where wait_type like 'PAGELATCH%'

 

-- PAGELATCH의Resource 상세정보를구함

select wt.session_id, wt.wait_type, wt.wait_duration_ms

, s.name as schema_name

, o.name as object_name

, i.name as index_name

from sys.dm_os_buffer_descriptors bd

join (

           select *

           , CHARINDEX(':', resource_description) as file_index

           , CHARINDEX(':', resource_description, charindex(':', resource_description)) as page_index

           , resource_description as rd

           from sys.dm_os_waiting_tasks wt

           where wait_type like 'PAGELATCH%'

           ) as wt

on bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index) and bd.file_id = SUBSTRING(wt.rd, wt.file_index, wt.page_index)

and bd.page_id = SUBSTRING(wt.rd, wt.page_index, len(wt.rd))

join sys.allocation_units au on bd.allocation_unit_id = au.allocation_unit_id

join sys.partitions p on au.container_id = p.partition_id

join sys.indexes i on p.index_id = i.index_id and p.object_id = i.object_id

join sys.objects o on i.object_id = o.object_id

join sys.schemas s on o.schema_id = s.schema_id