Improving SQL Server Database Backup Performance

Introduction

Does making your SQL Server database backups twice as fast sound interesting? SQL Server 2022 has new options to help you improve SQL Server database backup performance. If you are on an older version of SQL Server, you still have options for Improving SQL Server Database Backup Performance.

In order to improve your database backup performance, you need to understand what is happening during a database backup and what your bottleneck(s) are. There are several questions you need to answer as you determine this:

• How fast can SQL Server read data from your storage subsystem?
• Are you using any type of backup compression?
• Are you doing a striped backup?
• How fast are your processor cores?
• Are you using any optional backup parameters?
• How fast can you write data to your backup target(s)?

How Fast Can SQL Server Read Data from Your Storage Subsystem?

You can measure this by doing a full backup to disk, using the NUL option. This simply reads all of the data in your data file(s) and log file but does not actually write a database backup file. Doing this gives you an aspirational goal for how fast your database backup can possibly be.

This is also limited by your total sequential read performance across all of your database files. You can measure this using CrystalDiskMark.

For example, if all of my database files are located on a single 2TB Samsung 980 PRO SSD, I am not going to be able to read the data any faster than 6,862 MB/sec, no matter what I do. If I had two 2TB Samsung 980 PRO SSDs and I also have two equal sized data files, I could theoretically get 13,724 MB/sec. In reality, it will be a little lower than that.

If your database files are located on multiple logical drives, you can run multiple CrystalDiskMark instances concurrently (one against each drive letter) to see how much total sequential read throughput your system can sustain.

Are You Using Any Type of Backup Compression?

Whether backup compression makes sense or not depends on multiple factors. For example, if you have extremely fast storage, uncompressed backups are often faster than compressed backups. Of course, uncompressed backups are usually significantly larger than compressed backups.

SQL Server 2019 and older can use legacy native backup compression or software backup compression from third party products. With SQL Server 2022, you can use Intel QAT backup compression in software mode or hardware mode.

Using software-based backup compression creates extra CPU pressure during the backup. The amount of extra CPU utilization goes up with striped backups as you have more backup files in the backup set. If your storage is relatively slow, backup compression (software or hardware-based) can be much faster than uncompressed backups.

If you have SQL Server 2022 Enterprise Edition and supported Intel QAT backup hardware, you can run Intel QAT backups in hardware mode. This offloads the work to compress the backup from your CPU(s) to specialized QAT hardware.

Are You Doing a Striped Backup?

A striped backup is simply a database backup that has more than one backup file. The backup data from your actual database is spread evenly across the backup files. So, if you have two backup files, half of the data is in each backup file. You will need all of the backup files from a striped backup in order to restore the database.

Striped backups will generally reduce the elapsed time for your backup. The best-case scenario would be that each striped backup file would be written to a separate logical drive that is backed by separate physical storage. Unfortunately, most organizations do not have this luxury. Even if all of the striped backup files are going to the same location, you will see some benefit. This is true until you hit the sequential write limit of that target location.

How Fast Are Your Processor Cores?

If you are using software-based backup compression, your host CPU cores are doing the work to compress the database backup. This means that both your CPU single-threaded performance (how fast each core is) and your system CPU multi-threaded performance (how much total CPU capacity do you have) matter.

When you do a striped backup, your CPU utilization will go up pretty rapidly as you have more striped backup files. On a lower core count system, it is pretty easy to create more total CPU pressure than you may want, to the detriment of your regular workload.

If you are just doing a single file database backup, the extra CPU overhead from software-based backup compression is usually fairly small. This is true unless you have a smaller VM or physical machine with a very low number of CPU cores.

Are You Using Any Optional Backup Parameters?

You can experiment with using the optional backup parameters, which include BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE.

You should be careful with BUFFERCOUNT. Higher values can increase performance, but also use a lot more CPU and memory during a database backup.

How Fast Can You Write Data to Your Backup Target(s)?

Your backup targets could be local drives or a remote file share. Maybe you are backing up to a URL or some other form of cloud storage. Regardless of that, the sequential write performance that your backup target(s) have is another potential bottleneck for your backup performance.

It is pretty common to have less sequential write performance from your backup target(s) than you have sequential read performance from where your database files are located. If that is the case for your environment, backup compression is usually very beneficial.

Improving SQL Server Database Backup Performance

The key takeaway here is to do some backup testing with different options and run some storage benchmarks to find out where your main bottleneck is when you run a database backup. Once you know that, you have multiple options to try to alleviate that bottleneck (and probably move it somewhere else).

Ideally you can get a regular full database backup to run almost as fast as you can run a full backup to a Nul device. Again, if your backup target is slower than where your database files are located, backup compression will usually be beneficial.

How Do I Make My Backups Twice As Fast?

Remember that I said that you might be able to do that? Well, here is one example. With SQL Server 2022 Standard Edition, you can use Intel QAT backup compression in software mode. This will work no matter what type of system or hardware you have.

You just have to install the free Intel QAT driver, and then run a couple of simple T-SQL commands to enable QAT in SQL Server 2022. This works with any hardware.

These two striped backups are for the same 434GB database on the same system. The only difference is legacy native backup compression (MS_XPRESS) vs. Intel QAT backup compression in software mode (QAT_DEFLATE).

Here are the comparative results for an eight-file striped backup.

• MS_EXPRESS – 171.009 seconds
• QAT_DEFLATE – 80.873 seconds

In my testing across multiple systems, Intel QAT backup compression (in software mode) is consistently twice as fast as legacy Microsoft backup compression. This is true whether I am backing up to a single file or to a striped backup.

Final Words

I’m genuinely curious, does making your database backups faster matter to you?

If you want to learn more about Intel QAT backup compression, I have written several recent posts about it.

• Some Intel QAT Backup Compression Results
• How to Enable Intel QAT Backup Compression in SQL Server 2022
• How to Install the Intel QAT Driver

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!