微軟官方提供的用于監控MS SQL Server運作狀況的工具及SQL語句示例查詢監控 CPU 瓶頸記憶體瓶頸I/O 瓶頸阻塞

Microsoft SQL Server 2005 提供了一些工具來監控資料庫。方法之一是動态管理視圖。動态管理視圖 (DMV) 和動态管理函數 (DMF) 傳回的伺服器狀态資訊可用于監控伺服器執行個體的運作狀況、診斷問題和優化性能。

正常伺服器動态管理對象包括：

dm_db_*：資料庫和資料庫對象

dm_exec_*：執行使用者代碼和關聯的連接配接

dm_os_*：記憶體、鎖定和時間安排

dm_tran_*：事務和隔離

dm_io_*：網絡和磁盤的輸入/輸出

此部分介紹為監控 SQL Server 運作狀況而針對這些動态管理視圖和函數運作的一些常用查詢。

您可以運作以下查詢來擷取所有 DMV 和 DMF 名稱：

CPU 瓶頸通常由以下原因引起：查詢計劃并非最優、配置不當、設計因素不良或硬體資源不足。下面的常用查詢可幫助您确定導緻 CPU 瓶頸的原因。

下面的查詢使您能夠深入了解目前緩存的哪些批處理或過程占用了大部分 CPU 資源。

下面的查詢顯示緩存計劃所占用的 CPU 總使用率（帶 SQL 文本）。

下面的查詢顯示 CPU 平均占用率最高的前 50 個 SQL 語句。

下面顯示用于找出過多編譯/重新編譯的 DMV 查詢。

下面的示例查詢顯示已重新編譯的前 25 個存儲過程。plan_generation_num 訓示該查詢已重新編譯的次數。

效率較低的查詢計劃可能增大 CPU 占用率。

下面的查詢顯示哪個查詢占用了最多的 CPU 累計使用率。

下面的查詢顯示一些可能占用大量 CPU 使用率的運算符（例如 ‘%Hash Match%’、‘%Sort%’）以找出可疑對象。

如果已檢測到效率低下并導緻 CPU 占用率較高的查詢計劃，請對該查詢中涉及的表運作 UPDATE STATISTICS 以檢視該問題是否仍然存在。然後，收集相關資料并将此問題報告給 PerformancePoint 規劃支援人員。

如果您的系統存在過多的編譯和重新編譯，可能會導緻系統出現與 CPU 相關的性能問題。

您可以運作下面的 DMV 查詢來找出過多的編譯/重新編譯。

如果已檢測到過多的編譯或重新編譯，請盡可能多地收集相關資料并将其報告給規劃支援人員

開始記憶體壓力檢測和調查之前，請確定已啟用 SQL Server 中的進階選項。請先對 master 資料庫運作以下查詢以啟用此選項。

首先運作以下查詢以檢查記憶體相關配置選項。

運作下面的 DMV 查詢以檢視 CPU、計劃程式記憶體和緩沖池資訊。

檢查闩鎖等待統計資訊以确定 I/O 瓶頸。運作下面的 DMV 查詢以查找 I/O 闩鎖等待統計資訊。

如果 waiting_task_counts 和 wait_time_ms 與正常情況相比有顯著變化，則可以确定存在 I/O 問題。擷取 SQL Server 平穩運作時性能計數器和主要 DMV 查詢輸出的基線非常重要。

這些 wait_types 可以訓示您的 I/O 子系統是否遇到瓶頸。

使用以下 DMV 查詢來查找目前挂起的 I/O 請求。請定期執行此查詢以檢查 I/O 子系統的運作狀況，并隔離 I/O 瓶頸中涉及的實體磁盤。

在正常情況下，該查詢通常不傳回任何内容。如果此查詢傳回一些行，則需要進一步調查。

您還可以執行下面的 DMV 查詢以查找 I/O 相關查詢。

下面的 DMV 查詢可用于查找哪些批處理/請求生成的 I/O 最多。如下所示的 DMV 查詢可用于查找可生成最多 I/O 的前五個請求。調整這些查詢将提高系統性能。

運作下面的查詢可确定阻塞的會話。

使用此調用可找出 blocking_session_id 所傳回的 SQL。例如，如果 blocking_session_id 是 87，則運作此查詢可獲得相應的 SQL。

下面的查詢顯示 SQL 等待分析和前 10 個等待的資源。

若要找出哪個 spid 正在阻塞另一個 spid，可在資料庫中建立以下存儲過程，然後執行該存儲過程。此存儲過程會報告此阻塞情況。鍵入 sp_who 可找出 @spid；@spid 是可選參數。

以下是使用此存儲過程的示例。

from http://technet.microsoft.com/zh-cn/library/bb838723.aspx