SQL 伺服器：SQL Server 延遲完全解密
您可以使用 SQL Server 交易等候時間的追蹤資料來尋找使用過度的資源並消除瓶頸。
摘自"SQL 伺服器 DMV 起動器包，"由紅門書 （2010 年） 出版。
葛籣 · 貝裡、 路易 · 大衛森和蒂姆 · 福特
每次 SQL Server 會話已請求的工作才能繼續等待由於某種原因，SQL Server 將記錄等待的時間的長度。 它還記錄事務正在等待的資源。
Sys.dm_os_wait_stats 動態管理視圖 (DMV) 公開這些等待統計資料，聚合跨提供簡要審查主要等待在哪裡在任何給定的實例上的所有會話。 此相同的 DMV 還公開 (PerfMon) 效能計數器，該提供特定的資源使用情況測量 （磁片傳輸速率的 CPU 時間消耗等等）。
資源測量地段等待統計，可以快速找到您的系統上的最"有爭議的"資源和突出的潛在瓶頸。
SQL Server 2005 在等待和佇列
"等待佇列"的使用性能調優的方法論基礎中的說明優秀白皮書由湯姆大衛森。 實質上，SQL 伺服器的每個請求將導致開始大量的"工作任務"。
SQL 伺服器調度程式將每個任務分配給一個工作執行緒。 通常是每個 CPU，一個 SQL 作業系統調度程式和調度程式運行在任何時間每只有一個會話。 它是傳播的工作量可用工作執行緒之間均勻地調度程式的工作。
如果會話的工作執行緒運行的處理器，運行的會話狀態將會"，"sys.dm_exec_requests 左方的狀態列所暴露出來。 如果一個執行緒已做好準備，但它分配給當前的計畫程式已另一個會話運行，然後將它放在"Runnable"佇列中。 這意味著它是要獲取處理器上的佇列中。 這被稱為信號等待。
信號等待時間由 signal_wait_time_ms 列中，公開的是純粹的 CPU 的等待時間。 如果會話正在等待另一個資源可用要繼續進行，如鎖定頁，或者正在運行的會話需要執行 i/o 操作，它被移動到等待清單中。 這是一個資源等待，等待會話狀態將被記錄為"掛起"。
等待的理由是記錄，並暴露在 sys.dm_os_wait_stats 登記處的 wait_type 列中。 花費的總時間等待由 wait_time_ms 列中，公開的所以您可以計算資源等待時間，如下所示：
資源等待總等待時間 — — 信號等待 = = (wait_time_ms)-(signal_wait_time_ms)
等待信號是在連線交易處理 (OLTP) 系統，包括大量的短事務的不可避免的。 關鍵指標，對潛在的 CPU 壓力是信號等待總等待時間的百分比。 高百分比信號是過多的 CPU 壓力的跡象。 文學的傾向于報價超過了 25%，為"高"，但這取決於您的系統。
我們的系統，我們對待大比 10%到 15%的值為令人擔憂的跡象。 總體而言，使用等待統計數字代表一種非常有效的手段診斷您的系統中的回應時間。 簡單來說，你的工作或你等。 回應時間等於服務時間加等待的時間。
如果您發現沒有重大的等待或主要信號等待回應速度很慢，您需要將重點放在 CPU 上。 如果您發現回應時間主要由等待其他資源 （如網路、 I/O 和等等），然後你知道在哪裡將您優化的努力集中時間組成。
拿出胡亂猜測
馬里奧 · Broodbakker 寫了一次精彩介紹性系列文章上使用等待事件診斷性能問題。 我們在 OS 類別中的第一個腳本使用 sys.dm_os_wait_stats 汽車登記處，其定義為：返回有關執行的執行緒所遇到的所有等待的資訊。 診斷性能問題與 SQL Server 以及特定查詢和批次處理，可以使用此匯總的視圖。
此查詢計算信號等待和資源在等待一個百分比表示的總等待時間，以便診斷潛在的 CPU 壓力：
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure) SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;
此查詢可用於説明確認 CPU 壓力。 因為信號等待時間等待 CPU 服務執行緒，如果記錄總信號等待它大致大比 10%到 15%，則表明這是一種很好的 CPU 壓力指標。
這些等待統計資料是累積自從上次重新開機 SQL Server，所以你需要知道你基準值的等待信號，和觀看隨著時間的推移的趨勢。 不重新開機伺服器，通過發出 DBCC SQLPERF 命令，如下，可手動清除出等待統計中：
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
如果您的 SQL Server 實例已經運行了很長時間，和進行重大更改像添加一個新的索引，那麼您應該考慮結算舊等統計資訊。 否則，舊的累積等待統計資料，將掩蓋無論您的更改已等待時間的影響。
我們使用 sys.dm_os_wait_stats 登記處的第二個示例腳本將有助於確定 SQL Server 的支出最的資源的時間等待：
-- 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' ) ) 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 < 95 ; -- percentage threshold
此腳本將會説明您找到實例級最大的瓶頸。 這可以説明您您優化的努力集中在一個特定類型的問題。 例如，如果累積頂部等待類型是我/O 有關，磁片，然後會要調查這一問題進一步使用磁片相關 DMV 查詢和效能監視器計數器。
公開的效能計數器
公開的效能監視器計數器的登記處是 sys.dm_os_performance_counters，被描述為：返回行每伺服器維護的效能計數器。這是非常有用的汽車登記處，但可以使用沮喪。 取決於 cntr_type 的給定的行的值，你得要通過一些有趣的起伏不定，此 DMV 從獲取有意義的資訊。 它是一個用於從 SQL Server 2000 的舊 sys.sysperfinfo 的替換。
此腳本可説明您進行調查不尋常的條件，填滿你的事務日誌。 它返回復原模式、 日誌重用等待描述、 事務日誌的大小、 使用的日誌空間、 日誌使用百分比、 相容級別和頁面驗證當前的 SQL Server 實例上為每個資料庫選項：
-- Recovery model, log reuse wait description, log file size, -- log usage size and compatibility level for all databases on instance SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db. name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db. name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;
此查詢可説明評估不熟悉資料庫伺服器。 也是從監管角度很有用的。 例如，如果您日誌重複使用等描述是不尋常的東西和你的事務日誌的 85%，滿，應觸發報警。
.png)
葛籣 · 貝裡 在科羅拉多州丹佛的 NewsGator 技術工作作為資料庫建築師 他是 SQL 伺服器 MVP，，微軟認證，包括 MCITP、 MCDBA、 MCSE、 MCSD、 MCAD 和 MCTS，這證明了他喜歡參加測試的整個集合。
.png)
路易 · 大衛森 已在 IT 業 16 年作為公司的資料庫開發人員和架構師。 他六年來一直 SQL 伺服器微軟 MVP，寫了四本書對資料庫的設計。 目前他是資料架構師和有時對於基督教廣播網路，支援辦事處在佛吉尼亞海灘、 弗吉尼亞州和田納西州納什維爾，DBA
.png)
蒂莫西 · 福特 SQL 伺服器 MVP，與 SQL Server 工作 10 年以上。 他是主 DBA 和 SQL Server 平臺譜健康的主題事項專家。 他曾是自 2007 年以來為 Web 網站的各種技術寫作並維護他自己的博客，在 thesqlagentman.com，以及辦公和專業發展主題作為覆蓋 SQL。
瞭解更多有關"SQL 伺服器 DMV 起動器包"在 red-gate.com/our-company/about/book-store。