sql server 2005 sp1 is slow

Question

sql server 2005 sp1 is slow

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55539 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

george sibbald (7/19/2013)
as the message says it is an advanced setting
sp_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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 3

GilaMonster (7/22/2013)
george sibbald (7/19/2013)
as the message says it is an advanced setting
sp_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)

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

george sibbald (7/22/2013)
GilaMonster (7/22/2013)
george sibbald (7/19/2013)
as the message says it is an advanced setting
sp_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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grace09 SSCommitted Points: 1532 More actions · Answer 5

Thank you for the help, everyone. This is my first post here and feel grateful to get all your help.