如何正确使用事件探察器解决性能问题
系统响应缓慢，我们通常打开事件探察器进行跟踪。这真是火上加油啊！
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;
GO
IF OBJECT_ID('dbo.sp_perfworkload_trace_start') IS NOT NULL
DROP PROC dbo.sp_perfworkload_trace_start;
GO
CREATE PROC dbo.sp_perfworkload_trace_start
@dbid AS INT, --要跟踪的特定数据库
@tracefile AS NVARCHAR(254), --跟踪日志保存的路径，确保目录已存在
@traceid AS INT OUTPUT --返回跟踪的编号，用于停止跟踪使用
AS
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:
GO
--启动跟踪
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
GO
--查看跟踪信息，编号为1的是默认的跟踪，如果系统只有一个用户
--建立的跟踪则编号从2开始，可使用sp_configure系统存储过程将其关闭。
SELECT * FROM sys.traces
--停止并删除跟踪，根据上面的@traceid的返回值填写第一个参数
EXEC sp_trace_setstatus 2, 0;
EXEC sp_trace_setstatus 2, 2;
GO
得到跟踪的结果后，我们可能想对这些结果做一些统计。比如，我想知道哪些过程或语句是最常使用的。它们总的累计执行时间、占用的CPU及I/O计数分别是多少。但是因为过程或动态语句的参数是不一样的，无法直接对其进行统计。下面这个函数可以把参数的部分替换为一个#符号。
--==================================================================
--自定义函数来解决sp_get_query_template系统存储过程带来的性能损失,
--替换查询条件中的参数，使不同的查询语句相同以进行聚合统计
--==================================================================
IF OBJECT_ID(N'dbo.fn_SQLSigTSQL',N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_SQLSigTSQL;
GO
CREATE FUNCTION dbo.fn_SQLSigTSQL
(@p1 NTEXT, @parselength INT = 4000)
RETURNS NVARCHAR(4000)
AS
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
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
GO
--使用fn_SQLSigTSQL 函数进行参数替换
SELECT dbo.fn_SQLSigTSQL
(N'SELECT * FROM dbo.Customers WHERE custid > ''C0000000001'' AND custid < ''C0000000011''', 4000);
GO
---------------------------------------------------------------------
-- 分析跟踪数据
---------------------------------------------------------------------
-- 把跟踪信息导入表中进行分析
SET NOCOUNT ON;
USE Performance;
GO
IF OBJECT_ID('dbo.Workload') IS NOT NULL
DROP TABLE dbo.Workload;
GO
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;
GO
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;
GO
