Handle blocking issues in RDS for SQL Server

Problem description

Blocking occurs on an ApsaraDB RDS for SQL Server instance.

Cause

Blocking occurs when an application frequently reads from or writes to a specific table or resource. Severe blocking slows down statement execution in your application.

Troubleshooting

To troubleshoot blocking issues on an ApsaraDB RDS for SQL Server instance, follow these steps.

1. Continuously monitor SYS.SYSPROCESSES to collect blocking information. Run the following command:

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

   Note

   You can customize the polling interval. This example uses 00:00:01.

   The query result shows that spid=56 is blocked by spid=53 (blocked=53), is waiting for a lock of type LCK_M_S, and the wait resource is KEY: 5:72057594038845440 (98ec012aa510). Over multiple query cycles, the waittime value increases sequentially (9205, 10206, 11207, 12208, and 13209), indicating the lock has not been released.

   Note

   The blocked column shows the session_id of the blocking session, and the waitresource column identifies the resource the blocked session is waiting for. For more information about the returned fields, see the official sys.sysprocesses documentation.

2. Continuously monitor views such as sys.dm_tran_locks and sys.dm_os_waiting_tasks to identify the full blocking chain. Run the following command:

   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;

   Example query result: In the jacky database, the session with a request_session_id of 62 is blocked by the session with a blocking_session_id of 56. The blocked object is Tbl1, the resource type is KEY, the requesting statement (RequestingText) is (@1 int,@2 int)INSERT INTO [dbo].[Tbl2]([id],[c..., the blocking statement (BlockingText) is BEGIN TRAN INSERT into dbo.Tbl1 (id, col) VALUE..., and the requested lock mode is S.

   The following table describes the returned parameters:

   Parameter	Description
   DBName	The database name.
   request_session_id	The ID of the blocked (requesting) session.
   blocking_session_id	The ID of the blocking session.
   BlockedObjectName	The object that the blocked session is trying to access.
   resource_type	The type of resource being waited for.
   RequestingText	The statement executed by the blocked (requesting) session.
   BlockingText	The statement being executed by the blocking session.
   request_mode	The lock mode requested by the requesting session.

Tuning recommendations

Follow these recommendations to improve performance.

1. To resolve the block immediately, close the blocking session's connection.

2. Identify and promptly commit any uncommitted, long-running transactions.

3. If blocking is caused by a shared (S) lock and your application can tolerate a dirty read, use the WITH (NOLOCK) query hint. For example, you can write SELECT * FROM table WITH (NOLOCK); to prevent the query from requesting a lock, thereby bypassing the blocking.

4. Review your application logic to ensure that resources are accessed in a consistent order.

Related operations

• To resolve blocking in an emergency, see How to quickly resolve blocking issues on ApsaraDB RDS for SQL Server.

• Use the ApsaraDB RDS console to configure performance metrics and alert rules to quickly detect and respond to database performance issues. For more information, see Monitoring and alerts.

• To learn more about database performance tuning strategies, such as index optimization, query optimization, and storage optimization, see Performance optimization and diagnosis.