There are a few best practices we can follow for Azure SQL server VM. First, we can start by collecting the CPU, memory, IOPS, throughput, and latency of the source workload during high traffic times and attending the application performance checklist.
Gather data during high traffic hours such as workloads during your weekdays or business days and other high load processes such as end-of-day or month-end processing and weekend ETL workloads. We need to consider our resources for heavy workloads, such as end-of-quarter processing, and then scale down once the workload completes.
We can use the performance analysis to select the VM Size that can fit our workload's performance requirements.
Below are the points that we need to consider:
Security considerations for SQL Server on Azure Virtual Machines
Cluster configuration best practices (SQL Server on Azure VMs)
Application patterns and development strategies for SQL Server on Azure Virtual Machines
When running SQL Server on Azure Virtual Machines, continue using the same database performance tuning alternatives applicable to SQL Server in on-premises server environments. Many factors are involved in the performance of a relational database in a public cloud; for example, we have to consider the size of a virtual machine and the configuration of the data disks.
There is an advantage when you swap between optimizing for costs and optimizing for performance. The following implementation best practices series focuses on getting the best performance for SQL Server on Azure Virtual Machines. If our workload is less demanding, we might not require every recommended optimization. We need to consider a few factors with our performance needs, costs, and workload patterns as we assess these recommendations.
Quick Checklist for performance :
The following is a quick checklist for optimal performance of SQL Server on Azure Virtual Machines; briefly discussed topics include:
VM Size, Storage, Collect Baseline, Security Considerations, SQL Server Features.
Detail discussion on VM Size
The following is a checklist of VM size best practices for running SQL Server on Azure VM:
You can use VM sizes with four or more vCPU like the E4ds_v4, which are perfect for memory-intensive enterprise applications and applications that benefit from low latency and high-speed local storage, the Standard_M8-4ms, which offers a high vCPU count and a large amount of memory, or the DS12_v2 or higher.
Use memory-optimized virtual machine sizes that offer a high memory-to-CPU ratio that is great for relational database servers, medium to large caches, and in-memory analytics for the best performance of SQL Server workloads.
The DSv2 11-15, Edsv4 series, the M-, and the Mv2- series offer the optimal memory-to-vCore ratio needed for OLTP workloads. Both M series VMs offer the best memory-to-vCore ratio needed for mission-critical workloads and ideal for data warehouse workloads.
Favor a higher memory-to-vCore ratio for a job demanding and data warehouse workloads.
Use the Azure Virtual Machine marketplace images as the SQL Server settings and storage choices are configured for optimal SQL Server performance.
Gather the target workload's performance attributes and use them to determine the appropriate VM size for work.
Based on the documentation from Microsoft, it is advised to use a DS3 or higher sized VM for the Enterprise edition of SQL Server. The usage of DS2 or higher is OK when we use the Standard edition of SQL Server. When we choose a predefined image from the portal to deploy SQL Server, by default, it recommends the VM as shown below:
A vCPU is the abbreviation for virtual centralized processing unit and is a portion or share of the underlying, physical CPU assigned to a particular virtual machine. A Physical CPU can have multiple Virtual CPUs, and this Virtual CPU can be managed by monitored software like a hypervisor.
Here is the Math Behind Calculating vCPU:
Earlier, there were eight vCPUs per core. Today, the vCPU count is decided by the manufacturer. The calculation is done by taking the number of processing threads that a chipset offers per core and multiplying the occupied sockets. Below is the method
: (Threads x Cores) x Physical CPU = number vCPU
For example:
First, we need to select a virtual server and CPU. Let's say we choose Intel Xeon E-2288G as the underlying CPU. The Key stats for the Intel Xeon E-2288G will include eight cores/16 threads with a 3.7GHz Processor base frequency and a 5.0GHz Max turbo boost. There is 16MB of onboard cache.
(16 Threads x 8 Cores) x 1 CPU = 128 vCPU
To determine our Workload & Utilization, we need to look at a few questions before we start!
- Do our apps run at 100% CPU utilization at all times?
- Are there periods where utilization bursts?
- Do our apps have maintenance windows?
- By questioning, we will know our specifications, making an informed decision on the underlying hardware.
If we have small VMs that hardly use CPU time, we could quickly get 20-30 VMs from an 8-core server. But, if you have bigger workloads such as a database server, you will have far fewer VMs from that same eight-core server. It's all about resource utilization and allocation.
It is all about using the resources and allocating them.
For example:
Each vCPU allotment to each VM will depend on its specific load.
4 vCPUs per VM
128 vCPUs/4 vCPUs per VM = 32 VMs
2 vCPUs per VM
128 vCPUs/2 vCPUs per VM = 64 VMs
1 vCPUs per VM
128 vCPUs/1 vCPUs per VM = 128 VMs
In Azure cloud, there is a large array of machines that we can select from: Still, here, since we are referring only to SQL workloads, we will be emphasizing only on D, E, and M family machines as SQL Server database engine performance is more memory-driven as compared to several cores available .eg: Dsv2 11-15, Edsv4, Mv2, Msv2, M, Mdsv2, DCsv2, Dasv4, Dsv3, Ddsv4, Lsv2. These can be mostly grouped into General purpose, memory, and Storage Optimized machines.
Some of the naming conventions for machines are as follows :
a – AMD-based processor
d - Disk, i.e., local temp disk, is present
m - The maximum amount of memory for a particular size.
s - Premium Storage capable may include the possible use of ultra disk
i - Isolated storage, which can be dedicated to a single tenant.
v – version.
D series: Has an enduring memory to core ratio of 7:1, moderate to large caches, and in-memory analytics. Supports Premium Storage and are starting-level SQL Server virtual machines.
E Series: Has memory to core ratio of 8:1; these are standard for memory demanding enterprise applications, also have sizeable local storage SSD capacity and throughput. This acts as a solid, effective SQL Server virtual machine.
M Series: Should have a core to memory ratio of 30:1 without constrained cores and 122:1 with constrained cores. These offer vCore counts and memory for the largest SQL Server workloads. These are fundamental and Data Warehouse Virtual Machines. Also, it is important to note that these costs are huge, so we need to manage them carefully.
Also, it is important for us to understand the concept of constrained cores. Some database workloads like SQL Server do not require a high core count but require high memory, storage, and I/O bandwidth. Many database workloads are not CPU-intensive. Azure offers specific VM sizes to constrain the VM vCPU count to reduce software licensing costs while managing the same memory, storage, and I/O bandwidth. The vCPU count can be constrained to one-half or one-quarter of the original VM size.
For Example – Standard_M16ms comes with 16 cores and 437.5 GB of memory.
But if we go for Standard_M16-4ms, this is the same machine as Standard_M16ms, but only four cores are reserved for SQL Server with the same amount of I/O and memory. So, we gain from the SQL Server licensing fees.
We need to mention that, in this, we are charged full for the Parent VM but SQL Server licensing only for four cores. Finally, when not sure which server machine to start from, use the Edsv4 series. This generally works for all sorts of activities.
CPU Exhaustion & Poor Performance
It is aforesaid as CPU exhaustion can cause poor performance for any virtual machines. The number of virtual cores assigned to a VM is restricted. For example, Windows Server 2008 R2 limits the number of vCPUs to four per VM, which can be extended to 64 in Windows server 2016.
Conclusion:
In this article, we focused on some basic methods and key factors to consider when evaluating and optimizing Azure VM-based SQL Server. If we know the basic methods of tuning and troubleshooting while using SQL Server in Microsoft Azure Infrastructure Services it will surely help us to focus on the root problem and solve it.
References:
-https://docs.microsoft.com/en-us/azure/virtual-machines/sizes
-https://bobcares.com/blog/selecting-the-number-of-vcpus-and-cores-for-a-virtual-machine/
https://www.varonis.com/blog/sql-server-best-practices-part-configuration/
Author: Seema Sah
Microsoft Certified Azure Data Engineer Associate