原文: [SQL Server優化]善用系統監視器，確定系統瓶頸

來自:? http://hi.baidu.com/solorez/blog/item/f82038fa0e71b78d9e51468c.html

如果您運行的是 Microsoft Windows 服務器操作系統，則可以使用系統監視器圖形工具來測量系統當前的性能參數。可以查看包括處理器、內存、緩存、線程和進程等數據。每個對象都有一個相關的的計數器集，用于測量設備使用情況、隊列長 度、延時情況，另外還有吞吐量及內部擁塞指示器。

監視磁盤活動涉及兩個主要方面：

監視磁盤 I/O 及檢測過度換頁
隔離 SQL Server 產生的磁盤活動
監視磁盤 I/O 及檢測過度換頁
可以對下面兩個計數器進行監視以確定磁盤活動：

PhysicalDisk: % Disk Time
PhysicalDisk: Avg. Disk Queue Length
在系統監視器中，PhysicalDisk: % Disk Time 計數器監視磁盤忙于讀/寫活動所用時間的百分比。如果 PhysicalDisk: % Disk Time 計數器的值較高（大于 90%），請檢查 PhysicalDisk: Current Disk Queue Length 計數器了解等待進行磁盤訪問的系統請求數量。等待 I/O 請求的數量應該保持在不超過組成物理磁盤的軸數的 1.5 到 2 倍。大多數磁盤只有一個軸，但獨立磁盤冗余陣列 (RAID) 設備通常有多個軸。硬件 RAID 設備在系統監視器中顯示為一個物理磁盤。通過軟件創建的多個 RAID 設備在系統監視器中顯示為多個實例。

可以使用 Current Disk Queue Length 和 % Disk Time 計數器的值檢測磁盤子系統中的瓶頸。如果 Current Disk Queue Length 和 % Disk Time 計數器的值一直很高，則考慮下列事項：

使用速度更快的磁盤驅動器。
將某些文件移至其他磁盤或服務器。
如果正在使用一個 RAID 陣列，則在該陣列中添加磁盤。
如果使用 RAID 設備，% Disk Time 計數器會指示大于 100% 的值。如果出現這種情況，則使用 PhysicalDisk: Avg. Current Disk Queue Length 計數器來確定等待進行磁盤訪問的平均系統請求數量。

I/O 依賴的應用程序或系統可能會使磁盤持續處于活動狀態。

監視 Memory: Page Faults/sec 計數器可以確保磁盤活動不是由分頁導致的。在 Windows 中，換頁的原因包括：

配置進程占用了過多內存。
文件系統活動。
如果在同一硬盤上有多個邏輯分區，請使用 Logical Disk 計數器而非 Physical Disk 計數器。查看邏輯磁盤計數器有助于確定哪些文件被頻繁訪問。當發現磁盤有大量讀/寫活動時，請查看讀寫專用計數器以確定導致每個邏輯卷負荷增加的磁盤活動類型，例如，Logical Disk: Disk Write Bytes/sec。



隔離 SQL Server 產生的磁盤活動

可以進行監視以確定由 SQL Server 組件生成的 I/O 活動量的兩個計數器為：

SQL Server:Buffer Manager:Page reads/sec
SQL Server:Buffer Manager:Page writes/sec
在系統監視器中，這些計數器通過檢查以下操作的性能監視由 SQL Server 組件生成的 I/O 活動量。

向磁盤寫入頁
從磁盤讀取頁
如 果這些計數器的值達到硬件 I/O 子系統的容量限制，則需要減小這些值，方法是調整應用程序或數據庫以減少 I/O 操作（如索引覆蓋、索引優化或規范化），增加硬件的 I/O 容量或添加內存。例如，您可以使用數據庫引擎優化顧問分析典型的 SQL Server 工作負荷，并提出有關索引、索引視圖和分區的建議以提高服務器的性能。有關數據庫引擎優化顧問的詳細信息，請參閱優化物理數據庫設計。


監視CPU的使用率


定 期監視 Microsoft SQL Server 實例以確定 CPU 使用率是否在正常范圍內。持續的高 CPU 使用率可能表明需要升級 CPU 或需要增加多個處理器。或者，高 CPU 使用率也可能表明應用程序的調整或設計不良。優化應用程序可以降低 CPU 的使用率。

