John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Your Azure SQL Virtual Machine might be too big

19th January 2021 By John McCormack Leave a Comment

Cost optimisation

This is post #4 in my series on 7 ways for data teams to save money in Azure. Cost optimisation is crucial to any organisation that operates in the cloud, as costs can and do run away from away you without regular attention.

To avoid repetition of my my post regarding the size of your Azure SQL DBs, please read that to understand the concepts of elasticity, scalability and CAPEX vs OPEX.

One great thing about the cloud is that you can change your mind, or adjust as you go along. Unlike days gone by where you would have a bought a giant SQL Server and ran about 10% utilisation (just to allow room for future growth), you can be much smarter now and only pay for what you need.

Is my instance oversized?

You should be collecting metrics on CPU and memory, as well as looking at your throughput and disk latencies to see how well you are sized. If your CPU is always below 50%, including spikes and you haven’t identified memory pressure, you can look at scaling down. In an ideal world, you should run load tests against the old and new instance types, and compare them before making a decision. Cutting CPUs will give you the biggest financial saving in Azure due to the way that licensing works.

Is my instance undersized?

If your CPU is regularly pinned above 85%, you need to take action. Scaling up is one option, improving your queries is another. Some people will choose to scale up and then plan fix their queries, with the ambition to scale back down again later. In my experience, this rarely happens and they just accept the new instance size as normal. Other work becomes the priority. If you can hold off scaling your instance to give you time to fix your expensive queries first, I would recommend it.

Try not to rush a decision, there are so many instance types and sizes to choose from, each one optimised for a different use case. Do your research and choose the most appropriate instance type, not just next the one up.

I do need more compute power, what’s next?

SQL Server is licensed by the core. The more cores, the higher your licensing costs. However although instance classes tend to double cores and memory as you step up to the next level, there is a smarter way to do it. If you have determined that you don’t need more CPU but you do need more memory, look into the memory optimised instances such as the E-Series.

Looking at the table below, imagine you are on a D16 V4 which provides 64 GB of RAM, but you need to increase your RAM to 128 GB. If you choose the next highest D-Series machine (D32 V4), you double your costs. You might get the extra cores, and they will also help performance, but the expense is unjustified.

Consider instead changing to an E-Series (E16s V4) where you can keep your cores at 16, meaning no change to Windows or SQL licensing costs but you also get the desired 128 GB of RAM. You only pay £158 more per month. *

Instance TypeCPUsMemory (RAM)Monthly cost
D16 V41664 GBCompute £505.45
OS (Windows) £400.44
SQL Licence £3,264.45
Total £4,170.34
D32 V432128 GBCompute £1011.44
OS (Windows) £800.88
SQL Licence £6,528.91
Total £8,341.22
E16s V416128 GBCompute £663.77
OS (Windows) £400.44
SQL Licence £3,264.45
Total £4,328.67
* Prices taken from Azure Calculator on 18/1/2021. Subject to change

Constrained CPU Instances

For memory intensive applications such as SQL Server, constrained CPU instances provide another tool in the battle for cost optimisation.

Say you actually needed 256 GB of RAM but you would still be ok with 16 cores. Sticking with the D-Series, you would need to go up to 64 cores to get the desired memory. You can take the idea of memory optimisation a step further with constrained CPU instances. With these, you get the extra CPUs but they are not available to you. This allows an instance with even more RAM than the memory optimised instances. This means that whilst you have increased compute costs, you don’t have increased license costs as you only pay licenses based on available CPUs. This is a significant saving as you move into higher instance tiers.

Instance TypeCPUsMemory (RAM)Monthly cost
D64s V464256 GBCompute £2,022.33
OS (Windows) £1,601.76
SQL Licence £13,057.81
Total £16,681.90
E32 16s V416 available256 GBCompute £1,327.00
OS (Windows) £800.88
SQL Licence £3,264.45
Total £5,392.33
* Prices taken from Azure Calculator on 18/1/2021. Subject to change

Cost optimisation summary

Don’t provision for what you will need in the future, size your instance for what you need now and keep on top of it. Changing is quick and easy. Be aware of non standard instance types like memory optimised and Constrained CPU to maximise savings against your SQL licensing costs.

Play about with the Azure pricing calculator in order to compare instance types.

IT Certification Category (English)728x90

Share this:

  • Tweet
  • Email

Related

Filed Under: cost-optimization, front-page Tagged With: azure, azure iaas, cost optimisation, cost optimization

About John McCormack

John McCormack is an experienced SQL DBA with extensive knowledge of the two largest public clouds: AWS and Azure.

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2023

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.