[转]sql跟踪日志trc导入数据库的2种方法
2009-12-11 16:02 山不转水转... 阅读(566) 评论(0) 编辑 收藏 举报第一种。使用函数导入到某个库中
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;
GO
SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO temp_trc
FROM 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
go
set QUOTED_IDENTIFIER ON
go
ALTER 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
/*************************************************************/
@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): 15
WARNING: Event 10 does not contain the following recommended column(s): 13, 16, 17, 18
ERROR: Event 12 does not contain the following required column(s): 15
WARNING: Event 12 does not contain the following recommended column(s): 13, 16, 17, 18
ERROR: Event 14 does not contain the following required column(s): 15
ERROR: Event 15 does not contain the following required column(s): 15
WARNING: Event 15 does not contain the following recommended column(s): 1, 13, 16, 17, 18
ERROR: 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 120
READER: Total Read Count: 3 Read Stall Count: 0 accounting for 0 ms in reader thread
Signaling all workers to complete
Handling final IOs and closing open files
Flushing and Compressing SPID Files
Total Events Processed: 1
这时候就要在应用事件查探器里增加选取以下"数据列"监视
TSQL:
exec Propared SQL
SQL:BatchCompleted
SQL:BatchStarting
安全审核:
Audit login
Audit Logout
存储过程:
RPC:Completed
RPC:Starting
SP:Completed
SP:StmtCompleted
SP:StmtStarting
会话:
ExistingConnection
性能:
Show Plan statistics
数据列
DatabaseID
EndTime
ObjectID
NestLevel
IntegerData
EventClass
TextData
ApplicationName
NTUserName
Loginname
CPU
Reads
Writes
Duration
ClientProcessID
SPID
StartTime
附上一张完成后的截图
