SQL Server Profiler工具【轉】

標籤：style   blog   http   color   os   使用   io   strong   ar   

一、SQL Profiler工具簡介

轉自：http://www.cnblogs.com/kissdodog/p/3398523.html

SQL Profiler是一個圖形介面和一組系統預存程序，其作用如下：

• 圖形化監視SQL Server查詢；
• 在後台收集查詢資訊；
• 分析效能；
• 診斷像死結之類的問題；
• 調試T-SQL語句；
• 類比重放SQL Server活動；

也可以使用SQL Profiler捕捉在SQL Server執行個體上執行的活動。這樣的活動被稱為Profiler跟蹤。

　　1、Profiler跟蹤

　　從開始=》所有程式=》Microsoft SQL Server 2008=》效能工具開啟Profiler工具，也可以開啟SQL Server Management Studio=》工具=》SQL Server Profiler。

　　然後選擇檔案=》建立=》跟蹤開啟一個串連視窗，選擇將要跟蹤的伺服器執行個體然後串連。開啟如下“跟蹤屬性”對話方塊。

     

     如果有許多跟蹤，可以提供一個跟蹤名稱來協助在以後進行分類。不同的跟蹤模板可協助建立用於不同目的的跟蹤。

     開啟跟蹤屬性視窗後，單擊“事件選擇”選項卡，為跟蹤提供更詳細的定義。

     

　　2、事件

　　一個事件表現SQL Server中執行的各種活動。這些活動可以簡單地分類為事件類別，遊標事件，鎖事件，預存程序事件和T-SQL事件是常見的事件類別。

　　對於效能分析，主要對SQL Server上執行的各種活動的資源壓力水平的事件感興趣。資源壓力主要包含如下內容：

1. SQL活動涉及哪一類的CPU使用？
2. 使用了多少記憶體？
3. 涉及多少I/0操作?
4. SQL活動執行了多長時間？
5. 特定的查詢執行的頻率有多高？
6. 查詢面對哪類錯誤和警告？

　　下面給出跟蹤查詢結束的事件：

事件類別	事件	說明
Stored Procedures	RPC:Completed	RPC完成事件
	SP:Completed	預存程序完成事件
	SP:StmtCompleted	在預存程序中一條SQL陳述式完成事件
T-SQL	SQL:BatchCompleted	T-SQL批完成事件
	SQL:StmtCompleted	一條T-SQL陳述式完成事件

　　RPC事件表示預存程序使用遠端程序呼叫(RPC)機制通過OLEDB命令執行。如果一個資料庫應用程式使用T-SQL EXECUTE語句執行一個預存程序，那麼預存程序將被轉化為一個SQL批而不是一個RPC。RPC請求通常比EXECUTE請求快，因為它繞過了SQL Server中的許多語句解析和參數處理。

　　T-SQL由一條或多條T-SQL語句組成。語句或T-SQL語句在預存程序中也是單獨和離散的。用SP:StmtCompleted或SQL:StmtCompleted事件捕捉單獨的語句可能是代價很高的操作，這取決於單獨語句的數量。假設系統中的每個預存程序包含且只有一條T-SQL語句。在這種情況下，完成的語句集合相當小。現在假定過程中有多條語句，而且這些過程中有些使用其他語句調用其他過程。收集所有這些額外的資料現在變成系統上非常厲害的負載。在生產機上一定要慎用。

　　現在回到那個事件選擇面板，只有已經被選擇的事件才會被顯示。如果想顯示所有可供選擇的事件，則只需選中“顯示所有事件”單選框，要添加一個跟蹤事件，在Event列中尋找一個事件類別下的事件，並單擊其左邊的檢查框；要刪除不需要的事件，取消選中的事件選擇框。

　　光分類就有好多的說：

     

　　下面給出其他一些與效能診斷有關的事件:

事件類別	事件	說明
Security Audit(安全審計)	Audit Login(登入審計)	記錄使用者串連到SQL Server或中斷連線時資料庫的串連
	Audit Logout(登出審計)
