Search
StarWind is a hyperconverged (HCI) vendor with focus on Enterprise ROBO, SMB & Edge

Re-investigating performance of SQL Server Availability Groups on Storage Spaces. Why You Should Always Enable Read-Only Routing

  • June 7, 2019
  • 16 min read
IT and Virtualization Consultant. Dmitriy is specializing in Microsoft technologies, with a focus on storage, networking, and IT infrastructure architecture.
IT and Virtualization Consultant. Dmitriy is specializing in Microsoft technologies, with a focus on storage, networking, and IT infrastructure architecture.

INTRODUCTION

In this post, I am going to take a closer look at the impact of read-only routing on SQL Server Availability Groups performance.

I measured SQL Server Availability Groups (AG) performance before (https://www.starwindsoftware.com/blog/hyper-v/can-sql-server-failover-cluster-instance-run-s2d-twice-fast-sql-server-availability-groups-storage-spaces-part-1-studying-ag-performance/). And, a guy from Reddit recommended enabling read-only routing to achieve higher performance (find more about this policy here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017). Thanks for your comment, man! I highly appreciate your efforts. This article will shed light on how read-only routing affects SQL Server AG performance.

THE TOOLKIT USED

SRV153 and SRV154

In this study, I used a two-node setup where both nodes, SRV153 and SRV154, had the same hardware configurations:

Dell PowerEdge 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

Similarly to the setup used in my previous study (ADD HERE THE NEW LINK TO PART 1) , each host had four SSD-s. The thing is, S2D needs at least 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). In the original article, I used the underlying storage like that, so I decided to use the same hardware for this study.

DEPLOYING SQL SERVER AVAILABILITY GROUPS IN A 2-NODE CLUSTER

Creating a virtual disk

Install Windows Server Failover Cluster. I assigned the quorum vote to a separate share witness.

Next, start the Storage Spaces installation wizard on SRV153/

Start the Storage Spaces installation wizard on SRV153

Choose the node where you want to create a storage pool. You need to enter a storage pool name.

Choose the node

Select the disks that are to be included in the storage pool and press Next.

Select the disks that are to be included in the storage pool

Review the pool settings and click Create.

Review the pool settings

Once the storage pool is created, check the Create a virtual disk when this wizard closes box and press Close.

Create a virtual disk when this wizard closes

Select the storage pool.

Select the storage pool

Specify the virtual disk name and hit Next.

Specify the virtual disk name

Select Simple as the storage layout.

Select Simple as the storage layout

Specify the provisioning type afterwards. I used a fixed disk today.

Specify the provisioning type afterwards

Set the virtual disk size next.

Set the virtual disk size

Confirm virtual disk settings and click Create.

Confirm virtual disk settings

Once you are done with virtual disk creation, check the Create a volume when the wizard closes box and click Close.

Create a volume when the wizard closes

Select the recently created virtual disk in the New Volume wizard.

Select the recently created virtual disk in the New Volume wizard

Specify the volume size.

Specify the volume size

Assign the volume to a drive letter next.
Assign the volume to a drive letter

Select file system settings and specify the volume label.

Select file system settings and specify the volume label

Eventually, confirm all the settings and press Create.

Confirm all the settings

Once the wizard finishes, click Close. Then, go to Disk Management and check whether the new volume has been added.

 Disk Management

Create just the same disk on SRV154 (I called it VD2 here).

Create just the same disk on SRV154

Installing SQL Server AG

Once done with virtual disk creation, let’s start SQL Server AG installation. Install SQL Server 2016 on SRV153 first.

SQL Server AG installation

Select New SQL Server stand-alone installation or add features to an existing installation in SQL Server Installation Center.

New SQL Server stand-alone installation or add features to an existing installation

Specify the SQL Server edition that you want to install. I use the Developer free edition for this study.

Specify the SQL Server edition that you want to install

Look through the license terms, tick the checkbox saying that you accept it and press Next.

Look through the license terms

Select the Database Engine Services in the Instance Features and click Next again.

Database Engine Services

Database Engine Services

Specify the instance name.

Specify the instance name

Afterwards, enter the SQL Server credentials. It is a good idea to reuse the domain credentials in order to avoid any possible issues with creating Availability Groups.

Enter the SQL Server credentials

Specify the authentication mode and administrators for Database Engine.

Specify the authentication mode and administrators for Database Engine

Enter the database directories afterward. In this study, the database is kept on the virtual disk D over Storage Spaces.

Enter the database directories

Specify TempDB settings next.

Specify TempDB settings next.

Verify the settings and hit Install.

Verify the settings and hit Install.

After the wizard finishes, click Close.

After the wizard finishes, click Close.

In order to use AG, open SQL Server Configuration Manager, go to SQL Server (SQLAG) and enable AlwaysOn Availability Groups. Restart SQL Server afterwards.

SQL Server Configuration Manager

That’s pretty much it for SQL Server AG installation. Now, repeat the whole process for SRV154.

Then, after you are done with deploying SQL Server AG on SRV154, install SQL Server Management Studio and connect all nodes to SQL Server (SQLAG).

SQL Server AG on SRV154

Creating a test database

Here is how to create a new database.

Creating a test database

Specify its name.

Specify its name.

Back up this database next.

Back up this database next.

Back up this database next.

Back up this database next.

Creating an availability group

Creating an availability group

Specify the availability group name. Do not forget to enable Database Level Health Detection.

Back up this database next.

 

Select the database where you want to have this availability group created.

Select the database where you want to have this availability group created.

 

Next, at the Specify Replicas step, add the database replica. In this study, it resides on SRV154.

 Specify Replicas step,

 Specify Replicas step,

 Specify Replicas step,

Go to the Listener tab now and set the IP and availability group listener DNS name. Use the 1433 port.

Listener tab now and set the IP

 

Select Automatic seeding as a data synchronization option.

Select Automatic seeding

 

Validate the settings and click Next.

Validate the settings and click Next.

 

Afterwards, verify the choices made in the New Availability Group wizard and press Finish to start availability group creation.

New Availability Group wizard and press Finish

 

Close the wizard after it finishes.G

Close the wizard after it finishes.G

 

After availability group is created, you can find it in Microsoft SQL Server Management Studio.

Microsoft SQL Server Management Studio.

 

TEST TIME!

Enabling read-only routing

