SQL Server 2016 Always On
Industries & Applications

SQL Server® 2016 Always On AGs – Over 70K Transactions/Sec

The Professional Association for SQL Server (PASS) Summit 2016 starts the week of October 24, and our Data Propulsion Lab (DPL) is ready to rock it! Or should I say rocket? Yes, indeed—the DPL ha an HA/DR solution that leverages the latest-and-greatest improvements brought to us by SQL Server 2016 Always On Availability Groups (AGs). This three-replica system is fast!

Let me share with you some of our testing results and takeaways.

The Stars of the Show

The Western Digital portfolio of SanDisk® and HGST® flash solutions is huge. For this particular testing we used HPE ProLiant DL380 Gen9 Servers slinging IOPS via SanDisk Lightning Ascend™ Gen II with firmware optimized specifically for HPE P440 Smart Array I/O controllers.

The HPE ProLiant DL380 Gen9 Server is capable of supporting up to twenty-four 2.5” HPE-branded SanDisk SSDs. But in our tests we used only ten 800GB SSDs. This is just one of the many flexible aspects of this architecture and the fantastic features this system provides. For our testing we focused on four facets:

  • Consistent ultra-high performance
  • HA/DR
  • Affordability
  • Flexibility

There’s much to excited about regarding this solution, and we have a white paper covering each of these aspects. If you’re at PASS Summit, you’ll see that our key focus there will be on the performance solution enables.

SanDisk Lightning Ascend™ Gen II
HPE ProLiant DL380 Gen9 Server with SanDisk Lightning Ascend™ Gen II SSDs

Ultra-High Performance

Our test environment was designed to emulate a typical enterprise HA/DR scenario. This consisted of an AG comprised of three replicas (one primary and two secondaries) in Synchronous Commit Availability Mode.

By bringing together the improvements made to SQL Server 2016 Always On AGs, HPE ProLiant DL380 Gen9 Servers, SanDisk Lightning SSDs, and our DPL team’s configuration and tuning expertise, the system generated over 70K transactions/sec!

It did so by generating 1GB/sec of total I/O throughput, flushing over 200MB/sec of log from the primary replica database, and sending over 400MB/sec across the wire to the secondary databases. Owing to the improvements to the AG Log Transport, the data is hardened on the secondary replica logs in real-time, optimizing RPO.

Flash is required to consume this much data, this quickly. Not only does SanDisk brand flash do the job, it does so consistently in sub-millisecond latency—that includes 50K – 80K mixed, random IOPs even during checkpoint as well as the sequential I/O consumed by log files on both primary and secondaries.

Think again about this: Generating over 4 million data transactions per minute, generating 1GB/sec of I/O throughput, and consistently consuming 200MB/sec of log not in milliseconds, but at microsecond latency. The opportunities and possibilities this enables are truly game changing.

Performance Monitoring

Let’s drill down a bit by reviewing the Perfmon images below. Both display the very same data. However, on the left, we’ve kept the default scale for latency of 1.0, which has for decades been Perfmon’s default setting. The values for both read, write, and total latency at this scale are zero. Is Perfmon broken? No. Simply change the counter scale from 1.0 to 10,000 as we’ve done on the right; doing so provides the resolution necessary to expose the actual latency curve. In this image I’ve highlighted the data volume. Avg. Disk sec/Transfer is still reported as 0.000, and the highlighted line reveals why–the actual latency here is actually ~400µs, which Perfmon rounds down to zero.

At the DPL we’re used to amazing perf, yet this astonished even us!

SQL Server 2016 Always On Performance Monitoring with Perfmon
Performance Monitoring with Perfmon with changed counter scale from 1.0 to 10,000 to expose µsec I/O latency

HA/DR, Affordability, and Flexibility

System performance is impressive, yet the system offers much more. Its incredible cost savings and simple deployment features make it extremely compelling as an enterprise solution.

This system takes full advantage of the fantastic SQL Server 2016 AG Log Transport improvements. By leveraging the design characterized here you can maximize ROI, including saving on SQL Server licensing dollars. The in-server flash capabilities eliminate the need for a SAN infrastructure, so that the costs of footprint, proprietary hardware, licensing, and a dedicated skill set are no longer required.

Lastly, the easy-to-implement configuration offers generous resource bandwidth, particularly CPU and I/O. It provides flexible deployment options, including populating both sockets or just one, modifying I/O controller or SSD count (up to 24), capacity plays (up to 35TB raw), consolidation opportunities, and more.

Taking Advantage of SQL Server 2016 Always On AGs

The powerful updates made to SQL Server 2016 AGs can be maximized by deploying state-of-the-art (yet affordable) HPE hardware powered by high-performance and ultra-low-latency SanDisk flash storage. Performance and HA/DR service levels can be met that were previously simply not possible. And best yet, you can achieve this in a straightforward, easy-to-implement, and affordable configuration.

There’s a lot to be excited about.

Learn More

See for yourself how HPE, SQL Server, and the Western Digital SanDisk brand flip the /faster bit, unleashing the power of flash! Visit the SanDisk booth #222 at SQL PASS in the Exhibitor Hall, where I’ll be presenting regularly and we’ll have a SQL Server 2016 Always On AG demo station.

Download our SQL Server 2016 Always On AG performance white paper, including the comprehensive solution architecture, here!

Subscribe to this blog

Enter your email address to subscribe to this blog.

Jimmy May
Jimmy May is a SQL Server Certified Master and is a SQL Server Technologist for SanDisk Data Propulsion Lab. Jimmy was a Principal Architect in Microsoft IT and formerly a Senior Program Manager for the SQL Server Customer Advisory Team (SQL CAT) where he managed the Customer Lab which hosts the biggest, fastest, and most interesting SQL Server apps from around the world. His last day job was Senior Database Architect for one of the world’s largest, SQL Server high-throughput OLTP DBs. He was a founder & on the executive committees of both the Indiana Windows User Group (www.iwug.net) & Indianapolis Professional Association for SQL Server (www.indypass.org). His passion is “flipping the /faster bit”; his motto is “change the world or go home”. Visit his SQL Server performance and personal productivity blog at http://sqlblog.com/blogs/jimmy_may.