Investigating blocking

Blocking is a very common scenario in many database systems. This is what happens when one query holds exclusive access to a resource that another query also requires. It is normal for some blocking to occur, but severe blocking can cause major performance issues and should be investigated. When troubleshooting query performance, it’s a good idea to check for blocking first to see if queries are slow because they are expensive, or because they are being blocked by some other workload.

The key DMVs for investigating blocking are sys.dm_exec_requests and sys.dm_os_waiting_tasks. As we discussed previously, these DMVs show us which queries are currently running and what state they are in. They also have columns that will indicate which sessions may be causing blocking.

The following example shows a simple query that can be used to look for blocking on the system:

SELECT s.session_id, s.last_request_end_time, ISNULL(r.status,s.status) AS status, s...