Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

'ad hoc distributed queries' Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 11:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
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?
Post #1532198
Posted Friday, January 17, 2014 11:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 2,963, Visits: 2,982
No. It takes effect immediately.

Regards,
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1532214
Posted Friday, January 17, 2014 2:09 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
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.
Post #1532276
Posted Friday, January 17, 2014 2:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #1532278
Posted Friday, January 17, 2014 2:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
yes! i am sorry, i was referring to Optimize for ad-hoc workloads option.
Post #1532282
Posted Friday, January 17, 2014 2:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
It also takes effect immediately, no restart necessary.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1532286
Posted Friday, January 17, 2014 2:29 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
thank you guys!
Post #1532289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse