Database Blocking, kill the session which is responsible after 5 minutes

Question

I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This application uses typical primary key-foreign key relationship and all the tables are tightly coupled. Few tables have as many as 30 foreign keys and hence whenever there is an insert/update/delete, all of those underlying tables get blocked. I have communicated this to vendor and they also have tried to remove PK-FK for possible tables and deployed in the test environment however its taking ages to go to production as lot needs to be tested and its failing in some or other scenario.

Since, I am supporting vendor based application and any change needs to be tested by multiple stakeholders, I don't know when will it really go to production. Blocking becomes so severe at time that whole portal goes down and I am left with no option but to kill the blocking SPID.

I am using below query to check the blocking by using sp_whoisactive procedure Mr. Adam Machanic:

EXEC sp_WhoIsActive
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC',

So far, I was working on reactive approach meaning, I was killing SPID who are responsible for blocking however this doesn't seem to go well and sometime blocking escalates and I have to run like fire-fighter. I would like to switch my side from reactive to proactive and planning to put a procedure in place which will take input as SPID from above procedure, check the duration and if blocking is more than 5 minutes, kill it.

At times, I have seen there are many blockers responsible for blocking as shown below:

Don't really know how will these kind of blocking be handled. Read something similar by Mr. Jason Hall at this link and second one at this forum however I am looking for something having base of sp_whoisactive.

This is to add very pertinent point about database isolation that I have changed database isolation level from default to "Read Committed Snapshot". On the very same topic, I had asked this question when I joined this forum however it was closed as I was very new to this forum and was unclear on my question.

Any input/help on this will be appreciated.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)

Answer 1

You can use the SQL script I wrote up below to build a dynamic SQL string that will kill all blocking sessions that have been running for at least 5 minutes.

You'll want to create a SQL Agent job (SQL Agent Job Docs) that executes this SQL script. (You can schedule the SQL Agent job to run as often as every second if you want, but the Agent Job GUI only allows you to select a minimum of 1 minute when scheduling, so if you need greater frequency you have to script the job out.)

-- Variable to hold dynamic SQL of what blocking sessions will be killed
DECLARE @DynamicSQL AS NVARCHAR(MAX) = N'';

-- CTE of blocking sessions
WITH CTE_BlockingSessions AS
(
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)

-- Sets the @DynamicSQL variable = 'KILL ' + all blocking session IDs that have been running for at least 5 minutes
SELECT @DynamicSQL = @DynamicSQL + 'KILL ' + CAST(BS.blocking_session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_requests AS ER 
INNER JOIN CTE_BlockingSessions AS BS
    ON ER.session_id = BS.blocking_session_id
WHERE DATEDIFF(s, ER.start_time, GETDATE()) >= 300 -- 300 seconds = 5 minutes
    AND ER.[status] = 'RUNNING' -- Ensures the blocking session is still active

-- PRINT @DynamicSQL -- For testing, outputs the dynamic SQL to SSMS Messages tab
EXEC sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL

Answer 2

--- Get list of running Processes with greater than 5 mins

SELECT  spid,
 status,
 loginame,
 hostname,
 DB_NAME(dbid),
 cmd,
 cpu,
 physical_io,
 DATEDIFF(MI, last_batch, GETDATE()) EventTime ,
 program_name 
FROM SYS.SYSPROCESSES
WHERE 
 1 = CASE WHEN Status IN ( 'RUNNABLE', 'SUSPENDED' ) THEN 1
          --Transactions that are open not yet committed or rolled back
          WHEN Status = 'SLEEPING' AND open_tran  > 0 THEN 1 
          ELSE 0 END
AND cmd NOT LIKE 'BACKUP%'
AND DATEDIFF(MI,LastBatch,GETDATE())>5 -- greater than 5 mins
            
        

--- Get detail against each SPID retruned by the above query

DBCC INPUTBUFFER( @SPID ) 
        
  
            

--- KILL the process if it has to be

 KILL @SPID

NB Last_Batch not LastBatch

Answer 3

1. Try with NOLOCK it may solve your problem. It will probably will not create any type of locking by executing Select query.

2. Disadvantages of NOLOCK, you may face dirty read problem. so use NOLOCK very carefully.