May 12, 2014 at 8:04 am
I have an application on SQL 2008 R2 on VMware. We recently had a couple of outages that seemed to be memory related with numerous messages relating to big sorts. There are 8 vcpus on the server and 24GB of memory. My attention turned to the pagefile which is only 4GB. Since many DBAs consider best practice to make the pagefile 1.5 times the memory on the server, this looked like a smoking gun to me. Then I recalled some sessions at SQL Pass Summit on VMware that I vaguely remember saying that there is some VMware option that needs to be set in order to take full advantage of a big page file. Do I remember correctly? Anybody know what that is, so I can request it from our VMware people?
May 13, 2014 at 11:00 am
I see that this is a very complex issue involving issues of Memory "Ballooning" in the VMware environment and Power setting getting set on "balanced" or "high performance". It is discussed in this VMware doc (http://www.vmware.com/files/pdf/sql_server_best_practices_guide.pdf) but, of course since it is a VMware doc and not written by a DBA it begins every discussion with something like "Before you decide anything you must throroughly understand .....". Not too many DBAs are going to thoroughly understand how Ballooning works.
So what I'm hoping is that somebody who does know something about ballooning and SQL memory requirements in the virtual environment will have some Rules of Thumb and can dumb it down a bit. Does the consideration of Power Settings and Ballooning only impact memory usage, or does it also impact the use of a pagefile? If my server is dying because it's pagefile is inadequately sized, is making it bigger going to mean it will likely be used? Or do we need to mess with the VMware internals to make sure that it is used when the memory grows short?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply