December 29, 2014 at 2:24 am
Hi All,
I'm having issues with deadlocks, even though I took a lot of preventions for them.
There are deadlocks only in saturday and sunday during the week, when there are much updates/inserts (7K database transactions/sec)
In the deadlock primer you'll see SPs from different databases taking part in the deadlock.
PRIMARY filegroup is used for all databases. Can it be the reason for the deadlocks?
There are 30 small databases the one instance ...
Could anyone give an advice or suggestion for this situation?
Thanks
Igor Micev,My blog: www.igormicev.com
December 29, 2014 at 2:37 am
you'll have to investigate the object referred by waitresource.
e.g. "KEY: 27:72057594095534080 (989126c604a2)"
and interpret how that resource is being used by the conflicting threads
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 29, 2014 at 2:42 am
Have a read through this: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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
December 29, 2014 at 2:58 am
Thanks both for the replies
from the deadlock report you can see isolationlevel = "read committed (2)"
Isolation level for all databases is READ COMMITTED SNAPSHOT, and that is one of the recommendation for minimizing deadlocks.
I wonder, why is that isolation level used?
Igor Micev,My blog: www.igormicev.com
December 29, 2014 at 3:02 am
Read committed snapshot is the read committed isolation level, implemented with row versions. It's not a separate isolation level like Snapshot is.
Hence you see Read Committed as the isolation level.
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
December 29, 2014 at 3:07 am
GilaMonster (12/29/2014)
Read committed snapshot is the read committed isolation level, implemented with row versions. It's not a separate isolation level like Snapshot is.Hence you see Read Committed as the isolation level.
For a database, i have the following:
namesnapshot_isolation_state_descis_read_committed_snapshot_on
F1SBON1
what level is being used?
Igor Micev,My blog: www.igormicev.com
December 29, 2014 at 3:10 am
Depends. If you explicitly request an isolation level, you get whichever one you request. If you allow the default of read committed, then you get read committed using row versions.
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
December 29, 2014 at 3:33 am
GilaMonster (12/29/2014)
Depends. If you explicitly request an isolation level, you get whichever one you request. If you allow the default of read committed, then you get read committed using row versions.
Then, even though the isolation level is READ COMMITTED using row versions, there are sill deadlocks.
I've read that read committed snapshot uses pessimistic writes, and snapshot uses optimistic writes. Both use optimistic reads. Previously SNAPSHOT isolation level was in use, but the same situation.
Those isolation levels are supposed to overpass the issues with deadlocks, ..but there are still deadlocks in the databases...
I'm attaching an excel file with some of the most important performance counters, and compared with the poster for performance counters (http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf).
Igor Micev,My blog: www.igormicev.com
December 29, 2014 at 3:35 am
Igor Micev (12/29/2014)
GilaMonster (12/29/2014)
Depends. If you explicitly request an isolation level, you get whichever one you request. If you allow the default of read committed, then you get read committed using row versions.Then, even though the isolation level is READ COMMITTED using row versions, there are sill deadlocks.
I've read that read committed snapshot uses pessimistic writes, and snapshot uses optimistic writes. Both use optimistic reads. Previously SNAPSHOT isolation level was in use, but the same situation.
Those isolation levels are supposed to overpass the issues with deadlocks, ..but there are still deadlocks in the databases...
I'm attaching an excel file with some of the most important performance counters, and compared with the poster for performance counters (http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf).
Additional info: The server is 12 core, 24 logical cores, 128GB RAM, and a RAID 10 disk.
Igor Micev,My blog: www.igormicev.com
December 29, 2014 at 3:41 am
Igor Micev (12/29/2014)
Then, even though the isolation level is READ COMMITTED using row versions, there are sill deadlocks.
Yes, and?
I've read that read committed snapshot uses pessimistic writes, and snapshot uses optimistic writes.
Um, no. The difference between the two is about what row version is the correct one to use for a select. Read Committed Snapshot is consistency at the statement level, snapshot isolation is consistency at the transaction level.
Those isolation levels are supposed to overpass the issues with deadlocks,
No they're not. They reduce the likelyhood of deadlocks as readers no longer block writers, but that's it. Please read the article I referenced earlier.
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
December 29, 2014 at 3:53 am
the deadlock resource is dbid = "10" objectname = "F1SB.dbo.LiveMatches" indexname = "PK_LiveMatches"
that is the object involved in the merge operation as receiving object !
I've seen trancount =3 mentioned ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 29, 2014 at 4:54 am
GilaMonster (12/29/2014)
Igor Micev (12/29/2014)
Then, even though the isolation level is READ COMMITTED using row versions, there are sill deadlocks.Yes, and?
I've read that read committed snapshot uses pessimistic writes, and snapshot uses optimistic writes.
Um, no. The difference between the two is about what row version is the correct one to use for a select. Read Committed Snapshot is consistency at the statement level, snapshot isolation is consistency at the transaction level.
Those isolation levels are supposed to overpass the issues with deadlocks,
No they're not. They reduce the likelyhood of deadlocks as readers no longer block writers, but that's it. Please read the article I referenced earlier.
I'll read it in details, I knew that, but having 200 deadlocks per busy day is something we don't want to see. I'll think about the deadlocks due to parallelism, and I'll come back to this thread, thanks for now.
Igor Micev,My blog: www.igormicev.com
December 30, 2014 at 10:31 am
GilaMonster (12/29/2014)
Have a read through this: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
Hi Gail,
I read your article. In Reader-Writer Deadlocks, why do you move the merge statement into another SP (UpdateCustomerLatestOrderStatus ), can't you do that in a separate transaction within the same stored procedure? What is the difference?
Igor Micev,My blog: www.igormicev.com
December 30, 2014 at 10:35 am
Igor Micev (12/30/2014)
GilaMonster (12/29/2014)
Have a read through this: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/Hi Gail,
I read your article. In Reader-Writer Deadlocks, why do you move the merge statement into another SP (UpdateCustomerLatestOrderStatus ), can't you do that in a separate transaction within the same stored procedure? What is the difference?
Errrr... I never move anything into a different procedure in that article, and there are no merge statements in that section, there's only even a single mention of Merge in the section on Range locks..
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
December 30, 2014 at 11:26 am
GilaMonster (12/30/2014)
Igor Micev (12/30/2014)
GilaMonster (12/29/2014)
Have a read through this: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/Hi Gail,
I read your article. In Reader-Writer Deadlocks, why do you move the merge statement into another SP (UpdateCustomerLatestOrderStatus ), can't you do that in a separate transaction within the same stored procedure? What is the difference?
Errrr... I never move anything into a different procedure in that article, and there are no merge statements in that section, there's only even a single mention of Merge in the section on Range locks..
Sorry, I meant of the update statement ( I messed with the merge in my query). I did the same, separated the merge in a separate transaction within the stored procedure, ..it's still under test.
Thanks!
Igor Micev,My blog: www.igormicev.com
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply