Job_temp_monitor：监控后台运行缓慢的程序
经常会遇到卡慢的问题
经常会不能快速找到问题点
特别是针对数据库的慢的问题
需要有一个好的跟踪方法
在实际工作中以下代码非常有用。
USE [msdb]GO /****** Object: Job [_temp_monitor] Script Date: 08/23/2012 06:41:49 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 08/23/2012 06:41:49 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_temp_monitor', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'无描述。', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [scan] Script Date: 08/23/2012 06:41:49 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'scan', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' /*2012-08-22: 本版本 [duration]，第一次扫描时候为：[cpu]+[waittime]；之后，如果执行进程的批次相同，则为：[duration]+@scan_periad*10002012-08-23：将跟踪的记录表存放在msdb中*/ set nocount on /***************************************************/declare @scan_periad int --秒set @scan_periad=3declare @longtime int --秒set @longtime=6/***************************************************/ declare @scan_time datetime,@scan_id int declare @waitfor varchar(15)set @waitfor=right(convert(varchar(50),dateadd(second,@scan_periad,''2000-01-01''),121),12) if object_id(''msdb.._duration'') is nullbegin -- drop table msdb.._duration create table msdb.._duration ( [scan_id_base] [int] null, --实体表 独有的列 [scan_time_base] [datetime] null, --实体表 独有的列 [scan_id] [int] null, --实体表 独有的列 [scan_time] [datetime] null, --实体表 独有的列 [duration] [bigint] null, --实体表 独有的列 [spid] [smallint] not null, [waittime] [bigint] not null, [cpu] [int] not null, [status] [nchar](30) not null, [blocked] [smallint] not null, [open_tran] [smallint] not null, [waitresource] [nchar](256) not null, [dbid] [smallint] not null, [physical_io] [bigint] not null, [memusage] [int] not null, [last_batch] [datetime] not null, [cmd] [nchar](16) not null, [cmd_sql] [nvarchar](max) null, [program_name] [nchar](128) not null ) create index ix_scan_time on msdb.._duration(scan_time desc) create unique index uq_index on msdb.._duration(spid,last_batch)end declare @_duration table ( [spid] [int] not null, [waittime] [bigint] not null, [cpu] [int] not null, [status] [nchar](30) not null, [blocked] [smallint] not null, [open_tran] [smallint] not null, [waitresource] [nchar](256) not null, [dbid] [smallint] not null, [physical_io] [bigint] not null, [memusage] [int] not null, [last_batch] [datetime] not null, [cmd] [nchar](16) not null, [cmd_sql] [nvarchar](max) null, [program_name] [nchar](128) not null) select @scan_id=isnull(MAX(scan_id_base),0) from msdb.._duration while 1=1begin select @scan_time=getdate(),@scan_id=@scan_id+1 delete from @_duration -- drop table msdb.._duration -- 获取 长时间运行的 进程 insert into @_duration select * from ( select a.spid,a.waittime,a.cpu,a.status,a.blocked,a.open_tran,a.waitresource,a.dbid ,a.physical_io,a.memusage,a.last_batch ,a.cmd,b.text as cmd_sql,a.program_name from master..sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) b where a.dbid>1 and a.status in (''suspended'',''runnable'') and a.cmd not in (''WAITFOR'') and (a.cpu+a.waittime)>@longtime*1000 ) t update b set b.scan_time=@scan_time,b.scan_id=@scan_id ,b.duration=(b.duration+@scan_periad*1000),b.cpu=a.cpu,b.physical_io=a.physical_io,b.memusage=a.memusage ,b.waittime=a.waittime,b.waitresource=a.waitresource ,b.cmd=a.cmd, /*b.cmd_sql=a.cmd_sql,*/ b.blocked=a.blocked,b.dbid=a.dbid,b.status=a.status from msdb.._duration b inner join @_duration a on b.spid=a.spid and b.last_batch=a.last_batch insert into msdb.._duration select @scan_id,@scan_time,@scan_id,@scan_time,cast(a.cpu+a.waittime as bigint) ,a.* from msdb.._duration b right join @_duration a on b.spid=a.spid and b.last_batch=a.last_batch where b.spid is null waitfor delay @waitfor end go set nocount on select top 100 a.scan_id_base,a.scan_time_base,a.scan_id,a.scan_time,a.spid,a.duration/1000 as [duration(S)],a.waittime,a.cpu,a.blocked,a.open_tran,a.status,a.waitresource,a.physical_io,a.cmd,''set nocount on;select cmd_sql as [ ] from msdb.._duration where scan_id=''+CAST(scan_id as varchar(20))+'' and spid=''+CAST(spid as varchar(50))+'';'',a.duration/1000 as [duration2(S)],a.cmd_sql,a.dbid,a.memusage,a.last_batch,a.program_namefrom msdb.._duration a (nolock)order by scan_time desc ', @database_name=N'master', @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'scan_sche', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=4, @freq_subday_interval=1, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20120823, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'fdba5f82-a338-4767-ae13-c6c718761c4d'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave: GO