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

如果發(fā)現(xiàn)性能計(jì)數(shù)器Processor: % Processor Time的值很高，每一個(gè)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)行，如果這個(gè)數(shù)值非常高，說明存在CPU瓶頸。

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

下面的查詢將給出一個(gè)較高層的視圖來說明當(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重新編譯一個(gè)存儲過程時(shí)，整個(gè)存儲過程都會被重編譯，而不只是觸發(fā)重編譯的語句。SQL Server 2005引入了一種語句級別重編的存儲過程。當(dāng)SQL Server 2005重新編譯存儲過程時(shí)，只有引起重編譯的語句才會被編譯而不是整個(gè)過程。這就減少了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)編譯一個(gè)存儲過程時(shí)，為存儲過程和每一個(gè)被編譯的語句生成事件。然而，當(dāng)一個(gè)存儲過程被重新編譯時(shí)，只有引起重新編譯的語句才會被生成一個(gè)事件（不同于SQL Server 2000中的整體存儲過程編譯）。

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

l Event Class

l EventSubClass

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

l SPID

l Start Time

l SqlHandle

l TextData

EventSubClass數(shù)據(jù)列對于確定重編譯原因來說非常重要。一旦過程或者觸發(fā)器被重新編譯，SP:Recompile就會被觸發(fā)，但是有可能被重編譯的即席批處理不會引發(fā)這個(gè)事件。 在SQL Server 2005中，監(jiān)視SQL:StmtRecompiles時(shí)非常有用的，任何類型的批處理，即席查詢，存儲過程或者觸發(fā)器被重編譯時(shí)，這個(gè)事件類都會被觸發(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)行分組來查看是否有某個(gè)存儲過程存在大量的重編譯或者由于其他原因?qū)е碌闹鼐幾g（如Set選項(xiàng)變化）。

Showplan XML For Query Compile. 這個(gè)事件類在Microsoft SQL Server編譯或者重新編譯SQL語句時(shí)發(fā)生。這個(gè)事件中有關(guān)于被編譯或者重編譯的語句的信息。這些信息包括查詢計(jì)劃和存在問題的過程的Object ID。如果發(fā)現(xiàn)SQL Compilations/sec計(jì)數(shù)器數(shù)值很高，應(yīng)該監(jiān)視這個(gè)事件類。通過這些信息可以發(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í)間。這個(gè)事件一般接近于消耗在優(yōu)化上的CPU時(shí)間。

另外一個(gè)用來捕獲這些信息的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個(gè)被編譯的存儲過程。

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確定是哪一個(gè)SET發(fā)生了變化。盡量避免在存儲過程內(nèi)部修改SET選項(xiàng)。可以選擇在連接級別上設(shè)置，并確保SET選項(xiàng)在連接的生命周期中不會發(fā)生變化。

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

另外一個(gè)選擇是使用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查詢提示。通過這個(gè)選項(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è)表的架構(gòu)發(fā)生變化，或者表被sp_recompile存儲過程標(biāo)記，重編譯將會發(fā)生。

l 關(guān)閉被定義在一個(gè)表上的或者被索引的視圖上的index & statistics的statistics自動(dòng)更新防止由于在對象上的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如果存儲過程中的某個(gè)語句需要被重新編譯。這可以避免每次執(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)的解決方案。