MSDN Documentation

Understanding and Resolving Blocking in SQL Server

Blocking occurs in SQL Server when one session holds a lock on a resource that another session requires. This can lead to performance degradation, application unresponsiveness, and timeouts. Understanding the mechanisms of blocking and how to diagnose and resolve it is crucial for maintaining a healthy and performant SQL Server environment.

What is Blocking?

SQL Server uses a locking mechanism to ensure data integrity during concurrent transactions. When a session modifies data, it acquires locks on the relevant resources (e.g., rows, pages, tables). Other sessions that need to access these resources must wait until the locks are released. If a session acquires a lock that is incompatible with a lock held by another session, blocking occurs.

A blocking chain exists when session A is blocked by session B, and session B is blocked by session C, and so on. The session at the head of the chain, which is not blocked by anyone, is the root cause of the blocking.

Types of Locks

SQL Server employs various lock modes to govern data access, including:

• Shared (S) Locks: Acquired by a session when reading data. Multiple sessions can hold shared locks on the same resource concurrently.
• Exclusive (X) Locks: Acquired by a session when modifying data (INSERT, UPDATE, DELETE). Only one session can hold an exclusive lock on a resource at a time.
• Update (U) Locks: Acquired when reading data that might be updated. This prevents a common deadlock scenario where two sessions acquire shared locks, then both try to convert them to exclusive locks.
• Intent Locks: Indicate the type of lock a session intends to acquire at a lower granularity (e.g., an Intent Exclusive (IX) lock on a table indicates an intent to acquire exclusive locks on rows within that table).

Common Causes of Blocking

• Long-Running Transactions: Transactions that remain open for extended periods, holding locks unnecessarily.
• Inefficient Queries: Queries that perform table scans or access large amounts of data, acquiring many locks for a prolonged duration.
• Missing or Inappropriate Indexes: Lack of proper indexes forces SQL Server to scan more data, leading to more locks being acquired.
• Deadlocks: Occur when two or more sessions are blocked by each other, each waiting for a resource held by the other.
• Lock Escalation: When a large number of row or page locks are taken, SQL Server may escalate them to a table lock, potentially blocking more users.
• Poorly Written Application Logic: Applications that don't handle transactions efficiently or access data in an unoptimized manner.

Identifying Blocking

Effective identification is the first step to resolving blocking issues.

Using sp_who2

A quick and common way to see current activity, including blocking:

EXEC sp_who2;

Look for sessions where the BlkBy column contains a SPID (Server Process ID). The SPID listed in BlkBy is the session blocking the current session.

Using Dynamic Management Views (DMVs)

DMVs provide more detailed information for analysis.

SELECT
    session_id,
    blocking_session_id,
    wait_type,
    wait_time,
    resource_description
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

This query shows active requests that are being blocked. The blocking_session_id column indicates the SPID of the blocking session. You can join this with other DMVs like sys.dm_exec_sessions to get more context about the blocking and blocked sessions.

SELECT
    s1.session_id AS BlockingSessionID,
    s2.session_id AS BlockedSessionID,
    DB_NAME(r1.database_id) AS DatabaseName,
    s2.login_name AS BlockedLoginName,
    s2.host_name AS BlockedHostName,
    s2.program_name AS BlockedProgramName,
    r1.wait_type AS BlockingWaitType,
    r1.wait_time AS BlockingWaitTime,
    r1.command AS BlockingCommand,
    r2.command AS BlockedCommand,
    SUBSTRING(st.text, (er.statement_start_offset / 2) + 1,
        ((CASE er.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE er.statement_end_offset
         END - er.statement_start_offset) / 2) + 1) AS StatementText
FROM sys.dm_exec_requests AS r1
INNER JOIN sys.dm_exec_requests AS r2
    ON r1.session_id = r2.blocking_session_id
INNER JOIN sys.dm_exec_sessions AS s1
    ON r1.session_id = s1.session_id
INNER JOIN sys.dm_exec_sessions AS s2
    ON r2.session_id = s2.session_id
OUTER APPLY sys.dm_exec_sql_text(r2.sql_handle) AS st
WHERE r2.blocking_session_id <> 0;

Resolving Blocking

Once identified, blocking issues can be addressed through various strategies:

Optimize Queries

Review and tune the queries that are holding locks for extended periods. Ensure they are efficient, use appropriate indexing, and avoid unnecessary data retrieval.

Proper Transaction Management

Keep transactions as short as possible. Commit or rollback transactions promptly. Avoid placing user interaction within a transaction.

Effective Indexing

Ensure that appropriate indexes exist for the tables involved in blocking. This allows queries to find data faster and acquire fewer or shorter-lived locks.

Appropriate Isolation Levels

While most applications use the default READ COMMITTED isolation level, consider if a more restrictive level (e.g., REPEATABLE READ, SERIALIZABLE) is being used unnecessarily, as these can increase locking. Conversely, if read concurrency is a problem, examine the possibility of using READ COMMITTED SNAPSHOT ISOLATION (RCSI) or SNAPSHOT isolation, which uses row versioning instead of locking for readers.

Reduce Lock Duration

Break down large operations into smaller batches. Process data in smaller chunks to minimize the time locks are held.

Advanced Techniques

For complex blocking scenarios, consider using SQL Server Profiler or Extended Events to capture detailed trace information, analyze lock events, and identify the root cause of persistent blocking.

Conclusion

Blocking is an inherent part of relational database systems that manage concurrency. By understanding the causes, using the right tools to identify blocking sessions, and implementing effective resolution strategies, you can significantly improve the performance and stability of your SQL Server applications.