SQL Server database used for a busy OLTP production database comprises of more blocking locks compared to oracle server for similar application design. So while managing a sql server production database we observe that blocking locks are in common place. While locks are required for maintaining data integrity blocking locks causing halt to simultaneous transactions are something application always want to avoid. This is an approach to capture blocking locks in the SQL Server database.
In SQL Server query execution is not only delayed by poorly written sql queries but also due to blocking sessions causing delay in query execution.
1. Check all sql server process and blocking process
Check the BlkBy column in the output. If there is a particular session/spid that is blocking many other sessions. Also check for spid blocked by a main spid and in turn blocking many others as well. Look for spid >50 as spid<=50 are reserved for SQL Server internal processes.
2. Get SQL statement blocking other process
If a certain spid and list of spids are found to be blocking other sessions for more than ceratin interval,suppose >10 mins or during observation period it would be interesting to know the current sql queries executed by the spid.
Put the blocking spid as parameter to dbcc inputbuffer(52). This will show sql statement blocking other process.
3. Kill sql server process
kill <blocking spid>
Kill the process by providing the blocking spid as parameter to the kill statement
When queries get blocked by spid = -2 then kill (-2) will not work.
Process with spid -2 means an active transaction in sql server instance with no enlisted session.
Process with spid -2 is also known as Orphan Transaction.
Query to kill the Orphan Transaction blocking other process in sql server :
select distinct request_owner_guid from sys.dm_tran_locks where request_session_id = -2
If there are multiple Unit of Unit of Work ID (UOW), all needs to be killed as the blocking UOW cannot be identified separately.
The following example shows how to terminate an orphaned distributed transaction (session ID = -2) with a UOW of D5499D33-E396-45BA-BB7E-DF9C194B48CF.
To resolve a blocking scenario in the sql server instance do the following:
- Identify process causing blocking locks to other processes.
- Examine the sql statement executed by the blocking transaction.
- Kill the blocking transaction when required including orphaned transaction or transaction with spid -2 having no real session id.
Example for spid 52