如果在沒有額外復(fù)雜條件下突然出現(xiàn)CPU瓶頸，有可能是因?yàn)闆]有優(yōu)化查詢，錯誤的數(shù)據(jù)庫配置，或者是數(shù)據(jù)庫設(shè)計(jì)上的原因和硬件資源不足引起。在決定采用增加CPU數(shù)量或者使用更快速的CPU之前，應(yīng)該先檢查消耗CPU資源最多的操作是否能夠被優(yōu)化

如果發(fā)現(xiàn)性能計(jì)數(shù)器Processor: % Processor Time的值很高，每一個CPU的% Processor Time都超過80%時(shí)，可視為出現(xiàn)CPU瓶頸。也可以通過視圖sys.dm_os_schedulers監(jiān)視SQL Server的進(jìn)程調(diào)度（schedulers）來確認(rèn)可執(zhí)行的任務(wù)是否為非零值。非零值表示任務(wù)被迫等待時(shí)間片來運(yùn)行，如果這個數(shù)值非常高，說明存在CPU瓶頸。

Select scheduler_id,current_task_count,runnable_task_count from sys.dm_os_schedulers where scheduler_id<255

下面的查詢將給出一個較高層的視圖來說明當(dāng)前被緩存的消耗CPU資源最多的批處理或者過程。查詢通過相同查詢句柄的所有語句合計(jì)CPU的消耗情況。

Select top 50 sum (qs_total_worker_time) as total_cpu_time,sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements,qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc

過多的compilation和recompilation

在批處理或者遠(yuǎn)程過程調(diào)用（RPC）提交到服務(wù)器執(zhí)行之前，系統(tǒng)會檢查查詢計(jì)劃的有效性和正確性。如果在檢查過程中出現(xiàn)了失敗的情況，這些批處理可能會被再次編譯來產(chǎn)生新的查詢計(jì)劃。這樣的編譯被稱為重編譯（recompilations）。這些重編譯一般必須確定正確性且通常在服務(wù)器認(rèn)定在潛在數(shù)據(jù)發(fā)生變化后存在可能被優(yōu)厚的查詢計(jì)劃時(shí)執(zhí)行。編譯的特性是CPU敏感的操作，因此過分的重編譯可以導(dǎo)致CPU性能問題。

在SQL Server 2000中，當(dāng)SQL Server重新編譯一個存儲過程時(shí)，整個存儲過程都會被重編譯，而不只是觸發(fā)重編譯的語句。SQL Server 2005引入了一種語句級別重編的存儲過程。當(dāng)SQL Server 2005重新編譯存儲過程時(shí)，只有引起重編譯的語句才會被編譯而不是整個過程。這就減少了CPU帶寬并且減少了資源鎖出現(xiàn)的可能，例如：COMPLIE locks. 重編譯可以由于很多不同的原因造成，如：

l 架構(gòu)變化

l 統(tǒng)計(jì)變化

l 延期編譯

l SET選項(xiàng)變化

l 臨時(shí)表變化

l 存儲過程以RECOMPLIE選項(xiàng)建立。

檢測

使用System Monitor 或者 SQL Server Profiler來檢測過多的編譯和重編譯。

System Monitor

SQL Statistics對象提供計(jì)數(shù)器來監(jiān)視編譯和發(fā)送到SQL Server實(shí)例的請求類型。必須通過監(jiān)視查詢編譯和重編譯的數(shù)量結(jié)合接收到的批處理數(shù)量來找出高CPU消耗是否是由編譯引起。理想情況下，SQL Recompilations/sec和Batch Requests/sec的比率應(yīng)該應(yīng)該非常低，除非用戶提交的是即席查詢。

以下是關(guān)鍵數(shù)據(jù)計(jì)數(shù)器：

l SQL Server: SQL Statistics: Batch Requests/sec

l SQL Server: SQL Statistics: SQL Compilations/sec

l SQL Server: SQL Statistics: SQL Recompilations/sec

SQL Trace

如果性能計(jì)數(shù)器顯示非常大的重編譯數(shù)量，重編譯可能正在造成高CPU消耗。接下來需要需要利用SQL Profiler紀(jì)錄的trace來找出當(dāng)時(shí)被重新編譯的存儲過程。SQL Server Profiler trace可以給出這些信息連同重編譯的原因。可以使用事件來獲取這些信息。

SP: Recompile / SQL: StmtRecompile. The SP:Recompile and the SQL:StmtRecompile事件類顯示哪些存儲過程和語句曾經(jīng)被重新編譯過。當(dāng)編譯一個存儲過程時(shí)，為存儲過程和每一個被編譯的語句生成事件。然而，當(dāng)一個存儲過程被重新編譯時(shí)，只有引起重新編譯的語句才會被生成一個事件（不同于SQL Server 2000中的整體存儲過程編譯）。

SP:Recompile事件類中的重要的數(shù)據(jù)列如下所示：

l Event Class

l EventSubClass

l ObjectID(表示包含這個語句的存儲過程)

l SPID

l Start Time

l SqlHandle

l TextData

EventSubClass數(shù)據(jù)列對于確定重編譯原因來說非常重要。一旦過程或者觸發(fā)器被重新編譯，SP:Recompile就會被觸發(fā)，但是有可能被重編譯的即席批處理不會引發(fā)這個事件。 在SQL Server 2005中，監(jiān)視SQL:StmtRecompiles時(shí)非常有用的，任何類型的批處理，即席查詢，存儲過程或者觸發(fā)器被重編譯時(shí)，這個事件類都會被觸發(fā)。

保存trace文件，使用下面的查詢來查看所有的重編譯事件。

Select spid,starttime,textdata,eventsubclass,objected,databaseid,sqlhandle from fn_trace_gettable (‘filepath.trc’,1) where EventClass in(37,75,166)

EventClass 37是SP:Recompile, 75是CursorRecompile, 166是SQL:StmtRecompile.

也可以進(jìn)一步對這些查詢結(jié)果根據(jù)Sqlhandle和ObjectID列進(jìn)行分組來查看是否有某個存儲過程存在大量的重編譯或者由于其他原因?qū)е碌闹鼐幾g（如Set選項(xiàng)變化）。

Showplan XML For Query Compile. 這個事件類在Microsoft SQL Server編譯或者重新編譯SQL語句時(shí)發(fā)生。這個事件中有關(guān)于被編譯或者重編譯的語句的信息。這些信息包括查詢計(jì)劃和存在問題的過程的Object ID。如果發(fā)現(xiàn)SQL Compilations/sec計(jì)數(shù)器數(shù)值很高，應(yīng)該監(jiān)視這個事件類。通過這些信息可以發(fā)現(xiàn)哪些語句被頻繁的重編譯。可以使用這些信息改變那些語句的參數(shù)。這應(yīng)該會降低重新編譯的次數(shù)。

DMVs

當(dāng)使用sys.dn_exec_query_optimizer_info DMV時(shí)，可以得到SQL Server花費(fèi)在優(yōu)化上的時(shí)間。

Select * from sys.dn_exec_query_optimizer_info

Counter occurrence value

Optimizations XX XX

Elaspsed time XX XX

Elaspsed time是消耗在優(yōu)化上的時(shí)間。這個事件一般接近于消耗在優(yōu)化上的CPU時(shí)間。

另外一個用來捕獲這些信息的DMV是 sys.dm_exec_query_stats

下列是需要查詢的數(shù)據(jù)列：

l Sql_handle

l Total worker time

l Plan generation number

l Statement Start Offset

Plan_generation_num表示查詢被編譯的次數(shù)。下列語句給出前25個被編譯的存儲過程。

Select top 25 sql_text.test,sqlhandle,plan_geration_num,execution_count,dbid,objectid from sys.dm_exec_query_stats across apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num>1 order by plan_generation_num desc

解決方法

如果檢測到過多的編譯/重編譯，考慮以下解決方法：

l 如果重編譯是因?yàn)镾ET選項(xiàng)引起，使用SQL Profiler確定是哪一個SET發(fā)生了變化。盡量避免在存儲過程內(nèi)部修改SET選項(xiàng)。可以選擇在連接級別上設(shè)置，并確保SET選項(xiàng)在連接的生命周期中不會發(fā)生變化。

l 臨時(shí)表的重編譯極值比一般表要低。如果由于統(tǒng)計(jì)信息變化導(dǎo)致重新編譯臨時(shí)表時(shí)，可以考慮把臨時(shí)表替換為一個table變量，同樣的變化不會影響table變量。這種方法的缺點(diǎn)是查詢優(yōu)化器不能跟蹤table變量的信息，因?yàn)橄到y(tǒng)不會為table變量建立和維護(hù)統(tǒng)計(jì)信息。這可能導(dǎo)致不能優(yōu)化對于表變量的查詢。

另外一個選擇是使用KEEP PLAN查詢提示。它設(shè)置臨時(shí)表的極限值與永久表一致。EventSubClass列將顯示臨時(shí)表上發(fā)生了”Statistics Changed” 操作。

l 避免由于統(tǒng)計(jì)信息發(fā)生變化而導(dǎo)致的重編譯（例如，當(dāng)查詢計(jì)劃因?yàn)楦淖兘y(tǒng)計(jì)信息而不能被達(dá)到最優(yōu)時(shí)），指定KEEPFIXED PLAN查詢提示。通過這個選項(xiàng)的作用，重編譯僅當(dāng)出現(xiàn)正確性相關(guān)的變化時(shí)才會發(fā)生（例如，當(dāng)?shù)讓颖斫Y(jié)構(gòu)發(fā)生變化時(shí)才會重新編譯查詢）而不是由于統(tǒng)計(jì)數(shù)據(jù)。如果一個表的架構(gòu)發(fā)生變化，或者表被sp_recompile存儲過程標(biāo)記，重編譯將會發(fā)生。

l 關(guān)閉被定義在一個表上的或者被索引的視圖上的index & statistics的statistics自動更新防止由于在對象上的statistics的改變引起的重編譯。注意，無論如何，關(guān)閉”auto-stats” 功能不是很好的選擇。這是因?yàn)椴樵儍?yōu)化器不在對數(shù)據(jù)變化產(chǎn)生作，可能會導(dǎo)致非最優(yōu)查詢計(jì)劃被執(zhí)行。

l 批處理中應(yīng)該使用具屬對象名（如：dbo.table1）來避免重編譯和對象之間的二義性。

l 避免由于延遲編譯導(dǎo)致的重編譯，不要使用條件結(jié)構(gòu)（如IF）來插入DML和DDL或者建立DDL。

l 運(yùn)行DTA查看是否有可以改善編譯時(shí)間和查詢執(zhí)行時(shí)間。

l 檢查是否存儲過程使用WITH RECOMPILE選項(xiàng)建立或者查詢是否使用了RECOMPILE。如果存儲過程使用WITH RECOMPILE選項(xiàng)建立，在SQL Server 2005中，考慮利用語句級別的RECOMPILE如果存儲過程中的某個語句需要被重新編譯。這可以避免每次執(zhí)行存儲過程時(shí)的強(qiáng)制編譯，同時(shí)允許單獨(dú)的語句重編譯。

性能測試應(yīng)用

從性能測試的角度出發(fā)，可以在負(fù)載測試過程中收集有關(guān)的性能計(jì)數(shù)器，同時(shí)利用SQL Profiler收集負(fù)載測試期間有關(guān)重編譯的事件類。一般情況下負(fù)載測試都會產(chǎn)生較高的CPU利用率，特別是壓力測試。在測試結(jié)束后收集性能計(jì)數(shù)器確定是否存在過多的編譯和重編譯情況。

在確定系統(tǒng)出現(xiàn)過多的編譯和重編譯后，對trace和DMV結(jié)果進(jìn)行分析找出產(chǎn)生大量編譯和重編譯的存儲過程或者語句。根據(jù)不同的原因提出相應(yīng)的解決方案。