June 11, 2015 at 12:11 am
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?
June 11, 2015 at 12:18 am
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.
😎
June 11, 2015 at 12:27 am
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
June 11, 2015 at 3:57 am
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