SQL Server：深入發掘效能問題
有幾個策略，確定什麼造成大多數的等待時間在SQL Server，所以您可以直接你性能調優。
摘自SQL ServerDMV 起動包，"由紅門書 （2010 年） 出版。
Glenn Berry, Louis Davidson 和 Tim Ford
讓我們直接在 OS 一級。 我們看的輔助執行緒的執行任務所需的我們的交易。 此類別中的動態管理物件 （目的地） — — 所有的名稱 sys.dm_os_—provide，開始非常詳細SQL Server與作業系統與硬體進行交互的方式的資訊。 你可以使用這些目的地管理組織要獲得各種各樣的性能問題的答案：
- Wait_stats DMO 告訴您什麼樣的東西等了SQL Server作業系統執行緒。
- Performance_counters 告訴你他們如何解碼和SQL Server效能計數器的值。
- Ring_buffers、 os_schedulers 或 wait_stats 將通知您 CPU 利用率關注。
- Sys_info 會給你賴以運行的SQL Server機器的特點。
- Sys_memory 或 process_memory 會告訴你如何你作為一個整體正在使用的記憶體。
- Memory_cache_counters 或 buffer_descriptors 告訴您如何正在使用的快取記憶體記憶體。
所有這些查詢的工作與SQL Server2005年、 2008年和 2008 R2。 他們也都需要查看伺服器狀態的許可權。 注意：中的物件，應將統稱為目的地管理組織。 然而，使用 DMOs 往往造成一些混亂與完全無關"分散式管理物件，"，所以它仍是很常見的資料庫管理員可以將目的地管理組織集體稱為"動態管理檢視或 Dmv。
SQL Server在等待
可以說在作業系統類別中的最重要車管所是 sys.dm_os_wait_stats。 每次會話有之前請求的工作可以繼續等待出於某種原因，SQL Server記錄的長度時間等待和SQL Server正在等待的資源。 Sys.dm_os_wait_stats 車管所公開這些等待統計資訊，聚合跨所有的會話 Id，給你的簡要評述主要等待在哪裡在給定的實例上。
這同一車管所還公開 (PerfMon) 的效能計數器，它們提供特定資源使用測量 （磁片傳輸速率，CPU 時間消耗等等）。 通過關聯等待統計資訊與資源的度量，可以快速在您系統上找到的最有爭議的資源和突出的潛在瓶頸。
等待和佇列
使用等待和佇列是性能調優，優秀基礎上解釋了在的白皮書，SQL Server2005年等待和佇列，" 由TomDavidson。 本質上，每個SQL Server請求結果正在發起的"工作任務"的數目。 SQL Server調度程式將每個任務分配給一個工作執行緒。 通常有一個 SQL 作業系統調度程式每個 CPU，並且只有一個會話，每個計畫程式可以運行在任何時間。
它是工作負荷均勻地分佈在可用工作執行緒的調度程式的工作。 如果會話的工作執行緒運行在處理器上，將運行會話的狀態，如狀態列的 sys.dm_exec_requests 車管所公開的。
如果一個執行緒已經準備好，但到調度程式，它被分配目前有另一個會話在運行，它將被放置在"可運行"佇列中。 這只不過意味著它要得到處理器上的佇列中。 這被稱為信號等。
信號等待時間
信號等待時間被公開的 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 等等），然後你知道你需要將您優化的努力集中在這些資源上花費的時間。
性能分析
我們第一個腳本在 OS 類別中的使用 sys.dm_os_wait_stats DMV，返回時所遇到的執行執行緒的所有等待有關的資訊。 您可以使用此聚合的視圖診斷性能問題與SQL Server總體和特定查詢和批次。
這個簡單的查詢計算信號等待時間和資源以便診斷潛在的 CPU 壓力等待的總等待時間的百分比：
-- Total waits are wait_time_ms (high signal waits indicate 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實例已運行的相當一段時間和你作出重大的更改，例如添加新的重要指標，您應該考慮清除舊的等候狀態。 否則，舊的累積等待統計資訊會掩蓋無論您的更改已在等待時間的影響。
我們第二個示例腳本 （請參見圖 1） 顯示了如何使用 sys.dm_os_wait_stats 車管所將説明確定的資源為其SQL Server開支最時間等。
圖 1 此腳本會生成一個報告關於于最等待的原因。
-- 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
中的腳本圖 1 可以説明您找到實例級的最大瓶頸。 這可説明您在特定類型的問題上將調整工作的重點。 例如，如果頂部的累積等類型是磁片我/O-相關，然後你想要調查此問題進一步使用磁片相關車管所查詢和效能計數器。
效能計數器
車管所公開的效能監視器計數器是 sys.dm_os_performance_counters。 這將返回每個效能計數器由伺服器維護的行。 這是一個非常有用的 DMV，但它會令人沮喪的工作。
根據 cntr_type 對於給定的行的值，您可能必須挖深為此 DMV 從獲取有意義的資訊。 它是從SQL Server2000年的舊 sys.sysperfinfo 的替代。
中的腳本圖 2 可以説明您調查不尋常條件填滿你的事務日誌。 它將返回復原模式、 日誌重用等待描述、 事務日誌的大小、 使用的日誌空間、 日誌使用的百分比、 相容性級別和頁面驗證在當前的SQL Server實例上為每個資料庫選項。
圖 2 確定什麼填滿使用此腳本的事務日誌
-- 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)%' ;
在查詢圖 2 它有助於評估不熟悉資料庫伺服器。 也是從監控的角度通常更有用。 例如，如果您的日誌重用等說明是像 ACTIVE_TRANSACTION，不尋常的東西和你事務日誌是 85%滿，然後應該有一些會響的警鐘。
使用這種類型的監測是非常有用的鑒別來源和等待和其他性能問題的原因。 這種類型的調查可以説明您更好地指導你性能調優的努力，在正確的地方。
.png)
Glenn Berry 工程作為資料庫設計師 NewsGator 技術公司。 在丹佛，科羅拉多州 他是最有價值球員的SQL Server，並且有 Microsoft 認證，包括 MCITP、 MCDBA、 MCSE、 MCSD、 MCAD 和 MCTS，證明他喜歡做測試的整個集合。
.png)
Louis Davidson 已經在 IT 行業 16 年作為公司的資料庫開發人員和架構師。 他六年來一直SQL Server微軟最有價值球員，並寫了四本書的資料庫設計。 目前他是資料架構師和有時 DBA 支援辦事處在佛吉尼亞海灘，弗吉尼亞州和田納西州納什維爾的基督教廣播網路
.png)
Timothy Ford 是最有價值球員的SQL Server和與SQL Server工作 10 年以上。 他是初級 DBA 和頻譜保健的SQL Server平臺的主題事項專家。 他曾自 2007 年以來為 Web 網站的各種有關技術寫作並維護他自己的博客在 thesqlagentman.com，涵蓋 SQL 作為遠端辦公和專業發展主題。
瞭解更多有關SQL ServerDMV 起動包"在紅 gate.com。