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

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:

1. 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 session 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).

2. 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:

1. Terminate the session in the block header to resolve the blocking issue.

2. 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.

3. 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.

4. 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.