When can we downsize Azure Virtual machine for SQL Server?

Question

For now our production SQL Server is Standard_D96ds_v5 Azure VM machine.
We got request to check if we can downsize server to Standard_D48ds_v5.

What measures will take into consideration to have confirmation that we can downsize server ?

Answer 1

Simplest metric to monitor is CPU Utilization:

• If it is constantly above 50%, or often goes above 50% - going down from 96 cores to 48 cores wouldn't probably be a good idea.
• Even if it is sometimes goes above 50%, going down to 48 cores might not be a good idea either (but may be tolerable in your environment)

If you confirm that CPU is underutilized in current setup, there are further metrics to consider. Remember that reducing cores in Azure, your RAM reduces twice, too. That means less space for memory grants, database buffer cache, etc.

If query response time is important for you, baseline your wait stats with 96 cores.
Check the sys.dm_os_wait_stats:

• CXPACKET, SOS_SCHEDULER_YIELD (CPU related)
• PAGEIOLATCH_* (Disk related)
• LCK_M* (lock related)

Capture these waits daily or hourly, know how much these grow in a day, hour. What's your normal numbers with 96 cores ?

When you reduce cores to 48, even if your CPU utilization does not look high, but if above waits grow more compared to what they grew with 96 cores, that means queries will have increased response time.

Worth mentioning that such baselining will work only if your workload is predictable and does not change wildly over days or hours

Answer 2

• Check your database workload, including query performance, concurrency, and transactions to see if your current VM handles it well.

• Monitor VM's CPU, memory, and storage usage over time using Azure Monitor to understand resource patterns.

• Determine SQL Server's resource needs (CPU, memory, storage) and confirm the new VM size meets or exceeds them.

• Verify that VM size change won't impact backup and recovery strategies.