Memory Reservations and virtualized MS SQL

[joviyach]

This is more of a curiosity than anything else, but I am wondering if my colleagues in the field are placing physical memory reservations on their virtualized MS SQL Servers, and if so, how much?

I have Google this, and I also have my own ideas, I just want to hear some answers from others on this, and compare notes.

[rcporto]

If you are already sure about the amount of memory to provisioning to VM, as a general vSphere best practices for SQL workloads put the memory reservation equals to amount of memory provisioned.

Check thsi guide for more info: http://www.vmware.com/files/pdf/solutions/SQL_Server_on_VMware-Best_Practices_Guide.pdf

[Duncan Epping]

joviyach wrote:

This is more of a curiosity than anything else, but I am wondering if my colleagues in the field are placing physical memory reservations on their virtualized MS SQL Servers, and if so, how much?

I have Google this, and I also have my own ideas, I just want to hear some answers from others on this, and compare notes.

Depends on the situation / environment. In some cases I have advised customers  to set a reservation. (Because they were slightly overcommitted and needed to guarantee resources) In other scenarios I advised against it as there was no need at all,

[JarryG]

Not sure about MS-SQL, but I have spent some time with other DB-servers and I'm telling you this: a good DB-server can utilize all the memory it gets allocated (and still avoid swapping if not absolutely necessary). So I always give DB-server as much memory as I can reserve for it, but not more, and definitely not over-commiting. That is much worse than running out of RAM and using swap, because DB-server is not aware of using disk-space (or ram-pages compression, de-duplication, etc.) instead of RAM, and keeps using it...

[joviyach]

I always found the Best Practices information interesting, because one performance tuning tip for MS SQL is that the box in general should have as much RAM as it would take to hold your most import/most accessed table entirely in memory. Following that recommendation, the amount of provisioned RAM can get quite large, and then if we follow the recommendation that we reserve all of it too... we're getting close to dedicating a host (or more than one if we're insuring that we have a host for this VM to vMotion to) to this MS SQL box. At that point, it seems to be sort of a toss up of whether you actually want a physical box instead.

In the past what I have done is to reserve 20% or more of the provisioned RAM. I get the 20% number pretty much by arbitrarily doubling the MS SQL recommendation for memory left out of Max Memory for the guest OS (90% for MS SQL; 10% for Windows). This has worked okay for me most of the time, though there have been occasions where more than 20% seemed to required to get/stay out of Swapping/Ballooning territory. Of course all of the above assumes over-commitment in the first place, etc...

IMHO, any number greater than zero can possibly be the right answer. I was just interested to see what others were doing.