[SQL Server][系統資訊收集]SQLDiag.exe(二)混搭Perfmon效能監視器

今天下雨不能練跑,繼續在家認識神話故事中的SQLDiag,上一集用SQLDiag結合Profiler收集資訊,這一集來混搭Perfmon效能監視器,透過Profiler工具整合效能資訊找出效能問題。

 

警語及其應注意事項:

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

 

步驟: 

1.設定SQLDiag.xml啟用效能監視器

2.設定SQLDiag.xml 指定效能監視器開啟的項目

3.設定SQLDiag.xml 指定Profiler錄製項目

4.啟動SQLDiag

5.執行測試

6.檢視效能監視器檔案(Perfmon)

7.檢視效能監視器檔案(Profiler)

8.sys.dm_exec_query_stats

 


設定SQLDiag.xml啟用效能監視器

使用Visual Studio開啟

找到PerfmonCollector的區塊,使用熱鍵CTRL + J 選true開啟效能監視器Perfmon

 


設定SQLDiag.xml 指定效能監視器開啟的項目

因為預設所有Perfmon的計數項目都是開啟的,收集太多資訊可能會造成選擇困難症,所以我們得先關閉不必要的PerfmonCounter,也節省.BLG監視檔案空間使用。

關閉PerfmonCounter:  這邊選取perfmoncounter範圍,熱鍵Ctrl + H 取代,取代"選取範圍"

取代1,497個指定項目

開啟PerfmonCounter項目: 我們只Processor(*)下的\% Processor Time

*可以依照自己的需求開啟效能監視器,推薦參考Rico大的[SQL SERVER]SQL Server Perfmon Counters或是dba的第21天文章

 


設定SQLDiag.xml 指定Profiler錄製項目

設定ProfilerCollector啟動

設定要追蹤以下資訊: 

<EventType name="Stored Procedures"> 中的 SP:StmtCompleted、SP:StmtStarting

<EventType name="TSQL">中的SQL:BatchCompleted、SQL:BatchStarting

別忘了熱鍵CTRL + S存檔

 


啟動SQLDiag

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

 


執行測試

打開SSMS

第一個查詢視窗

USE master

SELECT TABLE_NAME,TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
WAITFOR DELAY '00:00:02'

GO 60

 

第二個查詢

USE master

SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)
INTO #temp
FROM sys.objects o1
JOIN sys.objects o2 ON o1.object_id < o2.object_id
JOIN sys.objects o3 ON o1.object_id < o3.object_id

SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))
FROM #temp o1
JOIN #temp o2 ON o1.MyInt < o2.MyInt

 

執行30秒後停下交易

熱鍵Ctrl + C 停止SQLDiag

 


檢視效能監視器檔案(Perfmon)

除了本來的.trc,這次也多了.BLG檔案,

 

可以單獨打開效能監視器,可以發現從11:03:30左右,CPU開始飆高

但這樣其實有點不方便檢視,我們可以試試從Profiler工具匯入效能資訊。

 


檢視效能監視器檔案(Profiler)

1.先用Profiler先打開.trc檔案

2.然後檔案 > 匯入效能資料 > 選取剛剛的.BLG檔案

3.選取要關聯的counter項目

4.然後找CPU飆高的時間點附近,那些SQL交易正在執行

5.最後就是語法判讀和調校了。

 

如果改用DMV sys.dm_exec_query_stats 擷取last_worker_time感覺會是更輕量的選擇


SELECT top 100
	a.creation_time
	,a.last_execution_time
	,a.execution_count
	,a.last_worker_time
	,a.last_elapsed_time
	,a.total_rows
	,a.last_logical_reads
	,a.last_logical_writes
	,a.last_physical_reads
	,DB_NAME(b.dbid)
	,b.text AS sql_text
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) b
ORDER BY a.last_worker_time desc 

執行結果:

 

上帝在看著你,總會找到原因的。

巴塞隆納-聖家堂

 


參考

SQLdiag 公用程式

SQLDIAG Utility

SQLDiag Configuration Tool

Running SQLDiag as Agent Job

Rico大的[SQL SERVER]SQL Server Perfmon Counters