How to resolve deadlock for MSSQL 2008

  • Hi all,

    I have issue by deadloack for MSSQL 2008

    How to resolve deadlock for MSSQL 2008 ?

    thanks,

    phuongnh

  • Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    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
  • It still deadlock occur.

    can resolve finish ?

  • Sorry, don't understand

    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
  • I ran the script "DBCC TRACEON(1222,-1)" but deadlock still appear

    Can resolve finish ?

  • nhphuong76 (9/21/2011)


    I ran the script "DBCC TRACEON(1222,-1)" but deadlock still appear

    Can resolve finish ?

    This trace flag will not resolve the deadlock prob. It'll write a graph in your error log. check it and post here.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • YOu need to analyze the deadlock graph which will be in the error log. Find out the victim if possible attach the graph here, we will help you out

    "More Green More Oxygen !! Plant a tree today"

  • nhphuong76 (9/21/2011)


    I ran the script "DBCC TRACEON(1222,-1)" but deadlock still appear

    Can resolve finish ?

    I never said the traceflag would fix the deadlocks, please read my post. I said

    That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    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
  • i have one exprience in my office!

    Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.

  • senfriends2008 (9/21/2011)


    i have one exprience in my office!

    Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.

    That's more likely luck than anything else.

    None of us can debug this without a lot more info.

  • Ninja's_RGR'us (9/21/2011)


    senfriends2008 (9/21/2011)


    i have one exprience in my office!

    Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.

    That's more likely luck than anything else.

    Agreed. What likely happened there was that the additional memory reduced the IO waits, so the queries are faster and less likely to deadlock. Give it a few months, queries get slower due to data volumes, deadlocks come right back.

    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 (9/21/2011)


    Ninja's_RGR'us (9/21/2011)


    senfriends2008 (9/21/2011)


    i have one exprience in my office!

    Daily five or six time deadlock accurse on production server. We optimize program coding also still getting deadlock. so resarch morethen a week Finally we found the reason memory usage is high. So increased memory 4GbB to 8GB , last five month server was running fine. We didn’t got any deadlock.

    That's more likely luck than anything else.

    Agreed. What likely happened there was that the additional memory reduced the IO waits, so the queries are faster and less likely to deadlock. Give it a few months, queries get slower due to data volumes, deadlocks come right back.

    Yup, or 4-5 other similar "non-fixes".

    Deadlock is mostly caused by bad code, that just doesn't fix itself with more ram.

  • Ninja's_RGR'us (9/21/2011)


    Deadlock is mostly caused by bad code, that just doesn't fix itself with more ram.

    Poor indexing's the other main cause.

    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 (9/21/2011)


    Ninja's_RGR'us (9/21/2011)


    Deadlock is mostly caused by bad code, that just doesn't fix itself with more ram.

    Poor indexing's the other main cause.

    So true, tho weirdly it's almost a synonym in my mind :-D.

  • A common cause that I have seen mentioned is bookmark lookups.

    Process A is reading data from a non-clustered index, but needs to get to the base table to get the rest of the data (bookmark lookup).

    At the same time, process B is updating the table and in the process needs to update the non-clustered index.

    Process A cannot acquire a shared lock (S) on the base table because process B has already acquired an exclusive (X) lock on it.

    Process B cannot acquire an X lock on the nonclustered index because Process A has already acquired an S lock on it.

    Deadlock!

    This may be rectified by defining the index with INCLUDE columns to prevent the lookup.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 15 (of 22 total)

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