Sessions(會話)	ExistingConnection(現有串連)	表示所有在跟蹤開始之間串連到SQL Server的使用者
Cursors(遊標)	CursorImplicitConversion(遊標隱含轉換)	表明建立的遊標類型與所請求的類型個不同
Errors and Warnings(錯誤和警告)	Attention(注意)	表示由於用戶端撤銷查詢或者資料庫連接破壞引起請求中斷
	Exception(異常)	表明SQL Server發生了異常
	Execution Warning(執行警告)	表明在查詢或預存程序執行期間出現了警告
	Hash Warning(雜湊警告)	表明hash操作發生了錯誤
	Missing Column Statistics(列統計丟失)	表明最佳化器要求的確定處理策略用的類統計丟失
	Missing Join Predicate(串連斷言丟失)	表明查詢在兩個表沒有串連斷言情況下執行
	Sort Warning(排序警告)	表明像SELECT這樣的查詢中執行排序操作沒有合適的記憶體
Locks(鎖)	Lock:Deadlock(死結)	標誌著死結的出現
	Lock:Deadlock Chain(死結鏈)	顯示產生死結的查詢鏈條
	lock:Timeout(鎖逾時)	表示鎖已經超過其逾時參數，該參數由SETLOCK_TIMEOUT timeout_perious(ms)命令設定
Stored Procedures(預存程序)	SP:Recompile(重編譯)	表明用於一個預存程序的執行計畫必須重編譯，原因是執行計畫不存在，強制的重編譯，或者現有的執行計畫不能重用
	SP:Starting(開始)  SP:StmtStarting(語句開始)	分別表示一個SP:StmtStarting預存程序和預存程序中的一條SQL語句的開始。他們對於識別開始單因為一個操作導致Attention事件未能結束的查詢很有用
Transactions(事物)	SQLTransaction(SQL事務)	提供資料庫事務的資訊，包括事務開始/結束的時間、事務持續事件等資訊

　　3、事件列

 　　事件以不同的特性(被稱為資料列)來表現。資料列表現一個事件的不通特性，如事件的類、用於該事件的SQL語句、事件的資源開銷以及事件來源。

資料列	說明
EventClass(事件類別)	事件類型，如SQL:StatementCompleted
TextData	事件所用的SQL語句，如SELECT * FROM Person
CPU	事件的CPU開銷（以ms表示），如對一個SELECT語句，CPU=100表示該語句執行100ms
Reads	為一個事件所執行的邏輯讀運算元量。例如對一個SELECT語句，Reads=800表示該語句需要800次邏輯讀操作
Writes	為一個事件所執行的邏輯寫運算元量
Duration	事件的執行時間(ms)
SPID	用於該事件的SQL Server進程標識符
StartTime	事件開始的時間

 　　以上是常用的資料列，另外還有一些不太常用的資料列：

• 　　BinaryData(位元據)
• 　　IntegerData(整數資料)
• 　　EventSubClass(事件子類)
• 　　DatabaseID(資料庫標識符)
• 　　ObjectID(物件識別碼)
• 　　IndexID(索引標識符)
• 　　TransactionID(事務標識符)
• 　　Error(錯誤)
• 　　EndTime(結束時間)

　　列資料可以重新安排以符合你自己所喜歡的風格，要控制列資料的安放，單擊組織列按鈕，將開啟如下對話方塊。可以單擊Up和Down按鈕修改列的位置，將列移入Groups意味著它將成為一個合計列。

     

　　4、資料行篩選

　　除了為一個Profiler追蹤定義事件和資料列之外，還可以定義各種過濾條件。這些條件協助縮小跟蹤的輸出，這往往是一個好主意。下面給出常用過濾條件列表。

事件	過濾條件執行個體	用處
ApplicationName(應用程式名稱)	Not like:SQL Profiler	過濾Profiler產生的事件。這是預設的行為
DatabaseID(資料庫標識符)	Equals:<ID of the database to monitor>	過濾特定資料庫產生的事件。資料庫ID:SELECT DB_IC(‘Northwind‘)
Duration(期間)	Greater than or equal:2	對於效能分析，經常會為一個大的工作負載捕捉跟蹤，在大的跟蹤中，許多事件記錄具有比所感興趣更小的持續周期(Duration)。過濾這個事件記錄，因為幾乎沒有可用於最佳化這些SQL活動的餘地
Reads(讀運算元)	Greater than or equal"2	過濾讀操作較小的事件
SPID	Equals:<Database users to monitor>	定位由特定的資料庫使用者發送的查詢

 　　下面給出設定過濾列的方式：

     

 　　5、跟蹤模板

