Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

sql server 2005 sp1 is slow Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 2:28 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
yes it would so you must set max memory in SQL (as I believe you have done at 2.25?)

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

Post #1474800
Posted Friday, July 19, 2013 9:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:40 PM
Points: 54, Visits: 1,282
Yes, I did set the max memory. Thank you very much for the clarification, George.

SQL Server is using around 1.6 GB of memory and CACHESTORE_SQLCP uses almost 0.8GB. Do you think the ad-hoc plan chaching is causing the internal memory issue and alsocause low PLE. I have SQL Server 2005 SP1. Thanks
Post #1475567
Posted Friday, July 19, 2013 1:53 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
buffer cache and proc cache are separate so I don't think it would affect PLE directly. Are you actually seeing any memory related error messages?

with such a large CACHESTORE_SQLCP you should test with sp_configure option 'optimise for ad-hoc workloads' turned on, this will reduce bloat.

Also SP1 is way out of date, you should patch to SP4, there may be improvements\fixes that will help


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

Post #1475679
Posted Friday, July 19, 2013 2:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:40 PM
Points: 54, Visits: 1,282
Can't find the option of 'optimize for ad hoc workloads', here is the error message
The configuration option 'optimize for ad hoc workloads' does not exist, or it may be an advanced option.
Post #1475682
Posted Friday, July 19, 2013 2:33 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
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


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

Post #1475686
Posted Monday, July 22, 2013 6:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,827, Visits: 8,479
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



Post #1475970
Posted Monday, July 22, 2013 6:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1475981
Posted Monday, July 22, 2013 7:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:08 AM
Points: 5,863, Visits: 12,941
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)


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

Post #1476020
Posted Monday, July 22, 2013 8:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1476024
Posted Tuesday, July 23, 2013 11:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:40 PM
Points: 54, Visits: 1,282
Thank you for the help, everyone. This is my first post here and feel grateful to get all your help.
Post #1476730
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse