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