　　SQL Server Profiler可以用自訂事件、資料列和過濾器建立一個跟蹤模板，然後定義一個新的跟蹤，然後重用跟蹤個模板來捕捉一個跟蹤。定義新跟蹤模板的過程類似於定義新跟蹤，步驟如下：

1. 建立一個新的跟蹤。
2. 和前面一樣定義事件，資料列和過濾器。
3. 從檔案=》另存新檔菜單將追蹤定義儲存為跟蹤模板。

　　SQL Server Profiler將自動將新的模板加入到其模板列表中。

　　建立模板：

     

　　儲存模板:

     

　　查看：

     

　　6、跟蹤資料

　　定義了跟蹤以後，單擊運行按鈕將開始捕捉事件並將其顯示在螢幕上，可以看到一系列滾動事件，可以在我們稱之為SQL TV的螢幕上看到系統的運行，可以像DVD播放機一樣或多或少地控制跟蹤，可以使用工具列上的按鈕暫停、開始和停止跟蹤，甚至可以在工作室暫停跟蹤並修改它。

　　一旦完成了SQL Server活動的捕捉，就可以將跟蹤輸出儲存為一個追蹤檔案或一個跟蹤表。儲存到追蹤檔案的跟蹤輸出是一個原生的格式，可以由Profiler開啟以分析SQL查詢。將跟蹤的輸出儲存為一個表，也可以使Profiler在跟蹤表上用SELECT語句來分析其中的SQL查詢。

　　具體的操作為 檔案 =》 另存新檔 =》 跟蹤表。選擇你希望存入的的資料庫和表，然後你就可以像普通表一樣執行各種SQL查詢。

二、跟蹤的自動化

　　Profiler GUI簡化了Profiler跟蹤的收集。不幸的是，這種簡易性有其代價。Profiler工具捕捉的事件進入記憶體中的緩衝以便通過網路反饋給GUI。GUI依賴網路，網路流量可能降低系統的速度並導致緩衝被填滿。這將在較小的程度上影響伺服器的效能。進一步地，當緩衝被填滿，伺服器將開始丟棄事件以避免嚴重地影響伺服器效能。

　　1、使用GUI捕捉跟蹤

　　可以以兩種方法兩建立一個指令碼化跟蹤-手工或者使用GUI。在輕鬆地滿足指令碼的所有要求之間，最簡易的方法就是使用Profiler工具的GUI,需要如下步驟：

1. 定義一個跟蹤；
2. 單擊檔案=》匯出=》指令碼追蹤定義；
3. 必須選擇目標伺服器類型， SQL Server2005/2008；
4. 未檔案命名，並儲存它；

　　這些不走將產生所有步驟跟蹤並將其輸出到一個檔案所需的所有指令碼命令。

　　使用Management Studio手工啟動新的跟蹤:

1. 開啟檔案；
2. 使用系統的相關名稱和路徑替換InsertFileNameHere；
3. 執行指令碼，它將返回帶有TraceId的單列結果集；

　　可以通過SQL Agent自動化這個指令碼的執行，甚至可以使用sqlcmd.exe使用程式從命令列運行這個指令碼。不管使用哪種方法，這個指令碼將啟動跟蹤。如果沒有定義跟蹤停止時間，就必須使用TraceId手工停止跟蹤。

　　2、使用預存程序捕捉跟蹤

　　查看上一節中定義的指令碼，會看到以特定順序條用的一系列命令：

• sp_trace_create:建立一個追蹤定義；
• sp_trace_setevent:添加事件和事件列到跟蹤中；
• sp_trace_setfilter:將過濾器應用到跟蹤；

　　一旦定義了SQL跟蹤持續到跟蹤被停止。因為SQL跟蹤作為一個後端進程持續運行，Managerment Studio會話不需要保持開啟。可以使用SQL Server內建函數fn_trace_getinfo確定正在啟動並執行跟蹤，查詢如下：

