性能调优12：阻塞

阻塞就是常说的等待（wait），是指事务A等待特定的资源得到满足之后，才能继续执行下去。发生阻塞的另外一种情况是被其他事务阻塞。阻塞对性能的影响，有时会比死锁更严重，这是因为死锁持续的时间非常短，SQL Server 一旦探测到死锁的发生，就会立即杀死一个进程，以结束死锁，使其他进程能够正常运行下去。然而，阻塞不会被杀死，可以持续很长很长时间，这就使得被阻塞的进程即使瞬间就可以完成，也必须等待资源得到满足才能执行下去。

一，探测阻塞

使用下面的脚本探测到当前活跃的阻塞：

select w.session_id as waiting_session_id
    ,w.waiting_task_address
    ,w.wait_type
    ,w.wait_duration_ms/1000 as duration_s
    ,db_name(k.resource_database_id) as resource_db_name
    ,k.resource_type
    ,k.resource_associated_entity_id
    ,w.resource_description
    ,k.request_mode
    ,k.request_type
    ,k.request_status
    ,w.blocking_session_id
    ,w.blocking_task_address
from sys.dm_os_waiting_tasks as w
inner join sys.dm_tran_locks as k
　　on w.resource_address=k.lock_owner_address
where w.wait_duration_ms>5000
　　and w.session_id>50

SQL Server 没有记录阻塞的历史信息，只有一个关于等待的统计信息，如果要查看数据库系统曾经遇到过的所有等待消息，那么就需要手动记录阻塞的信息。

二，捕获阻塞

要想捕获阻塞相关的信息，最方便的选项是通过SQL Server Profiler，并把数据保存到表以对阻塞进行分析。

配置SQL Server Profiler捕获阻塞相关的数据，首先打开SSMS的Tools菜单，打开SQL Server Profiler，在Events Selection中勾选Blocked process report 事件，并勾选所有的列，保存足够多的信息：

Blocked process report 表明一个Task被阻塞时间超过了一个阈值，该阈值可以通过 sys.sp_configue命令来设置。

在启动SQL Server Profiler之前，请务必配置 blocked process threshold 选项：

sp_configure 'show advanced options', 1
go
reconfigure
go 
sp_configure 'blocked process threshold', 20
go 
reconfigure 
go 

把追踪的数据存储到表中，可以通过sys.trace_events 来查看eventclass 代码代表的文本：

sys.trace_events

三，即时查看阻塞的动态管理视图

通常情况下，使用下面4个DMV来查看阻塞的信息。

1，sys.dm_os_waiting_tasks

该视图返回正在等待的task信息，字段分为两组：申请资源的Task和拥有资源的Task，简称为等待组（Task或Session）和阻塞组（Task 或 Session），等待组在申请资源时由于资源被阻塞组占用而必须等待，阻塞组拥有资源。

• 字段waiting_task_address ：表示申请资源的Task的内存地址，记作等待Task，该Task在申请资源时，由于资源无法被满足而被迫阻塞。
• 字段blocking_task_address：表示拥有资源的Task的内存地址，记作阻塞Task，该Task当前拥有资源，只有该Task释放资源，被阻塞的Task（即 waiting_task_address ）才能获得资源的使用权。

该视图的字段解释：

• waiting_task_address：处于等待资源状态的Task地址（即 等待Task）
• session_id：与“等待Task”相关联的Session的 ID（处于等待状态的Session）。
• exec_context_id：与“等待Task”关联的执行上下文的 ID。
• wait_type：等待类型的名称。
• wait_duration_ms：此等待类型的总等待时间（毫秒），此时间包含 signal_wait_time。
• resource_address：该“等待Task”等待的资源地址，该字段可以和 sys.dm_tran_locks  的lock_owner_address字段关联起来
• blocking_task_address：当前持有此资源的Task的地址（即 阻塞Task）。
• blocking_session_id：阻塞 “等待Task”的Session ID，也就是说，该Session拥有资源，阻塞了 “等待Task” 对资源的请求：
• 　　如果此列值为 NULL，则表示当前请求未被阻塞，或阻塞会话的信息不可用（或无法进行标识）。
• 　　-2 = 阻塞资源由孤立的分布式事务拥有。
• 　　-3 = 阻塞资源由延迟的恢复事务拥有。
• 　　-4 = 由于内部闩锁状态转换而无法确定阻塞闩锁所有者的会话 ID。
• blocking_exec_context_id：“阻塞Task”的执行上下文 ID。
• resource_description：争用的资源描述

2, sys.dm_tran_locks 

该视图返回当前活跃的锁管理器资源的信息，每一行代表一个向锁管理器申请锁的请求，该请求当前是活跃的，申请的锁已经被授予或者正在等待被授予。

该视图的字段主要分为两类，资源和请求，资源组描述锁定的资源，请求组描述锁的请求，主要字段解释：

• resource_type：锁定的资源类型，常见的资源类型是 OBJECT、PAGE、KEY、EXTENT、RID、HOBT 等。
• resource_subtype：锁定的资源类型的子类型，是对 resource_type的细分
• resource_database_id：此资源位于其范围之内的数据库的 ID。由锁管理器处理的所有资源均按数据库 ID 划分范围。
• resource_description：资源描述
• resource_associated_entity_id：数据库中与资源相关联的实体的 ID。该值可以是对象 ID、Hobt ID 或分配单元 ID，具体视资源类型而定。
• resource_lock_partition：已分区锁资源的锁分区 ID。对于未分区锁资源，该值为 0。
• request_mode：请求的模式。对于已授予的请求，为已授予模式；对于等待请求，为正在请求的模式。
• request_type：请求类型，该值为 LOCK。
• request_status：用于描述请求的状态，可能值为 GRANTED、CONVERT和 WAIT，granted 表示请求者（requestor）已经被授权，允许锁定资源；wait 表示请求者还没有被授权锁定资源， convet 表示请求者已经被授权，等待锁定资源。
• request_reference_count：同一请求程序已请求该资源的近似次数。
• request_session_id：当前拥有该请求的会话 ID
• request_exec_context_id：当前拥有该请求的进程的执行上下文ID。
• request_request_id：当前拥有该请求的进程的Request ID（即 Batch ID）
• request_owner_type：拥有该请求的实体类型。锁管理器请求可以由多种实体所拥有，可能的值有：
• 　　TRANSACTION = 请求由事务所有。
• 　　CURSOR = 请求由游标所有。
• 　　SESSION = 请求由用户会话所有。
• 　　SHARED_TRANSACTION_WORKSPACE = 请求由事务工作区的共享部分所有。
• 　　EXCLUSIVE_TRANSACTION_WORKSPACE = 请求由事务工作区的排他部分所有。
•        NOTIFICATION_OBJECT =请求由内部的SQL Server组件所有
• request_owner_id：请求的特定所有者 ID，分两种情况，第一种情况是：事务是该请求的所有者，request_owner_id值是事务ID。 第二种情况：
  •        如果FileTable是该请求的所有者，request_owner_id值 为 -4 表示FileTable持有database lock； request_owner_id值为 -3 表示 FileTable持有table lock。
  •        如果是其他值，request_owner_id值代表文件句柄，在sys.dm_filestream_non_transacted_handles 中显示为fcb_id字段。
• lock_owner_address：用于跟踪该请求的内部数据结构的内存地址。该列可以与 sys.dm_os_waiting_tasks 中的 resource_address 列连接。

3，sys.dm_os_wait_stats

等待统计，统计数据库系统中出现的等待：

• wait_type：等待类型的名称。
• waiting_tasks_count：该等待类型的等待次数
• wait_time_ms：该等待类型的总等待时间（毫秒）
• max_wait_time_ms：该等待类型的最长等待时间。
• signal_wait_time_ms：正在等待的线程从收到信号通知到开始运行之间的时差。

4，sys.dm_os_tasks

返回当前SQL Server实例中活跃的Task：

• task_address：Task结构的内存地址
• task_state：Task的状态，有效值有：PENDING、RUNNABLE、RUNNING、SUSPENDED、DONE和SPINLOOP
• context_switches_count：Task已经完成的调度程序上下文切换的次数
• pending_io_count：该Task执行的物理IO的次数
• pending_io_byte_count：该Task执行的物理IO的字节总数量
• pending_io_byte_average：Task执行的物理IO的字节平均数量
• scheduler_id：父调度程序的ID，这是调度程序的句柄
• session_id：该Task关联的Session ID
• exec_context_id：该Task关联的执行上下文ID
• request_id：该Task处理的请求 ID
• worker_address：执行该Task的Worker的内存地址，如果该值为NULL，表示该Task等待Worker去执行，或者已经执行完成。
• host_address：host的内存地址
• partner_task_address：该Task的父Task的内存地址

参考文档：

sys.dm_tran_locks