Can SQL Server Failover Cluster Instance run on S2D twice as fast as SQL Server Availability Groups on Storage Spaces? Summary

Introduction

Since I’m done with measuring SQL Server Availability Groups (AG) on Storage Spaces and SQL Server Failover Cluster Instances (FCI) on Storage Spaces Direct (S2D) performance, I can write the most interesting part in this series: performance comparison.

Toolkits used

Before I move to the comparison part, I want you to take a look at the schemes of setups that were used in two previous parts. Note that each setup has 8 drives in the underlying storage because S2D needs 4+ drives in each host to be deployed (https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/storage-spaces-direct-hardware-requirements#minimum-number-of-drives-excludes-boot-drive).

Below, find more details about the cluster used for testing SQL Server FCI performance.

SRV153, SRV154 both host are identical

both host are identical Dell R730, CPU 2x Intel Xeon E5-2697 v3 @ 2.60 GHz, RAM 128GB

Storage: 4x Intel SSD DC S3500 480GB

4x Intel SSD DC S3500 480GB LAN: 1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s

1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s OS: Windows Server 2016 Datacenter

Windows Server 2016 Datacenter Database Management System: Microsoft SQL Server 2016

Here are more details about the environment built for measuring SQL Server AG performance

SRV153, SRV154

Dell R730, CPU 2x Intel Xeon E5-2697 v3 @ 2.60 GHz, RAM 128GB

Storage: 4x Intel SSD DC S3500 480GB

4x Intel SSD DC S3500 480GB LAN: 1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s

1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s OS: Windows Server 2016 Datacenter

Windows Server 2016 Datacenter Database Management System: Microsoft SQL Server 2016

Let’s jump to comparison!

First, let’s see how reading performance of both solutions changes under the varying number of threads. I used SQLQueryStress here to see how fast both solutions can read 1M lines from the database.

SQL FCI SQL AG test run time. sec S2D. MB/s test run time. sec Storage Spaces. MB/s threads=1 3.14 240 3.09 283 threads=2 2.92 268 2.96 292 threads=4 4.77 346 2.97 282 threads=8 10.34 475 3.13 272 threads=10 13.6 525 3.51 441 threads=12 16.24 550 3.98 517

Now, let’s take a closer look at writing performance under the varying number of threads (Virtual Users). I measured it here for both solutions with HammerDB (Total Transactions per User = 50 000).

Test HammerDB (50000 Total Transactions per User) SQL FCI test run time, min SQL AG test run time, min Virtual User=1 5 3 Virtual User=2 5 3 Virtual User=4 6 5 Virtual User=8 10 7 Virtual User=10 11 9 Virtual User=12 12 10

Conclusion

Both solutions finished reading 1M lines from the database in 19 seconds regardless of storage configurations. Ok, that’s weird; let’s take a closer look at the performance measured with SQLQueryStress and HammerDB.

Results obtained with SQLQueryStress clearly shows that there’s a huge reading performance gap between SQL Server AG and SQL Server FCI. The former completes the reading request in 3.98 seconds, while SQL Server FCI needs around 16.24 seconds to finish it. Nevertheless, SQL Server on S2D has higher reading performance than one run on Storage Spaces (plots clearly show that).

Numbers derived with HammerDB say that it takes longer for SQL Server FCI to finish writing tests.

Why is SQL Server FCI that slow? I think it may be happening due to the resiliency type. While creating a virtual disk on S2D, I have selected Mirror for the resiliency type. For the PhysicalDiskRedundancy parameter, I have set 1 (Type – Two-way-mirror). Wait, but… I have configured everything just as Microsoft recommends (https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/plan-volumes)!