SELECT * FROM ::fn_trace_getinfo(default);

　　輸出圖：

     

　　fn_trace_getinfo函數的輸出中，不同的traceid的數量表示SQL Server上活動跟蹤的數量。

第三列(value)表示跟蹤是否正在運行(value=1)或者停止(value=0)。可以通過執行預存程序sp_trace_setstatus停止特定的跟蹤，如traceid=1，如下所示：

EXEC sp_trace_setstatus 1,0;

在跟蹤停止之後，它的定義必須執行sp_trace_setstatus關閉並且從伺服器中刪除，如下所示:

EXEC sp_trace_setstatus 1,2;

　　為了驗證跟蹤成功地停止，重新執行fn_trace_getinfo函數，並確定該函數的輸出不包含該traceid。

　　這種技術所建立的追蹤檔案的格式與Profiler建立的追蹤檔案相同。因此，這種追蹤檔案可以與Profiler建立的檔案以相同的方式進行分析。

　　使用前一小節所概述的預存程序捕捉SQL跟蹤，避免了與Profiler GUI相關的開銷。而且還比Profiler工具提供了管理SQL跟蹤計劃的更大靈活性。

三、結合跟蹤和效能監控器輸出

　　如果自動化了效能監控器捕捉到檔案，又自動化了Profiler資料捕捉到一個檔案。它們覆蓋相同的時間段，那麼就可以在SQL Profiler GUI中一起使用它們。確定跟蹤有StartTime和EndTime資料欄位，按照以下步驟進行：

• 開啟追蹤檔案(當然前提是你曾經 另存新檔=》追蹤檔案)；
• 單擊 檔案=》 匯入效能資料；
• 選擇匯入的效能監控器檔案；

　　執行上面的操作將開啟如下所示對話方塊，這裡允許選擇包含效能監控器計數器。

 

　　選擇了想要包含的計數器之後，單擊OK按鈕將一起開啟Profiler和效能監控器資料。現在，可以開始一起使用跟蹤資料和效能監控器資料。如果在頂部視窗選擇一個時間，它將在效能 監視器中放置一條紅線，顯示資料中事件發生的時間。相反，可以單擊效能監控器資料，表示那段 時間的事件將被選中。這些效能工作得很好，將可以在調整過程中定時使用它們以確認瓶頸和壓力 點，並確定導致這些壓力的特定查詢。

四、SQL Profiler使用要點

　　SQL Profiler使用建議如下：

• 限制事件和資料列的數量；
• 拋棄用於效能分析的啟動事件；
• 限制跟蹤的輸出大小；
• 避免聯機資料列排序；
• 遠程運行Proflier；

　　1、限制事件和資料列

　　在跟蹤SQL查詢時，可以通過過濾事件和資料列來決定哪些SQL活動應該被捕捉。選擇更多的事件造成了大量的跟蹤開銷。資料列不會增加太多的開銷，因為它們只是一個事件類別的特性。因此，知道每個所希望跟蹤事件的原因，並根據必要性來選擇事件是很重要的。

　　最小化捕捉的事件數目量避免SQL Server浪費寶貴的資源頻寬去產生所有的事件。捕捉像鎖和執行計畫這樣的事件時應該小心進行，因為這些事件會使跟蹤輸出變得非常大並降低SQL Server的效能。

　　過濾分兩個階段：預過濾由SQL Server執行，後過濾由使用者執行。預過濾是捕捉SQL Server活動的聯機階段，預過濾提供多種溢出：

• 降低了SQL Server的效能影響，因為產生有限數量的時間；
• 降低跟蹤輸出大小；
• 簡化後過濾操作，首先因為要捕捉的事件更少了；

　　預過濾的唯一缺點是，可能丟失一些徹底分析中需要的重要訊息。

　　2、丟棄效能分析所用的啟動事件

　　所用於效能分析的資訊圍繞一個查詢的資源開銷。想SP:StmtStarting這樣的啟動事件不提供這種資訊，因為只有在事件完成之後，才能計算I/O量、CPU負載和查詢的期間。所以，在跟蹤運行緩慢的查詢以進行效能分析時，不需要捕捉啟動事件。這種資訊由對應的完成事件來提供。

　　什麼情況下適合捕捉啟動事件呢？應該在預期某些SQL查詢因為錯誤而不能結束執行，或者頻繁發現Attention事件的時候捕捉啟動事件。Attention事件一般表示使用者中途撤銷了查詢或者查詢逾時，可能因為查詢運行了太長時間。

　　3、限制跟蹤輸出大小

　　除了預過濾事件和資料列，其他過濾條件也會限制跟蹤輸出的大小。同樣，限制大小可能丟失所關注的總體系統狀態中感興趣的事件。但是，如果關注於開銷較大的查詢，過濾器是有協助的。

　　通過過濾器，能夠篩選執行事件》=2或邏輯讀數量》=100的查詢，因為消耗太低的查詢基本上不需要最佳化。

　　4、避免線上資料列排序

　　在效能分析期間，一般在不同的資料列(如Duration、CPU、Reads)上排序以確定相應數字最大的查詢。如果離線排序，就能降低在與SQL Server互動時必須進行的Profiler活動。排序捕捉到的SQL跟蹤輸出的方法如下：

• 捕捉跟蹤，不做任何排序或分組；
• 另存新檔跟蹤輸出到一個追蹤檔案；
• 開啟追蹤檔案並按照需要在特定的資料列上排序或分組追蹤檔案輸出；

　　5、遠程運行Profiler

　　直接在生產伺服器上運行測試載入器一般不是一個好辦法。Profiler有一個大型的使用者介面，因此，在其他機器上運行它更好。與系統監視器相似，Profiler不應該通過終端機服務工作階段來運行，因為這樣工具的主要部分仍然在伺服器上運行。在直接將跟蹤輸出收集到一個檔案時，儲存在Profiler啟動並執行本地檔案上。這仍然是比通過系統預存程序將Profiler作為伺服器端跟蹤來運行更加資源密集的操作。使用系統預存程序仍然是最好的選擇。

　　6、限制使用某些事件

　　某些事件的開銷比其他的事件大。由於產生的查詢的特性，陳述式完成事件的開銷可能非常大。需要謹慎地使用，特別是在已經遇到壓力的系統上，必須謹慎使用的事件有：Showplan XML事件，Performance:Showplan XML、Performance:Showplan XML for Query Compile和Performance:Showplan XML sTATISTICS Prifile。雖然這些事件可能有用，但是不要在生產機器上使用它們。

五、沒有Profiler的情況下查詢效能度量

　　建立一個跟蹤能收集許多資料供以後使用，但是這種收集可能代價很大，必須等待結果。

　　如果要立即捕捉系統的效能度量，特別是關於查詢效能的度量，那麼動態管理檢視sys.dm_exec_query_stats正式所需要的。如果還需要查詢運行及其單獨開銷的記錄，那麼跟蹤仍然是更好的工具。但是，如果只需要知道這時候已耗用時間最長的查詢或者最多的物理讀操作，則可以從sys.dm_exec_query_stats得到這些資訊。

　　因為sys.dm_exec_query_stats只是一個視圖，可以簡單地對其進行查詢並獲得伺服器上查詢計劃統計的資訊。

列	描述
Plan_handle	引用執行計畫的指標
Creation_time	計劃建立的時間
Last_execution time	查詢最後一次使用的計劃時間
Execution_count	計劃已經使用的次數
Total_worker_time	從建立起計劃使用的CPU時間
Total_logical_reads	從建立器計劃使用的讀運算元量
Total_logical_writes	從建立器計劃使用的寫運算元量
Query_hash	可用於識別有相似邏輯的查詢的一個二進位hash
Query_plan_hash	可用於識別有相似邏輯的計劃的一個二jinzhihash

　　為了過濾從sys.dm_exec_query_stats返回的資訊，需要將其串連到其他動態管理函數上，如sys.dm_exec_sql_text可以顯示與計劃相關的查詢文本，sys.dm_query_plan顯示用於查詢的執行計畫。一旦串連到其他DMF，可以限制希望過濾得資料庫或過程。

SQL Server Profiler工具【轉】