Introduction

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

I measured SQL Server Basic Availability Groups (BAG) performance before (http://www.hyper-v.io/can-sql-server-failover-cluster-instance-run-twice-fast-sql-server-basic-availability-groups-2-node-cluster-part-2-studying-fci-performance/). And, a guy from Reddit (https://www.reddit.com/r/HyperV/comments/b6ktlm/can_sql_server_failover_cluster_instance_run/) 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 BAG performance.

The toolkit used

Windows server failover cluster

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 (https://www.hyper-v.io/can-sql-server-failover-cluster-instance-run-on-s2d-twice-as-fast-as-sql-server-basic-availability-groups-on-storage-spaces-part-1-studying-bag-performance/), 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 Basic 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.

Storage Spaces installation wizard

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 - img

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 next

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 selection

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 BAG

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

Install SQL Server 2016 on SRV153

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

Select New SQL Server stand-alone 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.

License terms

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

Database Engine Services

Feature Selection

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 Basic 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

Verify the settings and hit Install.

Verify the settings

After the wizard finishes, click Close.

Complete the SQL Server Setup

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

Enable AlwaysOn Availability Groups

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

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

Install SQL Server Management Studio

Creating a test database

Here is how to create a new database.

Create a new database

Specify its name.

Specify database name

Back up this database next.

Back up this database

Back up this database

Creating an availability group

Creating an availability group

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

Database Level Health Detection

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

Select user databases for the availability group

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

Add the database replica

Specify replicas

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

Go to the Listener tab

Select Automatic seeding as a data synchronization option.

Select Automatic seeding

Validate the settings and click Next.

Validate the settings

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

New Availability Group

Close the wizard after it finishes.

The wizard copleted

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

 After availability group is created, you can find it in Microsoft

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.

Enable Read-intent only

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

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

Checking the 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 with the read-only routing

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

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

Set up HammerDB connections

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.

Writing performance

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

RAM available for SQL Server

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.hyper-v.io/can-sql-server-failover-cluster-instance-run-on-s2d-twice-as-fast-as-sql-server-basic-availability-groups-on-storage-spaces-part-1-studying-bag-performance/).

SQL Server BAG reading performance

Here are more details about SQL Server BAG reading performance.

SQL Server BAG reading performance details

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.

Start SQLQueryStress and click Database

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

Number of Threads and Number of Iterations parameters

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 BAG SQL BAG 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.

Options to set up HammerDB

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.

Virtual User menu

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

HammerDB. Performance for 1 and 2 virtual users

HammerDB. Performance for 1 and 2 virtual users

HammerDB. Performance for 4 and 8 virtual users

HammerDB. Performance for 4 and 8 virtual users

HammerDB. Performance for 10 and 12 virtual users

 Number of virtual users SQL BAG
test run time, min
SQL BAG 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

Test Hummer DB - query execution time (50000 total transactions per User)

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

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

Test Hummer DB - query transactions per minute (50000 total transactions per User)

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.