Have you ever encountered a situation where you were unable to delete data from a table in SQL Server? It can be frustrating, especially when you’ve tried all the usual methods like TRUNCATE, DELETE, and DROP, but nothing seems to work.

In this article, we will discuss a dirty solution to this problem and explore the concept of SQL Server blocking. We will also provide alternative, safer methods to handle blocking in SQL Server.

The Story

Imagine receiving a late-night phone call from a friend who is desperately seeking your help with a strange SQL Server issue. They are unable to delete data from a table and have already exhausted all their options. However, due to security reasons, they cannot grant you access to their system.

After conducting some background checks, you discover that the problem lies with open transactions. There is a transaction that has been unnecessarily open for a long time, causing the blocking issue. In this particular case, it was safe to kill the transaction, which resolved the problem.

Identifying the Blocking Query

To identify the blocking query, you can use the following script:

SELECT db.name AS DBName,
       tl.request_session_id,
       wt.blocking_session_id,
       OBJECT_NAME(p.OBJECT_ID) AS BlockedObjectName,
       tl.resource_type,
       h1.TEXT AS RequestingText,
       h2.TEXT AS BlockingText,
       tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

This query will return information about the blocking session, including the database name, session IDs, blocked object name, resource type, and the SQL text of the requesting and blocking queries.

Handling Blocking

While killing the blocking session may seem like a quick solution, it is not recommended for production environments. Killing important transactions can lead to serious integrity issues.

Instead, consider the following safer methods to handle blocking:

1. Identify and optimize the blocking query: Analyze the blocking query and optimize it to improve performance. This may involve adding indexes, rewriting the query, or tuning the database configuration.
2. Implement proper transaction management: Ensure that transactions are properly managed and closed in a timely manner. Long-running transactions can increase the chances of blocking.
3. Use snapshot isolation: Implement snapshot isolation to reduce blocking. Snapshot isolation allows readers to access the data without being blocked by writers.
4. Consider using Read Committed Snapshot Isolation (RCSI): RCSI provides a consistent view of the data for readers, even when writers are modifying the data. This can help reduce blocking in certain scenarios.

By following these best practices, you can effectively handle blocking in SQL Server without resorting to risky solutions.

Conclusion

SQL Server blocking can be a frustrating issue, but it is important to handle it properly to avoid any potential integrity issues. While killing the blocking session may seem like a quick fix, it is not recommended for production environments. Instead, focus on identifying and optimizing the blocking query, implementing proper transaction management, and considering snapshot isolation or Read Committed Snapshot Isolation (RCSI).

By following these best practices, you can ensure the smooth operation of your SQL Server and minimize the impact of blocking on your database performance.