[轉]檢測SQLSERVER資料庫CPU瓶頸及記憶體瓶頸

一、sql 資料庫CPU瓶頸

對於SQL Server的一個工作程式的狀態有很多，主要狀態有執行中（RUNNING)、可執行（RUNNABLE)和掛起(SUSPENED)3種。

透過檢視系統監視計數器Processor:% Processor Time，可以確定CPU瓶頸。如果這個計數器的值很高。比如持續15-20分鐘超80%，就意味著CPU出現了瓶頸。

當您懷疑計算機硬體是影響SQL Server執行效能的主要原因時，可以透過SQL Server Performance Monitor監視相應硬體的負載，以證實您的猜測並找出系統瓶頸。下文將介紹一些常用的分析物件及其引數。

　　Memory: Page Faults / sec

　　如果該值偶爾走高，表明當時有執行緒競爭記憶體。如果持續很高，則記憶體可能是瓶頸。

　　Process: Working Set

　　SQL Server的該引數應該非常接近分配給SQL Server的記憶體值。在SQL Server設定中，如果將"set working set size"置為0, 則Windows NT會決定SQL Server的工作集的大小。如果將"set working set size"置為1，則強制工作集大小為SQLServer的分配記憶體大小。一般情況下，最好不要改變"set working set size"的預設值。

　　Process:%Processor Time

　　如果該引數值持續超過95%，表明瓶頸是CPU。可以考慮增加一個處理器或換一個更快的處理器。

　　Processor:%Privileged Time

　　如果該引數值和"Physical Disk"引數值一直很高，表明I/O有問題。可考慮更換更快的硬碟系統。另外設定Tempdb in RAM，減低"max async IO"，"max lazy writer IO"等措施都會降低該值。

　　Processor:%User Time

　　表示耗費CPU的資料庫操作，如排序，執行aggregate functions等。如果該值很高，可考慮增加索引，儘量使用簡單的表聯接，水平分割大表格等方法來降低該值。

　　Physical Disk:Avg.Disk Queue Length

　　該值應不超過磁碟數的1.5~2倍。要提高效能，可增加磁碟。

　　注意:一個Raid Disk實際有多個磁碟。

　　SQLServer:Cache Hit Ratio

　　該值越高越好。如果持續低於80%，應考慮增加記憶體。 注意該引數值是從SQL Server啟動後，就一直累加記數，所以執行經過一段時間後，該值將不能反映系統當前值。

檢測CPU壓力的另一個方法是計算可執行狀態下的工作程式數量，透過執行如下的DMV查詢可以得到這個資訊：

SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id

FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2

WHERE t1.state = 'RUNNABLE' AND t1.scheduler_address=t2.scheduler_address

AND t2.scheduler_id < 255

GROUP BY t2.scheduler_id

也可以執行如下的查詢得到工作程式在可執行狀態下花費的時間：

SELECT SUM(signal_wait_time_ms) FROM sys.dm_os_wait_stats

下面查詢是找出每次執行佔用CPU最多的前100位查詢：

SELECT TOP 100 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count,

(SELECT SUBSTRING(text, statement_start_offset/2+1,

(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2

ELSE statement_end_offset END - statement_end_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_query_stats

ORDER BY avg_cpu_cost DESC

稍做修改，找出執行最頻繁的查詢：

SELECT TOP 100 total_worker_time/execution_countASavg_cpu_cost, plan_handle, execution_count,

(SELECT SUBSTRING(text,statement_start_offset/2+1,

(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2

ELSE statement_end_offset END - statement_end_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

FROM sys.dm_exec_query_stats

ORDER BY execution_count DESC

可以使用下列系統監視效能計數器檢視編譯和重編譯的速度：

1. SQLServer: SQL Statistics: BatchRequests/Sec（每秒批處理請求數）

2. SQLServer: SQL Statistics: SQLCompilations/Sec（每秒SQL編譯次數）

3. SQLServer: SQL Statistics: SQLRecompilations/Sec（每秒SQL重編譯次數）

還可以透過下面語句得到SQLServer在最佳化查詢計劃上花費的時間：

SELECT * FROM sys.dm_exec_query_optimizer_info

WHERE counter='optimizations' OR counter = 'elapsed time'

下面查詢找到被編譯得最多的前10位查詢計劃：

SELECT TOP 10 plan_generation_num, execution_count,

(SELECT SUBSTRING(text, statement_start_offset/2+1,

(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2

ELSE statement_end_offsetEND-statement_end_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle))ASquery_text

FROM sys.dm_exec_query_stats

WHERE plan_generation_num> 1

ORDER BY plan_generation_num DESC

 <wbr>

二、sql 資料庫記憶體瓶頸

記憶體有壓力時，一個查詢計劃可能得移出記憶體。如果這個計劃被再次提交執行，就必須再最佳化一次，而由於查詢最佳化是CPU密集型運算，這就會給CPU帶來壓力。同樣，記憶體有壓力時，資料庫頁面可能需要被移出緩衝區池。如果這些頁面很快就再次被選中，就會導致更多的物理IO。

通常所說的記憶體指的是伺服器上的可用實體記憶體（既RAM）。還有另外一種記憶體叫做虛擬地址空間（VAS）或虛擬記憶體。在Windows系統上，所有位應用程式都有一個GB的程式地址空間，用來獲取最大GB的實體記憶體。在GB的可用記憶體之外，程式還可以在使用者模式下得到GB的VAS，另外GB保留只能透過核心模式獲取。要想更改這個配置，可以在boot.ini檔案中使用/3GB switch。

常見的作業系統機制是頁面除錯，它使用一個交換檔案來儲存最近未使用的部分程式記憶體。當這一記憶體被再次引用時，它就直接從交換檔案中讀取（或調入）實體記憶體。

 <wbr>

可以透過效能計數器，監測下面引數：

1. 記憶體：可用位元組（Available Bytes）

2. SQL Server:緩衝管理器：快取命中率（Buffer Cache Hit Ratio）指的是那些不用透過磁碟讀取而直接在緩衝區池中找到的頁的比例。對於大多數產品工作負荷而言，這個值應該是多。（應該是越大越好）

3. SQL Server:緩衝管理器：頁平均壽命（Page Life Expectancy）指的是一個沒有被引用的頁在緩衝區池中保留的秒數。如果數值較低，則說明緩衝區池遇到了記憶體不足的情況。

4. SQL Server:緩衝管理器：檢查點頁/秒（Checkpoint Pages/Sec）指的是被檢查點重新整理的頁數，或者要求所有髒頁被重新整理的其它操作的數目。它能顯示工作負荷中增加的緩衝區池活動量。

5. SQL Server:緩衝管理器：延遲寫入/秒（Lazywrites/Sec）指的是緩衝管理器的延遲寫入器寫入的緩衝數目，它的作用類似於前面提到的檢查點頁/秒。

 <wbr>

懷疑記憶體不足時：

方法1：

【監控指標】：Memory Available MBytes ，Memory的Pages/sec， page read/sec， Page Faults/sec

【參考值】：

如果 Page Reads/Sec 比率持續保持為 5，表示可能記憶體不足。

Page/sec 推薦00-20（如果伺服器沒有足夠的記憶體處理其工作負荷，此數值將一直很高。如果大於80，表示有問題）。

方法2：根據Physical Disk 值分析效能瓶頸

【監控指標】：Memory Available MBytes ，Pages read/sec，%Disk Time 和 Avg.Disk Queue Length

【參考值】：%Disk Time建議閾值90%

當記憶體不足時，有點程式會轉移到硬碟上去執行，造成效能急劇下降，而且一個缺少記憶體的系統常常表現出很高的CPU利用率，因為它需要不斷的掃描記憶體，將記憶體中的頁面移到硬碟上。

懷疑記憶體洩漏時

【監控指標】：Memory Available MBytes ，ProcessPrivate Bytes和ProcessWorking Set，PhysicalDisk/%Disk Time

【說明】：

資源監控中，如果ProcessPrivate Bytes計數器和ProcessWorking Set計數器的值在長時間內持續升高，同時MemoryAvailable bytes計數器的值持續降低，則很可能存在記憶體洩漏。記憶體洩漏應該透過一個長時間的，用來研究分析當所有記憶體都耗盡時，應用程式反應情況的測試來檢驗。

 <wbr>

 <wbr>

 <wbr>

CPU瓶頸問題

1、System\%Total processor time如果該值持續超過90%，且伴隨處理器阻塞，則說明整個系統面臨著處理器方面的瓶頸.

注：在某些多CPU系統中，該資料雖然本身並不大，但CPU之間的負載狀況極不均衡，此時也應該視作系統產生了處理器方面的瓶頸.

2、排除記憶體因素，如果Processor %Processor Time計數器的值比較大，而同時網路卡和硬碟的值比較低，那麼可以確定CPU 瓶頸。（記憶體不足時，有點程式會轉移到硬碟上去執行，造成效能急劇下降，而且一個缺少記憶體的系統常常表現出很高的CPU利用率，因為它需要不斷的掃描記憶體，將記憶體中的頁面移到硬碟上。）

造成高CPU使用率的原因：

頻繁執行程式，複雜運算操作，消耗CPU嚴重

資料庫查詢語句複雜，大量的 where 子句，order by， group by 排序等，CPU容易出現瓶頸

記憶體不足，IO磁碟問題使得CPU的開銷增加

 <wbr>

CPU分析

【監控指標】：

System %Processor Time CPU，Processor %Processor Time CPU

Processor%user time 和Processor%Privileged Time

systemProcessor Queue Length

Context Switches/sec 和%Privileged Time

【參考值】：

System\%Total processor time不持續超過90%，如果伺服器專用於 Server，可接受的最大上限是80-85% ，合理使用的範圍在60%至70%。

Processor %Processor Time小於75%

systemProcessor Queue Length值，小於CPU數量的總數+1

磁碟I/O分析

【監控指標】：PhysicalDisk/%Disk time，PhysicalDisk/%Idle Time，Physical Disk Avg.Disk Queue Length， Disk sec/Transfer

【參考值】：%Disk Time建議閾值90%

Windows資源監控中，如果% Disk Time和Avg.Disk Queue Length的值很高，而Page Reads/sec頁面讀取操作速率很低，則可能存在磁碟瓶徑。

Processor%Privileged Time該引數值一直很高，且如果在 Physical Disk 計數器中，只有%Disk time 比較大，其他值都比較適中，硬碟可能會是瓶頸。若幾個值都比較大，那麼硬碟不是瓶頸。若數值持續超過80%，則可能是記憶體洩露。如果 Physical Disk 計數器的值很高時該計數器的值（Processor%Privileged Time）也一直很高，則考慮使用速度更快或效率更高的磁碟子系統。

Disk sec/Transfer 一般來說，該數值小於15ms為最好，介於15-30ms之間為良好，30-60ms之間為可以接受，超過60ms則需要考慮更換硬碟或是硬碟的RAID方式了.

Average Transaciton Response Time（事務平均響應時間）隨著測試時間的變化，系統處理事務的速度開始逐漸變慢，這說明應用系統隨著投產時間的變化，整體效能將會有下降的趨勢

Transactions per Second（每秒透過事務數/TPS）當壓力加大時，點選率/TPS曲線如果變化緩慢或者有平坦的趨勢，很有可能是伺服器開始出現瓶頸

Hits per Second（每秒點選次數）透過對檢視“每秒點選次數”，可以判斷系統是否穩定。系統點選率下降通常表明伺服器的響應速度在變慢，需進一步分析，發現系統瓶頸所在。

Throughput（吞吐率）可以依據伺服器的吞吐量來評估虛擬使用者產生的負載量，以及看出伺服器在流量方面的處理能力以及是否存在瓶頸。

Connections（連線數）當連線數到達穩定狀態而事務響應時間迅速增大時，新增連線可以使效能得到極大提高（事務響應時間將降低）

Time to First Buffer Breakdown（Over Time）（第一次緩衝時間細分（隨時間變化））可以使用該圖確定場景或會話步驟執行期間伺服器或網路出現問題的時間。

碰到過的效能問題：

• 1. 在高併發的情況下，產生的處理失敗（比如：資料庫連線池過低，伺服器連線數超過上限，資料庫鎖控制考慮不足等）
• 2. 記憶體洩露（比如：在長時間執行下，記憶體沒有正常釋放，發生當機等）
• 3. CPU使用偏離（比如：高併發導致CPU使用率過高）
• 4. 日誌列印過多，伺服器無硬碟空間

如何定位這些效能問題：

1. 檢視系統日誌，日誌是定位問題的不二法寶，如果日誌記錄的全面，很容易透過日誌發現問題。

比如，系統當機時，系統日誌列印了某方法執行時丟擲out of memory的錯誤，我們就可以順藤摸瓜，很快定位到導致記憶體溢位的問題在哪裡。

2. 利用效能監控工具，比如：JAVA開發B/S結構的專案，可以透過JDK自帶的Jconsole，或者JProfiler，來監控伺服器效能，Jconsole可以遠端監控伺服器的CPU，記憶體，執行緒等狀態，並繪製變化曲線圖。

利用Spotlight可以監控資料庫使用情況。

我們需要關注的效能點有：CPU負載，記憶體使用率，網路I/O等

3. 工具和日誌只是手段，除此之外，還需要設計合理的效能測試場景

具體場景有：效能測試，負載測試，壓力測試，穩定性測試，浪湧測試等

好的測試場景，能更加快速的發現瓶頸，定位瓶頸

4. 瞭解系統引數配置，可以進行後期的效能調優

除此以外，還想說個題外話，就是關於效能測試工具的使用問題

在剛開始用Loadrunner和JMeter的時候，做高併發測試時，都出現過沒有把伺服器壓垮，這兩個程式自己先倒下的情況。

如果遇到這個問題，可以透過遠端呼叫多個客戶端的服務，分散效能測試工具客戶端的壓力來解決。

說這個的目的是想說，做效能測試的時候，我們一定要確保瓶頸不要發生在我們自己的測試指令碼和測試工具上