March 14, 2007 at 1:09 pm
I have a SQL Server 2000 (Enterprise Edition) database running on a Windows 2000 server with 8GB RAM. The database is about 60GB and the SQL Server Cache Hit Ratio is almost always above 99%. I am thinking of upgrading the server to 2003 Advanced Server so that I can add more RAM. Will extra RAM help SELECT statements run faster if the needed data is already in cache?
Mark
March 14, 2007 at 8:45 pm
I would not expect any significant change in performance because of any additional RAM being available. Yes, your cache hit ratio might improve because there it is more likely that data will already be cached but I would expect that no one will notive much different..
I am not sure whether you will get any changes because of the different operating systems though. I would like to think that Win 2003 does a better job than Win 2000 - does anyone have any information on this ?
March 15, 2007 at 3:41 am
Adding more RAM won't help at all, nor changing OS will. Hache hit ratio at 99% indicates that all needed data are usually in cache - that means you have enough memory. You should test another indicators like processor time user/privilleged , disk queue lenght etc. If you think about "select" improvements you should test particular selects - what tables do they hit , are there indexes for these tables, how does query plan look alike, are these tables frequently locked by other statements. In general there is a lot of performance factors and a lot of possible solutions. If you present more data about your case I would be more precise
March 15, 2007 at 6:41 am
"....SQL Server Cache Hit Ratio is almost always above 99%....."
Adding additional RAM might get rid of the "almost" part. I would look at the PLE (Page Life Expectancy) counter over a period of time. According to Microsoft, if this dips below 300, then you can benefit from more RAM.
Now, this 300 figure is a very general threshold and doesn't apply to every situation. Similarly, accepting the value of 99% on the Cache Hit Ratio as a clear indication of having enough RAM may be generally safe, but not always. I would shoot for higher, as every bit below 100% means a trip to the disks.
On one production system that I deal with, the Cache Hit Ratio averages 99.87 over a typical 5 minute period, but there is one time a day, every day where that tanks, along with the PLE counter. I know exactly why that is, and what I need to do to fix it, but since it happens when no people are on the system, it's not on the top of the list of things to fix.
March 15, 2007 at 7:46 am
It's unlikely to improve your exellent cache ratio, if the page life expectancy is reasonable (as Jeff Gray mentions), that's no reason to do it.
I will give you reasons to do it anyway. I will assume your data may grow, your user base may grow, and applications may be added.
1. If your company has budgeted for this, do it. If you need it a year from now, who knows if the budget will be there?
2. Check your disk queuing. Its probably fine, but if the disk queues are longer than you like, adding memory can be a big help.
It does not sound like you need more memory NOW. But moving to a system where you could add it in an emergency may be a good idea.
roger reid
Roger L Reid
March 15, 2007 at 8:07 am
I agree with most of what has been said here. I would like to reiterate what Adam has said. Check indexes, index fragmentation, updating statistics etc... It could possibly be a disk problem also. Your money might be better spent on new storage in order to get more spindals so that you can spread the I/O across more disks and reduce the disk read queues. Run a perfmon trace and gather the most obvious counters such as % Processor Time, Memory Pages/sec, Physical Disk disk queue length - read/write, SQL Server Full Scans/sec, Average wait time... When in doubt try tuning the sql, eliminate unnecessary joins, use left outer joins where possible... Good luck.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply