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_id
where 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 master
go 
while 1=1
begin
    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 实施与管理实战指南》上来的，还是非常实用的，这本书买了有一段时间，碰到问题了就起来来翻翻。

完毕