[SQL Server][系統資訊收集]SQLDiag.exe(一)啟用SQLProfiler

最近重新和SQLdiag.exe見面,神話傳說中一種以主控台程式或是Windows服務形式來執行的SQL診斷偵測工具,執行檔隨附在SQL Server的安裝目錄BINN之下。她出道的很早,她可以收集Windows相關資源使用、事件紀錄、SQL內的錯誤、封鎖及SQL Profiler可以錄到的資訊,以前認識不深,這次重新認識。

 

警語及其應注意事項:

        因為底層是SQL Trace,根據微軟文件的說明,未來的版本中可能沒辦法支援。不過,檢查了最近會使用的幾個版本,一直到SQL 2017都能使用,如果資料庫伺服器控制權在手上,使用SQLDiag也是資訊紀錄的選擇之一。

 


建立Outout目錄:

*執行SQLDiag時可以指定路徑,或者,依預設,讓SQLdiag將結果寫入執行檔所在的資料夾。

 


產生初始的SQLDiag組態檔案

1.輸入啟動SQLDiag命令,並且指定/P路徑Path= C:\Trace

SQLDIAG /PC:\trace

 

2.服務載入後,接著把服務停下來,CTRL + C停止SQLDiag

噹!產生SQLDaig.XML組態檔案

 


編輯SQLDiag.XML

1.使用地表最強IDE(Visual Studio)打開SQLDiag.XML

2.設定啟用ProfilerCollector

(CTRL + J 出現IntelliSense)

*如果要監控的資料庫是SQL 2008、2008R2及2005,可以試試看SQL Diag Configuration Tool (SDCT)

 

預設DeadLock Graph就是啟用的,這邊我們確認一下就可以,待會用死結練習SQLDiag。

重新啟動SQLDiag: 

SQLDIAG /PC:\trace

此時SQLDIAG視窗會跳出是否要覆蓋LOG檔案,這邊選Y(如果不想出現訊息,可以加上參數/Q,以安靜模式執行)

 


執行測試死結的語法

1.前置條件: 準備中外馳名的northwind北風資料庫

2.死結:

只要讓兩個交易互斥鎖定物件時間長 (15秒)而且存取Table資源的順序不同。

查詢視窗1(先更新客戶資料表再更新員工資料表)-52

USE [Northwind]

BEGIN Tran Tran_one
UPDATE [dbo].[Customers]
    set Region = 'Taipei'
WHERE   City = 'Berlin'

WAITFOR DELAY '00:00:15';  

UPDATE  [dbo].[Employees]
    set  Region = 'Taipei'
WHERE EmployeeID = 1

ROLLBACK

 

查詢視窗2(先更新員工資料表再更新客戶資料表)-53

USE [Northwind]

BEGIN Tran Tran_one
UPDATE  [dbo].[Employees]
    set  Region = 'Taipei'
WHERE EmployeeID = 1

WAITFOR DELAY '00:00:15';  

UPDATE [dbo].[Customers]
    set Region = 'Taipei'
WHERE   City = 'Berlin'

ROLLBACK

 

大約15秒之後,交易53犧牲

 


檢查SQLDiag結果

1.停止SQLDiag

Ctrl + C

 

2.接下來就可以在Trace目錄下的SQLDIAG路徑下找到一個sp_trace.trc 檔案

 


SQL Server Profiler打開trace檔案

CTRL + F 搜尋deadlock關鍵字

Deadlock graph本人出現

如果只是收集死結,還有許多選擇,今天先用Deadlock案例重新認識SQLDiag,下一篇我們來結合效能監視器(Perfmon)。

 

老水手介紹著無畏號

 


SQLDiag疑難排解

如果沒產生 .trc檔案,可以到C:\trace\SQLDIAG\internal\ 目錄,檢查Run_sp_trace.OUT

打開Run_sp_trace.OUT查看錯誤

建立缺少的LOG資料夾

c:\Program Files\Microsoft SQL Server\MSRS12.SQL2014\Reporting Services\LOG

 


小結:

SQL Server 2012、2014、2016、2017都還有SQLDiag的身影

 


參考:

SQLdiag 公用程式

SQLDIAG Utility

SQLDiag Configuration Tool

Running SQLDiag as Agent Job