Data Infrastructure

  Overview When we run SQL Server on Azure Virtual Machines, we can use the same database performance tuning options applicable to SQL Server in an on-premises environment.   For Azure SQL server VM, best practice starts with collecting the baseline requirements for the processing unit and the storage; for this, we have to know the IOPS, throughput, and latency of the source. Also noteworthy is that the CPU and Memory requirement also gather data during peak hours, such as workloads during your business hours, and other high load processes such as end-of-day processing or weekend ETL workloads. Then, use the performance analysis to select the VM Size that can scale to your workload performance requirements. However, the performance of a relational(SQL) database in the cloud depends on many factors, such as the size of a virtual machine, the configuration of the data disks, SQL Server features, Azure Features, High availability, and disaster recovery (HADR) features and also the Security optimization.   Consider scaling up your resources for typically heavy workloads such as end-of-quarter processing, and then scale done once the workload completes. There is typically a trade-off between reducing costs and optimizing for performance. This performance best practices series focuses on getting the best performance for SQL Server on Azure Virtual Machines. If your workload is less demanding, you might not require every recommended optimization. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations. It is about setting up or opting for the best infrastructure for a SQL Server on the Azure cloud. However, if the peak workload at your end is less demanding, you might not require all recommended optimizations. Inspect your performance needs, work pattern, and most importantly, the cost involved as you evaluate these recommendations.   The following is a check-list for optimal performance of SQL Server on Azure Virtual Machines: – VM Size – Storage and Scalability – Baseline Requirements – Security Considerations – SQL Server Features   Since this article is Azure SQL VM and no one knows Azure and SQL better than Microsoft, we will post the link to relevant Microsoft documentation for better understanding and transparency wherever needed. VM Size Here is a quick check-list of VM size best practices for running your SQL Server on Azure VM: · Use VM sizes with four or more vCPU. · Use memory-optimized virtual machine sizes for the best performance of SQL Server workloads. · Consider a higher memory-to-vCore ratio for mission-critical and data warehouse workloads. · Collect the target workload performance characteristics (IOPS) and use them to determine the appropriate VM size for your business.   A vCPU is the abbreviation for virtual centralized processing unit and represents a portion or share of the underlying, physical CPU assigned to a particular virtual machine (VM). A Physical CPU can have multiple vCPU, and this vCPU can be managed by controller software like Hyper-V. In the past, there was a standard procedure that there were eight vCPUs per core. Today, vCPU count is primarily determined by the manufacturer. It is determined by taking the number of processing threads that a chipset offers per core and multiplying the occupied sockets. This is how it looks: (Threads x Cores) x Physical CPU = Number vCPU. If you have small VMs that barely use CPU time, you could quickly get 20-30 VMs from an 8-core server. But, if you have larger workloads such as a database server, you will have far fewer VMs from that same eight-core server. Thus, it is all about resource utilization and allocation.   A few questions to consider before we get started: · Do our apps run at 100% CPU utilization all the time? · Do they have periods where utilization bursts? · Do they have maintenance windows? · When you know the requirements, you can make an informed decision on the underlying hardware. There is a large array of machines in the Azure cloud that we can select from; still, we will emphasize only on D, E, and M family machines since we refer only to SQL workloads. SQL Server database engine performance is more memory-driven as compared to a number of cores available .eg: Dsv2 11-15, Edsv4, Mv2, Msv2, M, Mdsv2, DCsv2, Dasv4, Dsv3, Ddsv4, Lsv2. These can be broadly categorized into General purpose, Memory, and Storage Optimized machines.   Some of the naming convention for machines are as follows: A – AMD based processor D – Disk(local temp, the temporary disk is present) M – The most amount of Memory in a particular size S – Premium Storage capable. I – Isolated storage V – Version   D series: Has a consistent memory to core ratio of 7:1, medium to large caches, and in-memory analytics. Supports Premium Storage and are entry-level SQL Server virtual machines. E Series: Has a Memory to core ratio of 8:1; these are ideal for Memory intensive enterprise applications, also have sizable local storage SSD capacity and throughput. This acts as a solid all-purpose SQL Server virtual machine. M Series: Has 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 mission-critical and Data Warehouse Virtual Machines. Also, it is the point to note that these costs are enormous, so we need to manage them accordingly.   Also, it is essential to understand the concept of constrained cores. Some database workloads like SQL Server or Oracle require high Memory, storage, and I/O bandwidth, but not a high core count. Conversely, many database workloads are not CPU-intensive. Azure offers specific VM sizes to constrain the VM vCPU count to reduce software licensing costs while conserving the same Memory, storage, and I/O bandwidth. For example, 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(RAM).   Screenshot taken from M-series – Azure Virtual Machines| Microsoft Docs