Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have the following kit:

windows server 2008r2
Microsoft SQL Server 2008r2
Dual Quad Core Xeons
52GB Ram

The Ram usage on the server is currently at 98% which is fine, SQL server is happily taking what it needs. What I want to know is how would I gauge how much RAM it actually needs? How do I know when I need to add more or If I can possibly take some away? I have reduced the RAM usage down to 40GB and my disk write/reads are currently at:

Over 5 Minutes of DB Activity
Disk Reads/Sec : Average : 59
Disk Writes/Sec : Average : 95

is this the correct counter to be checking? Im happy with leaving the db server with 40GB of Ram, but I want to know if it still has too much - its a virtual environment, so I can use those resources elsewhere. '

thanks

share|improve this question
    
Those stats are for disk I/O not for memory I/O. –  Jeremy May 29 '12 at 13:54
    
Yes, because I want to know the impact on disk usage as I reduce the amount of Ram SQL is using. –  MWard May 29 '12 at 14:14
    
If you are using VMware, if the memory is not needed by the VM, it will be "ballooned" and reclaimed by VMware for other VMs to use. I know this does not answer your question but I thought it would be worth mentioning as the resources wouldn't actually go to waste if they are unneeded. –  Jeremy May 29 '12 at 14:34
add comment

1 Answer

up vote 1 down vote accepted

How do I know when I need to add more or If I can possibly take some away?

CHeck the statistics it publishes on performance counters (how long pages stay in memory, how often it reloads stuff from disc) and check the statistics yhow long it waits for read IO on the MDF files - that simple.

Over 5 Minutes of DB Activity Disk Reads/Sec : Average : 59 Disk Writes/Sec : Average : 95

As useless to gauge anything as the price of the food I ate for lunch today.

Get the Seconds/IO number, that has relevance. When this number increases then - well - your IO subsystem is overloaded. But then - you ahve to be carefull, as RAM is useless to cache transaction log writes for obvious reasons.

Im happy with leaving the db server with 40GB of Ram, but I want to know if it still has too much - its a virtual environment, so I can use those resources elsewhere. '

Pull down to 16gb, use common sense, then go up as IO starts to get slow. Sadly your IO is likely also virtualized so you can not use up the discs proper? Also we have no idea how large your database is (which kind of is an imporatn number) and how large your hot set is (the data activly in permanent use).

I would glo with: * 16gb, watching counters, adjusting as needed.

share|improve this answer
    
Thanks for the obnoxious reply - I am asking the question as I dont know what counters I should be using. The database is 140GB in size. There's also no way I would cut the Ram usage in half in a live environment. –  MWard May 29 '12 at 14:09
    
I can't imagine that measuring IO latency is the correct way to size an application's RAM/cache requirements. Yes, you want to know if the app is hitting the disk, but if your disk subsystem can keep up with a ridiculous amount of page outs, your latency will be just fine and the application will still not be properly configured. –  Jeremy May 29 '12 at 14:37
    
Well, obviously you do not know anything about database servers? SQL Server uses RAM primarily for CACHING DISC CONTENT ;) So, measuring when your disc cache turns too small for it's use and starts overloading the disc subsystem is pretty much the only paramter here. Yes, some stuff is used for query plans, connections etc., but on a 40gb mem usage, I would expect 30+ (more like 35+) gb to be nothing else than database cache, which is EXACTLY ther for ONE reason: to avoir disc IO. Only. –  TomTom May 29 '12 at 15:28
    
Now, you CAN ask "is it smart to tax the disc subsystem to the limit", but if you want to use resources efficiently - which is the context of this question, then yes, you want to use as little RAM as possible, which means taxing the disc system to (80% of or so, gleave some reerve) the limit. If RAM is not the problem, noone cares, but when reducing RAM is the target, then yes, that is valid. THAT SAID - if that is a shared disc subsystem, the fallout may be significant (slowing down otehr VM's disc access). –  TomTom May 29 '12 at 15:30
    
I understand what you are saying. However, if you want to avoid disk I/O then measure the # of disk ops. If you want to determine whether your disk can keep up with what you are asking of it, measure latency. Also, your comments to the OP and to me have come off as condescending, I suggest you revisit the tone of your post before submitting it. –  Jeremy May 29 '12 at 18:47
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.