如何正确使用事件探察器解决性能问题

View Post

系统响应缓慢，我们通常打开事件探察器进行跟踪。这真是火上加油啊！

SQLServer2005下有170个事件及65个属性列，用于跟踪数据库的使用情况。在使用事件探察器时，因为要在内存中保存结果和占用处理器资源以产生这些跟踪事件。因此，我们在以生产服务器做跟踪时，一定要最低限度的产生这些跟踪事件。以防止对服务器产生过大的资源占用。有人会说如果我在另一台机器进行跟踪不就得了，因为要传递这些事件的消息，受网络影响可能会丢失一些数据。因此，一般还是在生产机进行跟踪。但是，一定要注意正确的使用跟踪，以免让服务器更忙。

select * from sys.traces

如果你没有运行事件探察器，运行上面的查询会发现有一个id列值为1的跟踪。这个是系统自带的跟踪，是随服务启动面运行的。跟踪结果保存成文件，保存于系统安装目录下的log目录下。这些文件的大小是128K，如果日志不满128K，是不会写日志的。例外情况是在系统关闭时，不管有没有满128K，都会写日志。并且下次重启服务时，不会再使用这个日志文件。这也就是造成日志文件不满128K的原因。

文件的名称是按照数字递增一直往后累加的，直到你把这些日志文件全部删除后。才又会从log.trc重新开始命名文件。这个默认的跟踪只跟踪一些不是很频繁的事件（比如文件增长、用户登录、创建对象、连接没有on、排序结果过大的警告等），因此这个跟踪对生产数据库的影响是极小的。生产服务器中没有必要一般不要关闭此跟踪。

SQLSERVER的各个组件中都会有各种事件生成器(event producers)，它们默认状态是禁用的。跟踪控制器(trace controller)如果发现有请求要跟踪某事件时，会启动相应的事件生成器。在内部会维护一个全局的位图，用于标识哪个生成器已启动和每个跟踪正在跟踪哪些事件。当有事件发生时，会把相应的信息保存于一个全局事件队列中，等待发送给各个跟踪。每个跟踪在内部会记录它所关心的事件和相应的属性列，在结果送达时只过滤自己感兴趣的信息。

我们在创建自己的跟踪时，会选择把结果保存于文件或表中。保存于文件比表中有两个主要优势：一是可以保证跟踪结果不会丢失，同时不会因为插入表中而造成对日志文件的影响。而且保存于文件时使用缓存，只在缓存满时才记录文件，如果保存的位置于数据库文件位于不同的高速磁盘，效率也是很明显的。最后，我们使用fn_trace_gettable函数把结果导入表中进行分析。为了安全考虑，如果你的过程或视图使用ENCRYPTION进行定义，在跟踪中与这些相关的事件都会被忽略掉。

那么，我们应该如何正确的在生产机进行跟踪呢？

首先，不要使用图形界面进行跟踪。下面是测试专家们测试的结果：

那么，我们应该如何进行跟踪呢？我们可以通过图形界面创建想要跟踪的事件，设置好相应的过滤选项后，运行一下。然后即刻停止。在文件菜单下把它保存成一个跟踪脚本

保存的结果，可以保存到一个存储过程中，正面是全部的定义，只需要把相应的跟踪事件替换为自定义的事件即可：

SET NOCOUNT ON;

USE master;

IF OBJECT_ID('dbo.sp_perfworkload_trace_start') IS NOT NULL

DROP PROC dbo.sp_perfworkload_trace_start;

CREATE PROC dbo.sp_perfworkload_trace_start

@dbid AS INT, --要跟踪的特定数据库

@tracefile AS NVARCHAR(254), --跟踪日志保存的路径，确保目录已存在

@traceid AS INT OUTPUT --返回跟踪的编号，用于停止跟踪使用

DECLARE @rc AS INT;

DECLARE @maxfilesize AS BIGINT;

SET @maxfilesize = 5;

EXEC @rc = sp_trace_create @traceid OUTPUT, 0, @tracefile, @maxfilesize, NULL

IF (@rc != 0) GOTO error;

DECLARE @on AS BIT;

SET @on = 1;

--此事件根据特定的需要进行增删，避免不必要的事件记录

--在完成了远程过程调用(RPC) 时发生

--EXEC sp_trace_setevent @traceid, 10, 15, @on;

--EXEC sp_trace_setevent @traceid, 10, 8, @on;

--EXEC sp_trace_setevent @traceid, 10, 16, @on;

--EXEC sp_trace_setevent @traceid, 10, 48, @on;

--EXEC sp_trace_setevent @traceid, 10, 1, @on;

--EXEC sp_trace_setevent @traceid, 10, 17, @on;

--EXEC sp_trace_setevent @traceid, 10, 10, @on;

--EXEC sp_trace_setevent @traceid, 10, 18, @on;

--EXEC sp_trace_setevent @traceid, 10, 11, @on;

--EXEC sp_trace_setevent @traceid, 10, 12, @on;

--EXEC sp_trace_setevent @traceid, 10, 13, @on;

--EXEC sp_trace_setevent @traceid, 10, 14, @on;

--指示存储过程中的Transact-SQL 语句已执行完毕

EXEC sp_trace_setevent @traceid, 45, 8, @on;

EXEC sp_trace_setevent @traceid, 45, 16, @on;

EXEC sp_trace_setevent @traceid, 45, 48, @on;

EXEC sp_trace_setevent @traceid, 45, 1, @on;

EXEC sp_trace_setevent @traceid, 45, 17, @on;

EXEC sp_trace_setevent @traceid, 45, 10, @on;

EXEC sp_trace_setevent @traceid, 45, 18, @on;

EXEC sp_trace_setevent @traceid, 45, 11, @on;

EXEC sp_trace_setevent @traceid, 45, 12, @on;

EXEC sp_trace_setevent @traceid, 45, 13, @on;

EXEC sp_trace_setevent @traceid, 45, 14, @on;

EXEC sp_trace_setevent @traceid, 45, 15, @on;

--在完成了Transact-SQL 语句时发生

EXEC sp_trace_setevent @traceid, 41, 15, @on;

EXEC sp_trace_setevent @traceid, 41, 8, @on;

EXEC sp_trace_setevent @traceid, 41, 16, @on;

EXEC sp_trace_setevent @traceid, 41, 48, @on;

EXEC sp_trace_setevent @traceid, 41, 1, @on;

EXEC sp_trace_setevent @traceid, 41, 17, @on;

EXEC sp_trace_setevent @traceid, 41, 10, @on;

EXEC sp_trace_setevent @traceid, 41, 18, @on;

EXEC sp_trace_setevent @traceid, 41, 11, @on;

EXEC sp_trace_setevent @traceid, 41, 12, @on;

EXEC sp_trace_setevent @traceid, 41, 13, @on;

EXEC sp_trace_setevent @traceid, 41, 14, @on;

--指示完成了存储过程

EXEC sp_trace_setevent @traceid, 43, 8, @on;

EXEC sp_trace_setevent @traceid, 43, 16, @on;

EXEC sp_trace_setevent @traceid, 43, 48, @on;

EXEC sp_trace_setevent @traceid, 43, 1, @on;

EXEC sp_trace_setevent @traceid, 43, 17, @on;

EXEC sp_trace_setevent @traceid, 43, 10, @on;

EXEC sp_trace_setevent @traceid, 43, 18, @on;

EXEC sp_trace_setevent @traceid, 43, 11, @on;

EXEC sp_trace_setevent @traceid, 43, 12, @on;

EXEC sp_trace_setevent @traceid, 43, 13, @on;

EXEC sp_trace_setevent @traceid, 43, 14, @on;

EXEC sp_trace_setevent @traceid, 43, 15, @on;

--指示已重新编译存储过程

EXEC sp_trace_setevent @traceid, 37, 8, @on;

EXEC sp_trace_setevent @traceid, 37, 16, @on;

EXEC sp_trace_setevent @traceid, 37, 48, @on;

EXEC sp_trace_setevent @traceid, 37, 1, @on;

EXEC sp_trace_setevent @traceid, 37, 17, @on;

EXEC sp_trace_setevent @traceid, 37, 10, @on;

EXEC sp_trace_setevent @traceid, 37, 18, @on;

EXEC sp_trace_setevent @traceid, 37, 11, @on;

EXEC sp_trace_setevent @traceid, 37, 12, @on;

EXEC sp_trace_setevent @traceid, 37, 13, @on;

EXEC sp_trace_setevent @traceid, 37, 14, @on;

EXEC sp_trace_setevent @traceid, 37, 15, @on;

--发生语句级别的重新编译时发生

EXEC sp_trace_setevent @traceid, 166, 8, @on;

EXEC sp_trace_setevent @traceid, 166, 16, @on;

EXEC sp_trace_setevent @traceid, 166, 48, @on;

EXEC sp_trace_setevent @traceid, 166, 1, @on;

EXEC sp_trace_setevent @traceid, 166, 17, @on;

EXEC sp_trace_setevent @traceid, 166, 10, @on;

EXEC sp_trace_setevent @traceid, 166, 18, @on;

EXEC sp_trace_setevent @traceid, 166, 11, @on;

EXEC sp_trace_setevent @traceid, 166, 12, @on;

EXEC sp_trace_setevent @traceid, 166, 13, @on;

EXEC sp_trace_setevent @traceid, 166, 14, @on;

EXEC sp_trace_setevent @traceid, 166, 15, @on;

--指示启动了表或索引扫描

EXEC sp_trace_setevent @traceid, 51, 8, @on;

EXEC sp_trace_setevent @traceid, 51, 16, @on;

EXEC sp_trace_setevent @traceid, 51, 48, @on;

EXEC sp_trace_setevent @traceid, 51, 1, @on;

EXEC sp_trace_setevent @traceid, 51, 17, @on;

EXEC sp_trace_setevent @traceid, 51, 10, @on;

EXEC sp_trace_setevent @traceid, 51, 18, @on;

EXEC sp_trace_setevent @traceid, 51, 11, @on;

EXEC sp_trace_setevent @traceid, 51, 12, @on;

EXEC sp_trace_setevent @traceid, 51, 13, @on;

EXEC sp_trace_setevent @traceid, 51, 14, @on;

EXEC sp_trace_setevent @traceid, 51, 15, @on;

DECLARE @intfilter AS INT;

DECLARE @bigintfilter AS BIGINT;

--根据RowCounts列的多少判断会否有网络压力

--跟踪特定的应用

EXEC sp_trace_setfilter @traceid, 10, 0, 7, N'SQL Server Profiler%';

--只跟踪指定数据库

EXEC sp_trace_setfilter @traceid, 3, 0, 0, @dbid;

--设置跟踪状态

EXEC sp_trace_setstatus @traceid, 1;

PRINT 'Trace ID: ' + CAST(@traceid AS VARCHAR(10))

+ ', Trace File: ''' + @tracefile + '.trc''';

GOTO finish;

error:

PRINT 'Error Code: ' + CAST(@rc AS VARCHAR(10));

finish:

--启动跟踪

DECLARE @dbid AS INT, @traceid AS INT;

SET @dbid = DB_ID('Performance');

EXEC dbo.sp_perfworkload_trace_start

@dbid = @dbid,

@tracefile = 'c:\temp\Perfworkload 20071212',--确保目录已经存在

@traceid = @traceid OUTPUT;

SELECT @traceid

--查看跟踪信息，编号为1的是默认的跟踪，如果系统只有一个用户

--建立的跟踪则编号从2开始，可使用sp_configure系统存储过程将其关闭。

SELECT * FROM sys.traces

--停止并删除跟踪，根据上面的@traceid的返回值填写第一个参数

EXEC sp_trace_setstatus 2, 0;

EXEC sp_trace_setstatus 2, 2;

得到跟踪的结果后，我们可能想对这些结果做一些统计。比如，我想知道哪些过程或语句是最常使用的。它们总的累计执行时间、占用的CPU及I/O计数分别是多少。但是因为过程或动态语句的参数是不一样的，无法直接对其进行统计。下面这个函数可以把参数的部分替换为一个#符号。

--==================================================================

--自定义函数来解决sp_get_query_template系统存储过程带来的性能损失,

--替换查询条件中的参数，使不同的查询语句相同以进行聚合统计

--==================================================================

IF OBJECT_ID(N'dbo.fn_SQLSigTSQL',N'FN') IS NOT NULL

DROP FUNCTION dbo.fn_SQLSigTSQL;

CREATE FUNCTION dbo.fn_SQLSigTSQL

(@p1 NTEXT, @parselength INT = 4000)

RETURNS NVARCHAR(4000)

BEGIN

DECLARE @pos AS INT;

DECLARE @mode AS CHAR(10);

DECLARE @maxlength AS INT;

DECLARE @p2 AS NCHAR(4000);

DECLARE @currchar AS CHAR(1), @nextchar AS CHAR(1);

DECLARE @p2len AS INT;

SET @maxlength = LEN(RTRIM(SUBSTRING(@p1,1,4000)));

SET @maxlength = CASE WHEN @maxlength > @parselength

THEN @parselength ELSE @maxlength END;

SET @pos = 1;

SET @p2 = '';

SET @p2len = 0;

SET @currchar = '';

set @nextchar = '';

SET @mode = 'command';

WHILE (@pos <= @maxlength)

BEGIN

SET @currchar = SUBSTRING(@p1,@pos,1);

SET @nextchar = SUBSTRING(@p1,@pos+1,1);

IF @mode = 'command'

BEGIN

SET @p2 = LEFT(@p2,@p2len) + @currchar;

SET @p2len = @p2len + 1 ;

IF @currchar IN (',','(',' ','=','<','>','!')

AND @nextchar BETWEEN '0' AND '9'

BEGIN

SET @mode = 'number';

SET @p2 = LEFT(@p2,@p2len) + '#';

SET @p2len = @p2len + 1;

END

IF @currchar = ''''

BEGIN

SET @mode = 'literal';

SET @p2 = LEFT(@p2,@p2len) + '#''';

SET @p2len = @p2len + 2;

END

ELSE IF @mode = 'number' AND @nextchar IN (',',')',' ','=','<','>','!')

SET @mode= 'command';

ELSE IF @mode = 'literal' AND @currchar = ''''

SET @mode= 'command';

SET @pos = @pos + 1;

END

RETURN @p2;

END

--使用fn_SQLSigTSQL 函数进行参数替换

SELECT dbo.fn_SQLSigTSQL

(N'SELECT * FROM dbo.Customers WHERE custid > ''C0000000001'' AND custid < ''C0000000011''', 4000);

---------------------------------------------------------------------

-- 分析跟踪数据

---------------------------------------------------------------------

-- 把跟踪信息导入表中进行分析

SET NOCOUNT ON;

USE Performance;

IF OBJECT_ID('dbo.Workload') IS NOT NULL

DROP TABLE dbo.Workload;

SELECT CAST(TextData AS NVARCHAR(MAX)) AS tsql_code,

Duration AS duration

INTO dbo.Workload

FROM sys.fn_trace_gettable('c:\temp\Perfworkload 20060828.trc', NULL) AS T--打开相应的跟踪日志导入表中进行分析

WHERE Duration IS NOT NULL;

WITH TSqlWorkload AS

(

SELECT

dbo.fn_SQLSigTSQL(TextData,4000) AS sig,

duration

FROM dbo.Workload

)

SELECT sig,SUM(duration) AS duration

FROM TSqlWorkload

GROUP BY sig

ORDER BY duration DESC;

在进行性能调整时，我们应该优先调整使用频繁的SQL语句，因此可以很方便的使用上面的结果查出哪些是常用的且耗时的语句。

最后请切记一定不要忘记执行下面的过程关闭并删除跟踪：

--停止并删除跟踪，根据上面的@traceid的返回值填写第一个参数

EXEC sp_trace_setstatus 2, 0;

EXEC sp_trace_setstatus 2, 2;

posted on 2008-04-10 15:47 凉面阅读(1105) 评论(1) 收藏举报

刷新页面返回顶部