This topic describes how to view the blocking statistics of an ApsaraDB RDS for SQL Server instance by using CloudDBA in the ApsaraDB RDS console. You can identify and resolve the blocking problems of the RDS instance based on the blocking statistics.

Prerequisites

The RDS instance is equipped with standard SSDs or enhanced SSDs (ESSDs).

Background information

When a session is modifying a specific resource, SQL Server locks the resource to prevent access and modifications from other concurrent sessions. This enables SQL Server to ensure data consistency. In most cases, SQL Server holds the lock for a short period of time. After the session finishes modifying the resource, SQL Server immediately releases the resource and grants approval for the next session to access the resource. However, the resource can stay locked for a long period of time due to slow SQL statements or other exceptions in the session. This significantly reduces the performance of the RDS instance.

To help you resolve the preceding blocking problem, ApsaraDB RDS provides blocking statistics in the ApsaraDB RDS console. The blocking statistics include the ID of the blocking session, the time at which the blocking problem occurred, and the SQL statement that caused the blocking problem.

Sampling

In most cases, if a session causes a blocking problem that lasts approximately 2 seconds, the performance of the RDS instance does not significantly decrease. However, if multiple consecutive sessions cause blocking problems that each last approximately 2 seconds, the performance of the RDS instance significantly decreases.

ApsaraDB RDS samples blocking problems once every 10 seconds. At each point in time when ApsaraDB RDS samples blocking statistics, the sessions that require more than 2 seconds to execute an SQL statement and block other sessions are recorded.

Procedure

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, choose CloudDBA > Lock Optimization.
  3. Click the Blocking tab. Then, view the details about the blocking problems that are detected in the RDS instance.

Introduction to the Blocking tab

  • Number of blocking sessions

    This section displays the number of blocking sessions over recent time ranges.

  • Blocking Trend

    This section displays the trend of blocking durations over a specific time range.

    Move the pointer over a specific point in time to query the details about the blocking problem that occurred at that point in time. The details include the following information:
    • The time at which the blocking problem occurred.
    • The number of blocked sessions. This number is indicated by the value of the Processes_blocked parameter.
    • The number of transactions that are not committed. This number is indicated by the value of the Uncommited_tran parameter.
      Note The lock that causes the blocking problem cannot be released because transactions are not committed.
    • The hash value of the executed SQL statement and the length of time during which the SQL statement is blocked.
    Blocking Trend
  • Blocking Source Details
    This section displays the details about each blocking session. The details include the following information:
    • Spid: indicates the ID of the session.
    • QueryHash: indicates the hash value of the requested statement in the session. The hash value of the same type of SQL statement is the same.
    • Wait Type: indicates the reason why the session blocks another session that is in the waiting state. For more information about wait types, see sys.dm_os_wait_stats (Transact-SQL).
    • Execution Duration (ms): indicates the length of time that is required by the session to execute the requested SQL statement. Unit: milliseconds.
    • SQL: indicates the SQL statement that causes the blocking problem.
      Note You can move the pointer over an SQL statement. Then, you can click the Copy icon icon that appears to the right of the SQL statement to copy the SQL statement.
    • Time: indicates the time at which the blocking problem occurred.
    • Database Name: indicates the name of the database in which the blocking problem occurred.

    You can click anywhere in a blocking record to view the diagram of the blocking problem.

  • Blocking Diagram

    This section displays the ID of the blocking session, the ID of the blocked session, the type of lock, and the blocking duration. In the following example, the blocking session is marked in red, and the blocked session is marked in blue. For more information about lock types, see Transaction Locking and Row Versioning Guide.

    Blocking Diagram
    You can move the pointer over the ID of a session to view the details about the blocking problem in the session. The details include the following information:
    • SPID: indicates the ID of the blocking session.
    • BlockedBySpid: indicates the ID of the blocked session.
    • WaitType: indicates the type of wait in the session.
    • WaitTimeMs: indicates the duration of blocking in the session. Unit: milliseconds.
    • CMD: indicates the type of SQL statement that is executed in the session.
    • CPU: indicates the length of time during which CPU resources are used by the session. Unit: milliseconds.
    • DBName: indicates the name of the database on which the session runs.
    • ClientAppName: indicates the name of the client from which the session is initiated.
    • HostName: indicates the hostname of the client from which the session is initiated.
    • LoginId: indicates the username that is used to log on to the session.
    • PhysicalIO: indicates the I/O resources that are consumed by the session to execute the requested SQL statement. Each physical I/O is equal to 8 KB.
    • QueryHash: indicates the hash value of the requested statement in the session. The hash value of the same type of SQL statement is the same.
    • StartTime: indicates the time at which the system started to execute the batch of SQL statements that contain the requested SQL statement in the session. Each batch can contain multiple SQL statements and share resources such as the values of variables.
    • Status: indicates the status of the RDS instance.
    • SQL: If you click the ID of the blocking session or blocked session, the details about the requested SQL statement in the session are displayed in the Blocking Diagram section.
  • SQL details

    In the blocking diagram, click the ID of the blocking session or blocked session to view the details about the requested SQL statement in the session. You can also copy and further analyze the statement.