I have a big data application, so many insert/udate/select statement execute at a single time. we are have so many sp's, function's, view's and sometime other people execute SQL commands from Management studio.
Generally we takecare of SQL hints, but We know some old SP's/function's/view's/statemtns/jobs may not using SQL hints.
Now problem is:
We want to detect which SQL statement is causing deadlock. we know sp_who2 and other commands that tell us how to see which spid is causing deadlock, but specifically we want to know Statement that causing problem, also name of SP/View/Job/Function that is related.
For SQL Statement, executed manually, can be determined easily using information available in sp_who2.