3️⃣1️⃣ MS-SQL 效能查詢指令
===
###### tags: `31` `MS-SQL`

--SELECT * FROM sys.traces
--Trace資料檢查

DECLARE @TraceName nvarchar(128)
DECLARE @TracePath nvarchar(256)
DECLARE @TracePath_default nvarchar(256)
DECLARE @TraceFolder nvarchar(256)

/******************** 設定參數 ********************/
DECLARE @LoginName nvarchar(64)=''			--登入帳號，沒填寫就不過濾
DECLARE @TextData_Filler nvarchar(64)=''	--過濾TextData欄位，沒填寫就不過濾

DECLARE @TraceFlag tinyint = 0		--0 檢查 Duration 1 檢查Deadlock
DECLARE @TraceDayRange tinyint = 1	--要取得從現在算起幾天內的資料

/******************** 設定參數結尾 ********************/


--要查詢的追蹤名稱/檔名
SET @TraceName = CASE WHEN @TraceFlag = 0 
					THEN 'DurationLog'
					WHEN @TraceFlag = 1
					THEN 'Deadlock' END

SET @TracePath = (SELECT TOP 1 path FROM sys.traces WHERE path LIKE '%'+@TraceName+'%')
SET @TraceFolder=SUBSTRING(@TracePath,1, CHARINDEX(@TraceName,@TracePath)-1)
--根據串接紀錄規則重新組成整個trace log的檔案名稱(無流水號的檔案名稱)
SET @TracePath_default = @TraceFolder+@TraceName+'.trc'
--SELECT SUBSTRING(@TracePath,1, CHARINDEX(@TraceName,@TracePath)-1), @TraceName, @TracePath

PRINT @TracePath_default

--取得過去時間範圍的資料
SELECT TOP 1000 
	ISNULL(StartTime,EndTime) AS LogTime
	,CONVERT(DECIMAL(18,2),(Duration / 1000000.0)) [Duration Sec]
	,* 
FROM fn_trace_gettable(@TracePath_default, default)
WHERE ISNULL(StartTime,EndTime) >= DATEADD(day,@TraceDayRange * -1,GETDATE())
AND ISNULL(LoginName,'')= CASE WHEN ISNULL(@LoginName,'') <> '' THEN @LoginName ELSE ISNULL(LoginName,'') END
AND TextData LIKE '%'+@TextData_Filler+'%'
ORDER BY ISNULL(StartTime,EndTime) DESC


/*
--只取deadlock的xml資料部分
SELECT TOP (100) TextData,StartTime
FROM fn_trace_gettable(@TracePath_default, default)
WHERE StartTime >= DATEADD(dd,-24,GETDATE())
AND EventClass=148
ORDER BY EndTime DESC
*/