原文出自：

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）包含的行數。

?

如何識別SQL Server中的CPU瓶頸