一個確定 CPU 使用率的有效方法是使用系統監視器中的 Processor:% Processor Time 計數器。該計數器監視 CPU 執行非閑置線程所用的時間。持續 80% 到 90% 的狀態可能表明需要升級 CPU 或需要增加更多的處理器。對于多處理器系統，應為每個處理器監視一個該計數器的獨立實例。這一值代表了在一個特定處理器上的處理器時間之和。若要確定所有 處理器的平均時間，請使用 System: %Total Processor Time 計數器。

另外還可以監視下列計數器來監視處理器的使用率：

Processor: % Privileged Time
對應于處理器執行 Microsoft Windows 內核命令（例如處理 SQL Server I/O 請求）所用時間的百分比。如果 Physical Disk 計數器的值很高時該計數器的值也一直很高，則考慮安裝速度更快或效率更高的磁盤子系統。
注意：
不同的磁盤控制器和驅動程序所用的內核處理時間不同。高效的控制器和驅動程序所用的特權時間較少，可留出更多的處理器時間給用戶應用程序，從而提高總體的吞吐量。
Processor: %User Time
對應于處理器執行用戶進程（例如 SQL Server）所用時間的百分比。
System: Processor Queue Length
對應于等待處理器時間的線程數。當一個進程的線程需要的處理器循環數超過可獲得的循環數時，就產生了處理器瓶頸。如果有很多進程在爭用處理器時間，可能需要安裝一個速度更快的處理器。如果使用的是多處理器系統，則可以增加一個處理器。
檢 查處理器使用率時，需考慮 SQL Server 實例執行的工作類型。如果 SQL Server 正在做大量的運算，例如包含聚合的查詢，或受內存限制但不需要磁盤 I/O 的查詢，此時所用的處理器時間可能是 100%。如果這導致其他應用程序的性能降低，應嘗試改變工作負荷。例如，讓計算機只運行 SQL Server 實例。

若使用率為 100% 左右（表示在處理大量的客戶端請求），可能表示進程正在排隊，等待處理器時間，并因而導致出現瓶頸。可以通過增加速度更快的處理器來解決這一問題。


監視內存使用量


定期監視 SQL Server 的實例以確認內存使用量在正常范圍內。

若要監視內存不足的情況，請使用下列對象計數器：

Memory: Available Bytes
Memory: Pages/sec
Available Bytes 計數器指示當前有多少內存（以字節為單位）可供進程使用。Pages/sec 計數器指示由于頁錯誤而從磁盤取回的頁數，或由于頁錯誤而寫入磁盤以釋放工作集空間的頁數。

Available Bytes 計數器的值低表示計算機總內存不足或應用程序沒有釋放內存。Pages/sec 計數器的比率高表示分頁過多。監視 Memory: Page Faults/sec 計數器以確保磁盤活動不是由分頁導致。

分 頁率偏低（以及由此產生的頁錯誤）是正常的，即使計算機有大量的可用內存。Microsoft Windows 虛擬內存管理器 (VMM) 在剪裁 SQL Server 和其他進程的工作集大小時會收走這些進程的頁。此 VMM 活動會導致頁錯誤。若要確定分頁過多是由 SQL Server 還是由其他進程導致，請監視用于 SQL Server 進程實例的 Process: Page Faults/sec。

有關解決分頁過多的詳細信息，請參閱 Windows 操作系統文檔。

隔離 SQL Server 所用的內存

默 認情況下，SQL Server 將根據可用系統資源動態改變其內存要求。如果 SQL Server 需要更多內存，它會查詢操作系統以確定是否有可用的空閑物理內存，然后使用可用內存。如果 SQL Server 當前不需要分配給它的內存，它會將內存釋放給操作系統。但是，您可以覆蓋此選項通過 min server memory 和 max server memory 服務器配置選項來動態使用內存。有關詳細信息，請參閱服務器內存選項。

若要監視 SQL Server 使用的內存量，請檢查下列性能計數器：

Process: Working Set
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
SQL Server: Buffer Manager: Total Pages
SQL Server: Memory Manager: Total Server Memory (KB)
Working Set 計數器顯示進程所用的內存量。如果此內存量一直小于 min server memory 和 max server memory 服務器選項設置的內存量，則 SQL Server 被配置為使用過多內存。

Buffer Cache Hit Ratio 計數器僅適用于應用程序。但是，90% 或更高的命中率是令人滿意的。添加更多內存，直到該值始終大于 90%。大于 90% 的值表示數據緩存滿足所有數據請求中 90% 以上的請求。

如果 Total Server Memory (KB) 計數器值相對于計算機的物理內存量而言一直很高，則可能表示需要更多內存。

[SQL Server優化]善用系統監視器，確定系統瓶頸