全部产品

RDS for SQL Server阻塞问题处理方法

更新时间:2017-02-22 21:38:39

当应用程序频繁读写某个表或者资源时,很容易出现阻塞情况。当阻塞问题严重时,应用程序端的语句执行会变得缓慢。针对SQL Server阻塞问题,排查建议如下:

  1. 循环监控sys.sysprocesses,获取阻塞信息。blocked列的值为阻塞头session_id,waitresource为被阻塞的session等待的资源。

    1. while 1=1
    2. begin
    3. select * from sys.sysprocesses where blocked<>0
    4. waitfor delay '00:00:01' --循环间隔时间可以自定义
    5. end

    查询结果结果字段的解释,请参考sys.sysprocesses

  2. 循环监控sys.dm_tran_locks,sys.dm_os_waiting_tasks等视图,可以得到阻塞图谱。Request_session_id是被阻塞的session_id,RequestingText是被阻塞的语句,blocking_session_id是阻塞头session_id,BlockingText是阻塞头语句。

    1. while 1=1
    2. Begin
    3. SELECT
    4. db.name DBName,
    5. tl.request_session_id, --被阻塞session
    6. wt.blocking_session_id, --阻塞头session
    7. OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
    8. tl.resource_type,
    9. h1.TEXT AS RequestingText,
    10. h2.TEXT AS BlockingText,
    11. tl.request_mode
    12. FROM sys.dm_tran_locks AS tl
    13. INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    14. INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    15. INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    16. INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    17. INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    18. CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    19. CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
    20. waitfor delay '00:00:01' --循环间隔时间可以自定义
    21. 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这个查询hint,让select语句避免申请锁,从而避免阻塞,例如 select * from table with(nolock)。
  4. 检查应用程序逻辑,按顺序访问某个资源。