Finding Blocking Queries in SQL Azure

[This article was contributed by the SQL Azure team.]

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.

The query below will display the top ten running queries that have the longest total elapsed time and are blocking other queries.

 SELECT 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

The cause of the blocking can be poor application design, bad query plans, the lack of useful indexes, and so on.

Understanding Blocking

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.

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.

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:

• Locking in the Database Engine
• Customizing Locking and Row Versioning
• Lock Modes
• Lock Compatibility
• Row Versioning-based Isolation Levels in the Database Engine
• Controlling Transactions (Database Engine)

Summary

The short and simple is that a lot of small short quick queries reduce the chance of blocking compared to fewer longer running queries. Breaking up the work you are doing into small units, reducing the amount of transactions, and writing fast running queries will get you the best performance. Remember: even one poorly written slow query can block faster more efficient queries making them slow. Do you have questions, concerns, comments? Post them below and we will try to address them.