原文: [SQL Server優(yōu)化]善用系統(tǒng)監(jiān)視器，確定系統(tǒng)瓶頸

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

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

監(jiān)視磁盤活動涉及兩個主要方面：

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

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

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

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

I/O 依賴的應用程序或系統(tǒng)可能會使磁盤持續(xù)處于活動狀態(tài)。

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

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



隔離 SQL Server 產(chǎn)生的磁盤活動

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

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

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


監(jiān)視CPU的使用率


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

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

另外還可以監(jiān)視下列計數(shù)器來監(jiān)視處理器的使用率：

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

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


監(jiān)視內(nèi)存使用量


定期監(jiān)視 SQL Server 的實例以確認內(nèi)存使用量在正常范圍內(nèi)。

若要監(jiān)視內(nèi)存不足的情況，請使用下列對象計數(shù)器：

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

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

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

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

隔離 SQL Server 所用的內(nèi)存

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

若要監(jiān)視 SQL Server 使用的內(nèi)存量，請檢查下列性能計數(shù)器：

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 計數(shù)器顯示進程所用的內(nèi)存量。如果此內(nèi)存量一直小于 min server memory 和 max server memory 服務器選項設置的內(nèi)存量，則 SQL Server 被配置為使用過多內(nèi)存。

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

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

[SQL Server優(yōu)化]善用系統(tǒng)監(jiān)視器，確定系統(tǒng)瓶頸