最近重新和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的身影
參考: