SQL SERVER 对象被BLOCK跟踪

接到现场实施同事报告，项目中有个delete操作有时非常慢，用时在20多分钟，而这个delete是以主键ID作为删除条件的，照说单这个delete语句而言不存在性能问题，怀疑是在进行删除操作的时候被其他操作阻塞。如是写个存储过程进行跟踪，存储过程如下：

create proc [dbo].[sp_monitor_lock] as begin declare @spid_1 smallint, @spid_2 smallint, @spid smallint, @kpid smallint, @blocked smallint , @waittype binary(2),@waittime bigint, @lastwaittype nchar(32), @waitresource nchar(256),@login_time datetime, @last_batch datetime,@status nchar(30), @sid binary(86), @hostname nchar(128),@program_name nchar(128),@hostprocess nchar(10), @cmd nchar(16), @sql_handle binary(20), @cur_cursor CURSOR; SET @cur_cursor = CURSOR LOCAL SCROLL FOR Select spid, kpid, blocked, waittype,waittime, lastwaittype,waitresource,login_time, last_batch,status,sid, hostname,program_name,hostprocess, cmd, sql_handle from master..sysprocesses where blocked > 0; open @cur_cursor FETCH NEXT FROM @cur_cursor INTO @spid, @kpid, @blocked, @waittype,@waittime, @lastwaittype,@waitresource,@login_time, @last_batch,@status,@sid, @hostname,@program_name,@hostprocess, @cmd, @sql_handle begin begin tran insert into mon_lock_object values(@spid, @kpid, @blocked, @waittype,@waittime, @lastwaittype,@waitresource,@login_time, @last_batch,@status,@sid, @hostname,@program_name,@hostprocess, @cmd, @sql_handle) ; declare @v varchar(3000) set @v= 'dbcc inputbuffer('+ +CONVERT(VARCHAR(20),@spid) + ')'; insert into mon_spid(eventtype , parameters , eventinfo) exec(@v); update mon_spid set spid = @spid where spid is null; set @v= 'dbcc inputbuffer('+ +CONVERT(VARCHAR(20),@blocked) + ')'; insert into mon_spid(eventtype , parameters , eventinfo) exec(@v); update mon_spid set spid = @blocked where spid is null; insert into mon_lock exec sp_lock delete from mon_lock where not exists(select 'x' from mon_spid t2 where mon_lock.spid = t2.spid); commit end CLOSE @cur_cursor end

然后设置一个定时任务，定时执行改存储过程。通过查看跟踪表，找到阻塞的语句。经过一段时间时间观察后，发现了问题所在：

spid	kpid	blocked	waittype	waittime	lastwaittype	waitresource
159	12336	76	0x0004	60396	LCK_M_U	KEY: 7:72057594347847680 (5e02a9cade30)
110	10880	144	0x0004	93584	LCK_M_U	KEY: 7:72057594362462208 (be01fe0b23c4)
98	8020	95	0x0004	12089	LCK_M_U	KEY: 7:72057594347257856 (f7013312862a)
110	10880	144	0x0004	993618	LCK_M_U	KEY: 7:72057594362462208 (be01fe0b23c4)
110	10880	144	0x0004	1293628	LCK_M_U	KEY: 7:72057594362462208 (be01fe0b23c4)
110	10880	144	0x0004	693604	LCK_M_U	KEY: 7:72057594362462208 (be01fe0b23c4)

可以看到在跟踪期间，110一直被114阻塞。再看看110和114分别是什么

110

Language Event

0

(@P0 nvarchar(4000))delete from mes_product_data_acquirement_transit where id= @P0

144

Language Event

0

exec p_data_acquirement_from_wms

发现110正是我们的删除操作，而144为一个存储过程。是改存储过程阻塞了该删除操作。

110

7

1924917929

2

KEY

(be01fe0b23c4)

U

WAIT

144

7

1924917929

2

KEY

(be01fe0b23c4)

X

GRANT

再细看存储过程，发现该存储过程就一条SQL语句类似：

insert into ... select ..

的形式 ，而insert的表就是110要delete的表，而且后面的select 执行相当缓慢。最后经过优化后面的select 语句。问题解决。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21124603/viewspace-767061/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21124603/viewspace-767061/