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.