VM Right-Sizing for SQL Servers?

Monitoring and reporting for Veeam Backup & Replication, VMware vSphere and Microsoft Hyper-V

VM Right-Sizing for SQL Servers?

Veeam Logoby jbarrow.viracoribt » Wed Dec 16, 2015 8:18 pm

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.

Image

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?

Image
jbarrow.viracoribt
Expert
 
Posts: 184
Liked: 18 times
Joined: Fri Feb 15, 2013 9:31 pm
Full Name: Jonathan Barrow

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby Vitaliy S. » Thu Dec 17, 2015 10:54 am

Hi Jonathan,

jbarrow.viracoribt wrote: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

jbarrow.viracoribt wrote: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!
Vitaliy S.
Veeam Software
 
Posts: 19566
Liked: 1104 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby Shestakov » Thu Dec 17, 2015 10:56 am

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!
Shestakov
Veeam Software
 
Posts: 4862
Liked: 396 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby jbarrow.viracoribt » Thu Dec 17, 2015 1:55 pm

I was using Memory Active for the report settings.
jbarrow.viracoribt
Expert
 
Posts: 184
Liked: 18 times
Joined: Fri Feb 15, 2013 9:31 pm
Full Name: Jonathan Barrow

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby Vitaliy S. » Thu Dec 17, 2015 3:56 pm

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.
Vitaliy S.
Veeam Software
 
Posts: 19566
Liked: 1104 times
Joined: Mon Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby skrause » Mon Dec 21, 2015 2:44 pm 1 person likes this post

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.)
Steve Krause
Veeam Certified Architect
skrause
Expert
 
Posts: 296
Liked: 45 times
Joined: Mon Dec 08, 2014 2:58 pm
Full Name: Steve Krause

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby Shestakov » Mon Dec 21, 2015 2:55 pm

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.
Shestakov
Veeam Software
 
Posts: 4862
Liked: 396 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby jbarrow.viracoribt » Thu Jan 07, 2016 3:59 pm 1 person likes this post

skrause wrote: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?
jbarrow.viracoribt
Expert
 
Posts: 184
Liked: 18 times
Joined: Fri Feb 15, 2013 9:31 pm
Full Name: Jonathan Barrow

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby Shestakov » Thu Jan 07, 2016 8:19 pm

Jonathan,
I just curious if you shrinked the SQL VM and how it works now.
Shestakov
Veeam Software
 
Posts: 4862
Liked: 396 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby jbarrow.viracoribt » Wed Jan 13, 2016 1:56 pm

Shestakov wrote: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.
jbarrow.viracoribt
Expert
 
Posts: 184
Liked: 18 times
Joined: Fri Feb 15, 2013 9:31 pm
Full Name: Jonathan Barrow

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby Shestakov » Wed Jan 13, 2016 2:07 pm

jbarrow.viracoribt wrote: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.

jbarrow.viracoribt wrote: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!
Shestakov
Veeam Software
 
Posts: 4862
Liked: 396 times
Joined: Wed May 21, 2014 11:03 am
Location: Saint Petersburg
Full Name: Nikita Shestakov

Re: VM Right-Sizing for SQL Servers?

Veeam Logoby skrause » Wed Jan 13, 2016 2:12 pm

jbarrow.viracoribt wrote:
skrause wrote: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?


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.
Steve Krause
Veeam Certified Architect
skrause
Expert
 
Posts: 296
Liked: 45 times
Joined: Mon Dec 08, 2014 2:58 pm
Full Name: Steve Krause


Return to Veeam ONE



Who is online

Users browsing this forum: No registered users and 5 guests