Solution to the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

Problem description

Requests are blocked on an ApsaraDB RDS for SQL Server instance.

Causes

An application frequently reads data from or writes data to a table or resource. If a large number of requests are blocked, statements are executed on the application at a low speed.

Identification

To identify the blocking issue on the RDS instance, we recommend that you perform the following operations:

Execute the following statement to monitor sys.sysprocesses at regular intervals to obtain blocking information :
```
WHILE 1 = 1
BEGIN
    SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
    WAITFOR DELAY '00:00:01';
END;
```
Note
You can specify the monitoring interval. In this example, 00:00:01 is used.
The following figure shows a sample output.
Note
The blocked column indicates thesession ID of the block header. The waitresource column indicates the resource for which the blocked session waits. For more information about the parameters in the output, see sys.sysprocesses (Transact-SQL).

Execute the following statement to monitor the sys.dm_tran_locks and sys.dm_os_waiting_tasks views to obtain blocking information :

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 following table describes the parameters in the output.

Parameter	Description
`DBName`	The name of the database.
`request_session_id`	The ID of the session in the current request. The session is the blocked session.
`blocking_session_id`	The session ID of the block header.
`BlockedObjectName`	The objects that are managed by the blocked session.
`resource_type`	The type of the resource for which the session waits.
`RequestingText`	The statement that is executed in the session. The statement is the blocked statement.
`BlockingText`	The statement that is executed in the session of the block header.
`request_mode`	The lock mode that is requested by the session.

Optimization suggestions

You can perform the following operations for optimization:

Terminate the session in the block header to resolve the blocking issue.
Check whether transactions that are not committed for an extended period of time exist. If the transactions exist, commit the transactions at the earliest opportunity.
If queries are blocked due to shared locks and your application allows dirty reads, use the WITH (NOLOCK) query hint. For example, you can execute the SELECT * FROM table WITH (NOLOCK); statement for the query. This way, the query does not apply for locks to prevent blocking issues.
Check the application logic to access a resource in sequence.

References

For more information about how to resolve the blocking issue in emergency situations, see What do I do if requests are blocked on an ApsaraDB RDS for SQL Server instance?
You can specify performance metrics and create alert rules in the ApsaraDB RDS console to identify and resolve performance issues at the earliest opportunity. For more information, see Monitoring and alerts.
For more information about performance optimization policies, such as index optimization, query optimization, and storage optimization, see Performance optimization and diagnosis.