Real-World Production Scenario: Squeezing all possible performance out of Microsoft SQL Server AGs (Availability Groups). Local NVMe Vs. RDMA-mapped (NVMe-oF) remote NVMe storage. Does the juice worth a squeeze?

Introduction

Sooner or later, every system administrator finds the need to have some data transferred over the network. And, of course, every system administrator usually knows how this goes, all pros and cons, which aren’t many, to be fair. Since the 2000s, one of the few popular and widely used solutions for such operations is iSCSI, a protocol for storage networking. Due to the latency, enterprises tend to apply iSCSI as secondary block storage. Essentially, it means that iSCSI has been used when the situation requires good performance, but it’s not vital. Common sense dictates that under such circumstances critical applications in dire need of the fastest performance should be run, for example, on a physically separate storage network. At least, that’s how it used to be.

The NVMe-oF protocol specification cannot offer much difference in terms of architecture or approach, but it does differ in the only way that matters: speed. As it has already been established at the example of Linux and StarWind solutions, the NVMe-oF initiators are capable of providing performance that is barely affected by latency. Strictly speaking, in the case with StarWind NVMe-oF Initiator for Windows, it would have hardly mattered for a lot of people whether the performance level could match local storage or not, since, in the absence of the Windows-native solution from Microsoft, the only alternative available is no NVMe-oF at all.

Purpose

StarWind NVMe-oF Initiator for Windows makes NVMe-oF available for SQL and M&E workloads on Windows and is compatible with all the major NVMe-oF storage vendors. Unfortunately, for enterprise-level IT infrastructures, all of these prospects are secondary in comparison with the performance. When the data transfer speed is critical to the results, every single microsecond can make all the difference, and ensuring the best performance and lowest latency for the users’ mission-critical applications is the number one priority.

That’s why we are going to use the high load Microsoft SQL Server (Always On Availability Groups) so that we could see for ourselves whether StarWind NVMe-oF Initiator for Windows is capable of handling the level of performance of local storage.

Benchmarking Methodology, Details & Results

For this benchmark, we will use two nodes, sw-mssql-01 and sw-mssql-02. These nodes will have two virtual disks respectively (this is where our test database goes):

Storage Spaces Simple from 2x Intel® Optane™ SSD DC P5800X Series (hereinafter referred to as local storage);

SPDK raid0 from 2x Intel® Optane™ SSD DC P5800X Series (hereinafter referred to as remote storage).

Testbed:

Testbed architecture overview:

Microsoft SQL Server nodes:

Hardware:

Software:

Client node:

Hardware:

Software:

Storage nodes:

Hardware:

Software:

The benchmark will be held using both DISKSPD and HammerDB utilities. It will be performed in separate stages. At first, we are going to see how different the performances of remote and local storage are using the DISKSPD utility. Furthermore, there comes the comparison of performances of both Microsoft SQL Server Standalone Instance and Microsoft SQL Server Availability Groups at the examples of local storage and remote storage using the HammerDB utility.

Preparation:

1.Add the nodes (sw-mssql-01 and sw-mssql-02) to the cluster using the Microsoft Failover Cluster:

2. Create a fault tolerant virtual disk to act as a quorum using StarWind Virtual SAN:

3. Create Storage Spaces Simple virtual disks on both nodes (sw-mssql-01 and sw-mssql-02):

4. Connect remote disks from the nodes sw-storage-01 and sw-storage-02 to the nodes sw-mssql-01 and sw-mssql-02 respectively using StarWind NVMeoF Initiator;

5. Format disks to NTFS format with the allocation unit size 64K (R: and L: disks).

L: – local disk, Storage Spaces Simple form 2x Intel® Optane™ SSD DC P5800X Series;

R: – remote disk, SPDK raid0 from 2x Intel® Optane™ SSD DC P5800X Series.

Benchmarking Results:

DISKSPD:

This benchmark serves to define the performance of the local storage (Storage Spaces Simple) and the remote one (SPDK RAID0), connected through the NVMe-oF Initiator. With the consideration of the fact that Microsoft SQL Server operates at the file level, the disks in NTFS have the allocation unit size 64K. Needless to say, the benchmark patterns and block sizes below are most common for Microsoft SQL Server storage operations.

Master Database Files (MDF) workloads:

As you can see clearly for yourself, there is an obvious difference in performance (~30%-40%) in the cases with the small blocks and short I/O queues. Whenever these parameters are growing, the performance is growing respectively and can match that of the local disk.

Log Database Files (LDF) workloads:

