Monitoring and reporting for Veeam Backup & Replication, VMware vSphere and Microsoft Hyper-V
Post Reply
jbarrow.viracoribt
Expert
Posts: 184
Liked: 18 times
Joined: Feb 15, 2013 9:31 pm
Full Name: Jonathan Barrow
Contact:

VM Right-Sizing for SQL Servers?

Post by jbarrow.viracoribt » 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

Vitaliy S.
Product Manager
Posts: 22747
Liked: 1518 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by Vitaliy S. » 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!

Shestakov
Veeam Software
Posts: 6830
Liked: 696 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by Shestakov » 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!

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

Re: VM Right-Sizing for SQL Servers?

Post by jbarrow.viracoribt » Dec 17, 2015 1:55 pm

I was using Memory Active for the report settings.

Vitaliy S.
Product Manager
Posts: 22747
Liked: 1518 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by Vitaliy S. » 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.

skrause
Expert
Posts: 431
Liked: 90 times
Joined: Dec 08, 2014 2:58 pm
Full Name: Steve Krause
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by skrause » 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

Shestakov
Veeam Software
Posts: 6830
Liked: 696 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by Shestakov » 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.

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

Re: VM Right-Sizing for SQL Servers?

Post by jbarrow.viracoribt » 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?

Shestakov
Veeam Software
Posts: 6830
Liked: 696 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by Shestakov » Jan 07, 2016 8:19 pm

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

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

Re: VM Right-Sizing for SQL Servers?

Post by jbarrow.viracoribt » 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.

Shestakov
Veeam Software
Posts: 6830
Liked: 696 times
Joined: May 21, 2014 11:03 am
Full Name: Nikita Shestakov
Location: Prague
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by Shestakov » 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!

skrause
Expert
Posts: 431
Liked: 90 times
Joined: Dec 08, 2014 2:58 pm
Full Name: Steve Krause
Contact:

Re: VM Right-Sizing for SQL Servers?

Post by skrause » Jan 13, 2016 2:12 pm

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

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest