profiler跟踪事件存为表之后性能分析工具

使用profiler建立跟踪，将跟踪结果存到表中，使用下面存储过程执行 exec temp_profiler 'tra_tablename'对表数据进行处理归类，然后进行性能分析

1.先建存储过程

2.再执行下面的查询

--处理trace数据 
--exec temp_profiler 'temp_profiler201509061618'
select top 10000 * from temp_profiler201509061618 order by reads desc
--1）查出最耗时的语句或过程  
select * from temp_profiler201509061618_stat order by total_duration desc
--3) 统计某个过程或者sql语句的个数 
select  proc_sql_id ,count(1) counts from  temp_profiler201509061618  group by  proc_sql_id  order by counts desc 
--2）查询某个过程或者sql语句详情 
select  *  from  temp_profiler201509061618  where proc_sql_id = 3--1047
select  *  from  temp_profiler201509061618  where proc_sql_id = 22--672
select  *  from  temp_profiler201509061618  where proc_sql_id = 18--33

 

exec temp_profiler 'temp_profiler201508210920'
select top 10000 * from temp_profiler201508210920 order by reads desc
select * from temp_profiler201508210920_stat order by total_duration desc
select  proc_sql_id ,count(1) counts from  temp_profiler201508210920  group by  proc_sql_id  order by counts desc
select  *  from  temp_profiler201508210920  where proc_sql_id = 2--5761
select * from temp_profiler_2015060816 where textdata like 'exec PageList%'

 

USE [temp_profiler]
GO

/****** Object:  StoredProcedure [dbo].[temp_profiler]    Script Date: 08/26/2015 10:16:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
--http://www.cnblogs.com/davidhou
-- Author:        <houpeidong>
-- Create date: <20150611>
-- Description:    <profiler抓取到的数据存入表中，此存储过程功能为性能分析工具，表名不能加中括号>
-- =============================================
CREATE PROCEDURE [dbo].[temp_profiler]
@tblName NVARCHAR(500)
as
begin

SET NOCOUNT ON

--DECLARE @tblName NVARCHAR(500) ;  
--set @tblName = 'temp_profiler_20151' ;

DECLARE @sqlStr NVARCHAR(2000);

--10. 对trace表的数据进行处理前的一些准备：
 -- 时间改为毫秒
SET @sqlStr = N'update ' + @tblName +' set duration = duration / 1000 where duration is not null ';
EXECUTE sp_executesql @sqlStr ;

--update temp_profiler_2015060917 set duration = duration / 1000 where duration is not null  -- 时间改为毫秒

    --修改 textdata 为 nvarchar（max）类型，因为textdata默认保存为ntext类型，处理不方便
SET @sqlStr = N'alter table '+@tblName+' alter column textdata nvarchar(max)';
EXECUTE sp_executesql @sqlStr ;
--alter table temp_profiler_2015060917 alter column textdata nvarchar(max)

    --新增两个字段
SET @sqlStr = N'alter table '+@tblName+' add proc_sql nvarchar(max)';
EXECUTE sp_executesql @sqlStr ;
--alter table temp_profiler_2015060917 add proc_sql nvarchar(max) -- 保存该textdata调用的存储过程，原始sql等；

SET @sqlStr = N'alter table '+@tblName+' add proc_sql_id int';
EXECUTE sp_executesql @sqlStr ;
--alter table temp_profiler_2015060917 add proc_sql_id int -- 为存储过程和原始sql指定一个编号

--11. 处理trace数据 
-- 1）找出执行的sql脚本（带参数） ，更新到 proc_sql 字段
SET @sqlStr = N'
update '+@tblName+'
         set proc_sql = replace(left(textdata,charindex('''''',N'''''',textdata) - 1),''exec sp_executesql N'''''','''')
         where (proc_sql is null or proc_sql = '''' )
         and charindex(''exec sp_executesql N'', textdata ) = 1
         ';
EXECUTE sp_executesql @sqlStr ;

--2）找出执行的存储过程，更新到 proc_sql 字段
SET @sqlStr = N'
update '+@tblName+'
         set proc_sql =
         replace(
         replace(
         left(
         right(textdata,len(textdata) - charindex(''exec '',textdata) + 3),
         charindex(''@'',
         right(textdata,len(textdata) - charindex(''exec '',textdata) + 3)
         )
         ),''exec '','''')
         ,''@'','''')
         where (proc_sql is null or proc_sql = '''' )
         and charindex(''exec '',textdata) > 0
         ';
EXECUTE sp_executesql @sqlStr ;
         

--3）找出没有参数的sql脚本，更新到 proc_sql 字段         
--update temp_profiler_2015060917 set proc_sql = textdata where proc_sql is null and textdata is not null
SET @sqlStr = N'update '+@tblName+' set proc_sql = textdata where proc_sql is null and textdata is not null'
EXECUTE sp_executesql @sqlStr ;


--12. 统计
    --1）新建表，用于保存统计数据，trace_20130910每个proc_sql对应一行
SET @sqlStr = N'    
create table ['+@tblName+'_stat]
(
id int identity(1,1) primary key,
databaseid int,
proc_sql nvarchar(max), -- 对应trace_20130910的proc_sql
total_duration bigint, -- 总耗时
max_duration int, -- 该语句最大耗时
min_duration int, -- 该语句最小耗时
rate_duration int -- 所耗时间百分比
)
';
EXECUTE sp_executesql @sqlStr ;



--2）生成统计数据，存入1）步的表中 trace_20130910_stat]
SET @sqlStr = N'
;with cte
(
databaseid,
proc_sql,
total_duration,
max_duration ,
min_duration
) as
(select databaseid,
proc_sql,
sum(duration) as total_duration,
max(duration) as max_duration,
min(duration) as min_duration
from '+@tblName+'
where proc_sql is not null and proc_sql <> ''''
group by databaseid,proc_sql
)
, cte2 as
(-- 总耗时，用来计算百分比
select sum(total_duration) as total_duration from cte
)
insert into ['+@tblName+'_stat]
(
databaseid,
proc_sql,
total_duration,
max_duration ,
min_duration ,
rate_duration
)
select
databaseid,
proc_sql,
total_duration,
max_duration ,
min_duration ,
100 * total_duration / ( select total_duration from cte2 ) as rate_duration
from cte
order by rate_duration desc
';
EXECUTE sp_executesql @sqlStr ;

-- 3）更新记录表[trace_20130910]的 proc_sql_id
SET @sqlStr = N'
update ['+@tblName+'] set proc_sql_id = b.id
         from ['+@tblName+'] a inner join ['+@tblName+'_stat] b
         on a.databaseid = b.databaseid and a.proc_sql = b.proc_sql
';
EXECUTE sp_executesql @sqlStr ;
end ;
GO