When an ApsaraDB RDS for SQL Server instance slows down unexpectedly, lock contention is a common cause. The Blocking tab in the ApsaraDB RDS console shows which sessions are holding locks, how long they have been blocking others, and which SQL statements are responsible — giving you the data you need to pinpoint and resolve the root cause.
Prerequisites
Before you begin, make sure that:
The RDS instance uses cloud disks
The RDS instance does not run SQL Server 2008 R2 with cloud disks
The RDS instance is deployed in one of the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Chengdu), China (Hong Kong), Singapore, or UAE (Dubai)
How blocking works
SQL Server locks a resource when a session modifies it, preventing other sessions from reading or changing the same data. Under normal conditions, locks are held briefly and released as soon as the transaction commits. When a session holds a lock for an extended period — due to a slow SQL statement or an uncommitted transaction — other sessions queue up and wait. If multiple sessions are blocked consecutively, overall instance performance degrades significantly.
ApsaraDB RDS samples blocking activity every 10 seconds. At each sample point, any session that has been blocking another session for more than 2 seconds is recorded. A single blocking event lasting around 2 seconds typically has minimal impact; sustained or repeated blocking across multiple sessions causes visible performance degradation.
View blocking statistics
Go to the Instances page. In the top navigation bar, select the region where the RDS instance is deployed, then click the instance ID.
In the left-side navigation pane, choose Autonomy Services > Lock Optimization.
Click the Blocking tab.
Blocking tab reference
The Blocking tab has four sections: Number of blocking sessions, Blocking Trend, Blocking Source Details, and Blocking Diagram.
Number of blocking sessions
Displays the number of active blocking sessions over recent time ranges. Use this section for a quick overview of blocking frequency.
Blocking Trend
Displays blocking duration over a selected time range as a trend chart.
Hover over a data point to see the following details:
| Field | Description |
|---|---|
| Time | The time when the blocking event was sampled |
Processes_blocked | The number of sessions blocked at that point in time |
Uncommited_tran | The number of uncommitted transactions at that point in time. Uncommitted transactions hold locks and prevent them from being released, so a non-zero value here often indicates the root cause of sustained blocking. |
| QueryHash + blocking duration | The hash value of the SQL statement being blocked, and how long it has been waiting |
Blocking Source Details
Lists each blocking session captured in the current sample. Click any row to load the corresponding blocking diagram.
| Field | Description |
|---|---|
| Spid | The session ID of the blocking session |
| QueryHash | The hash value of the SQL statement in the session. Statements of the same type share the same hash value, which helps you group repeated offenders. |
| Wait Type | The reason the session is blocking others. The wait type tells you what resource the session is waiting for and whether the block will resolve on its own. For the full list of wait types, see sys.dm_os_wait_stats (Transact-SQL). |
| Execution Duration (ms) | How long the session has been running the SQL statement, in milliseconds. A high value combined with a non-null wait type usually means the query is actively running and holding a lock. |
| SQL | The SQL statement causing the blocking. Hover over the statement and click the copy icon to copy it. |
| Time | The time when the blocking event was recorded |
| Database Name | The database where the blocking occurred |
Blocking Diagram
Displays the relationship between blocking and blocked sessions. The blocking session is marked in red; the blocked session is marked in blue. For lock type definitions, see Transaction locking and row versioning guide.
Hover over a session node to see the following details:
| Field | Description |
|---|---|
| SPID | The session ID of the blocking session |
| BlockedBySpid | The session ID of the session being blocked |
| WaitType | The wait type for the session. Use this to determine whether the block is caused by an active query or an idle session holding an open transaction. |
| WaitTimeMs | How long the session has been waiting, in milliseconds |
| CMD | The type of SQL statement being executed |
| CPU | Total CPU time consumed by the session, in milliseconds |
| DBName | The database the session is running against |
| ClientAppName | The application that initiated the session |
| HostName | The hostname of the client |
| LoginId | The username used to log in |
| PhysicalIO | The physical I/O consumed by the session. Each unit equals 8 KB. |
| QueryHash | The hash value of the SQL statement. Statements of the same type share the same hash value. |
| StartTime | The time when the current batch started executing. A batch can contain multiple SQL statements that share resources such as variable values. |
| Status | The status of the RDS instance. |
| SQL | Click the session node to view the full SQL statement in the Blocking Diagram section. |