SQL Server Buffer Pool Utilization under VMWare

Question

I recently migrated a physical SQL Server box (2012) over to ESX (5.1) simply for ease of administration. It is and will be the only guest on the server. As expected there was a marginal performance hit that for the most part is only noticeable on startup. Here in lies my issue.

I have noticed that under virtualization the buffer pool takes memory much much much slower then its physical counterpart.

Under the physical install within 2 days running SQL server had allocated all 120gb of memory allocated (max server memory). However, with practically the exact same setting running in the hypervisor (I have also given the guest full reservation of the memory) it has taken 7 weeks to reach 50gb. This was also painfully slow something like a Gb rise a day, this translated into a few days of slow queries.

So while I have seen this on multi guest boxes and attributed it to memory pressure, I am confused at why it is happening on a single guest box. I know vmware will compress and dedupe memory, however most of this is unique data.

So my question is:

1) Why exactly is this happening ? I'm interested in the exact mechanism that is causing this.

2) Is there a method to start SQL server with is full memory allocation ? I remember something like a traceflag for this but havent been able to find anything for 64bit. And is this necessary ?

Thanks in advance.

---

Another thing I noticed in the first few days after a restart is that the PLE stays very low in the 500 - 900 range this increases as the buffer pool grows.

Answer 1

Trace flag 834 can allow the maximum amount of memory to be allocated at startup for x64 machines. The following blog has more detail but here are the basics:

Enterprise Edition needs to be installed 8GB of RAM or more needs to be present Lock Pages in Memory needs to be on.

Link

Read the full article as there are caveats and warning spread throughout as this can lead to a much longer startup time (or failed starts). A big one being that it needs to allocate a contiguous chunk of memory and I'm not sure how that will work on VMWare. Also, if the physical memory in the host is 128GB I would reduce max server memory some more to make sure there's enough space left for VMWare to do it's thing.