Set up read-only routing first according to Microsoft’s (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server?view=sql-server-2017) and CodingSight’s (https://codingsight.com/read-only-routing-for-an-always-on/) recommendations.

Next, from the primary node, enable Read-intent only on the secondary replica.

Read-intent only on the secondary replica.

 

Now, create the query for read-only routing.

Create the query for read-only routing

Add read-only routing list afterwards.

Add read-only routing list afterwards

Here is how to check whether the read-only routing has been enabled.

Read-only routing has been enabled

Here is how it looks like in SQL Management Studio when read-only routing is enabled.

SQL Management Studio

Let’s finally jump to the tests!

HammerDB

First, it is necessary to populate the database with data. I used HammerDB (https://www.hammerdb.com/download.html) in this study.

Start HammerDB. Go to the Options menu and click Benchmark. Specify the testing parameters.

Start HammerDB. Go to the Options

 

Next, in the Schema Build menu, set up HammerDB connections. You also need to specify the number of virtual users (number of threads).

Next, in the Schema Build menu,

 

Double-click Build to start populating the database.

Double-click Build to start populating the database.

 

After the database is filled with data, details about writing performance are saved to a file. Here is what writing performance was like today.

After the database is filled with data

 

Once the database was populated, I reduced the amount of RAM available for SQL Server to prevent caching from altering the database performance.

Once the database was populated

 

I benchmarked database reading performance afterward. I judged on the reading performance based on the time that was needed to read 1M lines from dbo.customer. The result was the same as before – 19 seconds (https://www.starwindsoftware.com/blog/hyper-v/can-sql-server-failover-cluster-instance-run-on-s2d-twice-as-fast-as-sql-server-basic-availability-groups-on-storage-spaces-part-1-studying-AG-performance/).

benchmarked database

 

Here are more details about SQL Server AG reading performance.

Here are more details about SQL Server AG reading performance.

 

SQLQueryStress

Now, let’s see what reading performance was like when measured with SQLQueryStress (https://github.com/ErikEJ/SqlQueryStress). The interesting thing about SQLSqueryStress is that it allows playing around with the number of threads (number of virtual users = 1 ,2, 4, 8, 10, 12).

Start SQLQueryStress and click Database. Set up the connection and click Test Connection.

Database. Set up the connection and click Test Connection.

 

Specify the Number of Threads and Number of Iterations parameters. Click GO to start the test.

Specify the Number of Threads

 

Here are the results measured with SQLQueryStress for different numbers of threads. See the difference in reading speed once read-only routing is enabled.

Number of threads SQL AG SQL AG with Read-Only Routing
test run time,
sec
SSD,
MB/s
test run time,
sec
SSD,
MB/s
1 3.09 283 2.73 270
2 2.96 292 2.76 277
4 2.97 282 2.79 284
8 3.13 272 2.9 265
10 3.51 441 3.85 658
12 3.98 517 4.48 848

OLTP Workload (HammerDB again)

Run HammerDB again. Go to Options to set up HammerDB (i.e., enter connection parameters and number of transactions per virtual user. Click OK. Eventually, double-click Load to run the recently prepared script.

Run HammerDB again.

 

Go to the Virtual User menu and double-click Options. Specify the number of virtual users and hit OK. Double-click Create afterward to have the settings applied. To run the tests, double-click Run.

 

Now, let’s see how the number of virtual users impacts database performance.

HammerDB. Performance

 

HammerDB. Performance for 1 and 2 virtual users

HammerDB. Performance

 

HammerDB. Performance for 4 and 8 virtual users

HammerDB. Performance

 

HammerDB. Performance for 10 and 12 virtual users

 Number of virtual users SQL AG
test run time, min
SQL AG with Read-Only Routing
test run time, min
1 3 5
2 3 4
4 5 4
8 7 7
10 9 8
12 10 10

 

Now, let’s see how read-only routing affects database performance.

Number of virtual users SQL AG,
Tpm
SQL AG with Read-Only Routing,
Tpm
1 38718 20976
2 70788 62718
4 105054 96864
8 111798 117672
10 116628 121872
12 116364 123378

 

DISCUSSION

HammerDB still shows that reading 1M lines from dbo.customer takes 19 seconds even though read-only routing is enabled.

SQLQueryStress clearly shows that reading is done faster when read-only routing is enabled.

Now, let’s see how read-only routing impacts the performance for different numbers of virtual users. For 1, 2, and 4 virtual users, performance become lower after enabling the policy. It should also be noted that read-only routing made reading a bit slower (number of virtual users = 1 and 2). For larger numbers of virtual users (from 8 through 12) read-only routing made performance higher.

CONCLUSION

In general, you should better enable read-only routing. So, whenever it comes to high workloads, just make sure that you set this policy.

Found Dmitriy’s article helpful? Looking for a reliable, high-performance, and cost-effective shared storage solution for your production cluster?
Dmytro Malynka
Dmytro Malynka StarWind Virtual SAN Product Manager
We’ve got you covered! StarWind Virtual SAN (VSAN) is specifically designed to provide highly-available shared storage for Hyper-V, vSphere, and KVM clusters. With StarWind VSAN, simplicity is key: utilize the local disks of your hypervisor hosts and create shared HA storage for your VMs. Interested in learning more? Book a short StarWind VSAN demo now and see it in action!