Post reply

'ad hoc distributed queries'

  • January 17, 2014 at 11:21 am

    #288438

    hi, I have enabled 'ad hoc distributed queries' in my production sql server 2008 R2. For this change to take effect do i have to restart sql services?

  • January 17, 2014 at 11:52 am

    #1681202

    No. It takes effect immediately.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • January 17, 2014 at 2:09 pm

    #1681255

    what about to flushed the oolder values from DMVs? We got metrics from sys.dm_exec_cached_plans prior to enabling. We want to check in a couple of weeks again if actually this feature has help.

  • January 17, 2014 at 2:14 pm

    #1681256

    The 'ad hoc distributed queries' option has nothing to do with the plan cache. Setting it on enables the OPENROWSET and OPENDATASOURCE commands

    Were you thinking of 'optimise for ad-hoc queries' option?

    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

  • January 17, 2014 at 2:18 pm

    #1681260

    yes! i am sorry, i was referring to Optimize for ad-hoc workloads option.

  • January 17, 2014 at 2:27 pm

    #1681264

    It also takes effect immediately, no restart necessary.

    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

  • January 17, 2014 at 2:29 pm

    #1681266

    thank you guys!

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

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