寻找阻塞的一些思路笔记

阻塞是事务隔离带来的副作用，是一个数据库系统常见的现象。如果阻塞持续的时间非常短，可能对性能的影响不会很大。所以在遇到性能问题的时候，DBA或应用开发者要首先确认性能问题是不是由于阻塞直接导致，以及阻塞在多大程度上影响了SQL的性能。不要一看到SQL里发生了阻塞，就认为他是性能问题的主要原因 。  

 

初步定位需要知道的：

1、是哪些应用出了问题？这些应用是在使用同一个数据库还是不同的数据库？

2、应用端问题的现象是什么？

3、什么因素让你认为阻塞是问题的主要原因？   

4、阻塞发生的特征

 

1、哪些应用出了问题，这些应用是在使用同一个数据库还是不同的数据库？

一般来讲，一个SQL里会有多个数据库。每个数据库可能在支持多个应用程序假如其中一个数据库出现了阻塞，而我们关注的那个应用没有使用他，很有可能这个阻塞和我们关注的性能问题没关系，无须做太多分析但是如果有其他应用和关注的应用共享同一个数据库，而这个数据库上发生了阻塞，那DBA就不能只关注一个应用上。因为阻塞很有可能因为不同应用之间的相互作用导致的

 

2、应用端问题的现象

应用端问题表征对定位问题非常重要。不是所有问题都是阻塞导致的。但是很多时候数据库有阻塞，应用也能运行得很好

如果应用遇到一些错误，那至少要得到应用从数据库端收到的错误原文是什么。有时候应用会对错误封装，或者没有准确捕捉到错误原文。如果遇到这样情况可能有必要对应用修改，要求他把错误的详细原文打印出来

如果应用端的现象是长时间没有响应（hang）或很慢，那首先要从应用端分析。他是运行到哪一步以后才出问题，这一步是不是在做数据库操作，是不是等待数据库返回。造成应用没有响应或响应很慢，数据库只是可能的因素之一。如果直接跳进数据库性能分析，会有忽视其他因素的风险

 

3、什么因素让你认为阻塞是问题的主要原因？
得到错误信息后，要确认这个错误是否是SQL返回的。一般阻塞会导致命令不能及时完成。所以得到的错误应该是运行时错误。如果错误不是SQL返回的，或者不是运行超时那么问题的直接原因不是阻塞，要从其他角度去分析问题

 

4、阻塞发生的特征

如果怀疑阻塞是问题原因，要搞清楚阻塞本身的特征：阻塞每天在什么时间发生每次持续多久，是不是应用负载越重，阻塞越严重，还是在运行某些特定任务的的时候才容易发生阻塞，阻塞发生后，是自动消失，还是必须要重启SQL才能解决

 

 

通过以下来定位阻塞：

1、SQL server 里没有阻塞发生？是什么时候发生的？在哪个数据库上？阻塞发生在哪个或哪些表格上？哪些资源上？

2、和阻塞有关的连接是从哪些客户应用来的？

3、为什么阻塞会发生？这个问题包括：

    A、现在阻塞发生在哪个或哪些资源上？

    B、阻塞的源头是在做什么事情的时候申请了这些锁？为什么会申请这些锁？

    C、阻塞的源头当前的状态是什么？是一直在执行，还是已经进入空闲状态？

    D、如果它一直在执行，为什么要执行这么久？

    E、如果已经进入空闲状态，那为什么没有释放锁资源？

    F、其他被阻塞的连接它们想要做什么？为什么也要申请这些锁资源？

 

首先在范例数据库上造成一个阻塞，用SSMS建立两个连接
--连接1：
USE [AdventureWorks]
BEGIN TRAN
UPDATE [dbo].[Employee_Demo_Heap]
     SET [Title]='aaa'
WHERE [EmployeeID]=70

UPDATE [dbo].[Employee_Demo_BTree]
     SET [Title]='aaa'
WHERE [EmployeeID]=70

--连接2：
USE [AdventureWorks]
SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_Heap] WHERE [EmployeeID] IN (3,30,200)

--之前说过，由于连接2会选取一个表扫描的执行计划，所以被连接1阻塞住

--运行

select * from master.sys.sysprocesses          --sysprocesses是一张非常重要的系统管理视图

 

可以进这个网站查看 sysprocesses 一些字段的意思    https://technet.microsoft.com/zh-cn/library/ms179881(v=SQL.110).aspx  

 

1、数据库上面有没有阻塞发生，哪些连接发生了阻塞，是谁阻塞住了谁
查找[sysprocesses]的blocked字段不为0，如果不为0，而且也不是-2，-3，-4，那他就是被这个字段值的那个连接给阻塞住了。一般来讲，阻塞源头的blocked
字段会是NULL。如果他也不等于0，说明他也被别人阻塞，要继续查找阻塞住他的连接
如果你发现一个连接的blocked字段的值等于他自己，那倒不说明什么问题。常常是因为这个连接正在做磁盘读写，他要等自己的I/O做完。

 

2、什么时候开始的？
这里只要看waittime,就可以知道此次阻塞发生的时间。如果你运行多次查询，每次被阻塞住的SPID waittime都很短，那说明单个阻塞持续的时间都不是很长。阻塞可能还不是很严重（当然这也会影响响应速度）

范例结果SPID53的waittye是557421，也就是说已经阻塞了557.421秒，这是一个发生时间很长的阻塞。如果真的发生在生产环境里，这种阻塞的影响会很大

3、在哪个数据库上？
一般检查dbid即可。得到dbid以后，可以运行以下查询得到数据库的名字
SELECT name,dbid  FROM sys.[sysdatabases]

4、阻塞在哪个表上，哪些资源上？
可以运行sp_lock，在结果集中寻找状态是wait的锁资源。或者直接运行下面的查询得到一样的结果

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, 32) 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' 
     and substring (x.name, 1, 5) = 'WAIT'
order by spid 

 

数据库名字和表名可以使用DB_NAME和OBJECT_NAME函数
查询索引名字
SELECT * FROM sys.[indexes] WHERE [object_id]=1115151018 --表

 

以下是对不同种类的资源含义的解释,只有了解他们的意思,才能真正看懂阻塞
RID：格式为fileid:pagenumber:rid的标识符，其中fileid标识包含页的文件，
pagenumber标识包含行的页，rid标识页上的特定行。fileid与sys.databases_files目录视图中的file_id列相匹配
例如：例子中有个正处于wait状态的RID Resource是1:6681:26，他的意思是在第一个数据文件上的第6681页上的第26个行上的锁资源。如果要查看这个页面上到底有哪些数据，可以考虑使用DBCC PAGE()

KEY：数据库引擎内部使用的十六进制数。这个值和sys.partions.hobt_id相对应出现这种资源说明锁是在一个索引上面。通过查询sys.partitions视图里相应的object_id和index_id就能找到这个索引
例子里有个KEY(46000227c460)， 他的dbid是9，object_id是834674039，index_id是1，从上面sys.indexes的结果，就可以知道他是PK_Employee_EmployeeID_Demo_BTree这个索引

PAG：格式为fileid:pagenumber的数字，其中fileid标识包含页的文件，pagenumbe标识页
EXT：标识区中的第一页的数字。该数字的格式为fileid:pagenumber
TAB：没有提供信息，因为已在Objid列中标识了表
DB：没有提供信息，因为已经在dbid列中标识了数据库
FIL：文件的标识符，与sys.database_files目录视图中的file_id列相匹配
EXEC [sys].[sp_helpdb] @dbname = gposdb -- sysname
通过这些资源的名字，读者可以找到阻塞发生在哪个对象的哪种资源上

 

5、和阻塞有关的连接是从哪些应用来的？
[检查]：sysprocesses的结果提供了很多信息告诉用户一个连接是从什么客户端连接过来的。
hostname：建立连接的那台机器叫什么名字
program_name：建立的程序叫什么名字
hostprocess：运行程序的进程在他的机器上ID是多少。program_name是应用告诉SQL的名字。有些应用如果自己没有特别的名字，那很可能会是.NET SQLCLIENT DATA PROVIDER之类的通用名字。需要到任务管理器通过进程ID找到应用程序到底是哪一个
loginname、nt_domain、nt_username：从这些字段可以知道连接是用什么帐号连入SQL的
net_address：客户端机器的MAC地址。有时候应用程序没有告诉SQL自己的信息，hostname,program_name之类的字段会是空的。这时候只能通过MAC地址来找是哪台客户机连上来的
net_library：连接是使用哪个网络协议连上来的。一般为tcp/ip,named pipe,LPC(local procedure call)

 

6、为什么阻塞会发生

1、阻塞的源头是在做什么事情的时候申请了这些锁？为什麽会申请这些锁
这个问题比较复杂。锁可能是会话正在运行中的语句申请的，但也可能是这个会话在先前开启了一个事务，一直都没有提交或回滚，锁资源是事务开启后的任何一个
语句申请的，当时阻塞可能还没有发生

如果是前者只要捉住连接发过来的最后一句话即可。如果是后者，则要在阻塞发生之前预先开启SQL Trace，一直跟踪到问题发生。如果阻塞问题已经发生而跟踪没有开启，那就没有办法知道事务是怎麽开启的，以及锁是什么语句申请的。

所以有时候要捉住阻塞问题的根源，必须下决心在出问题之前就开启服务器端跟踪，等到问题重现为止

如果没有跟踪，可以运行一些脚本得到某个连接当前正在运行的语句，和空闲连接上次运行的最后一条语句
--正在运行中的语句
下面这个查询返回所有正在运行中的连接和他正在运行的语句，如果一个连接处于空闲状态，就不会被返回
SELECT  p.[session_id] ,
         p.[request_id] ,
         p.[start_time] ,
         p.[status] ,
         p.[command] ,
         p.[blocking_session_id] ,
         p.[wait_type] ,
         p.[wait_time] ,
         p.[wait_resource] ,
         p.[total_elapsed_time] ,
         p.[open_transaction_count] ,
         p.[transaction_isolation_level] ,
         SUBSTRING(qt.[text], p.[statement_start_offset] / 2,
                   ( CASE WHEN p.[statement_end_offset] = -1
                          THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                          ELSE p.[statement_end_offset]
                     END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,
         p.[statement_end_offset] ,
         batch = qt.[text]
 FROM    master.sys.[dm_exec_requests] p
         CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt
 WHERE   p.[session_id] > 50

 

--空闲连接上次运行的最后一条语句
运行 DBCC INPUTBUFFER(<SPID>) 可以获得从客户端发送到SQL实例的最后一个批处理语句，这句话的优点是不管连接是否正在运行，都会返回结果。缺点是他返回的是整个批处理语句，而不是当前正在执行的子句。所以对于正在运行的连接，第一种方法比较好DBCC INPUTBUFFER(58)

 

找到以后，一般就能确定阻塞是否是当前运行的语句造成的。如果阻塞发生在表 A 上，而当前这句话不可能在这个表上加相应的锁，那基本上可以断定阻塞是由一个先前开启的事务导致的。

2、阻塞的源头当时是什么状态，是一直在执行还是已经进入空闲状态
[检查]：一个最简单的方法，是看sysprocesses里面的 kpid 和 waittype 两个字段，如果两个都是0，就是处于空闲状态，如果不都是0，或者连接正在运行或者因为资源等待而暂时挂起

3、如果一直运行，为什麽执行这麽久？
[检查]：如果一个连接的kpid值不是0（连接拿到了一个线程资源），waittype值是0（他不需要等待任务资源），他的状态就会是runnable或running。如果一个连接
的kpid值不是0，waittype值也不是0，则说明他要等待某个资源才能继续执行。这时候连接的状态一般会是suspended

4、如果已经进入空闲状态，那为什么没有释放资源
[检查]：如果一个连接的kpid值是0（连接没有占用线程资源），waittype值也是0，他不需要等待任务资源，那么这个连接已经完成了客户端发过来的所有请求，现在进入空闲状态。正在等待客户端发送新的请求

如果这是他还是阻塞源头，一般是他有先前开启的事务没有及时提交。这可以通过检查sysprocesses里的open_tran字段是否大于0确认。

通过 DBCC INPUTBUFFER(58)

也可以知道最后发过来的那句话是什么

5、其他阻塞的连接他们想要做什么？为什麽也要申请这些锁资源

[检查]：使用下面的脚本，也能能够知道被阻塞住的连接子运行的语句。然后再去比较 sp_lock 的结果，就能大致判断它申请的锁的数量是否合理。如果不是很很合理，可以通过优化语句、加合适的索引解决。

--使用以下脚本
 SELECT  p.[session_id] ,
        p.[request_id] ,
         p.[start_time] ,
         p.[status] ,
        p.[command] ,
         p.[blocking_session_id] ,
         p.[wait_type] ,
         p.[wait_time] ,
         p.[wait_resource] ,
         p.[total_elapsed_time] ,
         p.[open_transaction_count] ,
         p.[transaction_isolation_level] ,
         SUBSTRING(qt.[text], p.[statement_start_offset] / 2,
                   ( CASE WHEN p.[statement_end_offset] = -1
                         THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                          ELSE p.[statement_end_offset]
                    END - p.[statement_start_offset] ) / 2) AS 'sql statement' ,
         p.[statement_end_offset] ,
        batch = qt.[text]
 FROM    master.sys.[dm_exec_requests] p
      CROSS APPLY [sys].[dm_exec_sql_text](p.[sql_handle]) AS qt
WHERE   p.[session_id] > 50
去比较sp_lock的结果，就能大致判断他申请的锁数量是否合理，如果不合理，可以通过优化语句，加合适索引解决

 

-------------------------------------------------霸气的分割线----------------------------------------------------

如何捕捉不定时出现的阻塞信息

如果阻塞一直发生或者固定在某段时间内发生，我们可以查询 sysprocesses 系统管理视图，已获得阻塞的具体信息。单管理员也经常会碰到这种情况：客户打电话来反映语句执行有阻塞现象，科室等我们连到数据库去查看时，阻塞现象已经消失了，性能又变好了。而且下一次阻塞什么时候会发生无法预测。碰到这种情况，管理员需要主动去检测系统的阻塞信息，事先打开一些脚本运行。这样下次问题再次发生时，就会有足够的信息来了解阻塞是怎么发生的。

 

可以用下面的脚本来主动检测系统的阻塞信息。脚本每隔 10s 执行一次。执行的语句简单，不会影响系统的性能。只要有足够的磁盘空间，就可以一直让这个脚本执行下去，直到问题再次发生位置。管理员可以每隔几天关闭它并重新运行，使得日志输出文件不要太大。

 

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 master.sys.sysprocesses

--where (kpid<>0) or (spid<51)

-- Change it if you only want to see the working processes

print '*******lockinfor********'

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, 32) 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' 
     and substring (x.name, 1, 5) = 'WAIT'
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 master.sys.sysprocesses

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

 

由于输出会比较大，请不要直接在 Management Studio 里运行。比较严谨的运行方法是：把上面的脚本存为一个文件如 blocking.sql，然后在客户端或服务器端执行，如下所示的语句。-E 表明我们用当前 Windows 登陆账户去连接数据库，-S 后面是数据库服务名，-i 后面是我们的脚本，-w 指明我们的输出最宽为 2000 个字符，-o 后面加的是日志名。如果要停止运行，我们按 Ctrl+C 组合键就可以了。阻塞的信息会存放在当前目录里 log.out 这个文件里。

 

输入下面的代码：

sqlcmd -Usa -P123456 -S实例名 -iblocking.sql -w2000 -olog.out

 

 

当发生阻塞后，可以打开 log.out 这个文件，找到发生阻塞的时间段的输出，就能看到如下的阻塞信息。

 

根据 blocked 字段的值，可以判断出 谁被谁阻塞住了，找到哪个阻塞发生的源头。而随后，可以看到 spid=53 在做什么事情，如下所示。

 

 

 

这个输出文件能告诉我们很多东西，非常重要。以后将进行详细的阐述。

 

PS：来自《SQL Server 2012 实施与管理实战指南》