SqlServer运维——查询阻塞

• 0. 演示阻塞
• 1. 基于sys.dm_exec_requests视图
• 2. 在MSSM中通过“活动和监视器”查看
• 3. 完整脚本查询阻塞

0. 演示阻塞

1. 首先MSSM中打开一个查询会话，这个是进程68

执行一个更新操作，但是不提交事务

BEGIN TRANSACTION
UPDATE dbo.AAMyTestMsg SET	Mes='1' WHERE CreateTime='2024-07-18 15:04:46.873'
--COMMIT TRANSACTION

2. 另开一个查询界面，进程：92

执行如下删除语句

DELETE	 dbo.AAMyTestMsg WHERE CreateTime='2024-07-18 15:04:46.873'

此时，会话92一直处于：正在执行查询

1. 基于sys.dm_exec_requests视图

1. 使用sys.dm_exec_requests视图，查询一个进程被另外一个进程阻塞

SELECT session_id,
       blocking_session_id,
       wait_time,
       wait_type,
       last_wait_type,
       wait_resource,
       transaction_isolation_level,
       lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

若是没有阻塞，则查询无结果。

在上述构造的阻塞的情形下，执行上述查询脚本，可以看到如下结果

• 说明1：进程92被进程68阻塞。
• 说明2：session_id（被阻塞会话），blocking_session_id（阻塞者）

2. 在MSSM中通过“活动和监视器”查看

可以在MSSM中的直接右键服务器连接-->活动和监视器-->查看：进程

这里我们简单的摘录几个主要字段如下：

在界面商可以直接选中行右键-->详细信息，可以查看当前会话在执行的语句

3. 完整脚本查询阻塞

• 基于该脚本，可以查询阻塞的的会话ID，阻塞者ID，以及当前会话的执行的SQL语句

• 来源金蝶官方提供

SELECT t1.resource_type AS [锁类型], DB_NAME(resource_database_id) AS [数据库名], t1.resource_associated_entity_id AS [阻塞资源对象]
      ,t1.resource_description AS [资源描述信息], t1.request_mode AS [请求的锁], t1.request_session_id AS [等待会话]
      ,t2.wait_duration_ms AS [等待时间], (SELECT [text]
                                       FROM  sys.dm_exec_requests AS r WITH(NOLOCK)
                                             CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
                                       WHERE r.session_id=t1.request_session_id) AS [等待会话执行的批SQL]
      ,(SELECT SUBSTRING(
                     qt.[text], r.statement_start_offset / 2
                     ,(CASE WHEN r.statement_end_offset=-1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text]))* 2 ELSE r.statement_end_offset END)
                      / 2)
        FROM  sys.dm_exec_requests AS r WITH(NOLOCK)
              CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
        WHERE r.session_id=t1.request_session_id) AS [等待会话执行的SQL], t2.blocking_session_id AS [阻塞会话]
      ,(SELECT [text]
        FROM  sys.sysprocesses AS p
              CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
        WHERE p.spid=t2.blocking_session_id) AS [阻塞会话执行的批SQL]
FROM  sys.dm_tran_locks AS t1 WITH(NOLOCK)
      INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH(NOLOCK)ON t1.lock_owner_address=t2.resource_address
OPTION(RECOMPILE);

结果：