Microsoft SQL Server local & remote storage performance (HammerDB):

This benchmark is meant to compare the performance of the Microsoft SQL Server for the OLTP workload in a scenario wherein the database is allocated both on the local and remote storages. The benchmark will be held to measure the performance of both Standalone Instance and Availability Groups.

Basically, an OLTP (online transaction processing) workload is usually identified by a database receiving both requests for data and multiple changes to this data from a number of users over time where these modifications are called transactions.

Every single transaction has a defined beginning point. It modifies the data within the database and either commits the changes or takes them back to the starting point (more info here). Although designing and implementing a database benchmark is quite a task to take upon, TPROC-C is the OLTP workload implemented in HammerDB derived from the TPC-C specification with modification to make running HammerDB straightforward and cost-effective on any of the supported database environments (details up here).

Now, let’s get back to work, shall we?

Building the Schema:

• Benchmark Options: TPROC-C;
• RDBMS: Microsoft SQL Server;
• Database Name: BenchmarkDB;
• Number of Warehouses: 5000;
• Number of Virtual Users: 100.

The amount of warehouses directly affects the database size, which is why it was picked specifically to match the size of available storage resulting in the database with 670GB size. To make sure the results remain untainted, we did a backup of the database and were recovering it before every single test.

Availability Group parameters:

• Name: SW-MSSQL-AG;
• Replicas: SW-MSSQL-01 and SW-MSSQL-02;
• Availability Mode: Synchronous Commit;
• Failover Mode: Automatic;
• Listener Name: SW-MSSQL-AG;
• Data Synchronization: Automatic seeding

HammerDB  benchmark parameters:

• RDBMS: Microsoft SQL Server;
• Database Name: BenchmarkDB;
• Use All Warehouses: TRUE;
• Timed Driver Script: TRUE;
• Ramp Up Time (Minutes): 3;
• Test Duration (Minues): 5;
• Number of Virtual Users: {100,150,200,400,600,800}.

In order to show how the performance and load are going through changes depending on RAM, here is additional benchmarking with Microsoft SQL Server being given 256GB, 128GB, and 64GB RAM respectively:

Microsoft SQL Server – 256GB RAM:

NOPM – new order per minute;

TPM – Transaction per minute.

Storage throughput usage:

Microsoft SQL Server – 128GB RAM:

NOPM – new order per minute;

TPM – Transaction per minute.

Storage throughput usage:

Microsoft SQL Server – 64GB RAM:

NOPM – new order per minute;

TPM – Transaction per minute.

Storage throughput usage:

The benchmarking results shown are clear and definite: as you can observe, with a large number of Virtual Users (100,150,200), the performance difference between the local and remote storages could go up to ~30%. However, if we are to enlarge that number to 400,600,800, the performance difference becomes almost insignificant and does not exceed ~5%-10%.

Read throughput comparison (Microsoft SQL Server 64GB, 128GB, 256GB of RAM):

Moreover, the shortage of RAM for SQL server practically does not have any impact on write operations, but it does have a huge impact on read operations.

Conclusion

To sum up, the results of testing both with the means of DISKSPD and HammerDB have shown a clear and coherent picture. In particular, in the case with the latter, it becomes evident StarWind NVMe-oF Initiator for Windows handles large amounts of Virtual Users (400,600,800) quite well considering that the difference in performance doesn’t exceed 5-10%. However, when it comes to the loads with short queues, the picture is entirely different. With less than a numerous amount of Virtual Users (100,150,200), the performance difference reaches about 30%! The DISKSPD shows nearly the same numbers, with performance difference while handling small loads going up to 30-40%. Nevertheless, there’s nothing mysterious in such disproportional results since it has fairly nothing to do with the solution itself.

Due to the specifics of Windows storage stack architecture, the StarWind NVMe-oF driver is written as a Storport miniport because it is the only way Microsoft would support a block storage driver. Are there other options, you may ask? Of course, there are. However, as you can rightly guess, there are upside downs to that as well. For example, a monolithic SCSI port could have been a great alternative, although it lacks proper documentation. A class driver is quite efficient and useful, but it brings another headache as the system will neither handle SCSI reservation properly nor allow putting CSV (Cluster Shared Volume) on top of it. Essentially, StarWind NVMe-oF Initiator for Windows provides a fully compatible and Windows-certified NVMe-oF specification. Its performance as of now matches the performance of local storage and is only limited from the system side.

This material has been prepared in collaboration with Viktor Kushnir, Technical Writer with almost 3 years of experience at StarWind.