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

這幾天看了下範偉主演的<<跟蹤孔令學>>，再一次欣賞了範偉精湛的演技特別是那種憨厚的表情。看完後，讓我想起了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有更好的理解的。

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