Trace flags vs SQL Server performance

  • Hi,

    We enabled the below Trace flags as suggested by MS to investigate a sql dump last year when we are on SQL Server 2005 with SP2 and Max memory was NOT set. Later, we applied SP3 & set Max memory and we never get that error again. But the trace flags enabled were still there as startup parameters.

    T 815

    -T 818

    -T 2551

    I’m wondering whether enabling these trace flags will impact SQL Server performance?

    Thanks

  • Mani-584606 (3/27/2010)


    Hi,

    We enabled the below Trace flags as suggested by MS to investigate a sql dump last year when we are on SQL Server 2005 with SP2 and Max memory was NOT set. Later, we applied SP3 & set Max memory and we never get that error again. But the trace flags enabled were still there as startup parameters.

    T 815

    -T 818

    -T 2551

    I’m wondering whether enabling these trace flags will impact SQL Server performance?

    Thanks

    815 enables memory latch enforcement

    818 helps detect stale reads

    2551 is used to produce a full-filtered memory dump instead of a minidump

    815 (particularly) and 818 will both affect performance. You should remove them. See SQL Server I/O Basics for full details.

    2551 is only needed to provide extra information to CSS in the event of a handled exception inside SQL Server. It can be safely removed.

  • 815 (particularly) and 818 will both affect performance. You should remove them. See SQL Server I/O Basics for full details

    I went through the document but I did not find how these trace flags effects performance and how much impact it will have on performance?

    thanks

  • Mani-584606 (3/28/2010)


    815 (particularly) and 818 will both affect performance. You should remove them. See SQL Server I/O Basics for full details

    I went through the document but I did not find how these trace flags effects performance and how much impact it will have on performance?

    There are several references like the following:

    The [815] trace flag should only be used under the direction of Microsoft SQL Server Support as it can have significant performance ramifications

    All three flags are intended for trouble-shooting - once the problem has been resolved it is recommended that you turn them off again.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply