怎样有效地跟踪SQL Server的阻塞问题？(网转）

http://support.microsoft.com/gp/anxin_techtip7/zh-cn

个人优化整理：

USE [gs_gep_lmz]

CREATE TABLE [dbo].[Blocking_SqlText](

[spid] [smallint],

[sql_text] [nvarchar](2000),

[Capture_Timestamp] [datetime]

)

CREATE TABLE [dbo].[Blocking_sysprocesses]( [spid] [smallint] NULL, [kpid] [int] NULL, [blocked] [int] NULL, [waitType] [nvarchar](50) NULL, [waitTime] [nvarchar](50) NULL, [lastWaitType] [nvarchar](50) NULL, [waitResource] [nvarchar](50) NULL, [dbID] [int] NULL, [uid] [int] NULL, [cpu] [int] NULL, [physical_IO] [int] NULL, [memusage] [nvarchar](10) NULL, [login_Time] [datetime] NULL, [last_Batch] [datetime] NULL, [open_Tran] [nvarchar](50) NULL, [status] [nvarchar](50) NULL, [sid] [int] NULL, [hostName] [nvarchar](50) NULL, [program_name] [nvarchar](100) NULL, [hostProcess] [nvarchar](100) NULL, [cmd] [nvarchar](2000) NULL, [nt_Domain] [nvarchar](50) NULL, [nt_UserName] [nvarchar](50) NULL, [net_Library] [nvarchar](50) NULL, [loginame] [nvarchar](50) NULL, [context_Info] [nvarchar](2000) NULL, [sql_Handle] [binary](20) NULL, [Capture_Timestamp] [datetime] NULL )

CREATE PROCEDURE [dbo].[checkBlocking_lmz]

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @Duration int -- in milliseconds, 1000 = 1 sec

declare @now datetime

declare @Processes int

select @Duration = 100 -- in milliseconds, 1000 = 1 sec

select @Processes = 0

select @now = getdate()

CREATE TABLE #Blocks_rg( [spid] [smallint] NULL, [kpid] [int] NULL, [blocked] [int] NULL, [waitType] [nvarchar](50) NULL, [waitTime] [nvarchar](50) NULL, [lastWaitType] [nvarchar](50) NULL, [waitResource] [nvarchar](50) NULL, [dbID] [int] NULL, [uid] [int] NULL, [cpu] [int] NULL, [physical_IO] [int] NULL, [memusage] [nvarchar](10) NULL, [login_Time] [datetime] NULL, [last_Batch] [datetime] NULL, [open_Tran] [nvarchar](50) NULL, [status] [nvarchar](50) NULL, [sid] [int] NULL, [hostName] [nvarchar](50) NULL, [program_name] [nvarchar](100) NULL, [hostProcess] [nvarchar](100) NULL, [cmd] [nvarchar](2000) NULL, [nt_Domain] [nvarchar](50) NULL, [nt_UserName] [nvarchar](50) NULL, [net_Library] [nvarchar](50) NULL, [loginame] [nvarchar](50) NULL, [context_Info] [nvarchar](2000) NULL, [sql_Handle] binary(20) NULL, [Capture_Timestamp] [datetime] NULL )

INSERT INTO #Blocks_rg

SELECT

[spid],

[kpid],

[blocked],

[waitType],

[waitTime],

[lastWaitType],

[waitResource],

[dbID],

[uid],

[cpu],

[physical_IO],

[memusage],

[login_Time],

[last_Batch],

[open_Tran],

[status],

[sid],

[hostName],

[program_name],

[hostProcess],

[cmd],

[nt_Domain],

[nt_UserName],

[net_Library],

[loginame],

[context_Info],

[sql_Handle],

@now as [Capture_Timestamp]

FROM master..sysprocesses where blocked <> 0

AND waitTime > @Duration

SET @Processes = @@rowcount

INSERT into #Blocks_rg

SELECT

src.[spid],

src.[kpid],

src.[blocked],

src.[waitType],

src.[waitTime],

src.[lastWaitType],

src.[waitResource],

src.[dbID],

src.[uid],

src.[cpu],

src.[physical_IO],

src.[memusage],

src.[login_Time],

src.[last_Batch],

src.[open_Tran],

src.[status],

src.[sid],

src.[hostName],

src.[program_name],

src.[hostProcess],

src.[cmd],

src.[nt_Domain],

src.[nt_UserName],

src.[net_Library],

src.[loginame],

src.[context_Info],

src.[sql_Handle]

,@now as [Capture_Timestamp]

FROM master..sysprocesses src inner join #Blocks_rg trgt on trgt.blocked = src.[spid]

if @Processes > 0

BEGIN

INSERT [dbo].[Blocking_sysprocesses]

SELECT * from #Blocks_rg

DECLARE @SQL_Handle binary(20), @SPID smallInt;

DECLARE cur_handle CURSOR FOR SELECT sql_Handle, spid FROM #Blocks_rg;

OPEN cur_Handle

FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID

WHILE (@@FETCH_STATUS = 0)

BEGIN

INSERT [dbo].[Blocking_SqlText]

SELECT @SPID, CONVERT(nvarchar(4000), [text]) ,@now as [Capture_Timestamp] from ::fn_get_sql(@SQL_Handle)

FETCH NEXT FROM cur_handle INTO @SQL_Handle, @SPID

END

CLOSE cur_Handle

DEALLOCATE cur_Handle

END

DROP table #Blocks_rg

END

USE msdb;

EXEC dbo.sp_add_job

@job_name = N'MonitorBlocking_lmz';

EXEC sp_add_jobstep @job_name = N'MonitorBlocking_lmz',

@step_name = N'execute blocking script',

@subsystem = N'TSQL',

@command = N'exec checkBlocking_lmz',

@database_name=N'gs_gep_lmz';

EXEC sp_add_jobSchedule

@name = N'ScheduleBlockingCheck_lmz',

@job_name = N'MonitorBlocking_lmz',

@freq_type = 4, -- daily

@freq_interval = 1,

@freq_subday_type = 4,

@freq_subday_interval = 1

EXEC sp_add_jobserver @job_name = N'MonitorBlocking_lmz', @server_name = N'(local)'