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

來(lái)自:? http://hi.baidu.com/solorez/blog/item/f82038fa0e71b78d9e51468c.html

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

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

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

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

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

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

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

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

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



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

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

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

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


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


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

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

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

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

若使用率為 100% 左右（表示在處理大量的客戶端請(qǐng)求），可能表示進(jìn)程正在排隊(duì)，等待處理器時(shí)間，并因而導(dǎo)致出現(xiàn)瓶頸。可以通過(guò)增加速度更快的處理器來(lái)解決這一問(wèn)題。


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


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

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

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

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

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

有關(guān)解決分頁(yè)過(guò)多的詳細(xì)信息，請(qǐng)參閱 Windows 操作系統(tǒng)文檔。

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

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

若要監(jiān)視 SQL Server 使用的內(nèi)存量，請(qǐng)檢查下列性能計(jì)數(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 計(jì)數(shù)器顯示進(jìn)程所用的內(nèi)存量。如果此內(nèi)存量一直小于 min server memory 和 max server memory 服務(wù)器選項(xiàng)設(shè)置的內(nèi)存量，則 SQL Server 被配置為使用過(guò)多內(nèi)存。

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

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

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