Find blocking by T-SQL Queries

Spread the love

Find blocking by T-SQL Queries

Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. As mentioned previously, in SQL Server, blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is small. When the owning session releases the lock, the second connection is then free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. Source:

DMV ‘sys.dm_exec_requests’  provides details on all of the processes running in SQL Server. Its returns information about each request that is executing in SQL Server. Use any one script out of three.

Here is another script where the result will be below the table.

Kill blocking process

1) Use an activity monitor to kill the process

Right-click on the SQL instance name -> Activity monitor -> Expand the process section -> Find the relevant process id and right-click to kill the process.

“Please let us know if there are any changes/modifications required. Feel free to comment below – We always appreciate hearing your tips.”

Spread the love
Author: OTi
Meet OTi, the brains behind the popular tech blog "" OTi is an accomplished author, tech enthusiast, and an expert in SQL Server. With over a decade of experience in the tech industry, he has become a trusted voice in the world of database management. OTi's passion for technology began at a young age, and he has been tinkering with computers ever since. He graduated with a degree in Computer Science and has since worked for some of the biggest tech companies in the world. His experience has given him a deep understanding of how technology works and how it can be used to improve our lives.

1 thought on “Find blocking by T-SQL Queries

Leave a Reply

Your email address will not be published. Required fields are marked *