SQL Server 監控 使用sp_trace_create

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

監控前言

上一節我們提到了MSSQL的基於SQL Event的監控，但是有些時候我們需要更加詳細、適用於調優排錯的監控。SQL Server內部啟動並執行可見度是的查詢調整、最佳化和綜合排查成為可能！這一節主要和大家說說SQL Server跟蹤（SQL Server Profile）的一些監控方式和途徑。

使用情境

    記得某次給一家公司調優的時候，負責人發給我一堆業務的T-SQL指令碼，我面對海量指令碼還是從容，雖然不瞭解內部複雜的業務，但是我們得專註問題的關鍵 “慢”，我們根據查詢的“慢”把他們篩選出來，一一調式最佳化，不就迅速解決問題嗎？三天后，負責人含淚握著我的手，哥們辛苦了，查詢響應得到了質的改善。

跟蹤提供者

    SQL Server 為我們兩者提供跟蹤的方式：一種是一個物理檔案（可儲存在本機或者UNC網路路徑），一種是行集。對於後者大家應該比較熟悉

這個工具在 SSMS 的 工具 –> SQL Profile

詳細的我暫時不介紹，先說說兩者的區別和類同點 DIFFAndSame(行集,檔案提供者)。

• 兩者都是用類似Buffer來儲存當前的事件數目據，很明顯是為了減少IO的壓力，這樣可以不阻塞和盡量不遺漏 事件數目據，當Buffer 到達一定量時候可能才會Flush到磁碟或者發送到網路的終端（用戶端）顯示監控行集。
• 物理檔案儲存監控結果的方式的重要保證是不能遺漏任何事件，一旦IO降速的時候，可能會影響到整個T-SQL的執行情況。

    SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN (‘SQLTRACE_LOCK‘,‘IO_COMPLETION‘);

    我使用這個語句來監控TRACE 和IO 完成對我當前機器的影響，我的某個客戶的IO情況：                       

wait_type

waiting_tasks_count

wait_time_ms

max_wait_time_ms

signal_wait_time_ms

IO_COMPLETION

66030898

24377499

3634  

418960

SQLTRACE_LOCK

12007

175943

1001

1281

因為我進行了大量的過濾，因此這個值還是能夠接受的，影響不是特別大。

• 行結果集的方式，其實也是我們最熟悉的，就是使用SQL Server Profile監控GUI 直接展現給我們看到的。但是，我是非常不建議使用的，首先如果Buffer滿了，它有一定的延遲，可能會拋棄事件已清空緩衝區繼續接受事件，而事件沒有發送到Client，也沒有寫到物理檔案，自然就丟失了。比如，SQL Server Profile 在DB伺服器進行監控，因為高負載的機器再用來展示，很有可能就會丟失事件，另外物理檔案方式，其實是接受一個足夠大的Buffer，進行的大塊寫操作，效能是優於行集的。

（行集）

保密性原則

    SQL Server的安全特性會自動過濾 包含隱私的資料，比如密碼。我在我的SSMS中執行了如下的語句：

EXEC sp_password ‘pp‘,‘pp1‘,‘sa‘;

這是修改sa帳號密碼的系統sp，我開啟了SQL Server Profile –> 選擇了T-SQL 監控模版 

然後執行上面的預存程序，監控結果：

監控結果：--*sp_password----------------------------

SQL Server Profile

    使用SQL Server Profile GUI工具還是很多優勢，首先是減少了我們監控的複雜性，可以快速的建立監控，在跟蹤屬性中，可以可以選擇MSSQL為我們提供的模版，包括常用的T-SQL、T-SQL Duration、T-SQL Locks模版分別監控當前DB啟動並執行所有查詢，所有查詢的耗時、所有的鎖定狀態。

在跟蹤屬性 –> 選擇事件選擇 我們可以選擇自己需要的事件，所有的事件在MSDN 都有定義->單擊資料行篩選 可以自訂過濾，排序噪點幹擾因素

（我隨便選擇了一個耗時 = 500 微妙的過濾條件）

其他的模版大家可以自己看看MSDN 手冊，自己嘗試一下：SQL Server 2008 R2 本機  MSDN

伺服器端跟蹤和物理方式收集

    SQL Server Profile 只是對一些預存程序的封裝，我更傾向於，自己定義常用的指令碼，將監控結果儲存在本機，用來大量的分析和存檔。

當然涉及4個預存程序，雖然設定過濾的指令碼非常麻煩，但是SQL Server Profile 可以利用 檔案->匯出 可以匯出監控指令碼意味著，我們不需要編寫複雜的T-SQL 指令碼，不過還是建議大家熟悉這幾個預存程序：

• sp_trace_create 定義跟蹤 ，建立的跟蹤會在sys.traces查詢的到。
• s_trace_setevent 設定監控事件
• sp_trace_setfilter 設定過濾
• sp_trace_setstatus 設定跟蹤的狀態  常用的是  sp_trace_setstatus @traceid,0 停止功能 、sp_trace_setstatus @traceid,2 移除跟蹤，這將導致sys.traces最終查詢不到該跟蹤

其實整個跟蹤還是比較簡單的。我這裡有一個常用的指令碼：

 

可以查詢所有的跟蹤計劃:

SELECT * FROM sys.traces

停止,刪除, 要先停止才能刪除:

EXEC sp_trace_setstatus 2,0    --停止,  第一個參數為SELECT * FROM sys.traces中的ID列
EXEC sp_trace_setstatus 2,2    --刪除

 

 

用來 監控超過指定秒數 和 資料庫 的 批處理和預存程序 語句（超過5MB的檔案，會執行ROLLOVER，根據檔案名稱在後面添加類似_1,_2.trc的跟蹤結果）：

CREATE PROC [dbo].[sp_trace_sql_durtion]

    @DatabaseName nvarchar(128),

    @Seconds bigint,

    @FilePath nvarchar(260)

AS

BEGIN

DECLARE @rc int,@TraceID int,@MaxFileSize bigint;

SET @MaxFileSize = 5;

EXEC sp_trace_create @TraceID OUTPUT,2,@FilePath,@MaxFileSize,NULL;

IF @rc != 0 

    RETURN;

DECLARE @On bit;

SET @On = 1;

EXEC sp_trace_setevent @TraceID,10,35,@On;

EXEC sp_trace_setevent @TraceID,10,1,@On;

EXEC sp_trace_setevent @TraceID,10,13,@On;

EXEC sp_trace_setevent @TraceID,41,35,@On;

EXEC sp_trace_setevent @TraceID,41,1,@On;

EXEC sp_trace_setevent @TraceID,41,13,@On;

SET @Seconds = @Seconds * 1000000;

EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds;

IF @DatabaseName IS NOT NULL

    EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName

EXEC sp_trace_setstatus @TraceID,1

SELECT TraceID = @TraceID;

END

參數非常的明了，資料庫名稱、執行事件超過多少秒、儲存的路徑。

當我們運行這個指令碼一段事件以後，可以快速的發現大量耗時的T-SQL，我們可以通過

SELECT * FROM fn_trace_gettable(N‘監控檔案路徑‘,1);

來查看行方式的結果。

同樣的富有創造力的讀者可以自己建立監控鎖定，監控死結等方式儲存檔案，但是我的建議是儘可能的減少噪音，也就是說我們要達到什麼目地就

建立什麼功能，這樣才能將大問題細化解決。

在《Microsfot SQL Server 2005 技術內幕： T-SQL 程式設計》 中有一個正則，用來將類似的語句全部組合成，只有參數形式替換具體值

的SQL CLR，但是我認為那個正則還有bug，等我空了給大家寫一個，自己也能使用的更完善。

監控異常

    在上個系列中，講述了具體的SQL Event抓去的異常，可以及時通知，但是具體的異常資訊，並不是特別詳細。因此我們可以選擇事件中的

Error來添加有關T-SQL批處理和SP的所有異常，用於分析，這個跟蹤非常有利於我們監控一些異常情況！！！

我建立了一個跟蹤的指令碼，和上面的跟蹤事件的指令碼一樣，超過5MB RollOver。

    我們要週期性執行這個跟蹤，雖然不建議長期開啟，但是定期監控處理異常是有利我們系統更加長時間運作的。

CREATE PROC [dbo].[sp_trace_sql_exception]

    @FilePath nvarchar(260)

AS

DECLARE @rc int,@TraceID int,@Maxfilesize bigint

SET @maxfilesize = 5 

EXEC @rc = sp_trace_create @TraceID output, 2, @FilePath, @Maxfilesize, NULL 

IF (@rc != 0) 

    RETURN;

DECLARE @on bit

SET @on = 1

EXEC sp_trace_setevent @TraceID, 33, 1, @on

EXEC sp_trace_setevent @TraceID, 33, 14, @on

EXEC sp_trace_setevent @TraceID, 33, 51, @on

EXEC sp_trace_setevent @TraceID, 33, 12, @on

EXEC sp_trace_setevent @TraceID, 11, 2, @on

EXEC sp_trace_setevent @TraceID, 11, 14, @on

EXEC sp_trace_setevent @TraceID, 11, 51, @on

EXEC sp_trace_setevent @TraceID, 11, 12, @on

EXEC sp_trace_setevent @TraceID, 13, 1, @on

EXEC sp_trace_setevent @TraceID, 13, 14, @on

EXEC sp_trace_setevent @TraceID, 13, 51, @on

EXEC sp_trace_setevent @TraceID, 13, 12, @on

DECLARE @intfilter int,@bigintfilter bigint;

EXEC sp_trace_setstatus @TraceID, 1

SELECT [email protected]

GOTO finish

ERROR: 

SELECT [email protected]

FINISH: 

定期執行吧，同志們，找異常。。。

預設跟蹤和黑盒跟蹤

    在sys.traces中的TraceID = 1的跟蹤是SQL Server 預設跟蹤，這個跟蹤比較輕量級，一般監控伺服器的啟用停止，對象的建立和刪除，日誌和資料檔案自動成長以及其他資料庫的變化。（監控那些沒事刪錯了表的人，是最好的，當然前提不要都使用一個帳號！）

可以通過

EXEC sp_configure ‘default trace enabled‘,0;

RECONFIGURE WITH OVERRIDE;

來關閉預設跟蹤。

黑盒跟蹤，就是可以協助我們診斷資料庫沒事自個奔了的異常，在MSDN 搜尋sp_create_trace的時候應該也發現了

的選項，那麼我們也能建立一個類似的預存程序來快速的建立黑盒跟蹤，協助我們診斷一些異常！

CREATE PROCEDURE sp_trace_blackbox    @FilePath nvarchar(260)ASBEGIN    DECLARE @TraceID int,@MaxFileSize bigint    SET @MaxFileSize = 25;    EXEC sp_trace_create @TraceID OUTPUT,8,@FilePath,@MaxFileSize    EXEC sp_trace_setstatus @TraceID,1;END

我這裡提供@FilePath = NULL參數，這個預設就儲存在SQL Server的資料檔案夾中。

結尾

  這裡詳細的描述了SQL Server Trace 的各種功能特性，有興趣的朋友可以深入到MSDN研究監控，我這是也只是一筆帶過，也參考了MSDN 和《Microsoft SQL Server 2005調優》那本書，下面的監控可能和大家講述 DDL觸發器監控，C2審核以及SQL Server的事件通知（涉及的Service Broker我會開一個系列和大家詳細說說Service Broker），最後的結束可能就是說說2008的資料收集監控，大家期待吧。休息~

 

 

引用: http://www.cnblogs.com/bhtfg538/archive/2011/01/21/1939706.html

SQL Server 監控 使用sp_trace_create