October 22, 2012 at 9:11 am
I maybe wrong about this, but wanted to check or revise my own concepts.
I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.
Had a very large databases, with code behind that needs to be improved, that usually generates bunch of deadlocks all Monday mornings. I decided to enable the 1222 flag plus create ReIndex jobs (this db never had one, incredible but true) But to my surprise, got no deadlocks today. Knock on wood, but nothing so far.
Could be that the ReIndex job helped somehow? and if that was the case, why ... because is reading pages faster creating less locking, maybe?
I should also say that I moved ldf to different drive, was on same one.
October 22, 2012 at 9:15 am
No, fragmentation will not cause deadlocks, neither will removing fragmentation resolve deadlocks.
Most likely the reason the deadlocks have gone away is that the rebuild also updates stats, which invalidates execution plans. Hence you got different plans, so no deadlocks for now.
That said, deadlocks are most of the time due to code and/or indexes, so worth looking into that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2012 at 9:22 am
GilaMonster (10/22/2012)
No, fragmentation will not cause deadlocks, neither will removing fragmentation resolve deadlocks.Most likely the reason the deadlocks have gone away is that the rebuild also updates stats, which invalidates execution plans. Hence you got different plans, so no deadlocks for now.
That said, deadlocks are most of the time due to code and/or indexes, so worth looking into that.
That is what I thought!
Thanks for reply, appreciate it! I certainly will keep the 1222 flag enable for a while, and see if it can capture something, just in case.
Our developers are also working hard in fixing those code issues based on my recommendations. We have had lot of improvements, but still there is a long road ahead ....
October 22, 2012 at 11:12 am
sql-lover (10/22/2012)
I maybe wrong about this, but wanted to check or revise my own concepts.I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.
Gail has already answered your question, but I want to be sure you're clear on what a deadlock actually is. What you describe here is how I would describe a block. That's very different from a deadlock.
A deadlock is when two different processes each have a resource the other needs, but neither can release the resources it has until it gets the resources the other has. The nickname for a deadlock is the deadly embrace.
It'll help to understand what's happening in your system if you're more sure of the difference between deadlocks and blocks.
"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
October 22, 2012 at 1:41 pm
Grant Fritchey (10/22/2012)
sql-lover (10/22/2012)
I maybe wrong about this, but wanted to check or revise my own concepts.I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.
Gail has already answered your question, but I want to be sure you're clear on what a deadlock actually is. What you describe here is how I would describe a block. That's very different from a deadlock.
A deadlock is when two different processes each have a resource the other needs, but neither can release the resources it has until it gets the resources the other has. The nickname for a deadlock is the deadly embrace.
It'll help to understand what's happening in your system if you're more sure of the difference between deadlocks and blocks.
Yep,
That's why I meant, Grant 🙂 ... English is my second language, so maybe I did not use the right words. But I am clear on what a deadlock is.
Thanks for the follow up, though!
October 22, 2012 at 6:45 pm
sql-lover (10/22/2012)
Grant Fritchey (10/22/2012)
sql-lover (10/22/2012)
I maybe wrong about this, but wanted to check or revise my own concepts.I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.
Gail has already answered your question, but I want to be sure you're clear on what a deadlock actually is. What you describe here is how I would describe a block. That's very different from a deadlock.
A deadlock is when two different processes each have a resource the other needs, but neither can release the resources it has until it gets the resources the other has. The nickname for a deadlock is the deadly embrace.
It'll help to understand what's happening in your system if you're more sure of the difference between deadlocks and blocks.
Yep,
That's why I meant, Grant 🙂 ... English is my second language, so maybe I did not use the right words. But I am clear on what a deadlock is.
Thanks for the follow up, though!
No worries. Just wanted to make sure things were clear.
"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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply