经常会遇到卡慢的问题

经常会不能快速找到问题点

特别是针对数据库的慢的问题

需要有一个好的跟踪方法

在实际工作中以下代码非常有用。

USE [msdb]
GO
 
/****** Object:  Job [_temp_monitor]    Script Date: 08/23/2012 06:41:49 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @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)
BEGIN
EXEC @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 OUTPUT
IF (@@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*1000
2012-08-23：将跟踪的记录表存放在msdb中
*/
 
set nocount on
 
 
/***************************************************/
declare @scan_periad int --秒
set @scan_periad=3
declare @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 null
begin
  -- 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=1
begin
  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