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

本文介绍如何使用T-SQL在SQL Server 2005中创建跟踪(trace),监控特定数据库事件,如登录失败、批处理完成等,并设置过滤器来优化监控效果。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

  当我们需要对数据库操作进行监控时,我们可以采用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   

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值