george sibbald (7/22/2013)
GilaMonster (7/22/2013)
george sibbald (7/19/2013)
as the message says it is an advanced settingsp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO
Optimise for ad-hoc was only added in SQL 2008. It's not there in SQL 2005.
I wonder then if alter database set parameterization forced would be of assistance here? (would need thorough testing)
I think a far better option would be to move to SP2 (or SP4) which reduced the max size that the plan cache could reach, move to 64 bit and add memory.
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