如何处理RDS SQL Server阻塞问题

问题描述

云数据库RDS SQL Server版出现阻塞问题。

问题原因

当应用程序频繁读写某个表或者资源时，很容易出现阻塞情况。当阻塞问题严重时，应用程序端的语句执行会变得缓慢。

排查方法

针对云数据库RDS SQL Server版阻塞问题，排查建议如下。

1. 循环监控SYS.SYSPROCESSES，获取阻塞信息。命令如下：

   WHILE 1 = 1
   BEGIN
       SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
       WAITFOR DELAY '00:00:01';
   END;

   说明

   您可以自定义循环间隔时间，此处以00:00:01为例。

   系统显示类似如下：

   

   说明

   blocked列表示阻塞头session_id，waitresource表示被阻塞的session等待的资源。更多返回值字段的说明，请参见sys.sysprocesses官方文档。

2. 循环监控sys.dm_tran_locks、sys.dm_os_waiting_tasks等视图，可以得到阻塞图谱。命令如下：

   WHILE 1 = 1
   Begin
   SELECT db.name DBName,
          tl.request_session_id,
   wt.blocking_session_id,
   OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
                                       tl.resource_type,
                                       h1.TEXT AS RequestingText,
                                       h2.TEXT AS BlockingText,
                                       tl.request_mode
   FROM sys.dm_tran_locks AS tl
   INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
   INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
   INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
   INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
   INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
   CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
   CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
   WAITFOR DELAY '00:00:01';
   END;

   系统显示类似如下：

   

   返回参数说明如下：

   参数	说明
   DBName	数据库名称。
   request_session_id	当前请求的session ID，即被阻塞的session。
   blocking_session_id	阻塞头session ID。
   BlockedObjectName	被阻塞的session操作的对象。
   resource_type	等待的资源类型。
   RequestingText	当前session执行的语句，即被阻塞的语句。
   BlockingText	阻塞头session执行的语句。
   request_mode	当前session请求的锁模式。

调优建议

可以参考以下步骤，进行调优。

1. 关闭阻塞头连接，可以帮助快速解除阻塞。

2. 查看是否有长时间未提交的事务，及时提交事务。

3. 如果有S锁导致的查询阻塞，并且您的应用允许脏读，可以使用WITH (NOLOCK)查询提示。例如在查询中写入SELECT * FROM table WITH (NOLOCK);使查询在执行时不申请锁，从而绕过阻塞状况。

4. 检查应用程序逻辑，按顺序访问某个资源。

相关操作

• 如需应对紧急情况下的阻塞问题，请参见如何快速解决RDS SQL Server的阻塞问题。

• 您可以在RDS控制台通过设置性能监控指标、创建报警规则等方式，以便及时发现和响应数据库性能问题。具体详情，请参见监控与报警。

• 如需了解更多数据库性能调优策略，例如索引优化、查询优化、存储优化。具体详情，请参见性能优化与诊断。