轉載自部落格園.
第一種。使用函數匯入到某個庫中A. 使用 fn_trace_gettable 從追蹤檔案匯入行
以下樣本在 SELECT...INTO 語句的
FROM 子句內部調用 fn_trace_gettable。
USE AdventureWorks;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('c:\temp\my_trace.trc', default);
GO
B. 使用 fn_trace_gettable 返回一個表，其中具有可以載入到 SQL Server 表中的 IDENTITY 列
以下樣本在 SELECT...INTO 語句中調用該函數，並返回一個表，其中具有可載入到表
temp_trc 中的 IDENTITY 列。
USE AdventureWorks;GOSELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO temp_trcFROM fn_trace_gettable('c:\temp\my_trace.trc', default);GO
第二種，使用Read80Trace工具完成（：http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en）
這種方法我在sql2005上沒成功過，會報is not a valid 8.0 trace file format,錯誤，我想是因為2005是9.0版本引起的。
sql2000的話，成功了，使用下面的命令
Read80Trace -f -Slocalhost -Usa -Ppassword -Dtracedb -IE:\profiler\mytrace.trc其中-f是關掉Read80Trace產生的rml檔案，這個檔案也很大，可以不用產生，我運行時就關掉了-I是指定trc檔案所在的位置，如果目錄下面有mytrace_1.trc...mytrace_n.trc，Read80Trace會自動讀取它們.-Dtracedb是將把trace日誌的資料載入的資料庫名稱，所以在-U時需要此使用者具有建立資料的許可權-U -P就不用說了吧，注意命令的格式，-S和localhost中間沒有空格 如果成功會在安裝目錄的的output目錄下面產生以下檔案安裝msxml4.0才能正常顯示（我安裝了6.0打不開），這個報表是這個預存程序執行結果 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[usp_GetAccessPattern]
@duration_filter int=-1 --傳入的參數，可以按照語句執行的時間過濾統計
as begin
/*首先得到全部語句的效能資料的總和*/
declare @sum_total float,@sum_cpu float,@sum_reads float,@sum_duration float,@sum_writes float
select @sum_total=count(*)*0.01,--這是所有語句的總數。
@sum_cpu=sum(cpu)*0.01, --這是所有語句耗費的CPU時間
@sum_reads=sum(reads)*0.01, --這是所有語句耗費的Reads數目，8K為單位。
@sum_writes=sum(writes)*0.01,--這是所有語句耗費的Writes數目，8K為單位。
@sum_duration=sum(duration)*0.01--這是所有語句的執行時間總和。
from tblBatches --這是Read80Trace產生的表，包括了Trace檔案中所有的語句。
where duration>=@duration_filter --是否按照執行時間過濾
/*然後進行Group by，得到某類語句佔用的比例*/
Select ltrim(str(count(*))) exec_stats,''+ str(count(*)/@sum_total,4,1)+'%' ExecRatio,
ltrim(str(sum(cpu)))+' : '++ltrim(str(avg(cpu))) cpu_stats,''+str(sum(cpu)/@sum_cpu,4,1)+'%' CpuRatio,
ltrim(str(sum(reads) ))+' : '+ltrim(str(avg(reads) )) reads_stats,''+str(sum(reads)/@sum_reads,4,1) +'%' ReadsRatio ,
--ltrim(str(sum(writes) ))+' : '+ltrim(str(avg(writes) )) --writes_stats,''+str(sum(writes)/@sum_writes,4,1) +'%)',
ltrim(str(sum(duration) ))+' : '+ltrim(str(avg(duration))) duration_stats,''+str(sum(duration)/@sum_duration,4,1)+'%' DurRatio ,
textdata,count(*)/@sum_total tp,sum(cpu)/@sum_cpu cp,sum(reads)/@sum_reads rp,sum(duration)/@sum_duration dp
into #queries_staticstics from
/* tblUniqueBatches表中存放了所有標準化的語句。*/
(select reads,cpu,duration,writes,convert(varchar(2000),NormText)textdata from tblBatches
inner join tblUniqueBatches on tblBatches.HashId=tblUniqueBatches.hashid where duration>@duration_filter
) B group by textdata --這個group by很重要，它對語句進行歸類統計。
print 'Top 10 order by cpu+reads+duration'
select top 10 * from #queries_staticstics order by cp+rp+dp desc
print 'Top 10 order by cpu'
select top 10 * from #queries_staticstics order by cp desc
print 'Top 10 order by reads'
select top 10 * from #queries_staticstics order by rp desc
print 'Top 10 order by duration'
select top 10 * from #queries_staticstics order by dp desc
print 'Top 10 order by batches'
select top 10 * from #queries_staticstics order by tp desc
End
/*************************************************************/
如果沒有匯入成功報錯，大部分情況是因為列不夠引起的，錯誤碼一般是這樣 WARNING: The following trace events were not captured: SQL:BatchStarting, RPC:Starting, SP:StmtStarting, SP:StmtCompleted, Showplan Statistics. Review the help file to ensure that you have collected the appropriate set of events
and columns for your intended analysis.ERROR: Event 10 does not contain the following required column(s): 15WARNING: Event 10 does not contain the following recommended column(s): 13, 16, 17, 18ERROR: Event 12 does not contain the following required column(s): 15WARNING: Event 12 does not contain the following recommended column(s): 13, 16, 17, 18ERROR: Event 14 does not contain the following required column(s): 15ERROR: Event 15 does not contain the following required column(s): 15WARNING: Event 15 does not contain the following recommended column(s): 1, 13, 16, 17, 18ERROR: Performance analysis has been disabled because one or more events is missing required columns. Consult the help file for the recommended set of trace events and necessary columns.Reads completed - Global Error Status 120READER: Total Read Count: 3 Read Stall Count: 0 accounting for 0 ms in reader threadSignaling all workers to completeHandling final IOs and closing open filesFlushing and Compressing SPID FilesTotal Events Processed: 1 這時候就要在應用事件查探器裡增加選取以下"資料列"監視TSQL:exec Propared SQLSQL:BatchCompletedSQL:BatchStarting安全性稽核:Audit loginAudit Logout預存程序:RPC:CompletedRPC:StartingSP:CompletedSP:StmtCompletedSP:StmtStarting會話:ExistingConnection效能:Show Plan statistics資料列DatabaseIDEndTimeObjectIDNestLevelIntegerDataEventClassTextDataApplicationNameNTUserNameLoginnameCPUReadsWritesDurationClientProcessIDSPIDStartTime 附上一張完成後的
