ApsaraDB RDS:What do I do if requests are blocked on an ApsaraDB RDS for SQL Server instance?

Method 1: Query sys.sysprocesses

Run the following statement to poll sys.sysprocesses at regular intervals:

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

The WAITFOR DELAY value controls the polling interval. The example uses one second (00:00:01). Adjust as needed.

The following figure shows a sample output.

Key columns in the output:

• blocked: The session ID of the block header — the session holding the lock that is preventing others from proceeding.

• waitresource: The resource that the blocked session is waiting to acquire.

For a full list of columns, see sys.sysprocesses (Transact-SQL).

Method 2: Query sys.dm_tran_locks and sys.dm_os_waiting_tasks

For more detail — including which objects and statements are involved — run the following statement to join sys.dm_tran_locks with 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;

The following figure shows a sample output.

The output includes the following columns: