none
Performance Impact of Deadlock trace 1222

Answers

  • I have never seen any performance implications of setting deadlock trace 1222, other than the fact that if there are too many deadlocks then it can bloat your error log,but thats what we want to capture.
    Regards, Ashwin Menon My Blog -> sqllearnings.wordpress.com
    • Marked as answer by Josh Ashwood Friday, March 18, 2011 3:41 AM
    Friday, March 18, 2011 1:37 AM
  • I concur with Ashwin. This trace flag comes into picture when there is a deadlock. If the number of deadlocks are more, the entries in the error log will increase.


    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Marked as answer by Josh Ashwood Friday, March 18, 2011 3:41 AM
    Friday, March 18, 2011 3:16 AM

All replies

  • I have never seen any performance implications of setting deadlock trace 1222, other than the fact that if there are too many deadlocks then it can bloat your error log,but thats what we want to capture.
    Regards, Ashwin Menon My Blog -> sqllearnings.wordpress.com
    • Marked as answer by Josh Ashwood Friday, March 18, 2011 3:41 AM
    Friday, March 18, 2011 1:37 AM
  • I concur with Ashwin. This trace flag comes into picture when there is a deadlock. If the number of deadlocks are more, the entries in the error log will increase.


    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    • Marked as answer by Josh Ashwood Friday, March 18, 2011 3:41 AM
    Friday, March 18, 2011 3:16 AM
  • Thanks for that guys.
    Friday, March 18, 2011 3:41 AM
  • The trace flag is excellent.  We ran this for about 1 month while diagnosing a deadlock and then post release analysis to confirm the issue had been resolved.  I saw no impact to production performance.

    While I agree that the trace flag has the potential to bloat the error log, as long as you are moving quick to resolve the issues, you should not have long term issues of a large error log file(s).

    Personally though, it's not a trace I would leave on once I have resolve the issue.  Our application logs capture the deadlock error so we can always flip the flag back on if we see other deadlock errros in the future.


    Clive
    www.sqlsvrdba.com
    Monday, October 17, 2011 9:43 AM