擦亮自己的眼睛去看SQL Server之說說跟蹤

作者：小軍人 2011-07-14 09:37:53

SQL Trace是構建服務器跟蹤和Profiler的基礎。如果你了解到這點，那你就會毫不猶豫的在生產環境使用服務器跟蹤。下面從五個方面介紹SQL Trace，一、SQL Trace跟蹤的代價二、SQL Trace架構三、具體跟蹤例子四、如何反跟蹤五、SQL Trace跟蹤原則

這幾天看了下范偉主演的<<跟蹤孔令學>>，再一次欣賞了范偉精湛的演技特別是那種憨厚的表情。看完后，讓我想起了SQLServer中的跟蹤與反跟蹤技術。覺得這部分內容值得寫一篇文章和大家分享分享。了解SQLServer跟蹤技術能讓我們比較簡單的在運行時實時的獲取SQLServer的內部運作。這種獲取方式比我們去使用跟蹤標志、動態管理視圖等來的方便簡單的多。說到跟蹤，很多人會想起SQL Profiler。SQL Profiler僅僅是一個GUI，SQL Trace才是本質。SQL Trace是構建服務器跟蹤和Profiler的基礎。如果你了解到這點，那你就會毫不猶豫的在生產環境使用服務器跟蹤。下面從五個方面介紹SQL Trace，一、SQL Trace跟蹤的代價二、SQL Trace架構三、具體跟蹤例子四、如何反跟蹤五、SQL Trace跟蹤原則

一、SQL Trace跟蹤的代價

必須指出，跟蹤會影響系統的性能這是不可完全避免的。當然可以通過一些方式我們能將這種代價降到最小。很多人往往以跟蹤會影響現網性能為理由而拒絕跟蹤。其實這是不對的，還有一些人平時也做跟蹤，不過他們喜歡在系統不繁忙的時候跟蹤。這樣的做法都是有問題的。前者往往會出現突然間你的系統出現問題，而你完全沒有任何預兆，后者往往會出現你錯過捕獲問題的***時機這樣在不繁忙時的跟蹤等于白費。那什么時候對生產環境進行跟蹤呢?正確的做法應該是每時每刻的收集系統信息，為對系統性能整體分析提供信息來源。

二、SQL Trace架構

如果你想理解SQL Trace，那***的方式莫過于用你自己的系統去對比。一般情況下我們都會在系統中記錄一些日志，根據我們關注的點來區分記錄日志的級別。典型的日志組件就是

Log4net之類的日志組件。這樣我們就能夠通過日志來分析系統的運行情況。知道這點，那理解SQL Trace就容易了。在SQLServer中，跟蹤信息由一系列的事件組成。既然有事件，那誰觸發事件呢。數據庫引擎中的各個組件都是事件的生產者。下面看看SQL Trace的架構圖：

如上圖所示：整個SQL Trace架構有三個部分組成，數據庫引擎、跟蹤控制器、跟蹤會話。數據庫引擎是事件生成者，跟蹤控制器負責事件的分發以及事件的過濾，跟蹤會話負責對事件的列過濾以及跟蹤事件的終點。下面簡單描述下整個過程，跟蹤控制器通過一個位圖讓數據庫引擎的其他組件知道跟蹤器請求了哪些事件，這個位圖是所有跟蹤的事件集合。一旦數據庫引擎生成一個事件后，就把事件信息保存在跟蹤控制器中的隊列中。然后跟蹤控制器把完整的事件信息傳遞給每個要求這個事件的跟蹤會話。跟蹤會話接收到自己關注的事件信息時，先經過過濾器(主要是過濾掉不感興趣的列與行)，過濾掉后發送給跟蹤的I/O提供者。這里面的隊列只是起緩沖作用。I/O提供者有很多種，比如Profiler、服務器跟蹤、SQLServer自己的跟蹤。

三、具體跟蹤例子

這里的例子不想用SQL Profiler進行舉例，因為我覺得它僅僅是方便我們跟蹤而已。但是它在跟蹤時既會把輸出寫入目標文件或者表(然后選擇保存文件中保存表)還有把跟蹤信息寫入運行Profiler的客戶端。把跟蹤信息寫入到運行Profiler客戶端，這個比直接寫入文件往往會慢。大家可以想想為什么?不過倒是可以用Profiler圖形化方式定義跟蹤，然后導出生成的跟蹤SQL。具體如下：

一旦你開啟了跟蹤后，你可以通過：

select * from sys.traces 查看到你正在跟蹤的會話。

四、如何反跟蹤

有時候，我們不希望自己的sql被人跟蹤。比如，我們不希望別人能看到我們程序中寫的sql。方法有很多，這里介紹一種簡單的方法。思路就是：強迫SQLServer停止跟蹤。具體存儲過程如下：

/*+--------------------------------------------------------------------------------------------------------------------------------------- 
*    名稱:        [DBO].[Performance_Trace_StopAll] 
*    功能:        防止反跟蹤 
*    作者:        junling 
*    創建時間:    2011-02-09 
*    項目名稱:    XXXX 
* ----------------------------------------------------------------------------------------------------------------------------------------- 
*    歷史記錄 
*    編號    日期        作者    備注 
*    1.0    2011-02-09    junling    創建 
------------------------------------------------------------------------------------------------------------------------------------------+*/ 
create  proc [dbo].[Performance_Trace_StopAll] 
 
AS 
    declare traceCursor cursor for select id from sys.traces where id <> 1 
    open traceCursor 
    declare @curid int 
    fetch next from traceCursor into @curid 
    while(@@fetch_status=0) 
      begin          
 
          exec  sp_trace_setstatus @curid,0 
          exec  sp_trace_setstatus @curid,2 
          fetch next from traceCursor into @curid 
      end 
    close traceCursor 
    deallocate traceCursor

具體什么時候調用，就是看你具體的情況了。

五、SQL Trace跟蹤原則

這里主要列出我們在跟蹤時應該注意的事項，或者說按照下面的原則會降低跟蹤對生產環境的影響。

1、不要使用Profiler GUI跟蹤，如果使用了盡量不要運行在跟蹤的SQLServer所在服務器;

2、不要把跟蹤數據直接寫入表，我們可以采用系統不是很繁忙時才把跟蹤信息導入表中(除非你想立刻分析數據);

3、跟蹤會有大量的I/O操作，盡量把跟蹤文件單獨放在物理磁盤中;

4、只選擇自己感興趣的事件，多選一個事件都會帶來開銷(除非你多選的事件不發生，那樣也就沒有選擇的必要;

5、過濾你的跟蹤信息，比如你只對某數據庫感興趣，你只對某些列感興趣(注意這里僅僅是減少了架構圖中的I/O提供者的開銷，想想為什么);

6、像XXXXXXStarting之類的事件往往沒有太大意義;

7、要注意你跟蹤的sql中是否使用了標量函數，對這些sql的跟蹤會嚴重影響性能，每個標量函數每處理一行都會觸發事件(如果表很大，這是件很恐怖的事件);

8、只給需要跟蹤的用戶指定跟蹤權限。

六、結尾

今天主要和大家討論了SQLServer的跟蹤方面的知識，其中的知識還有很多值得我們去挖掘，比如事件的分類、SQL Trace目錄視圖的每個列的意義、如何把trc格式文件導入表中分析統計、跟蹤的安全性問題、跟蹤的性能優化等等。在這些方面多花點時間，你會到SQLServer有更好的理解的。

今天分析就到此結束，文中如有描述不當的地方，歡迎指出。共同進步才是硬道理。

原文鏈接：http://www.cnblogs.com/yueyue_jwfm/archive/2011/07/09/2101853.html

【編輯推薦】

擦亮自己的眼睛去看SQL Server之簡單Insert
擦亮自己的眼睛去看SQL Server之簡單Select
擦亮自己的眼睛去看SQL Server之整體介紹

責任編輯：艾婧來源：小軍人的博客

SQL Server