Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
I'm intrigued. I've not encountered anyone virtualising with a 1/1 guest to host ratio, how does this simplify administration? –  Mark Storey-Smith Nov 27 '12 at 22:49
    
@MarkStorey-Smith Well we use VMWare for all our other infrastructure. The server has a very limited maintenance window. If I need to do maintenance on the box I can just VMotion the guest onto another box. I've baselined everything and the performance penalty has been relatively marginal. The biggest problem is a noticeable rise cxpacket (parallelism issues) waits. I'm assuming from NUMA jumps. –  bumble_bee_tuna Nov 27 '12 at 22:55
2  
I have a couple questions/thoughts first: 1.) How much physical memory is on the physical host? How much is allocated to the guest? 2.) How much memory are you leaving above the 120GB for the OS? 3.) What else is running on the SQL instance? 4.) When the queries were slow - how were they slow. What signs and symptoms did you see? What wait types were you seeing? –  Mike Walsh Nov 28 '12 at 3:07
1  
Read this -- very timely. –  Jon Seigel Nov 28 '12 at 16:41
1  
I agree with Jon. THe more I look at this the more I think Memory reservations are the issue. I was thinking that if you were the only guest it wouldn't matter, but I think reserving is key. You might also look into some of the NUMA settings with VMWare. This blog post is an interesting read on that also - blogs.vmware.com/vsphere/2012/02/vspherenuma-loadbalancing.html –  Mike Walsh Nov 30 '12 at 2:16
show 12 more comments

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
+1 on the memory. If you only have 128GB and you are giving all of that to your guest, that isn't good. –  Mike Walsh Nov 30 '12 at 2:17
    
@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
add comment

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.