VM Right-Sizing for SQL Servers?

[jbarrow.viracoribt]

Question, we have a good amount of SQL servers in our environment that do light workloads. We've recently started to use Veeam One's Oversized VM report to right-size our VM's based on workload but we're unsure what to do when it comes to SQL and Windows in some regard.

For instance, let's look at this server, S-SL-TES-SQL-1. Previously it had 8 vCPU's and 20GB of RAM assigned to it. Based on 30 day stats, Veeam One suggested we changed to 1 vCPU and 4GB of RAM. The vCPU I can understand the the RAM gets me here. The VM is running Server 2008 R2 which has a minimum RAM requirement of 512MB. Then we have SQL 2008 R2 installed which has a minimum RAM requirement of 1GB.



Here is what I struggle with RAM wise. Do we size the VM's based off the Oversized VM report, or do we honor application provider minimum requirements if Veeam One suggests settings that are below those minimums? For instance, I have another SQL server it suggests 1GB of RAM on. 1GB of RAM is less than the minimums for the OS (512MB) and SQL (1GB) combined. Do we ignore the app minimums in this case or use those as our minimums and not go below those regardless of what Veeam One suggests?

As a follow-up, how would you configure min and max memory settings in SQL if you went this low?

[Vitaliy S.]

Hi Jonathan,

Do we size the VM's based off the Oversized VM report, or do we honor application provider minimum requirements if Veeam One suggests settings that are below those minimums? For instance, I have another SQL server it suggests 1GB of RAM on. 1GB of RAM is less than the minimums for the OS (512MB) and SQL (1GB) combined. Do we ignore the app minimums in this case or use those as our minimums and not go below those regardless of what Veeam One suggests?

What performance metric were you using in the over-sized VMs report? Memory active or consumed? In any case, please be aware that SQL Server will always consume all the memory, this is how application is structured. For SQL Server right-sizing I would pay attention to the report showing average value for consumed and active memory. Once you have this data, check out in-guest memory usage to correlate it with the output of Veeam ONE reports.

Here is an existing topic for further reading > undersized and oversized vm reports

As a follow-up, how would you configure min and max memory settings in SQL if you went this low?

That's a good question. Do yo have any other applications installed in this VM? Why not to allow SQL Server consume all the memory it needs?

Thanks!

[Shestakov]

Hello,
I would not go against the minimum applications` requirements. However, in a case of your SQL VMs(first one), looks like the Veeam ONE recommendation is above minimal requirements.
For the second case I would suggest having 1.5 GB assigned to feel comfortable.
Thanks!

[jbarrow.viracoribt]

I was using Memory Active for the report settings.

[Vitaliy S.]

Ok, what values does the report show when selecting consumed memory performance metric? It is expected to have low values of active memory for a VM, which doesn't operate 24*7.

[skrause]

From my experience, running SQL server on a single core is a bad idea. Also, for all editions above Express 4GB is the recommended minimum RAM (1GB being the absolute minimum.)

[Shestakov]

Steve, in general you are correct, but in a case SQL server is barely used and you have a lack of resources, you can go below average recommendations.

I would check vCPU/lCPU ratio in the host, if it`s lower than 8 you can fearlessly add another vCPU and if you use dynamic memory(HV), you can just pull down the minimal memory allocation to 1GB leaving 4GB max.

[jbarrow.viracoribt]

From my experience, running SQL server on a single core is a bad idea. Also, for all editions above Express 4GB is the recommended minimum RAM (1GB being the absolute minimum.)

Why is it a bad idea? Did you gather stats from VMware or Veeam One on these servers to show that it was performing slowly with the 1 core?

[Shestakov]

Jonathan,
I just curious if you shrinked the SQL VM and how it works now.

[jbarrow.viracoribt]

Jonathan,
I just curious if you shrinked the SQL VM and how it works now.

Not yet. I still don't fully trust these numbers.

For instance. I had one VM that I set to the recommended levels suggested by this report. Then once I was RDP'd into that box, doing regular administrative tasks, I felt it was just slower than normal. Menus and screens in the app seemed to take much longer to pull up than normal. Increased RAM and CPU and things came back to life even though the suggestions were higher than this report shows.

So, while many seem to say don't trust the CPU and RAM measurements in Windows, I don't fully buy that yet. It seems when the Windows CPU and RAM sensors show pressure, the VM runs like poo. When they don't show pressure, things run smoothly. This contradicts what I seem to see in VMware and via this report.

I still have some research to do but I wanted to share what I've experienced so far.

[Shestakov]

Then once I was RDP'd into that box, doing regular administrative tasks, I felt it was just slower than normal.

RDP session + your activity may give an additional load.

It seems when the Windows CPU and RAM sensors show pressure, the VM runs like poo. When they don't show pressure, things run smoothly.

Simply talking that`s correct, but you can give 10x resources to the VM and definitely have it running fast. The goal of the report is to keep VMs` CPU and Memory Usage around 80-90% to have the VMs running smoothly, but without excessive resources.

If you think the report suggestions are too aggressive, I would suggest to add a small buffer to feel assure.
Thanks!

[skrause]

Why is it a bad idea? Did you gather stats from VMware or Veeam One on these servers to show that it was performing slowly with the 1 core?

I did not need Veeam or VMware to tell me that performance sucked, trying to use the actual SQL server to do anything was all the evidence I needed. Even trying to install new roles/features or install windows updates on a machine with only 1 CPU core takes about 5 times as long as it does with 2. The overhead of running the Windows GUI is enough that tasks that usually take 70-80% of CPU will peg the machine and make it nigh unusable. Anything that runs SQL or ASP.NET on IIS I go with a minimum of 2 CPU cores as a rule of thumb. Low use file servers that are not running DFS or Windows Deduplication I will typically leave at 1.