开启两个相同trace引起的性能问题

　　当我们需要对数据库操作进行监控时，我们可以采用t-sql或profiler进行监控。profiler可以提供图形界面，但耗用资源也多。采用t-sql方式，可以将捕获到的事件记录到.trc文件中。<br />　　mssqlserver2005中，可以采用sp_trace_create建立一个trace,同时通过sp_trace_setevent 指定需要监控的事件及事件列，并通过sp_trace_setfilter指定筛选条件，从而实现profiler一样的监控。<br />　　我们在用t-sql的方式启动一个trace时，必须先关闭掉原有的采用profiler方式的监控，否则两个相同的trace一起运行，且这两个trace都包含筛选条件时，将在几分钟内引用cpu性能问题，即cpu的综合使用率高居100%,从而导致整个系统瘫痪．<br />　
　　但监控事件相同，一个含有筛选条件，一个不含有筛选条件时，不会产生cpu性能问题，所以mssqlserver2005 sp3在多个相同trace的运行应该并没有优化，从而造成性能问题 。

 

　　以下为自建trace脚本，用于监控事件：20 audit　login　failed，12 SQL: batch completed，10 RPC:Completed

　　当同时运行以下两个trace时，cpu将出现的性能问题，所以在开启一个trace时，注意是否已存在一个相同的trace.

　　以下为cpu综合使用记录，由40%直至提高到96%：　

record_id

EventTime

CPU%

SystemIdle

OtherProcessUtilization

         7   2009-06-01 14:52:34.480   99   0
         6   2009-06-01 14:53:34.473   97   0

　　　5   2009-06-01 14:52:34.470   87   9 4
　　　4   2009-06-01 14:51:34.463   96   0 4
　　　3   2009-06-01 14:50:34.447   83   13 4
　　　2   2009-06-01 14:49:34.443   50   47 3
　　　1  2009-06-01 14:48:34.440    40   57 3

　　

 

    代码：

 USE [LCHSTUDY]
GO

--trace属性配置表

CREATE TABLE [dbo].[trace_confg](
 [directory] [nvarchar](200) NULL,
 [options] [int] NULL,
 [maxfileSize] [bigint] NULL,
 [filecount] [int] NULL,
 [traceID] [int] NULL
) ON [PRIMARY]

 

--sp:spb_trace_createAndRun
/****** Object:  StoredProcedure [dbo].[spb_trace_createAndRun]    Script Date: 06/01/2009 16:16:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [dbo].[spb_trace_createAndRun]       
AS       
DECLARE @traceID int   
,@tracefile  nvarchar(200)   
,@options  int   
,@maxfileSize  bigint   
,@filecount  int   
declare @retcode int   
   
--读trace配置表   
SELECT TOP 1 @tracefile=directory,@options=options   
  ,@maxfileSize=maxfileSize,@filecount=filecount    
FROM trace_confg   
   
IF @tracefile IS NULL   
  BEGIN    
    PRINT 'ERROR: 表TRACE_CONFG未配置，无法生成trace'   
    RETURN 2   
   END    
IF @maxfileSize IS NULL OR @maxfileSize=0   
set @maxfilesize = 2    
IF CAST(@filecount AS INT)<=0 or CAST(@filecount AS INT) >100   
 set @filecount=40   
   
--生成tracefile名称：'实例名'+'-lchstudy'   
SELECT @tracefile=@tracefile+'/'+REPLACE(@@SERVERNAME,'/','-')+'-lchstudy'   
SELECT @tracefile=replace(@tracefile,'//','/')   
--print @tracefile   
   
--据配置表建立一个trace    
exec @retcode=sp_trace_create  @traceid=@traceID output   
        ,@optins=@options   
        ,@trace_file=@tracefile --N'e:/lch_test/traceFiles/120.trc'   
        ,@maxfile=@maxfileSize,@stoptime=NULL   
        ,@filecount=@filecount    
IF(@retcode!=0)   
  BEGIN        
    PRINT 'ERROR:无法建立trace，ErrorCode:'+CAST(@retcode AS INT)   
    return @retcode --error   
  END   
   
--增加监控事件及列 Set the events   
declare @on bit   
set @on = 1   
exec sp_trace_setevent @TraceID, 20, 8, @on   
exec sp_trace_setevent @TraceID, 20, 12, @on   
exec sp_trace_setevent @TraceID, 20, 1, @on   
exec sp_trace_setevent @TraceID, 20, 9, @on   
exec sp_trace_setevent @TraceID, 20, 6, @on   
exec sp_trace_setevent @TraceID, 20, 10, @on   
exec sp_trace_setevent @TraceID, 20, 14, @on   
exec sp_trace_setevent @TraceID, 20, 11, @on   
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, 1, @on   
exec sp_trace_setevent @TraceID, 10, 9, @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, 6, @on   
exec sp_trace_setevent @TraceID, 10, 14, @on   
exec sp_trace_setevent @TraceID, 12, 15, @on   
exec sp_trace_setevent @TraceID, 12, 8, @on   
exec sp_trace_setevent @TraceID, 12, 16, @on   
exec sp_trace_setevent @TraceID, 12, 1, @on   
exec sp_trace_setevent @TraceID, 12, 9, @on   
exec sp_trace_setevent @TraceID, 12, 17, @on   
exec sp_trace_setevent @TraceID, 12, 6, @on   
exec sp_trace_setevent @TraceID, 12, 10, @on   
exec sp_trace_setevent @TraceID, 12, 14, @on   
exec sp_trace_setevent @TraceID, 12, 18, @on   
exec sp_trace_setevent @TraceID, 12, 11, @on   
exec sp_trace_setevent @TraceID, 12, 12, @on   
exec sp_trace_setevent @TraceID, 12, 13, @on   
   
-- Set the Filters   
declare @intfilter int   
declare @bigintfilter bigint   
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%insert bulk CounterData%'   
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%update DisplayToID%'   
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%sp_MSget_repl_commands%'   
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%sp_MShelp_distribution_agentid%'   
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%sp_MSget_subscription_guid%'   
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%sp_MSsubscription_status%'   
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%SQL Server Profiler%'   
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%repl%'   
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%sqlagent%'   
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%Activitymanager%'   
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%SQL Litespeed%' 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Queue Reader Main (distribution)' 
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Queue Reader History (distribution)' 
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'xx%'   
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'yy%'   
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'user1'   
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'user2'    
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'user3'      
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'user4'    
   
--启动trace   
exec sp_trace_setstatus @TraceID, 1   
   
--将新建立的traceID记录到trace配置表   
update trace_confg set traceid=@traceid     
   
select  @TraceID AS 'TRACE_ID'   
return 0