Find blocking by T-SQL Queries

What is blocking

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: https://docs.microsoft.com

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.”

Facebook Comments Box

Comments

  • […] Recently frequently we faced blocking issues on our production server. The main reason that I understand, recently we merged a few SQL servers and migrated all databases to a single server. Few databases are shared database which is used by some common applications. For me easiest way to find blocking is to run the mentioned script in this post > ‘Find blocking by T-SQL Queries’. […]

Leave a Reply

Your email address will not be published.