Identify sessions that cause blocking
In this we will learn how to identify sessions causing blocking.
However, 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.
Below, the query 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
Blocking
In 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. However, the time frame for which the first connection locks the resource is very small. But, when it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing.
Further, the duration and transaction context of a query determine how long its locks are held and, thereby, their impact on other queries. And, if the query is not executed within a transaction, the locks for SELECT statements will only be held on a resource at the time it is actually being read. Further, 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.
Further, for a description of locking, lock hints, and transaction isolation levels, see the following topics in SQL Server Books Online:
- Firstly, locking in the Database Engine
- Secondly, customizing Locking and Row Versioning
- Thirdly, lock Modes
- Then, lock Compatibility
- After that, row Versioning-based Isolation Levels in the Database Engine
- Lastly, controlling Transactions (Database Engine)
