关闭

sql跟踪日志trc导入数据库的2种方法

849人阅读 评论(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
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
/*************************************************************/
 
如果没有导入成功报错,大部分情况是因为列不够引起的,错误代码一般是这样
 
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
 
 
附上一张完成后的截图
 
 
 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1225212次
    • 积分:11735
    • 等级:
    • 排名:第1274名
    • 原创:42篇
    • 转载:384篇
    • 译文:0篇
    • 评论:75条
    最新评论