如果在沒有額外復雜條件下突然出現CPU瓶頸，有可能是因為沒有優化查詢，錯誤的數據庫配置，或者是數據庫設計上的原因和硬件資源不足引起。在決定采用增加CPU數量或者使用更快速的CPU之前，應該先檢查消耗CPU資源最多的操作是否能夠被優化

如果發現性能計數器Processor: % Processor Time的值很高，每一個CPU的% Processor Time都超過80%時，可視為出現CPU瓶頸。也可以通過視圖sys.dm_os_schedulers監視SQL Server的進程調度（schedulers）來確認可執行的任務是否為非零值。非零值表示任務被迫等待時間片來運行，如果這個數值非常高，說明存在CPU瓶頸。

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

下面的查詢將給出一個較高層的視圖來說明當前被緩存的消耗CPU資源最多的批處理或者過程。查詢通過相同查詢句柄的所有語句合計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

在批處理或者遠程過程調用（RPC）提交到服務器執行之前，系統會檢查查詢計劃的有效性和正確性。如果在檢查過程中出現了失敗的情況，這些批處理可能會被再次編譯來產生新的查詢計劃。這樣的編譯被稱為重編譯（recompilations）。這些重編譯一般必須確定正確性且通常在服務器認定在潛在數據發生變化后存在可能被優厚的查詢計劃時執行。編譯的特性是CPU敏感的操作，因此過分的重編譯可以導致CPU性能問題。

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

l 架構變化

l 統計變化

l 延期編譯

l SET選項變化

l 臨時表變化

l 存儲過程以RECOMPLIE選項建立。

檢測

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

System Monitor

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

以下是關鍵數據計數器：

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

如果性能計數器顯示非常大的重編譯數量，重編譯可能正在造成高CPU消耗。接下來需要需要利用SQL Profiler紀錄的trace來找出當時被重新編譯的存儲過程。SQL Server Profiler trace可以給出這些信息連同重編譯的原因?？梢允褂檬录慝@取這些信息。

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

SP:Recompile事件類中的重要的數據列如下所示：

l Event Class

l EventSubClass

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

l SPID

l Start Time

l SqlHandle

l TextData

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

保存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.

也可以進一步對這些查詢結果根據Sqlhandle和ObjectID列進行分組來查看是否有某個存儲過程存在大量的重編譯或者由于其他原因導致的重編譯（如Set選項變化）。

Showplan XML For Query Compile. 這個事件類在Microsoft SQL Server編譯或者重新編譯SQL語句時發生。這個事件中有關于被編譯或者重編譯的語句的信息。這些信息包括查詢計劃和存在問題的過程的Object ID。如果發現SQL Compilations/sec計數器數值很高，應該監視這個事件類。通過這些信息可以發現哪些語句被頻繁的重編譯?？梢允褂眠@些信息改變那些語句的參數。這應該會降低重新編譯的次數。

DMVs

當使用sys.dn_exec_query_optimizer_info DMV時，可以得到SQL Server花費在優化上的時間。

Select * from sys.dn_exec_query_optimizer_info

Counter occurrence value

Optimizations XX XX

Elaspsed time XX XX

Elaspsed time是消耗在優化上的時間。這個事件一般接近于消耗在優化上的CPU時間。

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

下列是需要查詢的數據列：

l Sql_handle

l Total worker time

l Plan generation number

l Statement Start Offset

Plan_generation_num表示查詢被編譯的次數。下列語句給出前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 如果重編譯是因為SET選項引起，使用SQL Profiler確定是哪一個SET發生了變化。盡量避免在存儲過程內部修改SET選項?？梢赃x擇在連接級別上設置，并確保SET選項在連接的生命周期中不會發生變化。

l 臨時表的重編譯極值比一般表要低。如果由于統計信息變化導致重新編譯臨時表時，可以考慮把臨時表替換為一個table變量，同樣的變化不會影響table變量。這種方法的缺點是查詢優化器不能跟蹤table變量的信息，因為系統不會為table變量建立和維護統計信息。這可能導致不能優化對于表變量的查詢。

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

l 避免由于統計信息發生變化而導致的重編譯（例如，當查詢計劃因為改變統計信息而不能被達到最優時），指定KEEPFIXED PLAN查詢提示。通過這個選項的作用，重編譯僅當出現正確性相關的變化時才會發生（例如，當底層表結構發生變化時才會重新編譯查詢）而不是由于統計數據。如果一個表的架構發生變化，或者表被sp_recompile存儲過程標記，重編譯將會發生。

l 關閉被定義在一個表上的或者被索引的視圖上的index & statistics的statistics自動更新防止由于在對象上的statistics的改變引起的重編譯。注意，無論如何，關閉”auto-stats” 功能不是很好的選擇。這是因為查詢優化器不在對數據變化產生作，可能會導致非最優查詢計劃被執行。

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

l 避免由于延遲編譯導致的重編譯，不要使用條件結構（如IF）來插入DML和DDL或者建立DDL。

l 運行DTA查看是否有可以改善編譯時間和查詢執行時間。

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

性能測試應用

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

在確定系統出現過多的編譯和重編譯后，對trace和DMV結果進行分析找出產生大量編譯和重編譯的存儲過程或者語句。根據不同的原因提出相應的解決方案。