Deadlock because of non cluster index

  • Could you please say that dead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Could you please suggest that is it correct way to remove index if dead lock is coming because of index?

  • keshab.basnet (6/11/2015)


    Could you please say that dead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Could you please suggest that is it correct way to remove index if dead lock is coming because of index?

    Quick question, could you please provide the full details, the table/index DDL, the query, actual execution plan and the deadlock graph/xml? Without the details, it's only guesswork.

    😎

  • Definitely need the deadlock graph. You can get it from the system_health extended events session

    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
  • Wild guess is that the index was not covering and you got the classic lookup deadlock SQL Server is (in)famous for.

    SNAPSHOT isolation is usually the cure for this desease.

    -- Gianluca Sartori

Viewing 4 posts - 1 through 3 (of 3 total)

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