Post reply

Parallel Thread Deadlocks Help?

  • March 26, 2013 at 5:10 am

    #272492

    Trace Flag 1222 enabled

    Profiler Captured

    Database Tuning Adviser

    I’m believe I’m getting “Intra-Query Parallel Thread Deadlocks” and not really sure how to go about fixing the problem.

    I know the problems are coming from a management tool database that we use in IT to remote and patch machines etc..This particular database has regular integrity checks as well as index rebuilds weekly

    I have captured the deadlock using Profiler then imported into the database Tuning Adviser. It's recommendations were to create various indexes which I applied but this didn’t make any different to the problem I’m getting.

    Here is the deadlock info I’m getting but not quite sure how to troubleshoot. Any help would be much appreciated.

  • March 26, 2013 at 5:49 am

    #1600727

    I think this query is problem

    update CVResults set LastScanDate = @P1<c/> Platform_Idn = @P2<c/> results = @P3 WHERE CVResults_Idn = @P4

    look at this query execution plan is it make index seek and then

    UK_LDMS90.dbo.CVResults indexname=XPKCVResults look at this index proporties row lock and page lock is enable

  • March 26, 2013 at 7:22 am

    #1600778

    Hi,

    Row lock and page lock are enabled on this index. I just noticed the index fragmentation was at 93%. I re-organised the index and fragmentation is down to 12.5%.

    Do you think re-creating the index might help?

  • March 26, 2013 at 7:23 am

    #1600780

    Maybe but look at this query execution plan. fragmentation is affect query performance

  • March 27, 2013 at 8:37 am

    #1601184

    http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

    Note there are 2 additional parts to this blog series.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • March 27, 2013 at 8:38 am

    #1601186

    also see this:

    http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 4, 2013 at 9:48 am

    #1603615

    Just a heads up. I finally managed to get the problem fixed. It turned out a Service pack needed applying. Completely fixed my deadlock issues.

    The version causing problems was 10.50.2500

    I upgraded this to 10.50.4000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply