一、SQL Profiler工具簡介

SQL Profiler是一個圖形界面和一組系統(tǒng)存儲過程，其作用如下：

1.圖形化監(jiān)視SQL Server查詢；

2.在后臺收集查詢信息；

3.分析性能；

4.診斷像死鎖之類的問題；

5.調(diào)試T-SQL語句；

6.模擬重放SQL Server活動；

也可以使用SQL Profiler捕捉在SQL Server實(shí)例上執(zhí)行的活動。這樣的活動被稱為Profiler跟蹤。

　　1、Profiler跟蹤

　　從開始=》所有程序=》Microsoft SQL Server 2008=》性能工具打開Profiler工具，也可以打開SQL Server Management Studio=》工具=》SQL Server Profiler。

　　然后選擇文件=》新建=》跟蹤打開一個連接窗口，選擇將要跟蹤的服務(wù)器實(shí)例然后連接。打開如下“跟蹤屬性”對話框。

　　如果有許多跟蹤，可以提供一個跟蹤名稱來幫助在以后進(jìn)行分類。不同的跟蹤模板可幫助建立用于不同目的的跟蹤。

　　打開跟蹤屬性窗口后，單擊“事件選擇”選項(xiàng)卡，為跟蹤提供更詳細(xì)的定義。

　　2、事件

　　一個事件表現(xiàn)SQL Server中執(zhí)行的各種活動。這些活動可以簡單地分類為事件類，游標(biāo)事件，鎖事件，存儲過程事件和T-SQL事件是常見的事件類。

　　對于性能分析，主要對SQL Server上執(zhí)行的各種活動的資源壓力水平的事件感興趣。資源壓力主要包含如下內(nèi)容：

SQL活動涉及哪一類的CPU使用？使用了多少內(nèi)存？涉及多少I/0操作?SQL活動執(zhí)行了多長時間？特定的查詢執(zhí)行的頻率有多高？查詢面對哪類錯誤和警告？

　　下面給出跟蹤查詢結(jié)束的事件：

　　RPC事件表示存儲過程使用遠(yuǎn)程過程調(diào)用(RPC)機(jī)制通過OLEDB命令執(zhí)行。如果一個數(shù)據(jù)庫應(yīng)用程序使用T-SQL EXECUTE語句執(zhí)行一個存儲過程，那么存儲過程將被轉(zhuǎn)化為一個SQL批而不是一個RPC。RPC請求通常比EXECUTE請求快，因?yàn)樗@過了SQL Server中的許多語句解析和參數(shù)處理。

　　T-SQL由一條或多條T-SQL語句組成。語句或T-SQL語句在存儲過程中也是單獨(dú)和離散的。用SP:StmtCompleted或SQL:StmtCompleted事件捕捉單獨(dú)的語句可能是代價很高的操作，這取決于單獨(dú)語句的數(shù)量。假設(shè)系統(tǒng)中的每個存儲過程包含且只有一條T-SQL語句。在這種情況下，完成的語句集合相當(dāng)小?，F(xiàn)在假定過程中有多條語句，而且這些過程中有些使用其他語句調(diào)用其他過程。收集所有這些額外的數(shù)據(jù)現(xiàn)在變成系統(tǒng)上非常厲害的負(fù)載。在生產(chǎn)機(jī)上一定要慎用。

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

　　光分類就有好多的說：

　　下面給出其他一些與性能診斷有關(guān)的事件:

　　3、事件列

 　　事件以不同的特性(被稱為數(shù)據(jù)列)來表現(xiàn)。數(shù)據(jù)列表現(xiàn)一個事件的不通特性，如事件的類、用于該事件的SQL語句、事件的資源開銷以及事件來源。

 　　以上是常用的數(shù)據(jù)列，另外還有一些不太常用的數(shù)據(jù)列：

　　BinaryData(二進(jìn)制數(shù)據(jù))　　IntegerData(整數(shù)數(shù)據(jù))　　EventSubClass(事件子類)　　DatabaseID(數(shù)據(jù)庫標(biāo)識符)　　ObjectID(對象標(biāo)識符)　　IndexID(索引標(biāo)識符)　　TransactionID(事務(wù)標(biāo)識符)　　Error(錯誤)　　EndTime(結(jié)束時間)

　　列數(shù)據(jù)可以重新安排以符合你自己所喜歡的風(fēng)格，要控制列數(shù)據(jù)的安放，單擊組織列按鈕，將打開如下對話框。可以單擊Up和Down按鈕修改列的位置，將列移入Groups意味著它將成為一個合計(jì)列。

　　4、列篩選器

　　除了為一個Profiler跟蹤定義事件和數(shù)據(jù)列之外，還可以定義各種過濾條件。這些條件幫助縮小跟蹤的輸出，這往往是一個好主意。下面給出常用過濾條件列表。

 　　下面給出設(shè)置過濾列的方式：

 　　5、跟蹤模板

　　SQL Server Profiler可以用自定義事件、數(shù)據(jù)列和過濾器創(chuàng)建一個跟蹤模板，然后定義一個新的跟蹤，然后重用跟蹤個模板來捕捉一個跟蹤。定義新跟蹤模板的過程類似于定義新跟蹤，步驟如下：

創(chuàng)建一個新的跟蹤。和前面一樣定義事件，數(shù)據(jù)列和過濾器。從文件=》另存為菜單將跟蹤定義保存為跟蹤模板。

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

　　新建模板：

　　保存模板:

　　查看：

　　6、跟蹤數(shù)據(jù)

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

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

　　具體的操作為 文件 =》 另存為 =》 跟蹤表。選擇你希望存入的的數(shù)據(jù)庫和表，然后你就可以像普通表一樣執(zhí)行各種SQL查詢。

二、跟蹤的自動化

　　Profiler GUI簡化了Profiler跟蹤的收集。不幸的是，這種簡易性有其代價。Profiler工具捕捉的事件進(jìn)入內(nèi)存中的緩沖以便通過網(wǎng)絡(luò)反饋給GUI。GUI依賴網(wǎng)絡(luò)，網(wǎng)絡(luò)流量可能降低系統(tǒng)的速度并導(dǎo)致緩沖被填滿。這將在較小的程度上影響服務(wù)器的性能。進(jìn)一步地，當(dāng)緩沖被填滿，服務(wù)器將開始丟棄事件以避免嚴(yán)重地影響服務(wù)器性能。

　　1、使用GUI捕捉跟蹤

　　可以以兩種方法兩創(chuàng)建一個腳本化跟蹤-手工或者使用GUI。在輕松地滿足腳本的所有要求之間，最簡易的方法就是使用Profiler工具的GUI,需要如下步驟：

定義一個跟蹤；單擊文件=》導(dǎo)出=》腳本跟蹤定義；必須選擇目標(biāo)服務(wù)器類型， SQL Server2005/2008；未文件命名，并保存它；

　　這些不走將生成所有步驟跟蹤并將其輸出到一個文件所需的所有腳本命令。

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

打開文件；使用系統(tǒng)的相關(guān)名稱和路徑替換InsertFileNameHere；執(zhí)行腳本，它將返回帶有TraceId的單列結(jié)果集；

　　可以通過SQL Agent自動化這個腳本的執(zhí)行，甚至可以使用sqlcmd.exe使用程序從命令行運(yùn)行這個腳本。不管使用哪種方法，這個腳本將啟動跟蹤。如果沒有定義跟蹤停止時間，就必須使用TraceId手工停止跟蹤。

　　2、使用存儲過程捕捉跟蹤

　　查看上一節(jié)中定義的腳本，會看到以特定順序條用的一系列命令：

sp_trace_create:創(chuàng)建一個跟蹤定義；sp_trace_setevent:添加事件和事件列到跟蹤中；sp_trace_setfilter:將過濾器應(yīng)用到跟蹤；

　　一旦定義了SQL跟蹤持續(xù)到跟蹤被停止。因?yàn)镾QL跟蹤作為一個后端進(jìn)程持續(xù)運(yùn)行，Managerment Studio會話不需要保持打開?？梢允褂肧QL Server內(nèi)建函數(shù)fn_trace_getinfo確定正在運(yùn)行的跟蹤，查詢?nèi)缦拢?/p>

　　輸出圖：

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

第三列(value)表示跟蹤是否正在運(yùn)行(value=1)或者停止(value=0)?？梢酝ㄟ^執(zhí)行存儲過程sp_trace_setstatus停止特定的跟蹤，如traceid=1，如下所示：

在跟蹤停止之后，它的定義必須執(zhí)行sp_trace_setstatus關(guān)閉并且從服務(wù)器中刪除，如下所示:

　　為了驗(yàn)證跟蹤成功地停止，重新執(zhí)行fn_trace_getinfo函數(shù)，并確定該函數(shù)的輸出不包含該traceid。

　　這種技術(shù)所創(chuàng)建的跟蹤文件的格式與Profiler創(chuàng)建的跟蹤文件相同。因此，這種跟蹤文件可以與Profiler創(chuàng)建的文件以相同的方式進(jìn)行分析。

　　使用前一小節(jié)所概述的存儲過程捕捉SQL跟蹤，避免了與Profiler GUI相關(guān)的開銷。而且還比Profiler工具提供了管理SQL跟蹤計(jì)劃的更大靈活性。

三、結(jié)合跟蹤和性能監(jiān)視器輸出

　　如果自動化了性能監(jiān)視器捕捉到文件，又自動化了Profiler數(shù)據(jù)捕捉到一個文件。它們覆蓋相同的時間段，那么就可以在SQL Profiler GUI中一起使用它們。確定跟蹤有StartTime和EndTime數(shù)據(jù)字段，按照以下步驟進(jìn)行：

打開跟蹤文件(當(dāng)然前提是你曾經(jīng) 另存為=》跟蹤文件)；單擊 文件=》 導(dǎo)入性能數(shù)據(jù)；選擇導(dǎo)入的性能監(jiān)視器文件；

　　執(zhí)行上面的操作將打開如下所示對話框，這里允許選擇包含性能監(jiān)視器計(jì)數(shù)器。

　　選擇了想要包含的計(jì)數(shù)器之后，單擊OK按鈕將一起打開Profiler和性能監(jiān)視器數(shù)據(jù)?，F(xiàn)在，可以開始一起使用跟蹤數(shù)據(jù)和性能監(jiān)視器數(shù)據(jù)。如果在頂部窗口選擇一個時間，它將在性能 監(jiān)視器中放置一條紅線，顯示數(shù)據(jù)中事件發(fā)生的時間。相反，可以單擊性能監(jiān)視器數(shù)據(jù)，表示那段 時間的事件將被選中。這些性能工作得很好，將可以在調(diào)整過程中定時使用它們以確認(rèn)瓶頸和壓力 點(diǎn)，并確定導(dǎo)致這些壓力的特定查詢。

四、SQL Profiler使用要點(diǎn)

　　SQL Profiler使用建議如下：

限制事件和數(shù)據(jù)列的數(shù)量；拋棄用于性能分析的啟動事件；限制跟蹤的輸出大小；避免聯(lián)機(jī)數(shù)據(jù)列排序；遠(yuǎn)程運(yùn)行Proflier；

　　1、限制事件和數(shù)據(jù)列

　　在跟蹤SQL查詢時，可以通過過濾事件和數(shù)據(jù)列來決定哪些SQL活動應(yīng)該被捕捉。選擇更多的事件造成了大量的跟蹤開銷。數(shù)據(jù)列不會增加太多的開銷，因?yàn)樗鼈冎皇且粋€事件類的特性。因此，知道每個所希望跟蹤事件的原因，并根據(jù)必要性來選擇事件是很重要的。

　　最小化捕捉的事件數(shù)量避免SQL Server浪費(fèi)寶貴的資源帶寬去生成所有的事件。捕捉像鎖和執(zhí)行計(jì)劃這樣的事件時應(yīng)該小心進(jìn)行，因?yàn)檫@些事件會使跟蹤輸出變得非常大并降低SQL Server的性能。

　　過濾分兩個階段：預(yù)過濾由SQL Server執(zhí)行，后過濾由用戶執(zhí)行。預(yù)過濾是捕捉SQL Server活動的聯(lián)機(jī)階段，預(yù)過濾提供多種溢出：

降低了SQL Server的性能影響，因?yàn)樯捎邢迶?shù)量的時間；降低跟蹤輸出大小；簡化后過濾操作，首先因?yàn)橐蹲降氖录倭耍?/p>

　　預(yù)過濾的唯一缺點(diǎn)是，可能丟失一些徹底分析中需要的重要信息。

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

　　所用于性能分析的信息圍繞一個查詢的資源開銷。想SP:StmtStarting這樣的啟動事件不提供這種信息，因?yàn)橹挥性谑录瓿芍?，才能?jì)算I/O量、CPU負(fù)載和查詢的持續(xù)時間。所以，在跟蹤運(yùn)行緩慢的查詢以進(jìn)行性能分析時，不需要捕捉啟動事件。這種信息由對應(yīng)的完成事件來提供。

　　什么情況下適合捕捉啟動事件呢？應(yīng)該在預(yù)期某些SQL查詢因?yàn)殄e誤而不能結(jié)束執(zhí)行，或者頻繁發(fā)現(xiàn)Attention事件的時候捕捉啟動事件。Attention事件一般表示用戶中途撤銷了查詢或者查詢超時，可能因?yàn)椴樵冞\(yùn)行了太長時間。

　　3、限制跟蹤輸出大小

　　除了預(yù)過濾事件和數(shù)據(jù)列，其他過濾條件也會限制跟蹤輸出的大小。同樣，限制大小可能丟失所關(guān)注的總體系統(tǒng)狀態(tài)中感興趣的事件。但是，如果關(guān)注于開銷較大的查詢，過濾器是有幫助的。

　　通過過濾器，能夠篩選執(zhí)行事件》=2或邏輯讀數(shù)量》=100的查詢，因?yàn)橄奶偷牟樵兓旧喜恍枰獌?yōu)化。

　　4、避免在線數(shù)據(jù)列排序

　　在性能分析期間，一般在不同的數(shù)據(jù)列(如Duration、CPU、Reads)上排序以確定相應(yīng)數(shù)字最大的查詢。如果脫機(jī)排序，就能降低在與SQL Server交互時必須進(jìn)行的Profiler活動。排序捕捉到的SQL跟蹤輸出的方法如下：

捕捉跟蹤，不做任何排序或分組；另存為跟蹤輸出到一個跟蹤文件；打開跟蹤文件并按照需要在特定的數(shù)據(jù)列上排序或分組跟蹤文件輸出；

　　5、遠(yuǎn)程運(yùn)行Profiler

　　直接在生產(chǎn)服務(wù)器上運(yùn)行測試工具一般不是一個好辦法。Profiler有一個大型的用戶界面，因此，在其他機(jī)器上運(yùn)行它更好。與系統(tǒng)監(jiān)視器相似，Profiler不應(yīng)該通過終端服務(wù)會話來運(yùn)行，因?yàn)檫@樣工具的主要部分仍然在服務(wù)器上運(yùn)行。在直接將跟蹤輸出收集到一個文件時，保存在Profiler運(yùn)行的本地文件上。這仍然是比通過系統(tǒng)存儲過程將Profiler作為服務(wù)器端跟蹤來運(yùn)行更加資源密集的操作。使用系統(tǒng)存儲過程仍然是最好的選擇。

　　6、限制使用某些事件

　　某些事件的開銷比其他的事件大。由于生成的查詢的特性，語句完成事件的開銷可能非常大。需要謹(jǐn)慎地使用，特別是在已經(jīng)遇到壓力的系統(tǒng)上，必須謹(jǐn)慎使用的事件有：Showplan XML事件，Performance:Showplan XML、Performance:Showplan XML for Query Compile和Performance:Showplan XML sTATISTICS Prifile。雖然這些事件可能有用，但是不要在生產(chǎn)機(jī)器上使用它們。

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

　　建立一個跟蹤能收集許多數(shù)據(jù)供以后使用，但是這種收集可能代價很大，必須等待結(jié)果。

　　如果要立即捕捉系統(tǒng)的性能度量，特別是關(guān)于查詢性能的度量，那么動態(tài)管理視圖sys.dm_exec_query_stats正式所需要的。如果還需要查詢運(yùn)行及其單獨(dú)開銷的歷史記錄，那么跟蹤仍然是更好的工具。但是，如果只需要知道這時候運(yùn)行時間最長的查詢或者最多的物理讀操作，則可以從sys.dm_exec_query_stats得到這些信息。

　　因?yàn)閟ys.dm_exec_query_stats只是一個視圖，可以簡單地對其進(jìn)行查詢并獲得服務(wù)器上查詢計(jì)劃統(tǒng)計(jì)的信息。

　　為了過濾從sys.dm_exec_query_stats返回的信息，需要將其連接到其他動態(tài)管理函數(shù)上，如sys.dm_exec_sql_text可以顯示與計(jì)劃相關(guān)的查詢文本，sys.dm_query_plan顯示用于查詢的執(zhí)行計(jì)劃。一旦連接到其他DMF，可以限制希望過濾得數(shù)據(jù)庫或過程。