(─━┘_└━─)/ 머리만1톤(Head1Ton)

시스템의 대기 정보 확인하기

SELECT * FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC;

GO

 래치 대기 정보 확인하기

SELECT * FROM sys.dm_os_latch_stats;

GO

대기 통계, 래치 대기 통계 재설정하기

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

GO

DBCC SQLPERF('sys.dm_os_latch_stats', CLEAR);

GO

I/O 대기 확인하기

SELECT database_id

                           , file_id

                           , io_stall,io_pending_ms_ticks

                           , scheduler_address

FROM sys.dm_io_virtual_file_stats (NULL, NULL) AS t1,

     sys.dm_io_pending_io_requests AS t2

WHERE t1.file_handle = t2.io_handle;

GO

SELECT * FROM sys.dm_io_virtual_file_stats (NULL, NULL);

GO

SELECT * FROM sys.dm_io_pending_io_requests;

GO

I/O 할당 대기 확인하기

-- Tempdb (DBID=2)

SELECT session_id, wait_duration_ms, resource_description

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%'

AND resource_description LIKE '2:%';

GO

-- 사용자 DB (다음에서 dbid 부분을 db_id() 값을 입력하여 실행하면 됨 )

SELECT session_id, wait_duration_ms, resource_description

FROM sys.dm_os_waiting_tasks

WHERE wait_type LIKE 'PAGE%LATCH_%'

AND resource_description LIKE '5:%';

GO