Continuous Blocking

Question

Continuous Blocking

ramana3327

SSCoach

Points: 19722
More actions
June 21, 2016 at 8:55 pm

#327657

Hi Experts,
We are receiving continuous alerts about the alers. One of the insert statement is the main blockger.
I didn't get the permission to kill that spid
Do I need to wait till it committed or is there any other immediate option is available?
Is it ok to run the update statistics on those tables. Is it will be helpful or it causes more blocking?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738632 More actions · Answer 1

To resolve the block? Nothing until it commits or rolls back. If you can post some code, maybe there's a way to shorten the transaction.

Statistics might help, or creating an index (or removing some), but I don't think this will block other readers. A Fullscan will read the entire table (Default is to sample), and I guess if the process read a row that needed to up updated there would be a short block, but since UPDATE STATS isn't processing rows, blocking should be minimal for writers.

However, in SQL 2012+, I saw a note that UPDATE STATS does not invalidate plans in cache, so whatever you are running won't necessarily be helped.

http://www.sqlskills.com/blogs/kimberly/what-caused-that-plan-to-go-horribly-wrong-should-you-update-statistics/

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

ramana3327 (6/21/2016)
Hi Experts,
We are receiving continuous alerts about the alers. One of the insert statement is the main blockger.
I didn't get the permission to kill that spid
Do I need to wait till it committed or is there any other immediate option is available?
Is it ok to run the update statistics on those tables. Is it will be helpful or it causes more blocking?

Hunt the idiot down that either wrote the code or did a manual transaction and forgot to commit it and tell them to fix it. Escalate the problem to the DBA. If you don't have a good DBA, get one. Good DBAs won't tolerate this kind of problem for more than about 5 seconds.

And don't just kill SPIDs even if you could. Find out what's going on first because killing a SPID can have devastating effects especially if it gets stuck in a "0% rollback".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)