Deadlocks Solution

  • Please see attached. Thank you.

  • Did you have a chance to look at my deadlock graphs?

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • Thank you for your advise.

  • 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