來自 sql server 的高磁碟 I/O 還是高磁碟 I/O 會減慢 sql server？

我一直在與 DBA 和幾個硬體人員就我們 SQL 伺服器的性能問題爭論不休。通常一切都很好，但是在過去的幾周里，我們在 sql server 中遇到了巨大的延遲峰值。很明顯，SQL Server 正在等待磁碟 I/O。但我一直被告知這是因為 SQL Server 要求異常高的 I/O。事實並非如此。我從執行中可以看出並沒有什麼異常，DBA 關心的只是什麼導致了阻塞等等，這是沒有用的。例如，我們看到的主要備份是對 ASPState 數據庫的操作，我們使用它來管理 Web 伺服器上的 ASP 會話狀態。這些操作通常不會出現在 Sp_who2 活動結果中，因為它們發生得如此之快。數據庫處於簡單恢復模式，日誌記錄很少。然而，在這些滯後峰值期間，我們可以看到數據庫上的許多選擇和更新操作被阻塞或等待。我確定正在發生的事情是某人或某項工作正在執行某些東西，這導致用於該數據庫日誌和數據文件的 RAID 陣列上的磁碟使用量很大。問題正在證明這一點，因為沒有人願意承認他們正在做的事情正在扼殺我們的網站。

我的問題是我可以記錄哪些性能計數器或任何內容來幫助顯示 SQL Server 正在等待 I/O，但不是因為它要求的超出正常範圍，而是因為磁碟忙於響應來自 sql server 的請求像往常一樣快？

查看以下性能計數器：

• SQL Server，緩衝區管理器對象：

Page lookups/sec

Page reads/sec

Readahead pages/sec

• SQL Server，訪問方法對象

Full Scans/sec

Range Scans/sec

Skipped Ghosted Records/sec

• SQL Server，等待統計對象

Page IO latch waits

驅動大量 IO 請求的 SQL Server 將得到大量掃描、頁面查找和頁面讀取增加以及頁面 IO 閂鎖等待量增加的證實。值得嘗試查看sys.dm_exec_query_stats具有高物理讀取計數的條目。他們可以迅速查明罪魁禍首。

一般來說，將問題作為性能故障排除問題來處理，遵循等待和隊列等方法是正確的方法。你的 DBA 似乎在做正確的事，所以你應該聽他的。

開始使用 Glenn Berry 的診斷查詢和 Adam Machanic 的SP_Whoisactive來找出真正發生的事情。

首先通過執行此查詢查看哪些數據庫文件的 IO 瓶頸最多（Glenn Berry 的查詢）

SELECT  DB_NAME(fs.database_id) AS [Database Name] ,
       mf.physical_name ,
       io_stall_read_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] ,
       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]
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
       INNER JOIN sys.master_files AS mf WITH ( NOLOCK ) ON fs.database_id = mf.database_id
                                                            AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC
OPTION  ( RECOMPILE );

然後執行此查詢以查看您的伺服器正在等待的前十個事件（由Jonathan Kehayias查詢）。您還可以從 Glenn Berry 診斷查詢中找到類似的查詢。

SELECT TOP 10
       wait_type ,
       max_wait_time_ms wait_time_ms ,
       signal_wait_time_ms ,
       wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
       100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
       100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
       100.0 * ( wait_time_ms - signal_wait_time_ms )
       / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0 -- remove zero wait_time
       AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH',
 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK',
 'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT',
 'FT_IFTSHC_MUTEX', 'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
 'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
 'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR',
 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

一旦掌握了這些資訊，解決問題就會容易得多。

引用自：https://dba.stackexchange.com/questions/16492