Post reply

Trace Flag 2861

  • January 23, 2006 at 9:41 am

    #112350

    I just reinstalled a SQL Server (Standard Edition) on a test server the other day.  I also installed SP4 directly from RTM.  When I went to look at the SQL Server log I noticed that every hour there is an entry:  DBCC TRACEON 2861, server process ID (SPID) 51.

    I didn't do anything different on this machine, that I'm aware of, than I have done on any other machine and I don't see this anywhere else.

    Does anyone know why I am getting this?

    Thanks,

    hawg

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • January 23, 2006 at 10:25 am

    #616491

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

    Trace flag 2861 causes SQL Server to cache query plans for trivial queries that have a cost of zero or close to zero. SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of generating a new plan for such a simple query. Generally this design makes sense and efficiently manages compilation.

    However, some people like to use the fn_get_sql() function to see what queries are running on a server, but fn_get_sql() can't see queries associated with the zero-cost plans unless you enable trace flag 2861.

    *

    Anyone else working on the test-server?

  • January 23, 2006 at 10:28 am

    #616492

    No, no one else is working on the server.  This is a small server just for me for test some SQL Server stuff.

    I did some research before I posted so I saw a lot of this information but I can't figure out how it got there and why it is running every hour.  I also don't know how to get it stopped.

    Also, it is the same SPID almost every time (except once or twice) and it ran over the entire weekend when I know no one else was on the server. 

    thanks,

    hawg

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • January 23, 2006 at 10:55 am

    #616500

    Any scheduled jobs?

    If you know it happens every hour, you could set up a trace for it in sql profiler.

  • January 24, 2006 at 4:57 am

    #616620

    Some 3rd party monitoring tools set this trace flag such as Veritas Indepth for SQL Server. In the case of Indepth you can configure it to set the flag to off, which I would recommend since it can cause performance problems.

  • January 24, 2006 at 6:55 am

    #616647

    cmille19, I believe you are right.  While I was doing some work last night I found some remnants of a trial version of QuestCentral software I had installed on this server.  Apparently when I uninstalled my client piece I didn't get all of the server installation.

    The reason I was concerned is that I never saw that before.

    Anyway, I finally got the Quest software removed and this does not show up anymore.

    Thanks for the replies.

    hawg

    ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

  • January 7, 2012 at 7:36 am

    #1429996

    This is absolutely correct. I3 seems to turn on 2861 secretly when it's trying to retrieve the sql stmt.

    This traceflag 2861 can be nasty when server is under memory pressure, especially for clustered environment which can cause SQL to go down as cluster service check alive may fail due to lack of enough memory to process the query.

  • January 7, 2012 at 7:37 am

    #1429998

    Please note: 5 year old thread.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 24, 2012 at 9:41 am

    #1516904

    The current version of Confio Ignite will also do this.

  • July 27, 2012 at 9:14 am

    #1518581

    Wow, 5 yr old thread answers the question I have today, especially the confio ignite comment 3 days ago.

    Go figure.

    Thanks,

  • August 26, 2014 at 6:30 am

    #1740996

    digitalox (7/24/2012)

    The current version of Confio Ignite will also do this.

    True.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • August 26, 2014 at 6:33 am

    #1740997

    Jo Pattyn (1/23/2006)

    <A href="http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_042705.mspx">http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_042705.mspx</A&gt;

    Trace flag 2861 causes SQL Server to cache query plans for trivial queries that have a cost of zero or close to zero. SQL Server typically won't cache plans for these trivial queries because the cost of caching the plan is higher than the cost of generating a new plan for such a simple query. Generally this design makes sense and efficiently manages compilation.

    However, some people like to use the fn_get_sql() function to see what queries are running on a server, but fn_get_sql() can't see queries associated with the zero-cost plans unless you enable trace flag 2861.

    Thanx, that helped.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • August 26, 2014 at 6:34 am

    #1740998

    sqlpadawan_1 (7/27/2012)

    Wow, 5 yr old thread answers the question I have today, especially the confio ignite comment 3 days ago.

    Go figure.

    Thanks,

    Even after 8.5 Yrs. still usefull 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • September 18, 2020 at 1:29 pm

    #3789428

    14 years, thanks guys 🙂

  • July 16, 2021 at 9:36 pm

    #3911051

    I have SolarWinds DPA enable this trace flag on my SQL Server cluster.

    This info is still valuable to this day.

    Thank you.

Viewing 15 posts - 1 through 14 (of 14 total)

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