0

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:

Blockings output of sp_whoisactive

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)

  • 1
    Have you looked at optimistic isolation levels such as READ COMMITTED SNAPSHOT or SNAPSHOT ISOLATION which may help avoid these large chains of blocking queries outright? These changes do have caveats though, so do some investigation and testing in non-production environments before making any changes to your live system. – John Eisbrener Apr 8 at 13:59
  • @JohnEisbrener That was the first thing I did after joining this company, change isolation level from default to read committed snapshot. – Learning_DBAdmin Apr 8 at 14:14
  • So READ COMMITTED SNAPSHOT is enabled and you're still getting this behavior? Can you update your question with this information and then provide some more context around the queries causing this behavior? – John Eisbrener Apr 8 at 14:31
  • @JohnEisbrener Updated the question with isolation level, as you could see in the screenshot - writer is blocking writer and there are lot of foreign keys in the table. That seems to be the culprit. Since, Its taking too long to check and fix, I am looking for some script/procedure to find the blocking spid and kill it if it stays on the server for more than 5 minutes.(Not a very good approach though but, can't hep it). – Learning_DBAdmin Apr 8 at 14:37
  • If possible, listing the queries of the lead blocker as well of a few of the blocked queries would be helpful as well. – John Eisbrener Apr 8 at 14:46
0

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

SELECT 

    spid,status,loginame,hostname,DB_NAME(dbid),cmd,cpu,physical_io,DATEDIFF(MI,LastBatch,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 rolledback
                            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
  • 1
    link-only answers aren't really seen as acceptable on DBA.SE - could you add some more detail as to why you think the link will help? It may also be useful to directly copy the relevant information from the article into your answer. – George.Palacios Apr 8 at 11:47
  • I assume that the recommendation to directly copy test from an article into a reply only applies to when you yourself are the author of that article? I.e., we don't encourage "stealing" information on the net...? – Tibor Karaszi Apr 8 at 12:07
  • 3
    @TiborKaraszi absolutely it does not - As long as you credit the author, it's much better to have the text itself on SE. What if the original author removes the post? This post is then completely useless. The advice I've always been given is copy as much out of the article as needed to ensure your answer will always be helpful. – George.Palacios Apr 8 at 12:11
  • @user164767 - Thank you for response. This will work when checked manually and killed however if there are multiple blocking, how will it get all of them? – Learning_DBAdmin Apr 9 at 3:00
  • @user164767 - I tested this and it doesn't seem to be working.We should be targeting the lead blocker and also should check for the lock type. – Learning_DBAdmin Apr 9 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.