[SQL][個人筆記]Using Wait Statistics
SQL Server 在執行命令時，回應時間 = 處理時間 + 等待時間，因此考慮消除等待時間來提升執行時的效能，在這些處理過程中，我們多半都是透過 DMV 來查看，基本上是透過以下兩個來查看相關 Waiting 的時間
1. sys.dm_os_wait_stats
2. sys.dm_os_waiting_task
由於 sys.dm_os_wait_stats 內所存放的是從服務啟動以來的累計值，因此如果需要特定時間內的資訊，可透過以下指令清除 Wait 相關統計資訊
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
當被重新設定之後，我們可以利用以下的指令來查看相關系統重 Reset 之後的 Wait 的項目和時間 ( 參考資料來源做部分修正，縮短執行時間 )
WITH Waits AS
( SELECT
wait_type, wait_time_ms / 1000. AS wait_time_s,
signal_wait_time_ms/1000. AS signal_time_s,
( wait_time_ms-signal_wait_time_ms)/1000. AS resource_time_s,
waiting_tasks_count,
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 waiting_tasks_count > 0 AND wait_time_ms > 0 AND
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.signal_time_s AS DECIMAL(12, 2)) AS signal_time_s,
CAST(W1.resource_time_s AS DECIMAL(12, 2)) AS resource_time_s,
W1.waiting_tasks_count AS waitcount,
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.signal_time_s,W1.resource_time_s, W1.waiting_tasks_count, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95 OPTION (RECOMPILE);
如果要從指令的角度來查看的話，則可以使用以下的指令來進行查看
SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
-- Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
OUTER APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t
OUTER APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
CXPACKET
如果 Wait 有很多是耗在 CXPACKET 的時候，可能是因為查詢指令因為 CPU or IO 的估算的耗用成本高於設定值，使得 SQL Server 使用多執行緒來做處理，基本上有可能造成的原因有些時候是因為指令沒有符合 SARG、或者是資料型別的轉換導致過多的 TABLE or INDEX SCAN，因此除了調整相關的設定值之外，另外最好也要查看語法是否有需要改善。一般來說平行處理原則的最大程度，建議設定核心數的 1/2 或者是 1/4
SOS_SCHEDULER_YIELD
正常來說 SQL Server 會儘量讓一個 Thread 一次做完，但當 CPU 負荷過高的時候，則會造成 request 是在 RUNNABLE 的狀態，因此可以透過查看 CPU 每個核心的分配處理狀況
SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count, pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
PAGEIOLATCH_*
一般來說 CPU , Memory & 磁碟IO 是影響 SQL Server 三個主要影響 SQL Server 伺服器的效能關鍵，如果大部分等待都是在 PAGEIOLATCH 相關上，則可能要注意是否有發生 I/O 瓶頸的問題，這類的處理可以透過 Windows 所提供的 Perfmon 來查看相關磁碟狀況，也可以透過以下的指令查看針對每個資料庫檔案的讀寫狀況
SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name AS logical_name, mf.physical_name,
vfs.IoStallReadMS / ( case when vfs.NumberReads >0 THEN vfs.NumberReads else 1 end ) AS avg_read_latency_ms,
vfs.IoStallWriteMS / ( case when vfs.NumberWrites >0 THEN vfs.NumberWrites else 1 end ) AS avg_write_latency_ms,
vfs.BytesRead, vfs.BytesWritten,
vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,
vfs.NumberReads, vfs.NumberWrites,
(Size*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId AND mf.FILE_ID = vfs.FileId而我自己在處理上有時比較偷懶就直接用 Crystal Diskmark 來查看相關數據。
雖然磁碟 I/O 的效能是一個很大的影響關鍵，但有時候可能因為索引破碎、過多沒有使用的索引、記憶體不足等狀況，也會相對造成因為磁碟 I/O 過慢而影法的效能問題，因此這個可能是一個造成效能不佳的原因，但不見得就會是根本的問題。
IO_COMPLETION
跟 PAGEIOLATCH 類似，但 IO_COMPLETION 主要是發生在 non-data page 上面，因此檢測方式和處理方法也都雷同。
LOCK_M_*
鎖定是資料庫維持交易一致性的一種保護機制，避免當多人同時修改到相同資料，一般我們可以透過 sys.dm_exec_requests 裡面查到是否有 session 被其他的 session 給 block 造成等待，相關語法可以參考「利用 DMV 和 SQL Agent 自動刪除一些造成其他作業被 Blocking 的連線」，而文件上和網路上也有介紹類似的 DMV , 也可以有類似的效果。
SELECT
tl.request_session_id,
wt.blocking_session_id,
DB_NAME( p.resource_database_id ) DatabaseName,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO
資料來源 :
1. http://blog.sqlauthority.com/
2. SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide