5

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.

2

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.

http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx

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.

  • @cfradenburg I've actually tried this to no avail. It seems to no longer be applicable / have that effect in 2012. I think large pages is built into 2012 if you have 8+ gb of ram. Good call though. – bumble_bee_tuna Nov 30 '12 at 5:38
  • 1
    How much RAM is in the physical host? If it's not more that 128 I would see how much you need to set aside for VMWare and adjust accordingly. – cfradenburg Nov 30 '12 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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