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

optimize for ad hoc workload Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 11:30 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
Could someone please help me?
When trying to configure "optimize for ad hoc workload" in a 2005 Developer edition , getting below error.

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5808, Level 16, State 1, Line 2
Ad hoc update to system catalogs is not supported.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'optimize for ad hoc workloads' does not exist, or it may be an advanced option.
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.
Post #1397566
Posted Tuesday, December 18, 2012 2:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:13 PM
Points: 39,866, Visits: 36,208
The optimise for ad-hoc workloads option does not exist in SQL 2005. It was added in SQL 2008.

p.s. you may want to change 'allow updates' back to 0 if you want to get rid if that 'adhoc updates to the system catalogs are not allowed' message every time you try to reconfigure.



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 #1397608
Posted Wednesday, December 19, 2012 2:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
We were examining plan cache size. Actually cache size of most of the adhoc queries are more than 40KB. We are working to convert them either parameterized or SPs.

Thanks Gail.

Post #1398241
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse