SQL Server Profiler檔案匯入Table
一、開啟檔案SqlTrace.trc
D. 點選連接後，選擇資料庫ProfilerDB，在資料表的地方輸入欲存放的資料表tbProfiler，再點選確定
二、至SQL SERVER Management Studio，確認資料是否匯入成功
Select Count(1) From dbo.tbProfiler
三、再下以下語法，查詢耗時的T-SQL
查詢語法如下：
1、提到超過 1 秒的7278 句T-SQL Raw Data
Select TextData [語法], Duration/1000. [耗時(秒)]
From dbo.tbProfiler
Where DURATION >= 1000
2、超過 10 秒鐘的226筆T-SQL Raw Data
Select TextData [語法], Duration/1000. [耗時(秒)]
From dbo.tbProfiler
Where DURATION >= 10000
3、SQL 語法有大量的 * 號沒有 Where，但又 Order By的T-SQL Raw Data
Select TextData, Duration/1000. [耗時(秒)]
From dbo.tbProfiler
Where
TextData LIKE '%SELECT *%' AND
DURATION > 3000
4、Insert 效能不佳的T-SQL Raw Data
Select SUBSTRING(TextData,1,200) [語法], Duration/1000. [耗時(秒)]
From dbo.tbProfiler
Where
TextData LIKE '%Insert%' AND
DURATION > 1000
5. 語法執行統計
select SUBSTRING(TextData,1,200) [語法],COUNT(*) [執行次數],
SUM(Duration)/1000. [總耗時(秒)],AVG(Duration)/1000. [平均耗時(秒)]
from dbo.tbProfiler
WHERE
TextData NOT LIKE '%Backup Database%' AND
TextData NOT LIKE '%Backup LOG%' AND
TextData NOT LIKE '%RESTORE VERIFYONLY%'
GROUP BY SUBSTRING(TextData,1,200)
ORDER BY SUM(Duration) DESC
感謝百敬老師指導~
