SQL 2005
where do you set the parameter for optimize for ad-hoc workloads in SQL 2005?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
>>We can't do anything as such in 2005. <<
Right. It is available starting with SQL Server 2008.
Right. It is available starting with SQL Server 2008.
ASKER
>>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.
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.
ASKER
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.
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.
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.
We can't do anything as such in 2005.