SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2000 memory recommendations


SQL 2000 memory recommendations

Author
Message
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
I wonder if anyone can eyeball the below setup and comment as to whether the below is still optimal we have done a P2V, and left all settings as they were implemented many years ago by an independent consultant.

RAID 10
6 processors
8GB RAM
SQL 2000 standard
Windows 200 Advanced
Dedicated SQL box - only AV is also installed.

Memory is fixed @ 6483
Set to use all 6 processors
Boost SQL Server priority is set
/3GB and /PAE are set in the boot.ini file
SQL server service - set to lock pages in memory

However SQL server appears to be only using 2GB RAM in task manager.

Any advice on making things run any better? I am getting substantially fewer timeouts since the P2V but would like to make things as optimal as I can with this setup.

Thanks for any thoughts
D
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211973 Visits: 46259
Don't use Task Manager to check SQL's memory, it displays incorrect values.

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


Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
Thanks man, will see what I can find in performance monitor!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211973 Visits: 46259
Oh, and turn boost priority OFF. It's a setting that should almost never be enabled

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


Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
GilaMonster (1/15/2014)
Oh, and turn boost priority OFF. It's a setting that should almost never be enabled


Will do Gail, would you mind explaining this?

Not being argumentative just wanting/trying to understand

Dave
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
Will reboot with this setting off and report back.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211973 Visits: 46259
Plateau (1/15/2014)
GilaMonster (1/15/2014)
Oh, and turn boost priority OFF. It's a setting that should almost never be enabled


Will do Gail, would you mind explaining this?

Not being argumentative just wanting/trying to understand


Books Online

priority boost Option

Use the priority boost option to specify whether Microsoft SQL Server should run at a higher Microsoft Windows 2000 or Windows 2003 scheduling priority than other processes on the same computer. If you set this option to 1, SQL Server runs at a priority base of 13 in the Windows 2000 or Windows Server 2003 scheduler. The default is 0, which is a priority base of 7.

Caution:
Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.


The setting has no benefit on a dedicated SQL Server machine as there's nothing else to compete for CPU time other than the OS and you don't want SQL taking priority over the OS.

Four dangerous settings that should not be messed with: http://www.brentozar.com/archive/2012/12/dont-touch-that-button-four-dangerous-settings-in-sql-server-video/

From Glenn Berry (http://sqlserverperformance.wordpress.com/2010/05/11/five-things-sql-server-should-drop/)
The “Boost SQL Server Priority” Instance Setting. This setting is like a shiny piece of candy that seems to attract many people. After all, who would not want to boost the priority of SQL Server and hopefully get better performance. Just like the Turbo button on ancient personal computers, right? It turns out that this setting does not help performance, and can actually destabilize the operating system, since the OS can be starved of CPU resources because of the priority given to the SQL Server process.


And, if that's not enough, a kb article (http://support.microsoft.com/kb/319942)
Based on actual support experience, you do not need to use priority boost for good performance. If you do use priority boost, it can interfere with smooth server functioning under some conditions and you should not use it except under very unusual circumstances. For example, Microsoft Product Support Services might use priority boost when they investigate a performance issue.

IMPORTANT Do not use priority boost for clustered servers that are running SQL Server 7.0 and later.


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


Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
Plateau (1/15/2014)
Thanks man, will see what I can find in performance monitor!


Hi,
for total server memory (KB) I am getting the following

Last: 1671008
Average: 1670883
Minimum: 1670856
Maximum: 1671648

I was hoping that at the least I would be utilising 3GB of memory.
Plateau
Plateau
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 73
Thanks for the notes above!

D
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211973 Visits: 46259
Probably means SQL doesn't need more. If you're not running load on it, it won't use more memory than it needs.

That said, that looks like 32-bit without AWE or /3GB. Is AWE enabled in SQL?

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search