Understanding and Managing Blocking in SQL Server: A Step-by-Step Guide for New DBAs

Blocking is a common phenomenon in SQL Server that occurs when one session holds a lock on a resource (such as a row, page, or table) and another session is forced to wait until that lock is released. In this guide, we’ll explore what blocking is, how to detect it using built-in tools like sp_who2, sp_whoisActive (a community tool), and dynamic management views (DMVs), and demonstrate a blocking scenario using the AdventureWorks database.

---

Table of Contents

1. Introduction to Blocking
2. Key Tools to Monitor Blocking
3. Demonstrating a Blocking Scenario in AdventureWorks
   • Step 1: Open Multiple Sessions in SSMS
   • Step 2: Start a Transaction to Hold a Lock (Session 1)
   • Step 3: Attempt to Access the Locked Resource (Session 2)
   • Step 4: Monitor Blocking with DMVs and sp_who2
   • Step 5: Resolve the Blocking
4. Key Takeaways
5. Conclusion

---

Introduction to Blocking

In SQL Server, blocking happens when one session (or query) holds a lock on a resource, preventing other sessions from modifying or reading the same data until the lock is released. This is a natural part of the transaction isolation mechanism but, if not managed well, can lead to performance issues or even deadlocks.

---

Key Tools to Monitor Blocking

Before diving into the demonstration, here are some essential tools and queries used by DBAs to monitor and diagnose blocking issues:

1. sp_who2

• Usage:
  Run EXEC sp_who2; to display all active SQL Server sessions.
• What to Look For:
  The BlkBy column shows which sessions are blocking others. If a session has a non-zero value in this column, it is being blocked by the session with that ID.

2. sp_whoisActive

• Usage:
  sp_whoisActive is a popular community tool that provides detailed insights into active sessions, including blocking and long-running queries.
• Download:
  You can download it from its GitHub repository.

3. Dynamic Management Views (DMVs)

• Query to Identify Blocking Sessions:

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

  This query displays details about sessions that are currently blocked and the resources they are waiting on.

• Query to Check Specific Locks:

  SELECT request_session_id, resource_type, resource_description, request_mode
  FROM sys.dm_tran_locks
  WHERE request_session_id = 60; -- Replace with your session ID
  

  This query helps you inspect the locks held by a specific session (replace "60" with the appropriate session ID).

---

Demonstrating a Blocking Scenario in AdventureWorks

Below is a practical demonstration of blocking using the AdventureWorks database. Follow these steps in SQL Server Management Studio (SSMS):

Step 1: Open Multiple Sessions in SSMS

• Action:
  Open at least two query windows. We will refer to these as Session 1 and Session 2.

Step 2: Start a Transaction to Hold a Lock (Session 1)

In Session 1, execute the following commands:

USE AdventureWorks;
GO

BEGIN TRANSACTION;
UPDATE Sales.SalesOrderDetail
SET UnitPrice = UnitPrice + 1
WHERE SalesOrderID = 43659;
-- Notice: We are NOT committing or rolling back yet!

Explanation:

• This query updates the SalesOrderDetail row for a specific order (SalesOrderID = 43659).
• The transaction remains open, and the updated row is locked. As a result, any other session trying to access this row will have to wait.

Step 3: Attempt to Access the Locked Resource (Session 2)

Switch to Session 2 and run:

USE AdventureWorks;
GO

SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43659;

Observation:

• This query will hang (not return results immediately) because it is waiting for the lock held by Session 1 to be released.

Step 4: Monitor Blocking with DMVs and sp_who2

While Session 2 is waiting, open a third session (or use an existing one) and run the following commands to monitor the blocking:

• Using sp_who2:

  EXEC sp_who2;
  

  • Check: Look at the BlkBy column to see which session is causing the block.

• Using DMVs:

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

  • Check: This query displays detailed information about the blocked sessions and what they’re waiting for.

Step 5: Resolve the Blocking

Return to Session 1 and resolve the blocking by either committing or rolling back the transaction:

COMMIT TRANSACTION;
-- OR
-- ROLLBACK TRANSACTION;

Result:

• Once the transaction is committed (or rolled back), the lock on the row is released.
• Session 2 will now complete its query and display the results.

---

Key Takeaways

• Understanding Blocking:
  Blocking occurs when a session holds a lock that prevents other sessions from accessing the same resource.

• Monitoring Tools:
  Use tools like sp_who2, sp_whoisActive, and DMVs (such as sys.dm_exec_requests and sys.dm_tran_locks) to detect and diagnose blocking.

• Best Practices:

  • Always commit or roll back transactions promptly to minimize blocking.
  • Monitor long-running transactions that might lead to blocking.
  • Use diagnostic queries to understand what resources are being locked and by whom.

---

Conclusion

Blocking is a critical concept in SQL Server that, if not managed properly, can impact performance and user experience. This guide has walked you through a real-world demonstration using the AdventureWorks database, showing you how to create a blocking scenario, monitor it using different tools, and resolve it effectively.

As you gain more experience, you'll learn to balance transaction isolation and concurrency to maintain an optimal and responsive SQL Server environment. If you have any questions or need further assistance with diagnosing blocking issues, feel free to leave a comment below.

Happy database managing!

---

Would you like additional details on how to detect and even kill a blocking session? Let me know, and we can dive deeper into advanced blocking management techniques!