Trace Flag 2861

  • hawg

    SSCertifiable

    Points: 6039

    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

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • Jo Pattyn

    SSC-Dedicated

    Points: 31417

    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?

  • hawg

    SSCertifiable

    Points: 6039

    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

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • Jo Pattyn

    SSC-Dedicated

    Points: 31417

    Any scheduled jobs?

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

  • cmille19

    SSCertifiable

    Points: 5950

    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.

  • hawg

    SSCertifiable

    Points: 6039

    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

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • xia.wu.1983

    SSC Enthusiast

    Points: 128

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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
  • digitalox

    SSCommitted

    Points: 1830

    The current version of Confio Ignite will also do this.

  • sqlpadawan_1

    SSCommitted

    Points: 1571

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

    Go figure.

    Thanks,

  • Hany Helmy

    SSChampion

    Points: 13488

    digitalox (7/24/2012)


    The current version of Confio Ignite will also do this.

    True.

  • Hany Helmy

    SSChampion

    Points: 13488

    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.

  • Hany Helmy

    SSChampion

    Points: 13488

    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 🙂

  • Frenchise

    Grasshopper

    Points: 20

    14 years, thanks guys 🙂

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

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