Blocking

Question

Blocking

sqlguru

SSC-Addicted

Points: 471
More actions
December 7, 2022 at 11:41 pm

#4123158

We were getting constant blocking on the db server. However, after stopping the process blocking went away. Just wanted to check automatic SQL table locking seems to be messed up. So the process is trying to access the locked table and would never finish and cause the use the CPU. Process hasn't been changed. What could be the issue?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Heh... you're the one touting the handle of "sqlguru". You should be teaching us how you fixed the issue. 😀

If you want help with a performance issue, we need more information. Please Read'n'Heed the article at the second link in my signature line below.

To SWAG a several of the more common answers for the "What could be the issue?" question...

Crap code that reached a tipping point in the data.
Someone did some index maintenance that changed execution plans or the rebuilds removed the wrong kind of space and all inserts and updates are now causing massive page splits, which easily take 43 times longer (or worse) than normal.
Someone added an index.
Underpowered hardware reached a tipping point and started hitting the "swap file".
Someone changed a server setting.
Someone added an anti-virus and forgot to exclude SQL Server files.
Statistics no longer represent the truth of the data and need to be rebuilt.
Process hasn't been changed? Famous last words.
Someone changed a system or database setting.
Someone added another "process" that uses most of the same things and has similar problems as above.

Without the things listed in the article I talked about, though, any recommendations or suggestions are basically a crap shoot.

--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)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Make sure you turn on blocked process report in Extended Events. It'll be a lot easier to KNOW what's going on rather than relying on some random people on the internet guessing based on thoroughly incomplete information.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Fant SSC Rookie Points: 38 More actions · Answer 3

Yep. Get a Blocked Process report and feed your findings back to us. We're willing to help you out. It can be a lot of things.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

As a bit of a sidebar, check out Adam Machanic's sp_WhoIsActive at the following link. Click on the "Documenation" tab and look for "blocking" on the next list of links. Add that kind of check to a blocking alert and have it write the output to a table and you'll have an awesome tool to figure out what's going on with blocking.

http://whoisactive.com/

--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)

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

SQLGuru any update on your findings?.

Regards
Durai Nagarajan