January 20, 2010 at 10:37 am
I need to monitor for a deadlock occurance. Which will have the least performance impact to the server, setting the trace flage 1222 or running a profiler trace for a deadlock graph?
January 20, 2010 at 11:45 am
the trace flag. I certainly cannot see it would ever have more impact than profiler. to minimise profiler impact you should run it as a server side trace.
the trace flag has the advantage of being easy to set up, and if the deadlocks are unpredictable as to when they occur you can just leave it running and be sure you will capture them.
It also comes down to whether you prefer the graphical representation or 1222 output. If the deadlocks are very frequent you could get away with profiler, soon as you get one, stop the trace.
---------------------------------------------------------------------
January 20, 2010 at 12:33 pm
The trace flag. I've never noticed an impact (though there will obviously be a slight one) and you don't have to worry about whether or not the trace is running. Add the traceflag as a startup parameter and monitor the error log from time to time.
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
January 20, 2010 at 1:02 pm
Exactly what I assumed. I needed some expert opinions to validate.
Thank you both for the timely answers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply