July 22, 2013 at 6:02 am
Did you recently migrate from 2000 ? I recall some slowness issues after migrating. In our case I think they were related to joins with converted data types or something .... Don't remember exactly.
I also wonder why you are still on SP1
July 22, 2013 at 6:28 am
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.
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
July 22, 2013 at 7:46 am
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)
---------------------------------------------------------------------
July 22, 2013 at 8:01 am
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
July 23, 2013 at 11:43 am
Thank you for the help, everyone. This is my first post here and feel grateful to get all your help.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply