June 15, 2011 at 3:40 pm
Please see attached. Thank you.
June 16, 2011 at 11:13 am
Did you have a chance to look at my deadlock graphs?
June 16, 2011 at 11:22 am
mya (6/16/2011)
Did you have a chance to look at my deadlock graphs?
Yup but Gail'S the expert so I'll let her answer this.
June 16, 2011 at 11:22 am
I haven't, I'm busy with deadlock graphs and a performance intervention for a paying client. Maybe someone else has some time (deadlock problems aren't quick)
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
June 17, 2011 at 6:37 am
GilaMonster (6/16/2011)
I haven't, I'm busy with deadlock graphs and a performance intervention for a paying client. Maybe someone else has some time (deadlock problems aren't quick)
Nope, they sure aren't. My STRONG recommendation is to get professional help to resolve these issues. You could hunt-and-peck on a forum literally for days before stumbling across a fix.
If you want to go it yourself, here are some quick points:
1) you have a big aggregate with distinct in it. Gonna be costly. Maybe nothing we can do about that.
2) you have MANY functions (such as DATEPART and ISNULL) wrapped around table columns in WHERE clauses. This is a HUGE no-no. Get rid of those and your problem may well go away.
3) But go away assumes you have adequate indexing - which I have no idea about since I have no table/index definitions. Proper indexing can be a magic bullet for deadlock problems.
4) Go away also assumes you put OPTION (RECOMPILE) around these queries since they have a datestart and dateend parameters. The plan for a 1 day range is VASTLY different from a 1000 day range and you will get screwed in multiple ways by that plan caching. This is known as parameter sniffing.
5) See here and the associated 2 posts for some help on deadlock resolution if you want to go it your own: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 17, 2011 at 7:37 am
Thank you for your advise.
June 20, 2011 at 9:52 am
are the commands just simple update statements that are being executed causing the deadlocks in the dl_7.xdl file?
do you have the full query batch around the deadlocking area?
the deadlock image yellow boxes just specify a simple update, but its locked the page, have you tried the update hint with updlock or rowlock
e.g. update table1 with (updlock) set col1 = value1 where col2 = value2
on dl_12 the text for spid 71 wasn't recorded but spid 75 was, and this is doing an insert who has locked the whole key, i would try a paglock to try and lock the page it needs instead of the whole key.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply