How to check blocking by T SQL Queries in SQL Server

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.

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


Comments

Leave a Reply

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