How do I view row lock waits on an ApsaraDB RDS for SQL Server instance? - ApsaraDB RDS

This topic describes how to view row lock waits on an ApsaraDB RDS for SQL Server instance.

Note

Take note of the following items:

Before you perform high-risk operations, such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
Before you modify the configurations or data of an instance, such as an Elastic Compute Service (ECS) instance or an RDS instance, we recommend that you create snapshots or enable backup for the instance. For example, you can enable log backup for an RDS instance.
If you granted permissions on sensitive information or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity. Sensitive information includes usernames and passwords.

Procedure

Execute the following SQL statement to query the row lock waits of the RDS instance:

use master;
go
WITH CTE_SID ( BSID, SID, sql_handle )        
    AS ( SELECT [Blocking_Session_ID],
                        [Session_ID] ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    [Blocking_Session_ID] <> 0
               UNION ALL
               SELECT   A.[Blocking_Session_ID] ,
                        A.[Session_ID] ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN CTE_SID B ON A.[Session_ID] = B.BSID
             )
    SELECT C.BSID,
            C.SID ,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM CTE_SID C
            JOIN sys.dm_exec_sessions S ON C.sid = s.[Session_ID]
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid

Note

If row lock waits or blocking issues occur on the RDS instance, the IDs of sessions that wait for a lock and the IDs of sessions that hold locks are displayed in the output.

References

You can view the monitoring data of an RDS instance in the ApsaraDB RDS console. For more information, see View standard monitoring information.
You can perform performance optimization and lock optimization on an RDS instance in the ApsaraDB RDS console. For more information, see Performance optimization and diagnosis.