Gain on OLTP price-performance with Azure SQL Virtual Machines

已于 十月 22, 2019 发布

Senior Program Manager, SQL Database

This post was co-authored by Jamie Reding, Senior Program Manager, Sadashivan Krishnamurthy, Principal Architect, and Bob Ward, Principal Architect.

Today, most applications are running online transactional processing (OLTP) transactions. Online banking, purchasing a book online, booking an airline ticket, sending a text message, and telemarketing are examples of OLTP workloads. OLTP workloads involves inserting, updating, and/or deleting small amounts of data in a database and mainly deals with large numbers of transactions by large number of users. Majority of OLTP workloads are read heavy, use diverse transactions, and utilizes wide range of data types.

Azure brings many price-performance advantages for your workloads with SQL Server on Azure Virtual Machines (VM) with a wide range of Azure Virtual Machine series and Azure disk options. Memory optimized VM series like Intel based Es_v3 series or AMD based Eas_v3 series offer high virtual CPU (vCPU) to memory ratio at a very low cost. Constraint vCPU capable VM sizes offer reduced cost of SQL Server licencing by constraining the vCPU abailable to the VM, while maintaining the same memory, storage, and input or output (I/O) bandwidth. Premium Solid State Drives (SSDs) deliver high-performance and low-latency managed disks with high IOPS and throughput capabilities needed for SQL Server data and log files. Standard SSDs, cost-effective storage options optimized for consistent performance, come as an optimum destination for most SQL Server backup files.

In addition to the large IOPS capacity of the Premium Disks, Azure Blobcache is a huge value for mission critical OLTP workloads as it brings significant additional high-performance I/O capacity to Azure Virtual Machine for free. Blobcache is a multi-tier caching technology enabled by combining the VM RAM and local SSD. You can host SQL Server data files on premium SSD managed disks with read only Blobcache and leverage extremely high-performance read I/Os that exceed the underlying disk’s capabilities. High scale VMs comes with very large Blobcache sizes that can host the all the data files for most applications. As all I/O activity from the Blobcache is free, you can boost application throughput with extremely high performance reads and optimize price-performance by only paying for the writes. Considering the majority of the OLTP workloads today come with 10 to 1 ratio for read and write, this is up to a 90 percent price-performance gain.

Additionally, for workloads demanding very low I/O latency, Azure ultra-disks deliver consistent low latency disk storage at high throughput and high IOPS levels. Ultra-disks maximize application throughput if the workload was bottlenecked on I/O latencies.

Based on read to write ratio, transaction complexity and scale pattern you may choose to use TPC-E or TPC-C for performance measurements. In general, TPC-E represents majority of the OLTP workloads in these days as it includes complex transactions and high read to write ratio. If you have write intensive workloads running simple transactions, then you can leverage the simplicity of TPC-C benchmark for performance validation. For detailed testing of SQL Server performance on Azure Virtual Machines with a scaled down TPC-E workload and HammerDB TPC-C kit please see this article.

Get started with SQL Server in Azure Virtual Machines

Azure SQL Virtual Machine service offers full control on the VM, storage and SQL Server configuration and gives you full flexibility to deploy the most cost-efficient solution for your workload’s specific requirements. You can create an SQL VM with performance optimized storage configuration enabled by SQL VM resource provider today and boost price-performance gain for your workload with performance best practices for SQL virtual machine.

Click here to start testing with free SQL Server Developer edition images on Azure Virtual Machines.

Get started on your Azure migration with the Data Migration Guide.