原文出自：

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

?

問題：

如果經(jīng)常遇到CPU瓶頸而導(dǎo)致的SQLServer宕機(jī)，那如何去發(fā)現(xiàn)并解決這些相關(guān)的問題？

?

解決方案：

導(dǎo)致CPU成為SQLServer性能問題的原因有很多，比較明顯的原因是因?yàn)橘Y源不足。但是，CPU的利用率可以通過配置的更改和查詢的優(yōu)化來降低，所以當(dāng)你想買更快更好的處理器之前，先要考慮前面的操作。下面是使用一些內(nèi)置工具來識(shí)別CPU相關(guān)瓶頸：

?

性能監(jiān)視器（Performance Monitor）：

可以使用性能監(jiān)視器來檢查CPU的負(fù)載。檢查 Processor:% Processor Time 這個(gè)計(jì)數(shù)器：如果長期超過80%/處理器，那很有可能面臨了CPU相關(guān)瓶頸。

?

CPU密集操作主要是編譯和重編譯。你可以通過使用SQL Statistics對(duì)象計(jì)數(shù)器來監(jiān)視它們的情況。也可以監(jiān)控批處理接收的數(shù)量來查看。如果 SQL Recompilations/sec 中的 BatchRequests/sec 的速率很高，那就有潛在的問題：

配置和監(jiān)視以下計(jì)數(shù)器：

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

可以從MSDN中獲取關(guān)于這部分的詳細(xì)信息：? MSDN Library .

?

另外一個(gè)用于探測CPU相關(guān)問題的計(jì)數(shù)器是： SQL Server: Cursor Manager By Type – CursorRequests/Sec ，用于顯示你的服務(wù)器上游標(biāo)使用情況。如果你看到每秒有數(shù)以百計(jì)的游標(biāo)請(qǐng)求，那很有可能是因?yàn)榈托У挠螛?biāo)使用和小體積提取操作（small fetch size）引起性能問題。

?

內(nèi)部并行查詢同樣會(huì)引起CPU問題，可以檢查：

SQL Statistics:Batch Requests/sec ?counter 計(jì)數(shù)器。在CPU生命周期中，每秒的批處理應(yīng)該很小。如果過多，意味著正在使用并行計(jì)劃運(yùn)行。

?

動(dòng)態(tài)管理視圖（DMVs）：

以下是對(duì)排查CPU瓶頸游泳的DMVs。動(dòng)態(tài)視圖：sys.dm_exec_query_stats顯示目前緩存的批處理或者使用CPU的過程。下面的查詢用于檢查耗費(fèi)CPU的執(zhí)行計(jì)劃：

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在每個(gè)查詢編譯時(shí)，會(huì)計(jì)算其hash值。你可以在query_hash列中找到該值，是否兩個(gè)查詢僅僅字面值不同但是使用相同query_hash值。該值也在? Showplan/Statistics XML ?QueryHash屬性中可以查看。

Plan_generation_num列顯示一個(gè)查詢被重編譯的次數(shù)。

SQLServer優(yōu)化器嘗試選擇能提供最快響應(yīng)時(shí)間的執(zhí)行計(jì)劃，但是不代表總是低CPU利用。低效的查詢計(jì)劃會(huì)引起CPU的好用，此時(shí)同樣可以使用 sys.dm_exec_query_stats 來監(jiān)控。

如果你想有一個(gè)對(duì)SQLServer優(yōu)化所耗費(fèi)時(shí)間的總覽，可以檢查：

sys.dm_exec_query_optimizer_info 。其中的消耗時(shí)間和最后開銷會(huì)非常有用。

可以使用以下DMVs來查詢內(nèi)部并行查詢及其查詢文本、執(zhí)行計(jì)劃的情況：

• 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:

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

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

·???????? Showplan XML For Query Compile : 這個(gè)事件是針對(duì)T-SQL語句的重編譯。包含了查詢計(jì)劃和過程的對(duì)象ID.注意對(duì)這個(gè)事件運(yùn)行一個(gè)跟蹤，能得到利用系統(tǒng)資源的重要信息。但是，如果性能計(jì)數(shù)器報(bào)告 SQL Compilations/sec 的值很高時(shí)，跟蹤將非常好資源。

低效的游標(biāo)可以使用RPC:Completed事件來跟蹤。查看sp_cursorfetch語句并檢查第四個(gè)參數(shù)，包含每次提前（fetch）包含的行數(shù)。

?

如何識(shí)別SQL Server中的CPU瓶頸