We have a front end which fires an sp into an OLTP to cache some static data. However we have see it get deadlocked ever so often. The condition is often difficult to re create ..Is it possible to find which query in the sp is causing the dead lock ?
(1)run sp_who , see spid of blk column
(2)see the related object name in master..sysprocess of the spid
(3)use sp_object_stats to verify dead lock information
(4)identify any long running transactions and divide it in shorter one