I have an application that is only used every other weekend and used concurrently by about 60 people. It is a 2005 SP3 environment. I am told that the application was very slow and users had trouble using it effectively. The problem got so bad they had to change the server to a new 16 core server and 16GB at the beginning of these year. Last week the users started to use the application again but the problem is still there and when I do some tracing and I have found that there is a Deadlock situation almost every 10-20 min. Application has no mechanism to trap the error that is thrown by sql server and resubmit the transaction. So users are confused as to where their data goes after they submit their data and the application is painfully slow. On the dead lock graph I captured almost always the dead lock is created when sessions are trying to get exclusive access on indexes and below is an example of one of the select query that is frequently involved:
(@p0 int,@p1 int,@p2 int,@p3 int) select coursecase0_.Id as Id45_, coursecase0_.Code as Code45_, coursecase0_.Description as Descript3_45_, coursecase0_.Status as Status45_, coursecase0_.Priority as Priority45_, coursecase0_.OrderNum as OrderNum45_, coursecase0_.KeyFindings as KeyFindi7_45_, coursecase0_.IsSAM as IsSAM45_, coursecase0_.DifficultyId as Difficul9_45_, coursecase0_.CourseId as CourseId45_ from CourseCase coursecase0_ where (coursecase0_.CourseId=@p0 ) and(coursecase0_.Priority=@p1 ) and(coursecase0_.Status=@p2 ) and(not exists(select casetestpa1_.Id from CaseTestPass casetestpa1_ where (casetestpa1_.CourseTestPassId=@p3 )and(casetestpa1_.CaseId=coursecase0_.Id )))
I informed the developers to look into their application but in the mean time I need ideas on how to mitigate the problem from my side. I need advise on what to do with indexes to avoid the dead locks or at least decrease the frequency. I would also appreciate it if you also point me to a document about minimizing deadlock by better indexing.