{"id":7094,"date":"2010-08-13T00:00:00","date_gmt":"2010-08-13T00:00:00","guid":{"rendered":"https:\/\/azure.microsoft.com\/blog\/finding-blocking-queries-in-sql-azure"},"modified":"2025-09-10T11:35:42","modified_gmt":"2025-09-10T18:35:42","slug":"finding-blocking-queries-in-sql-azure","status":"publish","type":"post","link":"https:\/\/azure.microsoft.com\/en-us\/blog\/finding-blocking-queries-in-sql-azure\/","title":{"rendered":"Finding Blocking Queries in SQL Azure"},"content":{"rendered":"\n

[This article was contributed by the SQL Azure team.]<\/p>\n\n\n\n

Slow or long-running queries can contribute to excessive resource consumption and be the consequence of blocked queries; in other words poor performance. The concept of blocking is not different on SQL Azure then on SQL Server. Blocking is an unavoidable characteristic of any relational database management system with lock-based concurrency.<\/p>\n\n\n\n

The query below will display the top ten running queries that have the longest total elapsed time and are blocking other queries.<\/p>\n\n\n

\nSELECT TOP 10 r.session_id, r.plan_handle,      r.sql_handle, r.request_id,      r.start_time, r.status,      r.command, r.database_id,      r.user_id, r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      r.row_count, st.text  FROM sys.dm_exec_requests r  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st  WHERE r.blocking_session_id = 0       and r.session_id in       (SELECT distinct(blocking_session_id)           FROM sys.dm_exec_requests)  GROUP BY r.session_id, r.plan_handle,      r.sql_handle, r.request_id,      r.start_time, r.status,      r.command, r.database_id,      r.user_id, r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      r.row_count, st.text  ORDER BY r.total_elapsed_time desc\n<\/pre><\/div>\n\n\n
The cause of the blocking can be poor application design, bad query plans, the lack of useful indexes, and so on.<\/p>\n\n\n\n
Understanding Blocking<\/h2>\n\n\n\n
On SQL Azure, blocking occurs when one connection holds a lock on a specific resource and a second connection attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first connection locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.<\/p>\n\n\n\n
The duration and transaction context of a query determine how long its locks are held and, thereby, their impact on other queries. If the query is not executed within a transaction (and no lock hints are used), the locks for SELECT statements will only be held on a resource at the time it is actually being read, not for the duration of the query. For INSERT, UPDATE, and DELETE statements, the locks are held for the duration of the query, both for data consistency and to allow the query to be rolled back if necessary.<\/p>\n\n\n\n
For queries executed within a transaction, the duration for which the locks are held are determined by the type of query, the transaction isolation level, and whether or not lock hints are used in the query. For a description of locking, lock hints, and transaction isolation levels, see the following topics in SQL Server Books Online:<\/p>\n\n\n\n