创建以及分析跟踪

创建跟踪的存储过程

  1 set nocount on 
  2 use master
  3 go
  4 
  5 if OBJECT_ID('sp_PerfWorkLoad_Strac_Start') is not null
  6     drop proc sp_PerfWorkLoad_Strac_Start
  7 go
  8 
  9 /****************************************************/
 10 /* Created by: SQL Server 2008 R2 Profiler          */
 11 /* Date: 2013/05/15          */
 12 /* Desc:自定义跟踪。根据数据的数据库ID和文件名称作为参数。
 13         指定跟踪的数据库，并把指定的文件名称作为跟踪数据的目的地.
 14         返回最新生成的跟踪ID                        */
 15 /* 使用规则:当处于优化为目的而跟踪生产环境的工作负荷时,
 16             要确保只跟踪那些具有代表性的事件
 17             在一些情况下,只需跟踪几个小时;在另外一些情况下,需要跟踪几天*/
 18 /****************************************************/
 19 create proc sp_PerfWorkLoad_Strac_Start
 20 @dbid int,--数据库id
 21 --@loginName varchar(20)=null,--登录名
 22 @traceFile nvarchar(254),--跟踪文件路径
 23 @traceID int output  --跟踪ID
 24 as
 25 
 26 -- 创建一个队列
 27 declare @rc int
 28 declare @maxfilesize bigint
 29 set @maxfilesize = 5 
 30 
 31 
 32 exec @rc = sp_trace_create @TraceID output, 0,@TraceFile, @maxfilesize, NULL 
 33 if (@rc != 0) goto error
 34 
 35 -- 设置要跟踪的事件
 36 declare @on bit
 37 set @on = 1
 38 exec sp_trace_setevent @TraceID, 10, 15, @on
 39 exec sp_trace_setevent @TraceID, 10, 16, @on
 40 exec sp_trace_setevent @TraceID, 10, 48, @on
 41 exec sp_trace_setevent @TraceID, 10, 1, @on
 42 exec sp_trace_setevent @TraceID, 10, 17, @on
 43 exec sp_trace_setevent @TraceID, 10, 18, @on
 44 exec sp_trace_setevent @TraceID, 10, 11, @on
 45 exec sp_trace_setevent @TraceID, 10, 12, @on
 46 exec sp_trace_setevent @TraceID, 10, 13, @on
 47 exec sp_trace_setevent @TraceID, 10, 14, @on
 48 exec sp_trace_setevent @TraceID, 43, 15, @on
 49 exec sp_trace_setevent @TraceID, 43, 48, @on
 50 exec sp_trace_setevent @TraceID, 43, 1, @on
 51 exec sp_trace_setevent @TraceID, 43, 11, @on
 52 exec sp_trace_setevent @TraceID, 43, 12, @on
 53 exec sp_trace_setevent @TraceID, 43, 13, @on
 54 exec sp_trace_setevent @TraceID, 43, 14, @on
 55 exec sp_trace_setevent @TraceID, 45, 16, @on
 56 exec sp_trace_setevent @TraceID, 45, 48, @on
 57 exec sp_trace_setevent @TraceID, 45, 1, @on
 58 exec sp_trace_setevent @TraceID, 45, 17, @on
 59 exec sp_trace_setevent @TraceID, 45, 18, @on
 60 exec sp_trace_setevent @TraceID, 45, 11, @on
 61 exec sp_trace_setevent @TraceID, 45, 12, @on
 62 exec sp_trace_setevent @TraceID, 45, 13, @on
 63 exec sp_trace_setevent @TraceID, 45, 14, @on
 64 exec sp_trace_setevent @TraceID, 45, 15, @on
 65 exec sp_trace_setevent @TraceID, 12, 15, @on
 66 exec sp_trace_setevent @TraceID, 12, 16, @on
 67 exec sp_trace_setevent @TraceID, 12, 48, @on
 68 exec sp_trace_setevent @TraceID, 12, 1, @on
 69 exec sp_trace_setevent @TraceID, 12, 17, @on
 70 exec sp_trace_setevent @TraceID, 12, 14, @on
 71 exec sp_trace_setevent @TraceID, 12, 18, @on
 72 exec sp_trace_setevent @TraceID, 12, 11, @on
 73 exec sp_trace_setevent @TraceID, 12, 12, @on
 74 exec sp_trace_setevent @TraceID, 12, 13, @on
 75 exec sp_trace_setevent @TraceID, 41, 15, @on
 76 exec sp_trace_setevent @TraceID, 41, 16, @on
 77 exec sp_trace_setevent @TraceID, 41, 48, @on
 78 exec sp_trace_setevent @TraceID, 41, 1, @on
 79 exec sp_trace_setevent @TraceID, 41, 17, @on
 80 exec sp_trace_setevent @TraceID, 41, 18, @on
 81 exec sp_trace_setevent @TraceID, 41, 11, @on
 82 exec sp_trace_setevent @TraceID, 41, 12, @on
 83 exec sp_trace_setevent @TraceID, 41, 13, @on
 84 exec sp_trace_setevent @TraceID, 41, 14, @on
 85 
 86 
 87 -- 设置筛选器
 88 declare @intfilter int
 89 declare @bigintfilter bigint
 90 
 91 --登录名称筛选器
 92 --exec sp_trace_setfilter @TraceID, 11, 0, 6, N'sa'
 93 --数据库筛选器
 94 exec sp_trace_setfilter @TraceID,3,0,0,@dbid;
 95 -- 启动跟踪
 96 exec sp_trace_setstatus @TraceID, 1
 97 
 98 -- 打印跟踪ID和文件名称供以后引用
 99 --select TraceID=@TraceID  自动生成的输出
100 print 'TraceID:'+cast(@traceid as varchar(10))+',TraceFile:'''+@traceFile+'''';
101 goto finish
102 
103 error: 
104 --select ErrorCode=@rc 自动生成的输出
105 print 'ErrorCode:'+cast(@rc as varchar(10));
106 
107 finish: 
108 go

执行存储过程

 1 declare @dbid int,@traceId int,@traceFile nvarchar(254)
 2 set @dbid=DB_ID('Northwind')
 3 set @traceFile='C:\temp\perfWorkLoad 20130515.trc' 
 4 
 5 exec sp_PerfWorkLoad_Strac_Start 
 6      @dbid=@dbid,
 7      @traceFile=@traceFile,
 8      @traceId=@traceId output;
 9      
10 -- 输出
11 -- TraceID:3,TraceFile:'C:\temp\perfWorkLoad 20130515.trc'
12 
13 --停止跟踪
14 exec sp_trace_setstatus 3,0;
15 --关闭跟踪
16 exec sp_trace_setstatus 3,2;
17 --查询所有运行的跟踪信息
18 select * from sys.traces

分析跟踪数据

　　对于处于演示为目的的小型项目，可以直接使用sql自带的profile打开进行分析。对于生产环境，这样做就不现实了，

需要将跟踪数据加载到一个表中并使用查询来分析这些数据。

　　举例：以下代码只返回了TextData 和Duration数据列，它只关注运行时间。通常情况下，你可能还返回CPU、读写次数、应用程序、登陆用户名等

 1 set nocount on 
 2 use Northwind 
 3 go
 4 
 5 if OBJECT_ID('workLoad') is not null
 6     drop table workLoad
 7 go
 8 
 9 select
10 CAST(t.TextData as nvarchar(max)) tSql_Code,
11 t.Duration as duration
12 into workLoad
13 from
14 sys.fn_trace_gettable('C:\temp\perfWorkLoad 20130515.trc',null) as t
15 where t.Duration is not null 
16 
17 --根据执行语句查询累计执行时间，以及每个语句的平均执行时间
18 select
19 t.tSql_Code,
20 COUNT(t.tSql_Code) exec_count,--执行次数
21 SUM(t.duration) totle_durtion, --总体时间
22 cast(1.0*SUM(t.duration)/COUNT(t.tSql_Code) as decimal(12,2)) avg_durtion --平均执行次数
23 from
24 workLoad t
25 group by t.tSql_Code
26 order by totle_durtion desc
27 
28 /*desc:由于存在逻辑上一致或遵循相同模式的查询按照上边的方法会放到不同组中进行统计
29        因为存在只是由于查询条件不同的情况下，可以使用局部字符串进行分组查询，不过也存在不精确的情况
30        具体的子字符串的长度根据你的具体情况进行设定.
31        查询累计执行时间，以及每个语句的平均执行时间.
32        总之,要找到正确的长度存在麻烦,而且准确性和可靠性令人质疑.
33        */
34 select
35 substring(t.tSql_Code,1,100) tSql_Code,
36 COUNT(t.tSql_Code) exec_count,--执行次数
37 SUM(t.duration) totle_durtion, --总体时间
38 cast(1.0*SUM(t.duration)/COUNT(t.tSql_Code) as decimal(12,2)) avg_durtion --平均执行次数
39 from
40 workLoad t
41 group by substring(t.tSql_Code,1,100)
42 order by totle_durtion desc
43 
44 /*desc:使用SQL标示的自定义函数fn_SQLSigTSQL. 对于数据的查询更加准确
45        fn_SQLSigTSQL详情请查看:http://www.cnblogs.com/worfdream/articles/3080203.html
46        fn_SQLSigCRL(推荐使用)详情请查看:http://www.cnblogs.com/worfdream/articles/3080203.html
47        */
48 select 
49 fn_SQLSigCRL(t.tSql_Code) tSql_Code,
50 sum(t.duration) totle_duration
51 from
52 workLoad t
53 group by  fn_SQLSigCRL(t.tSql_Code)

还以使用CLR正则表达式的方式进行查询，更加高效.--现在只有图片没有代码，未完待续

posted on 2013-05-15 17:00 代岳强阅读(353) 评论(0) 编辑收藏举报