今天下雨不能練跑,繼續在家認識神話故事中的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
執行結果:
上帝在看著你,總會找到原因的。
巴塞隆納-聖家堂
參考
Rico大的[SQL SERVER]SQL Server Perfmon Counters