[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

SQL 2005

where do you set the parameter for optimize for ad-hoc workloads in SQL 2005?
0
dastaub
Asked:
dastaub
  • 3
  • 2
  • 2
1 Solution
 
TempDBACommented:
First run the below statement to make all the properties visible

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO

Then run the following query to optimize it.
EXEC sp_configure 'optimize for ad hoc workloads',1
GO
RECONFIGURE WITH OVERRIDE
GO
0
 
TempDBACommented:
oops... you said 2005. Sorry I missed it.
We can't do anything as such in 2005.
0
 
dastaubAuthor Commented:
I RAN IT IN SQL 2008 R2 AND GOT THIS IN RESPONSE

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
>>We can't do anything as such in 2005. <<
Right.  It is available starting with SQL Server 2008.
0
 
dastaubAuthor Commented:
>>We can't do anything as such in 2005. <<
Right.  It is available starting with SQL Server 2008.

I ran it in Server 2008 and got the response
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
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.
0
 
dastaubAuthor Commented:
when i ran it in SQL 2008, i got:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.

when i ran it in 2008, but in a 2005 instance, i got:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
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.
0
 
Anthony PerkinsCommented:
And again in case it was not clear the first two times:  That option is only available starting with SQL Server 2008.  It does matter what SSMS version you are using, if the result of SELECT @@VERSION < 10.0 then you are simply out of luck you cannot use it.  Period.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now