未加星标

Mission critical performance with Ultra SSD for SQL Server on Azure VM

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

This blog post was authored by Mine Tokus, Senior Program Manager, COGS Data - SQL DB.

We recently published “ Storage Configuration Guidelines for SQL Server on Azure VM ” on the SQL Database Engine Blog summarizing the test findings from running TPC-E profile test workloads on premium storage configuration options. We continued this testing by including Ultra SSD.Ultra SSD is the new storage offering available on Microsoft Azure for mission-critical workloads with sub-millisecond latencies at high throughput. We will summarize the test details and findings in this blog.

We used DS14_v2 VM with 16 cores, 112GB memory and 224GB local SSD for this test. This virtual machine (VM) is capable of scaling up to 51,200 uncached IOPS and 64,000 cached and temporary IOPS. We selected a TPC-E workload representative OLTP app in e-commerce/trade space as the test workload. Our test workload drives a similar percentage of read and write IO activity.

Size vCPU Memory: GiB Temp storage (SSD) GiB Max cached and temp storage throughput: IOPS/MBps (cache size in GiB) Max uncached disk throughput: IOPS/MBps Standard_DS14_v2 16 112 224 64,000/512 (576) 51,200/768 Premium Storage Configuration

For Premium Storage Configuration, we added 10 -P30 disks and enabled RO cache for all of them. We created a single storage pool over all 10 disks which enables 50,000 IOPS for the VM. We placed SQL Server data, log, and Temp DB files on the single storage pool of 10 -P30 disks. This is exactly the same as how SQL files are placed when Storage Configuration feature is used through the portal for SQL VMs created from Azure Marketplace Images.

Ultra SSD configuration

With Ultra SSD, we need only one Ultra SSD disk of 1TB which can scale up to 50,000 IOPS. Ultra SSD can be configured flexibly, size and IOPS can scale independently. With Premium Storage Configuration we had to use 10 of P30 disks to get 50,000 IOPS which bring 10TB capacity that we did not need, as our database is less than 1TB. With Ultra SSD we can provision a disk with our exact size, IOPS, and throughput requirements and we only pay for the provisioned capacity. Also, as one Ultra SSD disk can scale up to 50,000 IOPS (maximum 160,000) we did not need to create a storage pool; single disk hosts all SQL Server files including data, log, and Temp DB. Ultra SSD does not require cache configuration for reads, it already offers sub-millisecond latency for all reads and writes with the brand-new architecture and hardware; so we did not configure any cache for the disk.

Test findings

We executed the TPC-E profile test workload for an hour where it builds up the load in the first 10 minutes and peaks for 40 minutes. We observed the average disk read and write latencies below 1 ms during the run with Ultra SSD where with Premium Storage Pool the average write latency was 4 ms and read latency was 1 ms. We could drive 33 percent more SQL Server throughput by replacing 10 -P30 disks with single Ultra SSD drive on the same VM with the exact same configuration of SQL Server and test workload. CPU usage during our test was around 70 percent, for heavier workloads the throughput gain would be bigger.

Premium Storage Pool Ultra SSD Number of disks 10 1 Read-only cache All disks Not applicable Read-write cache None of the disks Not applicable Data, log, and temp DB files Storage pool more than 10 P30 disks Single Ultra SSD disk Average red latencies 1 ms <1 ms Average write latencies 4 ms <1 ms Average transfer per second 3 ms <1 ms Average transfer size 9.5 KB 10.7 KB Batch requests per second 13.1K 17.5K Business transaction per second 1,688 1,980

As this test shows, a typical SQL Server will gain significant throughput on Ultra SSD compared to Premium Storage driven by the latency differences. To find the most effective storage configuration for SQL Server workloads on an Azure VM, we recommend starting with choosing the correct VM size with enough storage scale limits for your workload. Placing Temp DB on the Local SSD would bring the maximum performance with no additional cost for storage. Premium storage with host blob cache offers low latency cached reads. Placing data files on RO cache enabled Premium Storage Pool with a VM that has large temporary and cached IOPS limits is a cost-effective option for workloads with low latency read requirements.

Ultra SSD offers great storage performance with very low read and write latencies. Use Ultra SSD according to the latency and throughput requirements for your data and log files. For SQL Server workloads, log write latencies are critical, especially when in-memory OLTP is used. Placing log file on Ultra SSD disk will enable high SQL Server performance with very low storage latencies. By combining Ultra SSD, Azure Premium Storage together with memory and storage optimized Virtual Machine Types, Azure Virtual Machines offers enterprise-grade performance for SQL Server workloads.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Mission critical performance with Ultra SSD for SQL Server on Azure VM
本站链接:https://www.codesec.net/view/610839.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(111)