:How to troubleshoot blocking problems of apsaradb for RDS SQL Server

Description

Apsaradb for RDS SQL Server is experiencing a blocking problem.

Causes

When an application frequently reads or writes a table or resources, it is prone to blocking. When the blocking problem is serious, the statement execution on the application side becomes slow.

Solutions

Troubleshooting

For the blocking problem of apsaradb for RDS SQL Server, the following are the troubleshooting suggestions.

1. Cycle monitoring sys.sysprocesses to obtain blocking information. The value of the blocked column is the blocking header session_id, and waitresource is the resource waiting for the blocked session. The operation code is as follows:

   while 1=1
   begin
   select * from sys.sysprocesses where blocked<>0;
   waitfor delay '00:00:01' 
   end

   Note

   The Loop gap can be customized. Here 00:00:01 as an example.

   The following command output is displayed:

   Note

   Note: for the explanation of the fields in the figure, see documentation for sys.sysprocesses.

2. Cycle monitoring sys.dm_tran_locks,sys.dm_os_waiting_tasks and other views, you can get the blocking graph. The Request_session_id indicates the blocked session_id, the RequestingText indicates the blocked statement, the blocking_session_id indicates the blocking session_id header, and the BlockingText indicates the blocking header statement. The operation code is as follows:

   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 command output is displayed:

   Note

   • DBName: the name of the database.

   • request_session_id: the ID of the blocked session.

   • blocking_session_id: the ID of the blocking header session.

   • BlockedObjectName: the object of blocked session operations.

   • resource_type: the type of the requested resource.

   • RequestingText: The statement executed in the current session, that is, the statement blocked.

   • BlockingText: blocking the statement executed by the session header.

   • request_mode: the lock mode requested by the current session.

Recommendations

Follow these steps to optimize the configuration.

1. Closing the blocking head connection can help relieve the blocking quickly.

2. Check whether a transaction has not been submitted for a long time and submit the transaction in time.

3. If locks are involved in the blocking and dirty reads are allowed, you can use the query hint with nolock to allow the query statement to request locks and avoid blocking, such as select * from table with(nolock);.

4. Check application logic to access a resource in order.

Application scope

• ApsaraDB RDS for SQL Server