SQL Server 2005效能測試之CPU篇

如果在沒有額外複雜條件下突然出現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. 重編譯可以由於很多不同的原因造成，如：

◆架構變化
◆統計變化
◆延期編譯
◆SET選項變化
◆臨時表變化
◆儲存過程以RECOMPLIE選項建立。

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

SQL Statistics物件提供計數器來監視編譯和傳送到SQL Server例項的請求型別。必須通過監視查詢編譯和重編譯的數量結合接收到的批處理數量來找出高CPU消耗是否是由編譯引起。理想情況下，SQL Recompilations/sec和Batch Requests/sec的比率應該應該非常低，除非使用者提交的是即席查詢。
以下是關鍵資料計數器：
◆SQL Server: SQL Statistics: Batch Requests/sec
◆SQL Server: SQL Statistics: SQL Compilations/sec
◆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事件類中的重要的資料列如下所示：
◆Event Class
◆EventSubClass
◆ObjectID(表示包含這個語句的儲存過程)
◆SPID
◆Start Time
◆SqlHandle
◆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
下列是需要查詢的資料列：
◆Sql_handle
◆Total worker time
◆Plan generation number
◆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 

解決方法

如果檢測到過多的編譯/重編譯，考慮以下解決方法：
◆如果重編譯是因為SET選項引起，使用SQL Profiler確定是哪一個SET發生了變化。儘量避免在儲存過程內部修改SET選項。可以選擇在連線級別上設定，並確保SET選項在連線的生命週期中不會發生變化。

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

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

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

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

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

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

◆執行DTA檢視是否有可以改善編譯時間和查詢執行時間。

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

效能測試應用

從效能測試的角度出發，可以在負載測試過程中收集有關的效能計數器，同時利用SQL Profiler收集負載測試期間有關重編譯的事件類。一般情況下負載測試都會產生較高的CPU利用率，特別是壓力測試。在測試結束後收集效能計數器確定是否存在過多的編譯和重編譯情況。
在確定系統出現過多的編譯和重編譯後，對trace和DMV結果進行分析找出產生大量編譯和重編譯的儲存過程或者語句。根據不同的原因提出相應的解決方案。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-629948/，如需轉載，請註明出處，否則將追究法律責任。