September 27, 2009 at 10:30 am
I'm trying to investigate production deadlocks for an application recently turned on in production. Deadlock graph analysis shows an exclusive lock on PK indexes and along comes a SELECT that get vicitimized in the contention because it uses a PK based join (INNER or OUTER). DBCC SHOWCONTIG([table_name]) WITH ALL_INDEXES, TABLERESULTS gives a 90+% frag on the table and indexes. This table has about 1000 rows.
As a next step, I ran a rebuild and the next day at close of business DBCC SHOWCONTIG([table_name]) WITH ALL_INDEXES, TABLERESULTS returned close to 50+% fragmentation again on all indexes. There were probably about 6000 total transactions for the day. As expected towards the end of the day, deadlock creeped in.
It almost seems like the drives are behaving sequential ...
Such high levels of fragmentation baffles me ... i tried capturing wait statistics for the next couple of days. Below is a typical pattern I am noticing ...
CXPACKET 5.2% resource wait, 75.4% signal wait, 15.3s max wait, 200s max signal
PAGEIOLATCH_SH 4.9% resource wait, 3% signal wait, 2.6s max wait, 7.9s max signal
CMEMTHREAD 0% resource wait, 1.8% signal wait, 46ms max wait, 4.7s max signal
LATCH_EX 0.3% resource wait, 1.5% signal wait, 3.9s max wait, 3.9s max signal
WRITELOG 0% resource wait, 0.8% signal wait, 875ms max wait, 2s max signal
PAGEIOLATCH_EX 0.5% resource wait, 0.5% signal wait, 2.5ms max wait, 453s max signal
At this stage, we think these deadlocks are introduced by physical resources. We are planning on reviewing SAN configuration, splitting LOG and DATA on different LUNs to address the fragmentation. Also, hyperthreading/ MAXDOP configuration due the signal pressure.
To make this interesting, prior to go-live we ramped a load test with 1000 virtual users; we barely never noticed any stress on the SQL server.
Any guidance is much appreciated....
September 28, 2009 at 4:47 am
Why do you think that fragmentation causes the dead lock? If you want I can give you a script that causes deadlock on 2 tables that each one of them resides on 1 page.
On small tables fragmentation is not really an issue and can’t always be corrected. You wrote that you have about 1000 records in the table. How many pages does the table have?
Can you use trace flag 1222 to capture the deadlock’s information and then post it? If you’ll do so there is a good chance that some of the frequent posters will be able to help you.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 28, 2009 at 6:08 am
I can only echo what has already been said, fragmentation is relative to the table size in some respects, if you have a small table, which is constantly being changed, some fragmentation is an accepted principal. if you want to post what dbcc showcontig shows, maybe that can give us something else to look at.
If a new application is causing the deadlocks to appear, what has your investigation found out and indicated is the issue.
If you troubleshoot for deadlocks and post the logs, we can help you more.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 29, 2009 at 7:20 am
1) this is the first of 3 blog posts that are the Bible for troubleshooting deadlocks. http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
2) If you are getting that much frag in a day then you almost certainly need to up your fill factor.
3) doing a PK based join could be BAD if it is a clustered PK (most common) and it is chosen simply because there are no other nonclustered indexes to help out.
4) deadlocks are often the result of bad programming where DML and SELECTs are unnecessarily done in different orders in different sprocs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply