locked
Trace Flag 253 RRS feed

  • Question

  • One of our new sql 2005 SP3 4053 pre-prod server has trace flag 253 setup by the app team,  their doc just says its to "prevent adhoc query plans to stay in cache", Internet or BOL search for this trace flag doesn't give much info.
    Has anyone worked with this before or has info?
    Also, Is there a place where all trace flags are mentioned with their uses, BOL only has a few listed?

    Thanks for replying!

    Monday, December 21, 2009 10:33 PM

Answers

  • Thanks all of you for replying & for your time. I am in process of checking with the app team about how the trace flag got initiaited. Will post if I get more info from them.

    I heard back from a member of the product team, and this is an undocumented trace flag that public information won't be available for.  As I mentioned in my last post, there are a number of reasons that trace flags such as this one aren't documented, the main one being that if used incorrectly they can cause more harm than good.  

    I was informed however, if it was implemented based on a CSS case that you opened with Microsoft, you can receive additional information and support by referencing that support case with CSS without charge.  If you have a Technical Account Manager (TAM) with Microsoft, they may be able to assist you in locating that case number.  If you can locate the case number, make sure you update your SQL Documentation to include the case number so that you, or someone else later on, don't have to repeat this exercise in the future.

    Even if you do learn exactly what this trace flag is used for, I'd caution against publishing that information online since it is undocumented by Microsoft for a reason and you open it up to being misused in the wrong scenarios.  I know that doesn't necessarily make sense but that is my two cents on the matter.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by SQLRocker Thursday, December 24, 2009 2:38 PM
    Thursday, December 24, 2009 1:34 AM

All replies

  • If this trace flag is not documented in BOL then this might be used by PSS team or might be suggested by MS support. If your company has raised any case with MS support you can verify the case clousure mail, you can get more information from there. If nothing is suggessted from Ms and it's set manually then probably you can remove it (test it before implementing in prod).

    Vidhya Sagar. Mark as Answer if it helps!
    Tuesday, December 22, 2009 2:02 PM
  • I too couldn't find any docs for this trace flag , may be this setting is set by a microsoft professional when dealing with a case or issue.
    So better discuss and then decide on playing around with this option . Normally everyone would like to have adhoc query compile plans to stay in buffer , but other way around here.

    Thanks, Leks
    Tuesday, December 22, 2009 5:32 PM
    Answerer
  • One of our new sql 2005 SP3 4053 pre-prod server has trace flag 253 setup by the app team,  their doc just says its to "prevent adhoc query plans to stay in cache", Internet or BOL search for this trace flag doesn't give much info.
    Has anyone worked with this before or has info?
    Also, Is there a place where all trace flags are mentioned with their uses, BOL only has a few listed?

    Thanks for replying!


    Most trace flags in SQL Server are undocumented, and there is no expectation that the product team is going to document them any time soon.  Trace flags cause the database engine to execute alternate code paths that may not have been as rigorously tested or have a specific purpose and may have side effects that are unpredictable.  Some of the trace flags like 1205, 1222 for deadlock graph capture to the errorlog are well known and documented.  These are generally accepted to be safe by the product teams.  There are others that were implemented for specific scenarios that are not documented and have other side effects, possibly the trace flag you ask about 235 which if it functions as your documentation internally says by preventing adhoc query plans from being cached would force higher compilation rates which isn't good and can be a performance problem itself (side effect to not caching).  Then there are other trace flags that are undocumented, even internally at Microsoft, because they were added for a specific development/testing purpose and the developer is no longer on the product team.

    Trace flags are somewhat like index and query hints in that they get used in one version of a product to solve a problem and persist in later versions where changes remove the need for the flag, or a newer construct can be used that is documented by Microsoft.  You'd probably be better setting PARAMETERIZATION FORCED in SQL 2005/2008 so that the engine forces parameterization of adhoc requests which allows for cache reuse, or specify a plan guide to cover the queries.  That being said, I have asked about this trace flag on the MVP newsgroup to see if I can find out some more information on it for you. 
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Proposed as answer by tosc Wednesday, December 23, 2009 8:05 PM
    Wednesday, December 23, 2009 3:00 PM
  • Thanks all of you for replying & for your time. I am in process of checking with the app team about how the trace flag got initiaited. Will post if I get more info from them.
    Wednesday, December 23, 2009 6:08 PM
  • Thanks all of you for replying & for your time. I am in process of checking with the app team about how the trace flag got initiaited. Will post if I get more info from them.

    I heard back from a member of the product team, and this is an undocumented trace flag that public information won't be available for.  As I mentioned in my last post, there are a number of reasons that trace flags such as this one aren't documented, the main one being that if used incorrectly they can cause more harm than good.  

    I was informed however, if it was implemented based on a CSS case that you opened with Microsoft, you can receive additional information and support by referencing that support case with CSS without charge.  If you have a Technical Account Manager (TAM) with Microsoft, they may be able to assist you in locating that case number.  If you can locate the case number, make sure you update your SQL Documentation to include the case number so that you, or someone else later on, don't have to repeat this exercise in the future.

    Even if you do learn exactly what this trace flag is used for, I'd caution against publishing that information online since it is undocumented by Microsoft for a reason and you open it up to being misused in the wrong scenarios.  I know that doesn't necessarily make sense but that is my two cents on the matter.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    • Marked as answer by SQLRocker Thursday, December 24, 2009 2:38 PM
    Thursday, December 24, 2009 1:34 AM
  • Thanks a lot Jonathan, lovely desciption here, Thanks everyone!
    Thursday, December 24, 2009 2:38 PM