3

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

MWard
  • 33
  • 3
  • 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

1 Answers1

1

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.

Jeremy
  • 938
  • 2
  • 7
  • 18
TomTom
  • 51,649
  • 7
  • 54
  • 136
  • 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
  • Yes and no - you will NEVER avoid IO totally. Also - depending on database use- that would leave to a LOT of space used because you basically totally throw away your IOPS budget - which, in some scerarios, is ridiculous, in some may mage sense (Especialyl virtualization - IOPS tend to be exactly the weak part of most virutalization platforms. And my tone is totally Ok. – TomTom May 29 '12 at 18:52
  • Ok, this is getting a little out of hand. Your tone is condescending, I asked a simple question and didn't expect to get belittled. I want to know what performance counters I need to use, to measure if SQL server is using the amount of RAM I have allocated, to its fullest. I presumed that measuring disk reads was a good indication of how much SQL was requiring my disk - I presumed that high disk reads indicated that it was requiring it more/less as I reduced the amount of RAM available to it. Specifically what performance counters should I use? – MWard May 30 '12 at 08:52
  • Depends - if you are not bound to limit disc IO (which in MANY virtualization scenarios is the bottleneck, so you if you tune hard there, you may hinder other VMs) then look at SECONDS/IO (which ahs 2 sub-counters for read and write). It should be SMALL (0.00x). You can reduce RAM until the number goes up - this says NOTHING about HOW MANY IO operations you produce, but it shows you (when the number goes up) when you start overloading the discs - and then you have to little RAM. Again, this may be BAD from the virtualization infrastructure ;) Excessive IO from your machine... – TomTom May 30 '12 at 09:11
  • I dont know why you just didn't write that in the first place. Thanks for the answer. – MWard May 30 '12 at 09:17