SQL Server阻塞的检查

1. 阻塞  

除了内存、cpu、i/o这些系统资源以外，阻塞和死锁是影响数据库应用性能的另一大因素。

所谓的「阻塞」，是指当一个数据库会话中的事务，正在锁定其他会话事务想要读取或修改的资源，造成这些会话发出的请求进入等待的状态。sql server 默认会让被阻塞的请求无限期地一直等待，直到原来的事务释放相关的锁，或直到它超时、服务器关闭、进程被杀死。一般的系统中，偶尔有短时间的阻塞是正常且合理的；但若设计不良的程序，就可能导致长时间的阻塞，这样就不必要地锁定了资源，而且阻塞了其他会话欲读取或更新的需求。遇到这种情况，可能就需要手工排除阻塞的状态。

2.阻塞和死锁可能带来的问题

（1）并发用户少的时候，一切还都正常。但是随着并发用户的增加，性能越来越慢。

（2）应用程序运行很慢，但是sql server 这个cpu和磁盘利用率很低。

（3）客户端经常受到以下错误。

   error 1222--lock request time out period exceeded.

   error 1205--your transaction(process id #xx) was deadlocked on resources with another process and has been chosen as the deadlock victim. return your transaction.

  超时错误--timeout expired. the timeout period elapsed prior to completion of the operation or the server is not responding.

（4）有些查询能够进行，但是有些特定的查询或修改总是不能返回。

（5）重启sql server就能解决。但是有可能跑一段时间以后又会出现问题。

3.阻塞的检查

3.1 主要的系统表或函数

sys.sysprocesses 系统表是一个很重要的系统视图，主要用来定位与解决sql server的阻塞和死锁。主要字段1.spid：sql servr 会话id 2.blocked：正在阻塞求情的会话 id。如果此列为 null，则标识请求未被阻塞 3. program_name：应用程序的名称，就是 连接字符串中配的 application name 4. hostname：建立链接的客户端工作站的名称。

sys.dm_exec_requests、sys.dm_exec_sql_text返回指定spider的 sql 查询文本。

dbcc inputbuffer 显示从客户端发送到 microsoft sql server 实例的最后一个语句。

sp_lock 系统存储过程，报告有关锁的信息。

3.2 check逻辑

对应的存储为dblockcheck（job为db_lockcheck），主要check逻辑如下：

3.3 保存的数据

所收集的数据保存dblock_information中,主要包含信息如截图,定期的统计分析可获得经常被阻塞和引起阻塞sql语句和table，这些信息是进行数据库优化的一个角度。

select top 100* from dblock_information
order by transdatetime desc 

4.代码实现

4.1 table的创建脚本

create table [dbo].[dblock_information](
    [id] [int] identity(1,1) not null,
    [message] [nvarchar](300) null,
    [locktype] [char](1) null,
    [spid1] [int] null,
    [spid2] [int] null,
    [eventtype] [nvarchar](100) null,
    [parameters] [nvarchar](10) null,
    [eventinfo] [nvarchar](3000) null,
    [individualquery] [nvarchar](1000) null,
    [transdatetime] [datetime] null constraint [df_dblock_information_transdatetime]  default (getdate()),
    [appname] [varchar](50) null,
    [hostname] [varchar](50) null,
 constraint [pk_dblock_information] primary key clustered 
(
    [id] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]

4.2 存储的创建脚本

create procedure [dbo].[dblockcheck] 
     @alarmthreshold as tinyint=10
 as
set nocount on
--------------------------------------------------------------------------------------------------------
--*program*: <dblock check for job>
--*programer*:<>
--*date*:<>
--*description*:<query sql locking process>
--*unify*:<ua>
--########## parameter description begin ##########

--########## parameter description end # ##########

--##########update log begin ###################
--##########update log end # ###################
--------------------------------------------------------------------------------------------------------
declare @sql as varchar(200)
declare @subject as varchar(200)
declare @body as nvarchar(max)
declare @spname as nvarchar(max)
declare @message as nvarchar(200)
declare @dbname varchar(15)
declare @ip varchar(20)
declare @cnt as int
declare @cnt2 int
declare @individualquery nvarchar(1000)
declare @hostname varchar(50)
declare @appname varchar(50)
set @dbname=db_name()

select @ip='xxx.xxx.xxx.xxx'
----不手动定义ip也可通过以下函数来实现
declare @serverip nvarchar(30)='', @servername nvarchar(60)='' 
    select top 1 @servername = @@servername ,@serverip=local_net_address
    from sys.dm_exec_connections where local_net_address is not null
--------
begin
    declare @spid int,@bl int,
    @inttransactioncountonentry int,
    @introwcount int,
    @intcountproperties int,
    @intcounter int

create table #tmp_lock_who (
    id int identity(1,1),
    spid smallint,
    bl smallint)

create table #tmp_lock_information (
    id int identity(1,1),
    message nvarchar(200),
    locktype char(1),
    spid1 int,
    spid2 int,
    eventtype nvarchar(100),
    parameters nvarchar(10),
    eventinfo nvarchar(max),
    individualquery nvarchar(1000),
    appname varchar(50),
    hostname varchar(50)
    )

if @@error<>0 return @@error
    insert into #tmp_lock_who(spid,bl) 
    select 0 ,blocked
        from (select * from master..sysprocesses where blocked>0 ) a
        where not exists(select * from (select * from master..sysprocesses where blocked>0 ) b
    where a.blocked=spid)
    union 
    select spid,blocked from master..sysprocesses where blocked>0
    if @@error<>0 return @@error
    -- 找到临时表的记录数
        select @intcountproperties = count(*),@intcounter = 1
            from #tmp_lock_who
    if @@error<>0 return @@error
        if @intcountproperties=0
            select n'现在没有阻塞信息!' as message
            -- 循环开始
                while @intcounter <= @intcountproperties
                    begin
                    -- 取第一条记录
                        select @spid = spid,@bl = bl
                            from #tmp_lock_who where id = @intcounter
                        begin
                            select @individualquery= substring (qr.text,qs.statement_start_offset/2, 
                                     (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qr.text)) * 2 
                                           else qs.statement_end_offset end - qs.statement_start_offset)/2)
                            from sys.dm_exec_requests qs outer apply sys.dm_exec_sql_text(qs.sql_handle) as qr
                            where qr.text is not null and qs.session_id=@bl
                            select @hostname=left(hostname,50),@appname=left(program_name,50) 
                                from master..sysprocesses with(nolock) where spid=@bl
                            set @sql='dbcc inputbuffer ('+cast(@bl as char(20))+')'
                            if @spid =0
                                begin
                                    select @message=n'引起数据库阻塞的是: '+ cast(@bl as nvarchar(100)) + n'进程号,其执行的sql语法如下'
                                    --set @sql='dbcc inputbuffer ('+cast(@bl as char(20))+')'
                                    insert into #tmp_lock_information(eventtype,parameters,eventinfo) exec(@sql)
                                    update #tmp_lock_information set locktype='1',spid1=@bl,spid2=@bl,individualquery=@individualquery,[message]=@message,appname=@appname,hostname=@hostname where [message] is null
                                end
                            else
                                begin
                                    select @message=n'进程号spid：'+ cast(@spid as nvarchar(100))+ n'被' + n'进程号spid：'+ cast(@bl as nvarchar(10)) +n'阻塞,其当前进程执行的sql语法如下'
                                    insert into #tmp_lock_information(eventtype,parameters,eventinfo) exec(@sql)
                                    update #tmp_lock_information set locktype='2', spid1=@spid,spid2=@bl,individualquery=@individualquery,[message]=@message,appname=@appname,hostname=@hostname where [message] is null
                                end 
                        end
                        -- 循环指针下移
                        set @intcounter = @intcounter + 1
                    end
            drop table #tmp_lock_who
            if exists(select 0 from #tmp_lock_information)
            begin

                    insert into dblock_information(message,locktype,spid1,spid2,eventtype,parameters,eventinfo,individualquery,appname,hostname) 
                    select [message],locktype,spid1,spid2,eventtype,parameters,substring(eventinfo,1,500),individualquery,appname,hostname from #tmp_lock_information

            end

            drop table #tmp_lock_information
            return 0
end