All Products
Search
Document Center

How to troubleshoot blocking problems of apsaradb for RDS SQL Server

Last Updated: Jul 21, 2020

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

 

Description

Apsaradb for RDS SQL Server is experiencing a blocking problem.

 

Causes

When an application frequently reads or writes a table or resources, it is prone to blocking. When the blocking problem is serious, the statement execution on the application side becomes slow.

 

Solutions

Alibaba Cloud reminds you that:

  • When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

 

Troubleshooting

For the blocking problem of apsaradb for RDS SQL Server, the following are the troubleshooting suggestions.

  1. Cycle monitoring sys.sysprocesses to obtain blocking information. The value of the blocked column is the blocking header session_id, and waitresource is the resource waiting for the blocked session. The operation code is as follows:
    while 1=1
    begin
    select * from sys.sysprocesses where blocked<>0;
    waitfor delay '00:00:01'
    end
    Note: The Loop gap can be customized. Here 00:00:01 as an example.
    The following command output is displayed:
    Note: for the explanation of the fields in the figure, see documentation for sys.sysprocesses.
  2. Cycle monitoring sys.dm_tran_locks,sys.dm_os_waiting_tasks and other views, you can get the blocking graph. The Request_session_id indicates the blocked session_id, the RequestingText indicates the blocked statement, the blocking_session_id indicates the blocking session_id header, and the BlockingText indicates the blocking header statement. The operation code is as follows:
    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
    The following command output is displayed:
    Note:
    • DBName: the name of the database.
    • request_session_id: the ID of the blocked session.
    • blocking_session_id: the ID of the blocking header session.
    • BlockedObjectName: the object of blocked session operations.
    • resource_type: the type of the requested resource.
    • RequestingText: The statement executed in the current session, that is, the statement blocked.
    • BlockingText: blocking the statement executed by the session header.
    • request_mode: the lock mode requested by the current session.

 

Recommendations

Follow these steps to optimize the configuration.

  1. Closing the blocking head connection can help relieve the blocking quickly.
  2. Check whether a transaction has not been submitted for a long time and submit the transaction in time.
  3. If locks are involved in the blocking and dirty reads are allowed, you can use the query hint with nolock to allow the query statement to request locks and avoid blocking, such as select * from table with(nolock);.
  4. Check application logic to access a resource in order.

 

Application scope

  • ApsaraDB RDS for SQL Server

 

If the problem persists, you can receive a free consultation in Alibaba Cloud Community or submit a ticket to contact Alibaba Cloud technical support personnel.