Posted on Leave a comment

Trace Flag 2861

Some monitoring tools turn this on.


http://logicalread.solarwinds.com/why-dpa-uses-sql-server-trace-flag-2861-and-zero-cost-plans-tl01/#.VF5CMvnF9Ps

From Microsoft Technet:

SQL 8 – Cache query plans for queries that have a cost of zero or near to zero.

When turned on, fn_get_sql function can return the SQL text for activities that have zero cost plans kb 325607 

Tip: Avoid Using Trace Flag 2861 to Cache Zero-Cost Query Plans

http://social.technet.microsoft.com/wiki/contents/articles/13105.trace-flags-in-sql-server.aspx

http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_042705.mspx

Basically if you have a very very busy server, you should turn this off.  Otherwise, if it not making an appreciable difference, it can benefit some monitoring tools.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.