原文出自：

http://www.mssqltips.com/sqlservertip/2316/how-to-identify-sql-server-cpu-bottlenecks/

 

問題：

如果經常遇到CPU瓶頸而導致的SQLServer當機，那如何去發現並解決這些相關的問題？

 

解決方案：

導致CPU成為SQLServer效能問題的原因有很多，比較明顯的原因是因為資源不足。但是，CPU的利用率可以通過配置的更改和查詢的優化來降低，所以當你想買更快更好的處理器之前，先要考慮前面的操作。下面是使用一些內建工具來識別CPU相關瓶頸：

 

效能監視器（Performance Monitor）：

可以使用效能監視器來檢查CPU的負載。檢查Processor:% Processor Time 這個計數器：如果長期超過80%/處理器，那很有可能面臨了CPU相關瓶頸。

 

CPU密集操作主要是編譯和重編譯。你可以通過使用SQL Statistics物件計數器來監視它們的情況。也可以監控批處理接收的數量來檢視。如果SQL Recompilations/sec 中的BatchRequests/sec的速率很高，那就有潛在的問題：

配置和監視以下計數器：

• SQL Server: SQL Statistics: SQL Compilations/sec
• SQL Server: SQL Statistics: SQL Recompilations/sec
• SQL Server: SQL Statistics: Batch Requests/sec

可以從MSDN中獲取關於這部分的詳細資訊： MSDN Library.

 

另外一個用於探測CPU相關問題的計數器是：SQL Server: Cursor Manager By Type – CursorRequests/Sec ，用於顯示你的伺服器上游標使用情況。如果你看到每秒有數以百計的遊標請求，那很有可能是因為低效的遊標使用和小體積提取操作（small fetch size）引起效能問題。

 

內部並行查詢同樣會引起CPU問題，可以檢查：

SQL Statistics:Batch Requests/sec counter 計數器。在CPU生命週期中，每秒的批處理應該很小。如果過多，意味著正在使用並行計劃執行。

 

動態管理檢視（DMVs）：

以下是對排查CPU瓶頸游泳的DMVs。動態檢視：sys.dm_exec_query_stats顯示目前快取的批處理或者使用CPU的過程。下面的查詢用於檢查耗費CPU的執行計劃：

select plan_handle,

      sum(total_worker_time) as total_worker_time,

      sum(execution_count) as total_execution_count,

      count(*) as number_of_statements

from sys.dm_exec_query_stats

group by plan_handle

order bysum(total_worker_time), sum(execution_count) desc

 

SQLServer2008在每個查詢編譯時，會計算其hash值。你可以在query_hash列中找到該值，是否兩個查詢僅僅字面值不同但是使用相同query_hash值。該值也在 Showplan/Statistics XML QueryHash屬性中可以檢視。

Plan_generation_num列顯示一個查詢被重編譯的次數。

SQLServer優化器嘗試選擇能提供最快響應時間的執行計劃，但是不代表總是低CPU利用。低效的查詢計劃會引起CPU的好用，此時同樣可以使用sys.dm_exec_query_stats 來監控。

如果你想有一個對SQLServer優化所耗費時間的總覽，可以檢查：

sys.dm_exec_query_optimizer_info 。其中的消耗時間和最後開銷會非常有用。

可以使用以下DMVs來查詢內部並行查詢及其查詢文字、執行計劃的情況：

• sys.dm_exec_cached_plan: Shows the cached query plans.
• sys.dm_exec_requests: Shows each executing request in the SQL Server instance.
• sys.dm_exec_sessions: Shows all active user connections and internal tasks.
• sys.dm_exec_sql_text: Shows the text of the SQL batches.
• sys.dm_os_tasks: Shows each active task within SQL Server.

 

SQL Server Profiler:

如果效能監視器發現有問題，同樣可以使用SQLServer Profiler來發現不必要的編譯和重編譯。SQLServer Profiler 跟蹤能幫助你找到一直重編譯的儲存過程。可以使用下面的事件：

• SP:Recompile, CursorRecompile, SQL:StmtRecompile: 這個事件是針對SQLServer的重編譯。SP:Recompile事件中的EventSubClass 說明了重編譯的原因。

·        Showplan XML For Query Compile: 這個事件是針對T-SQL語句的重編譯。包含了查詢計劃和過程的物件ID.注意對這個事件執行一個跟蹤，能得到利用系統資源的重要資訊。但是，如果效能計數器報告SQL Compilations/sec 的值很高時，跟蹤將非常好資源。

低效的遊標可以使用RPC:Completed事件來跟蹤。檢視sp_cursorfetch語句並檢查第四個引數，包含每次提前（fetch）包含的行數。