Introduction

Since I’m done with measuring SQL Server Basic Availability Groups (BAG) 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.

S2D

 

  • SRV153, SRV154 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
  • LAN: 1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s
  • OS: Windows Server 2016 Datacenter
  • Database Management System: Microsoft SQL Server 2016

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

SQL_cluster_2

  • SRV153, SRV154
  • Dell R730, CPU 2x Intel Xeon E5-2697 v3 @ 2.60 GHz, RAM 128GB
  • Storage: 4x Intel SSD DC S3500 480GB
  • LAN: 1x Broadcom NetXtreme Gigabit Ethernet, 2x Mellanox ConnectX-4 100Gbit/s
  • OS: 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 BAG
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

SQLQueryStress - query execution time:reading the first 1000000 term from the table

SQLQueryStress - Peak read speed from data base file 1000000 term from the table

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 BAG

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

test hammer DB - query execution time (50000 total transaction per user)

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 BAG 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)!

TIP