Performance best practices for SQL Server on IaaS Azure VMs
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
