SQL server 2012 阻塞分析查询

最近公司的数据库并发有点大，由于CPU不高、内存不高、硬盘正常、网络也正常等等，但系统还是会卡，所以就怀疑是数据库阻塞导致的，于是去查询资料，看书及经过用以下sql观查，经过几天对数据的分析找到原因并解决后，在这里分享下。

一、准备知识

需要了解数据阻塞的基本知识，了解数据库常见锁的运行机制，有了这个基础后，对下面的sql执行的结果可以更加的了解，在使用以下sql时，准备这些知识是很重要的。

二、查看当前阻塞

运行 sp_lock 或

select spid,blocked,cmd,waittime,lastwaittype,waittype,waitresource,dbid,uid,cpu,physical_io,memusage,login_time,last_batch,        open_tran,status,hostname,PRogram_name,net_library,loginame    from sysprocesses    where blocked>0

三、查看数据库当前锁的情况

select request_session_id,resource_type,resource_associated_entity_id,request_status,request_mode,resource_description,p.object_id,object_name(P.OBJECT_ID) as object_name,p.*from sys.dm_tran_locks left join sys.partitions p on sys.dm_tran_locks.resource_associated_entity_id=p.hobt_idwhere resource_database_id=db_id('数据库名')order by request_session_id,resource_type,resource_associated_entity_id

四、捕获数据库阻塞情况，将以下代码保存成sql文本件

/* sqlcmd -E -S . -i 捕获数据库阻塞情况.sql -w2000 -o log.out*/use mastergo while 1=1begin    print 'Start time:'+convert(varchar(26),getdate(),121)    print 'Running processes'    select spid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,uid,cpu,physical_io,memusage,login_time,last_batch,        open_tran,status,hostname,program_name,cmd,net_library,loginame    from sysprocesses    --where(ipid<>0) or(spid<51)    --Change it if you only want to see the working processes    print '*****lockinfo*****'    select convert(smallint,req_spid) as spid,            rsc_dbid as dbid,            rsc_objid as objid,            rsc_indid as indid,            substring(v.name,1,4) as type,            substring(rsc_text,1,16) as resource,            substring(u.name,1,8) as mode,            substring(x.name,1,5) as status    from master.dbo.syslockinfo,            master.dbo.spt_values v,            master.dbo.spt_values x,            master.dbo.spt_values u    where master.dbo.syslockinfo.rsc_type=v.number    and v.type='LR'    and master.dbo.syslockinfo.req_status=x.number    and x.type='LS'    and master.dbo.syslockinfo.req_mode+1=u.number    and u.type='L'    order by spid        print 'inputbuffer for running processes'    declare @spid varchar(6)    declare ibuffer cursor fast_forward for    select cast(spid as varchar(6)) as spid from sysprocesses where spid>50    open ibuffer    fetch next from ibuffer into @spid    while (@@FETCH_STATUS!=-1)    begin        print ''        print 'DBCC inputbuffer for spid '+@spid        exec ('dbcc inputbuffer('+@spid+')')        fetch next from ibuffer into @spid    end    deallocate ibuffer    waitfor delay '0:0:10'end

代码每隔10秒运行一次。

在cmd里运行以下命令，会在当前目录里产生一个log.out，可能用editplus查看此日志，日志对锁的申请和占用都有详细的记录，可以量大的话可以每天重新运行。

sqlcmd -E -S . -i 捕获数据库阻塞情况.sql -w2000 -o log.out

PS：以上的代码大多都由《SQL Server 2012 实施与管理实战指南》上来的，还是非常实用的，这本书买了有一段时间，碰到问题了就起来来翻翻。

完毕