Post reply

Cache Plan Adhoc workloads

  • October 31, 2013 at 9:14 am

    #299824

    Anybody ever experienced any negative effects from turning on.

    sp_CONFIGURE 'show advanced options',1

    RECONFIGURE

    GO

    sp_CONFIGURE 'optimize for ad hoc workloads',1

    RECONFIGURE

    GO

    Thinking about putting this tweak into production.

  • October 31, 2013 at 10:06 am

    #1663155

    Read through this below article and decide for your self.

    http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

    http://technet.microsoft.com/en-us/library/cc645587.aspx

  • October 31, 2013 at 10:19 am

    #1663164

    Thanks I read through a post by Dave Pinal, nowhere have I read about a con for turning this on, only pros.

    If there is no negative why is this not common knowledge.

    I'm looking for a gotcha but there may not be one.

  • October 31, 2013 at 10:27 am

    #1663172

    It's a pretty safe setting. Worst it can do is slightly increase the number of compiles if you happen to have ad-hoc queries that do get sent multiple times (identically)

    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

  • October 31, 2013 at 10:33 am

    #1663179

    Simple as setting back to 0 to turn off with no damage done if there are any performance issues.

  • October 31, 2013 at 10:55 am

    #1663189

    Exactly.

    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

  • November 1, 2013 at 3:02 am

    #1663364

    Thanks, wasn't a tricky one just needed confirmation.

Viewing 7 posts - 1 through 6 (of 6 total)

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