Introduction to Wait Stats and Wait Types

Không phải tất cả các request đến SQL Server đều được đáp ứng ngay lập tức,  mà có thể chúng phải chờ 1 resource nào đó, như I/O (ổ cứng), ram, CPU...Ta có thể biết được mỗi câu truy vấn đang chờ đợi cái gì, và resource nào được chờ nhiều nhất.

Đoạn script thu thập Wait Stats và Wait Types trong SQL Server. Đoạn script này không cho ta biết mỗi câu SQL đang chờ resource gì.
-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold
GO

Nếu muốn clear Wait Stats:

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

Đoạn script này cho ta biết câu SQL đang chờ resource gì:

Nguồn: http://blog.sqlauthority.com/2011/02/01/sql-server-introduction-to-wait-stats-and-wait-types-wait-type-day-1-of-28/ Check wait type by SessionID

SELECT session_id, wait_type, wait_time, start_time, *
FROM SYS.dm_exec_requests
WHERE session_id > 50
GO