ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL SERVER 对象被BLOCK跟踪

SQL SERVER 对象被BLOCK跟踪

原创 Linux操作系统 作者:aushai 时间:2013-07-25 09:51:19 0 删除 编辑
接到现场实施同事报告,项目中有个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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: session阻塞分析
请登录后发表评论 登录
全部评论

注册时间:2013-07-18

  • 博文量
    10
  • 访问量
    29583