Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jon3k
Starting Member
49 Posts |
 Posted - 2008-06-12 : 12:21:38
|
| I've got a SQL Server 2000 standard server running a custom app database, and after reviewing these monitors:http://msdn.microsoft.com/en-us/library/aa905152(SQL.80).aspxI've found that:Process: Working Set (for sqlserver process): 100% (perfmon value of 1817153536 avg)SQL Server: Buffer Manager: Buffer Cache Hit Ratio: Over 95%SQL Server: Buffer Manager: Total Pages: Pegged at 100% (perfmon value of 206827 avg)SQL Server: Memory Manager: Total Server Memory (KB): pegged at 100% (perfmon value of 1662128 avg)From looking at those monitors, it looks to me like it might be time for me to move to Windows Server 2003 Enterprise with SQL Server 2000 Enterprise and increase the memory in the SQL server. Would you feel that's a pretty safe assumption? If that's true, then, how would I go about estimating the amount of memory that I should add? The server itself is a four socket HP ML570 G3 capable of (iirc) 64GB total system memory but it's my understanding that Windows Server 2003 Enterprise edition supports a maximum of only 32GB. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-06-12 : 13:31:39
|
| Total pages, and Total Server Memory are not measured in %. If you are looking for fluctuations, you should probably adjust the scale for those counters.To tell if you are having memory pressure, look at the following counters:memory : pages/secSQL Server Buffer Manager: Page Life expectancyPages/sec should be less than 20, unless you are copying files to and from the server a lot.Page Life Expectancy should be 300 or more.If you find you are having problems with memory, you can also check to see if you have queries running that are habitually doing table scans on medium to large tables. Getting these queries to use indexes (if possible) can have vastly better effects for performance. Also, if you are solely having memory problems, you should look into 64-bit SQL Server, instead of Enterprise Edition. |
 |
|
|
jon3k
Starting Member
49 Posts |
Posted - 2008-06-12 : 13:59:15
|
| Just ignore the scale then, refer to the actual values above, but thanks for pointing that out.Page life expectancy sits at appx 2150 average and pages per second is zero over the 5 minutes I looked at it. I'm going to configure a counter log and let it run for a few weeks.My understanding is that the memory limit for SQL server 2000 standard on windows 2003 server standard as a 2GB memory limit. It hovers around 1.8GB of memory in use.edit: nevermind, it's building the buffer cache, that explains itedit: thank you for your response, I appreciate the insight |
|
|
|
|
|
|
|
|
Time for SQL Server Enterprise?