[Azure] Troubleshoot blocking issues in Azure SQL Database

# Blocking issues in Azure SQL Database

Blocking is complex a topic, and it's better to understand the basic concepts before diving into the issue.

- [Transaction locking and row versioning guide](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=azuresqldb-curren)
> A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the **atomicity, consistency, isolation, and durability (ACID)** properties, to qualify as a transaction.
> **Locking and row versioning** prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that hasn't yet been committed in the database.
- [Understand and resolve blocking problems](https://learn.microsoft.com/en-us/azure/azure-sql/database/understand-resolve-blocking?view=azuresql)
> **Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency**. Blocking in a database in Azure SQL Database occurs when one session holds a lock on a specific resource and a second session ID attempts to acquire a conflicting lock type on the same resource.

[Lock modes](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17#lock_modes) /  [Lock compatibility](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17#lock_compatibility)

## Troubleshooting steps
### Investigate the active blocking issues

- This query helps identify which sessions are causing blocking in SQL Server. A “head blocker” is a session that is blocking others but is not blocked itself.

```sql
-- This query identifies sessions that are blocking others (head blockers) and provides details about blocking chains.
-- It uses SQL Server DMVs to gather session, request, and wait information.
-- Run this in a context where you have VIEW SERVER STATE permission.

SELECT
    head_blocker = CASE
        -- Mark as head blocker if the session is blocking others but is not itself blocked
        WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
        ELSE ''
    END,
    session_id = s.session_id,
    login_name = s.login_name,
    database_name = DB_NAME(r.database_id),
    blocked_by = r.blocking_session_id,
    open_transactions = r.open_transaction_count,
    status = s.status,
    wait_type = w.wait_type,
    wait_time_ms = w.wait_duration_ms,
    wait_resource = r.wait_resource,
    wait_resource_desc = w.resource_description,
    command = r.command,
    application = s.program_name,
    total_cpu_ms = s.cpu_time,
    total_physical_io_mb = (s.reads + s.writes) * 8 / 1024,
    memory_use_kb = s.memory_usage * 8192 / 1024,
    login_time = s.login_time,
    last_request_start_time = s.last_request_start_time,
    host_name = s.host_name,
    query_hash = r.query_hash,
    blocker_query_or_most_recent_query = txt.text
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c ON s.session_id = c.session_id
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
LEFT JOIN sys.dm_os_tasks AS t ON r.session_id = t.session_id AND r.request_id = t.request_id
LEFT JOIN (
    -- Get the most significant wait for each waiting task
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks
) AS w ON t.task_address = w.waiting_task_address AND w.row_num = 1
LEFT JOIN sys.dm_exec_requests AS r2 ON s.session_id = r2.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(ISNULL(r.sql_handle, c.most_recent_sql_handle)) AS txt
WHERE s.is_user_process = 1
AND (r2.session_id IS NOT NULL OR r.blocking_session_id > 0)

```

- Other useful queries can be found in [Understand and resolve blocking problems](https://learn.microsoft.com/en-us/azure/azure-sql/database/understand-resolve-blocking?view=azuresql)

```sql
-- To catch long-running or uncommitted transactions, use another set of DMVs for viewing current open transactions
SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

-- Use the sys.dm_tran_locks DMV for more granular information on what locks have been placed by queries.
SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = 'TestTable';
```

### Investigate the past blocking issues

It's possible to monitor the blocked process by using Extended Events.
https://sqland.wordpress.com/2022/10/04/how-to-monitor-blocked-processes-in-azure-sql-db/

> Back in the old days, one of the ways this could be achieved was by letting SQL Server generate a “blocked process report” which is disabled by default. To use this mechanism, we start by configuring a threshold (called the blocked process threshold) that can go from 5 to 86400 seconds

```sql
-- Check thresholds
SELECT @@SERVERNAME as azure_sql_db_server, name, value_in_use, description
FROM sys.configurations
WHERE name = 'blocked process threshold (s)'
```

There is an alternative options to record past blocked process by using the **Diagnostics logs (Category: Blocks)**.  See [Configure streaming export of metrics and resource logs - Azure SQL Database & Azure SQL Managed Instance | Microsoft Learn](https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?view=azuresql&tabs=azure-portal) for setup instruction. The blocked process report is only generated after exceeding the blocked process threshold.(When I examine in my Azure SQL DB the threshold's value is 20 seconds.)

Sample query for logs stored in Log Analytics.
```kql
AzureDiagnostics
| where Category == 'Blocks'
```

Additionally, [Database watcher](https://learn.microsoft.com/en-us/azure/azure-sql/database-watcher-data?view=azuresql&tabs=sqldb)  is also an useful managed monitoring solution to collect in-depth workload monitoring data, which give you a detailed view of database performance including blocking occurences.

### Waiting: NETWORKIO
Another common issue is waiting on NETWORKIO.
If issue is reproducible, It's recommended to utilize the following DMV query to check the real-time status and wait type.

```sql
SELECT s.session_id,r.status,r.wait_time,r.wait_type,r.wait_resource,r.cpu_time,r.logical_reads,r.reads,r.writes,mg.required_memory_kb,mg.used_memory_kb,r.total_elapsed_time/(1000*60) AS [Elaps M],SUBSTRING(st.text,(r.statement_start_offset/2)+1,((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS statement_text,COALESCE(QUOTENAME(DB_NAME(st.dbid))+N'.'+QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid,st.dbid))+N'.'+QUOTENAME(OBJECT_NAME(st.objectid,st.dbid)), '') AS command_text,r.command,s.login_name,s.host_name,s.program_name,s.last_request_end_time,s.login_time,r.open_transaction_count,atrn.name AS transaction_name,atrn.transaction_id,atrn.transaction_state FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_query_memory_grants AS mg ON s.session_id=mg.session_id JOIN sys.dm_exec_requests AS r ON r.session_id=s.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st LEFT JOIN (sys.dm_tran_session_transactions AS stran JOIN sys.dm_tran_active_transactions AS atrn ON stran.transaction_id=atrn.transaction_id) ON stran.session_id=s.session_id WHERE r.session_id!=@@SPID ORDER BY r.total_elapsed_time DESC;
```
NETWORKIO is a wait category in Query Store, from sys.dm_os_wait_stats we can further check more detailed wait type during the query execution.

- [sys.query_store_wait_stats](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql?view=sql-server-ver16)
Network IO: ASYNC_NETWORK_IO, NET_WAITFOR_PACKET, PROXY_NETWORK_IO, EXTERNAL_SCRIPT_NETWORK_IOF
- [sys.dm_os_wait_stats](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver16)
ASYNC_NETWORK_IO: Occurs on network writes when the task is blocked waiting for the client application to acknowledge that it has processed all the data sent to it. Verify that the client application is processing data from the server as fast as possible or that no network delays exist. Reasons the client application can't consume data fast enough include: application design issues like writing results to a file while the results arrive, waiting for user input, client-side filtering on a large dataset instead of server-side filtering, or an intentional wait introduced. Also the client computer might be experiencing slow response due to issues like low virtual/physical memory, 100% CPU consumption, etc. Network delays can also lead to this wait - typically caused by network adapter driver issues, filter drivers, firewalls, or misconfigured routers.

Please check below the documentation for troubleshooting guidance.
[Troubleshoot slow queries that result from ASYNC_NETWORK_IO wait type](https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-query-async-network-io)

## Deadlocks guide

- [deadlock guide](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=azuresqldb-current)
>**Deadlocking is often confused with normal blocking.** When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. By default, transactions in the Database Engine don't time out, unless LOCK_TIMEOUT is set. **The requesting transaction is blocked, not deadlocked, because the requesting transaction hasn't done anything to block the transaction owning the lock. Eventually, the owning transaction completes and releases the lock**, and then the requesting transaction is granted the lock and proceeds. **Deadlocks are** resolved almost immediately, **whereas blocking can, in theory, persist indefinitely.** Deadlocks are sometimes called a deadly embrace.

- [Analyze and prevent deadlocks in Azure SQL Database](https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer)
> A **deadlock** occurs when two or more tasks permanently block one another because each task has a lock on a resource the other task is trying to lock. A deadlock is also called a **cyclic dependency**: in the case of a two-task deadlock, transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

A deadlock occurs when two or more sessions lock the database objects they each need, blocking each other from continuing, creating a circular wait dependency. Also, deadlocks mainly occur when several transactions are running simultaneously on the same database. Longer transactions increase the likelihood of deadlocks, as exclusive or update locks become longer and block other processing.

<a href="https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer#analyze-a-deadlock-for-azure-sql-database">Analyze and Prevent Deadlocks - Azure SQL Database | Microsoft Learn</a>

#### How to analyze deadlocks

We can obtain the deadlock graph to analyze the details. First, log into the master database and run the following query.

```sql
	-- Run the following in the master database
	WITH CTE AS ( 
	      SELECT CAST(event_data AS XML)  AS [target_data_XML] 
	  FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
	)
	SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
	target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
	target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM CTE 
```

From the [deadlock_xml] of the query above, if there is any information about the deadlock in the database, it will be output in XML format. If you change this extension to .xdl and save it, you can view the deadlock graph by opening the .xdl file in SQL Server Management Studio (SSMS).

A deadlock graph typically has three nodes:
- `Victim-list`. The deadlock victim process identifier.
- `Process-list`. Information on all the processes involved in the deadlock. Deadlock graphs use the term 'process' to represent a session running a transaction.
- `Resource-list`. Information about the resources involved in the deadlock.

When analyzing a deadlock, it's useful to step through these nodes.
The deadlock victim list shows the process that was chosen as the deadlock victim. In the visual representation of a deadlock graph, processes are represented by ovals. The deadlock victim process has an "X" drawn over the oval.

In the XML view of a deadlock graph, the victim-list node gives an ID for the process that was the victim of the deadlock.

Prevent a deadlock from reoccurring: 
There are multiple techniques available to prevent deadlocks from reoccurring, such as index tuning, forcing plans with Query Store, and modifying Transact-SQL queries.

## General suggestions

++ Reduce a query lock footprint by making the query as efficient as possible: Ensure related tables have proper indexes to maximize the efficiency of index seeks.  
++ Review the application's concurrency design. Instead of updating all updating all rows in one transaction, break it into smaller chunks can reduce lock duration and contention. If possible, please schedule bulk updates during low activity.

## Useful link

[SQL Serverのロックについて出来る限り分かりやすく解説 #DB - Qiita](https://qiita.com/p2sk/items/38fd95b142b07acf7700)

ロックの粒度: [SQL Server の Locks オブジェクト](https://learn.microsoft.com/ja-jp/sql/relational-databases/performance-monitor/sql-server-locks-object?view=sql-server-ver17)
- Object	すべてのデータとインデックスを含む、テーブル、ストアド プロシージャ、ビューなどのロック。 オブジェクトには、 sys.all_objectsにエントリを含む任意のオブジェクトを指定できます。
- Key	インデックスの行のロック。
- Metadata	カタログ情報 (メタデータ) のロック。
- Extent	連続した 8 ページのグループのロック。
- ページ	データベース内の 8 KB のページのロック。
- Xact	トランザクションのロック リソース。

更新の競合：1 ページに格納できるレコード数は、ページサイズ (8KB) とレコードサイズによって決まります。例えば、1 レコードのサイズが 800 バイトの場合、1 ページには約 10 レコードが格納されます。同じレコードを同時に更新する場合、データの整合性を保つためにブロッキングが発生します。
また、同じページ (ヒープまたはクラスタ化インデックス) に格納されている場合、レコードが異なっていても同じページを共有しているため、ブロッキングが発生する可能性があります。