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

2017-11-25

1028
0
SQLTrace

今天下雨不能練跑，繼續在家認識神話故事中的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

執行結果: