全面掌握SQL Server Profiler
1. 原理與相關概念介紹
SQL Server Profiler，大家已經非常熟悉。常常在性能優化中使用，本文檔詳細介紹SQL Server Profiler。随着SQL Server2008的釋出，伴随着XEvent的出現，SQL Profiler會慢慢的被替代，但是，Profiler依然是性能調優與故障排除中的一個利器。
SQL Server Profiler的中文意思是SQL Server事件探查，其實SQL Server Profiler就是一個Sql的監視工具，可以捕獲和分析資料庫中發生的相關的事件，例如，存儲過程的執行等，可以具體到每一行Sql語句，每一次操作，和每一次的連接配接。感覺這個工具的作用還是很大的，給大家分享一下，對于新手了解Sql語句的寫法和提高語句的性能方法有很大的幫助。
為什麼要學習這門技能，學習完了之後，可以給我們帶來什麼好處？可以這樣說：區分一個有經驗的DBA與一個新手DBA的差別就在于：有經驗的DBA知道在核實充分利用何種工具可以快速的将問題搞定，如果熟練的掌握了SQL Profiler之後，有經驗的DBA就可以對資料庫的運作狀況進行跟蹤，并且利用跟蹤的資料進行問題的分析與性能的診斷與優化，有時候，甚至可以再問題還沒有發生之前發現潛在的問題。
掌握SQL Profiler的好處列下來：
1.可以監視SQL Server内部發生的各種活動：例如發生在資料庫引擎中的活動，發生在Analysis Services以及Integration Services中的活動。
2.監控并且确認某些查詢，事務相關的性能問題等。
3.可以對執行計劃進行分析。
4.可以監視并且确認SQL Server内部的發生的一些錯誤和警告。
5.可以捕獲資料庫發生的資料，并且還可以對資料庫的壓力進行重放。
6.可以對資料庫中使用者的活動進行審計跟蹤。
7.可以通過多種方式對資料進行分析與跟蹤。
8.可以将跟蹤的資料與系統的性能計數器關聯起來，進而更加全面的檢查發生的問題。
9.可以将跟蹤的資料以DTA來進行分析
10.還可以對SQL Profiler進行程式設計，可以不使用它的GUI工具。
1.1SQL Server Profiler的工作原理
其實SQL Server Profiler這個工具是SQL Trace的一個GUI的版本，而SQL Trace是一組腳本，自SQL Server 2000就已經有了的，這一組腳本捕獲和跟蹤SQL Server内部發生一些事件，并且将跟蹤的資料儲存以便後續使用。從本質上說，SQL Trace就是一個非常簡單的工具，它的作用就是捕獲用戶端應用與SQL Server之前的通信資訊。它扮演一個非常特殊的網絡嗅探器的功能，用來捕獲與SQL Server相關的網絡活動，同時也允許我們檢視用戶端應用的請求觸發了SQL Server的哪些事件。另外，與我們熟悉的網絡嗅探器不同的是：SQL Trace隻是捕獲發生在SQL Server内的相關的事件和活動，而不會擷取網絡上面傳輸的資料。
那麼對于上面的圖：
1、SQL Server事件是指由SQL Server本身内部觸發的活動，或者由用戶端與SQL Server進行連接配接時在SQL Server内部觸發的活動。事件包含很多不同的種類。
2、因為事件發生在SQL Server的内部，SQL Trace的任務就是根據Filter來捕獲自己感興趣的事件。
3、一旦事件被SQL Trace捕獲，那麼發生的事件與相關的事件資訊就會被儲存在記憶體的隊列中。
4、SMO(Server Management Objects)，是可以直接的通路SQL Server的對象模型。其實，SQL Trace對于我們而言，就可以将之看成是一個黑盒，我們能做的就是通過一些方式和工具去與這個黑盒互動，SQL Profiler和系統的存儲過程就是這樣的工具。
1.2SQL Profiler中常用的術語概念
事件(Events)。簡而言之就是發生的活動，例如一個存儲過程的執行，就是一個活動，也是發生了一個事件。SQL Profiler允許我們捕獲大約170多種不同的與SQL Server有關的事件。另外，在SQL Profiler中，使用Event Category這個概念，對不同的事件進行了一個大緻的分類，使得為我們的監控更加的友善，例如所有與存儲過程相關的事件都被劃分在一個分類中。
資料列(Data Columns)
簡而言之就是描述事件發生的一些資訊。這個概念和我們在.NET程式設計中的事件資訊的概念是一樣的。如圖所示，可以看到事件發生的時候，包含了一些實事件資訊，即資料列。圖中列出了很多事件的資料列，例如，對于RPC:Completed事件而言，包含了TextData，ApplicationName等。同時，還可以看出 ，不是所有事件都有相同的資料列。
過濾器(Filters)。過濾器主要是SQL Profiler用來對發生的事件進行過濾的，隻捕獲感興趣的事件。過濾器非常有用，因為它可以決定我們收集到的資料的正确性，減少采集的資料量。可以根據很多條件來建立靈活的過濾，如圖所示：
跟蹤(Trace)。請注意：這裡的跟蹤，不是個動詞，而是一個名詞！每一個跟蹤包含了發生的事件以及相關的資料列資訊，并且我們常常将這些資訊儲存在實體的檔案中。其實，我們常常所說的跟蹤，就是一個儲存SQL Profiler資料的跟蹤檔案。跟蹤檔案可以以很多的方式儲存：儲存在記憶體中，以特定的格式導出，儲存在資料庫中，儲存為XML檔案等。下圖就是在SQL Profiler中直接顯示了跟蹤資訊，即跟蹤資訊儲存在記憶體中：
2. 快速使用SQL Server Profiler
在開始使用SQL Profiler之前，有一些問題需要注意。
1、不要随意的在生産環境下（或者說：實際的資料庫工作的伺服器環境）輕易的使用SQL Profiler，特别是在初學的時候。因為使用SQL Profiler會對伺服器産生壓力，帶來一定的性能影響，在初學的時候，不要拿正式環境做實驗。
2、最好在自己的本地搭建測試的資料庫，然後采用一些腳本或者工具來模拟對資料庫的使用。
2.1了解如何配置權限
在SQL Server中，正如不是誰都可以建立表，視圖一樣，也不是誰都可以自由的運作SQL Profiler的。因為SQL Profiler在運作的過程中可以看到很多與伺服器以及用戶端相關的資訊，是以要求運作這個工具的權限也很高。在預設情況下，隻有sa（SQL Server中的一個超級使用者）和在SYSADMIN組中的使用者可以運作SQL Profiler。在SQL Server 2005中，也可以通過用sa或者SYSADMIN使用者給其他使用者授權，進而使得其他的SQL Server使用者也可以有這個權限。授權的SQL 腳本如下圖所示（将LoginID替換為我們想要授權的使用者名）：
下面的一段腳本是收回這個授權的，如圖所示：
2.2啟動SQL Profiler
可以通過很多的方式打開SQL Profiler。
1、使用指令行工具打開。步驟非常簡單，隻要在CMD指令視窗輸入“profiler“，然後按下“回車”就行了。這裡需要注意的是，如果安裝的是SQL Server 2008，那麼就輸入“profiler”，如果安裝的是SQL Server 2005，那麼就輸入“profiler90”。
2、直接在SQL Server Management Studio的菜單中打開，如圖所示：
3、我們單擊開始--程式--Microsoft SQL Server 2005--性能工具--SQL Server Profiler，如圖：
不管用什麼方式，打開SQL Profiler之後，就看到如下的界面，如圖所示：
2.3建立跟蹤任務
我們單擊檔案---【建立跟蹤（N）...】（在菜單“File”中選擇“New Trace”）,這是一個多視窗多任何的工具，我們可以同時建立不同的跟蹤視窗，也可以是不同的資料庫，
在這裡我們輸入我們的跟蹤的資料庫的伺服器名稱，使用者名和密碼等資訊。單擊連接配接進入下一個界面
在這個界面中，包含了兩個頁籤，一個是“General”（正常），另外一個是“Events Selection”（事件選擇）。這裡做一些簡單的介紹，在“General”頁籤中，我們可以對跟蹤進行命名，還可以選擇不同的模闆，并且還可以設定很多不同的選項。其中，Trace Provider name（跟蹤提供程式名稱）就是指的我們要跟蹤的SQL Server的執行個體名；Trace provide type（跟蹤提供程式類型），指的就是資料庫的版本名，而version就是版本的數字表示。在這裡，比較重要的一個選擇就是“Use the template”（使用模闆），如圖所示：
在這裡可以選擇一個跟蹤的模闆，不同的模闆，功能不一樣，并且模闆中事件，資料列，過濾器等都不一樣！每一種模闆的用處。
另外，在“Events Selection頁籤”（事件選擇）中，選擇我們要跟蹤的事件，如圖所示：
上圖中可以進行一個基本設定，使用的模闆選擇，和檔案的儲存選擇等。我們一般使用預設的就OK了，不用動上面的東西。
也可以自己選擇 事件名稱、以及需要顯示的列名資訊。然後将其儲存為模版，友善以後跟蹤直接使用。也就是說我們要跟蹤的事件有那個，在這裡可以一一的選擇，基本上Sql上有的事件都有，包括你用SQL Server Management Studio操作資料庫的過程都可以跟蹤的到。具體的事件和說明大家可以自己看一下。隻要單擊顯示所有事件就可以進行全部事件的選擇了。假如我需要經常使用SQL Server Profiler監控死鎖的資訊，可以如下圖所示操作
選擇自己需要的事件和列名資訊
我們還可以對統計的字段進行篩選，單擊任意一個列标題可以檢視列的說明如下圖，例如執行時間大于1000ms的事務
我們從上圖上依次說明為：
| TextDate | 依賴于跟蹤中捕獲的事件類的文本值 |
| ApplicationName | 建立 SQL Server 連接配接的用戶端應用程式的名稱。此列由該應用程式傳遞的值填充，而不是由所顯示的程式名填充的； |
| NTusername Windows | 使用者名 |
| LoginName | 使用者的登入名(SQL Server 安全登入或 Windows 登入憑據，格式為“域\使用者名”) |
| CPU | 事件使用的 CPU 時間(毫秒)。 |
| Reads | 由伺服器代表事件讀取邏輯磁盤的次數。 |
| Writes | 由伺服器代表事件寫入實體磁盤的次數。 |
| Duration | 事件占用的時間。盡管伺服器以微秒計算持續時間，SQL Server Profiler 卻能夠以毫秒為機關顯示該值，具體情況取決于“工具”>“選項”對話框中的設定 |
| ClientProcessID | 調用 SQL Server 的應用程式的程序 ID。 |
| SPID | SQL Server 為用戶端的相關程序配置設定的伺服器程序 ID。 |
| StratTime | 事件(如果可用)的啟動時間。 |
| EndTime | 事件結束的時間。對訓示事件開始的事件類(例如 SQL:BatchStarting 或 SP:Starting)将不填充此列。 |
| BinaryData | 依賴于跟蹤中捕獲的事件類的二進制值。 |
全部操作完成後，就開始啟動SQL Profiler去監控資料庫中發生的活動：點選“Run”按鈕，如圖所示：
2.4介紹SQL Profiler 的GUI
下面熟悉一下對于SQL Profiler的跟蹤視窗：
1、界面分為兩個部分，在上面标記為紅色的那個區域，在這裡就顯示了Profiler捕獲到的事件，每一行就代表一個發生的事件。
2、不是所有的事件都包含相同的資料列，例如在圖中，有些事件，如Audit Logout, 在TextData列沒有資料，這就說明，TextData不是這個事件的事件資訊列。
3.在界面下半部分的那個紅色區域，顯示了TextData的詳細的資訊（前提是如果事件有TextData列），另外，對于不同的事件，TextData顯示的資料是不一樣的。例如，對與RPC:Completed事件，TextData就顯示了正在執行的存儲過程的名稱。對于一些事件，還可以顯示執行計劃的XML格式。
在界面的最下面，還顯示了“Trace is running”，顯示目前Profiler的狀态。我們還可以點選工具欄中的按鈕，對其中的資料進行分析，查詢，跟蹤可以暫停，開始和停止操作，可以同時啟動多個跟蹤，同時跟蹤不同的資料庫和表
2.5儲存一個跟蹤
當Profiler運作了一段時間之後，就可以停止，并且将跟蹤的資料儲存。這裡需要注意的是：因為我們之前采用了預設的設定，此時資料全部是在伺服器的記憶體中的，如果跟蹤的時間越長，那麼資料量就會越多，會消耗很多的記憶體。
（1）如圖，将跟蹤結果儲存為一個trc檔案，儲存此次跟蹤：
在彈出的界面中輸入跟蹤名稱後資料就儲存起來了，如果要檢視這個跟蹤檔案中的資料，可以再用Profiler加載，如圖所示：
加載之前我們建立的跟蹤檔案，這時候，我們就可以檢視之前資料庫中發生的活動，進而進行分析，如圖所示：
（2）将跟蹤結果儲存到資料庫的某個資料表中。分析時利于進行資料篩選。
SQL Server Profiler跟蹤結束後，依次點選【檔案】→【另存為】→【跟蹤表】，在彈出框中輸入資料庫資訊，點選【連接配接】按鈕。
選擇導出資料庫，輸入表名，點選【确定】按鈕，跟蹤結果即可導出到表log中。
（3）提取死鎖事件
（4）儲存跟蹤模闆
可以選擇另存為-->“跟蹤模版”（當然，也可以自己預定義模版）
也可以将模版導出，友善在别處使用
再次建立跟蹤的時候，看模版選項，就可以看到并使用我們自己儲存的模版了。
3. SQL Profiler腳本自動化
介紹了SQL Profiler 的使用，那麼除了通過GUI界面操作，還可以通過腳本來實作。這樣不用每天都打開 SQL Server Profiler 點一點滑鼠，而是每天定時自動抓取一段時間的資料庫SQL運作情況來進行同比分析，實作長期的SQL語句的性能監控。下面做一些介紹和舉個例子，大家可以通過注釋和聯機文檔中的說明來進行了解。
DECLARE @TraceID int
DECLARE @fileAddress nvarchar(1000)
DECLARE @maxFileSize bigint
DECLARE @endDateTime datetime
DECLARE @result int
DECLARE @strSqlCmd VARCHAR(2000)
--跟蹤檔案儲存在D盤下面，檔案名加上當天的年月日
SET @fileAddress = N'D:\' + Convert(char(10),GetDate(),120)
--如果檔案存在先删除
SET @strSqlCmd= 'DEL '[email protected]+'.trc'
EXEC master.dbo.XP_CMDSHELL @strSqlCmd
--每個檔案最大200M，不限定個數
SET @maxFileSize = 200
--5分鐘後跟蹤結束
SET @endDateTime = DATEADD(minute,5,GETDATE())
--定義一個跟蹤
--參數可以參考聯機文檔，不做詳述
EXECUTE @result = sp_trace_create @TraceID output, 2, @fileAddress, @maxFileSize, @endDateTime
IF (@result != 0) GOTO error
DECLARE @on BIT
SET @on=1
--在跟蹤中添加或删除事件或事件列
--設定需要跟蹤的事件，跟蹤遠端調用存儲過程
EXECUTE sp_trace_setevent @TraceID, 10, 1, @on
EXECUTE sp_trace_setevent @TraceID, 10, 8, @on
EXECUTE sp_trace_setevent @TraceID, 10, 10, @on
EXECUTE sp_trace_setevent @TraceID, 10, 11, @on
EXECUTE sp_trace_setevent @TraceID, 10, 13,@on
EXECUTE sp_trace_setevent @TraceID, 10, 14, @on
EXECUTE sp_trace_setevent @TraceID, 10, 15, @on
EXECUTE sp_trace_setevent @TraceID, 10, 16, @on
EXECUTE sp_trace_setevent @TraceID, 10, 17, @on
EXECUTE sp_trace_setevent @TraceID, 10, 18, @on
EXECUTE sp_trace_setevent @TraceID, 10, 27, @on
EXECUTE sp_trace_setevent @TraceID, 10, 35, @on
--跟蹤 Transact-SQL
EXECUTE sp_trace_setevent @TraceID, 12, 1, @on
EXECUTE sp_trace_setevent @TraceID, 12, 8, @on
EXECUTE sp_trace_setevent @TraceID, 12, 10, @on
EXECUTE sp_trace_setevent @TraceID, 12, 11, @on
EXECUTE sp_trace_setevent @TraceID, 12, 13, @on
EXECUTE sp_trace_setevent @TraceID, 12, 14, @on
EXECUTE sp_trace_setevent @TraceID, 12, 15, @on
EXECUTE sp_trace_setevent @TraceID, 12, 16, @on
EXECUTE sp_trace_setevent @TraceID, 12, 17, @on
EXECUTE sp_trace_setevent @TraceID, 12, 18, @on
EXECUTE sp_trace_setevent @TraceID, 12, 27, @on
EXECUTE sp_trace_setevent @TraceID, 12, 35, @on
--設定需要過濾的條件，不跟蹤 SQL Server Profiler 和 SQLAgent
EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL%'
EXECUTE sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft%'
--按照HostName排除請求
EXECUTE sp_trace_setfilter @TraceID, 8, 0, 7, N'hostname%'
--啟用跟蹤
EXECUTe sp_trace_setstatus @TraceID, 1
SELECT TraceID = @TraceID
error:SELECT ErrorCode = @result
--間隔5:30分鐘跟蹤結束後再傳回
WAITFOR DELAY '00:05:30'
4. 資料庫引擎優化顧問使用方法
有了這個跟蹤的記錄我們怎麼樣使用資料庫引擎優化顧問對其進行分析優化呢？當然第一步我們要先把跟蹤到的記錄導出到檔案.trc類型的檔案。單擊檔案--另存為
假如我們把檔案儲存為123.trc，現在我們來打開資料庫引擎優化顧問 我們單擊開始--程式--Microsoft SQL Server 2005--性能工具--資料庫引擎優化顧問
如下圖
和上面一樣我們要先輸入資料庫的登入資訊，單擊連接配接進入如下界面
我們可以看一下界面，一般不用選擇隻要安預設的就OK了，需要配置的我上面都有注釋。在這裡大家一定要記得選擇用于工作負荷的資料庫和表，意思就是設定要分析的資料庫否則會分析不成功。現在就可以單擊開始了
在這裡我們可以看到分析成功後的分析報告，這還不算，我們單擊上面的建議頁籤
在這裡還給出了你的那些表，需要優化，應該怎麼建立索引和視圖才能更有效的提高性能，更好玩的是連需要優化的Sql語句都給生成好了，我們直接複制執行就要可以了。
轉載于:https://www.cnblogs.com/hfclytze/p/SQLServerProfiler.html