This article is part of a series of posts on the forthcoming SQL Server 2025 release. Once 2025 has been released, posts will be kept if the feature hasn't changed for the final release. For other posts in this series, you can find them in this collection.

Whether you are a bank or a hot dog stand, creating backups is a boring but essential part of managing databases. Compressing backups – like other types of data compression – can save time and storage space, at the usually unavoidable cost of CPU. There has been little change in compression throughout SQL Server’s long history, but this year, in SQL Server 2025, there is an exciting change coming.

But first…

A very brief history of backup compression

In the SQL Server 2005 timeframe, there were several vendors who offered backup management products that included compression as a feature, since SQL Server did not offer this capability. The three I remember using were Redgate SQL Backup, Idera SQL Safe Backup, and Quest LiteSpeed. These products still exist but, in SQL Server 2008, Microsoft started taking their lunch by adding native backup compression to Enterprise Edition. By SQL Server 2012, it had made its way to all editions. These products offer other functionality too but, if all you were after was some compression, you no longer needed a 3^rd party tool.

Microsoft has been using the MS_XPRESS compression algorithm, which they openly documented starting in 2011 here:

In my experience, the native backup compression has always worked well. So much so that, at least a decade ago, I adopted the firm stance that all backups should use compression.

Nothing much in this landscape really changed until SQL Server 2022, when they added an algorithm called QAT (“QuickAssist Technology”), which is most effective when used with specific Intel hardware. I haven’t played with this algorithm, but Glenn Allan Berry has talked about it here:

My quick take: It is complex and disruptive to configure and feels like overkill if you can’t get the full benefit. Which also explains why I haven’t tried it.

And now…

Enter Zstandard

SQL Server 2025 adds the option to use Zstandard (“ZSTD”) compression, which promises to produce better results than MS_XPRESS while being easier to configure than QAT. ZSTD as a technology has been around for nine years, described in detail here by Facebook’s Yann Collet and Chip Turner.

Now you will be able to say:

And you can specify the level (LOW, MEDIUM, or HIGH):

The scale from LOW to HIGH being higher compression = smaller size in exchange for more CPU.

You can even set the default to use ZSTD, but currently there isn’t a way to set the default level to LOW, MEDIUM, or HIGH – so by default it will always use LOW:

But not so fast! If you try this in CTP 2.0, the maximum is still hard-coded to 2, so (as the release notes warn):

Surely, they’ll fix that before RTM (and maybe add a way to specify the default compression level, too).

If you use Ola Hallengren’s backup solution, he has already updated them with support for this new algorithm. All you’ll have to change is the interface(s) you use to call his stored procedure.

But do I want to switch?

Of course, you have to be punishing your servers with large enough backups to make the payoff worthwhile and noticeable – never mind being sure it won’t add substantial CPU overhead. If you don’t have large databases, or you’re not trying to reduce the space or time their backups require, then maybe your current solution is already good enough.

I’ve seen a few posts on this already, and I have to say, they haven’t moved me – because they don’t really show me anything. Reducing the size and duration of a 640kb WideWorldImporters or 13kb AdventureWorks backup that takes 2 seconds on modern hardware – even without compression – is hardly worth lifting a finger and, as a demo, not particularly exciting.

Luckily, I have access to a large, real-world database that most of you don’t: Stack Overflow.

So, I decided to take a sanitized copy of this ~8 TB database and put it through the ringer. As a starting point, with MS_XPRESS compression, in recent history and in its existing environment, the Stack Overflow backup has been around 2 TB, across 32 files, and takes roughly 2.5 hours across the network. I don’t have any reference for what it takes without compression because we simply don’t ever do that.

I restored the sanitized copy in a VM in GCP with the following characteristics:

To approximate what we do in production, I wanted to back up to 32 files, using the default MS_XPRESS algorithm, then follow up with each level of the ZSTD algorithm. I wanted to analyze both generating new backups and restoring from them which means, unless I wanted to provision hundreds of terabytes of disk, I’d have to be dropping and deleting everything I create before starting the next test.

I wrote a stored procedure to help run tests and a table to store the results. This way I didn’t have to watch paint dry or interact with the system after each test. Here is the table:

And here is the procedure:

There’s a lot to unpack there. I needed to:

With the debug argument set to true (and a limited number of files for brevity), the following command…

…outputs everything it would have run (with a little formatting finesse here from yours truly):

Then I just needed to write a little harness to run each of the tests:

The table made it much easier to analyze the results than trying to parse and collect the output from the messages pane in SSMS. It only doesn’t account for average CPU over the course of any operation, but that I can pull from other monitoring.

Initial Tests

Here are the results averaged across 5 tests for each compression option:

The backups were all capped out by the storage, which has a maximum throughput of 2,400 MB/s. In fact, all the output messages said things that reflected almost exactly that figure, e.g.:

This means, in GCP, at least with our current chosen disk (Hyperdisk Balanced), no amount of compression is going to improve our backup duration, unless we introduce more drives capable of the same throughput (on both the read and the write sides). It’s not likely we’ll be upgrading our storage across the board anytime soon as our current solution performs perfectly fine. Plus, we back up across the network or to S3, so faster local drives will still be subject to network and other constraints anyway.

What was important for me from this initial test was saving more than a quarter of a terabyte in the final output. This came at the cost of a modest and acceptable increase in CPU (~2-3%) and an equally acceptable uptick in duration (~5%).

What if I enable trace flag 3042?

These tests were run without trace flag 3042, which means the .bak files were initially placed on disk at some inflated guesstimate of their full, uncompressed size (in this case, 2.48 TB total), then populated with data, and shrunk down at the end of the operation to < 2 TB.

3042 prevents that from happening. Under the trace flag, the .bak files are initialized at 0 bytes, grow to accommodate data as it is written, and then there is no shrink operation required at the end. This can be useful in scenarios where you’re desperately short on space.

I tried that:

It made very little difference. Results were identical, down to the byte and second, except for a minor saving in backup time for ZSTD HIGH, and slightly faster restores across the board:

I turned the trace flag back off.

What about a better disk class?

As its name implies, the Hyperdisk Balanced disk is, paraphrased, decent performance at a decent price. You can configure the number of IOPs you want (up to 50,000) and the throughput you want (capping out at 2,400 MB/s), and this just dictates how many network-attached disks will be behind that drive. What you actually get in performance also depends on the machine class you attach it to – an M3 with 64 cores will be capable of better I/O to these same disks than a C4 with 8 cores.

For our mix of machines, we landed on 20,000 IOPs and the max throughput for all of our database servers, after reviewing performance tests and taking budget into consideration.

Hyperdisk Extreme is a little bit different, in that you provision IOPs only (up to 350,000), and not throughput. At the top level, the documentation states you can get 5,000 MB/s. The performance here is expected to be far superior to the balanced, though we didn’t spend a lot of time testing this initially because it wasn’t feasible to outfit this disk type across the board. But I’m happy to test it in isolation!

I created a 20TB drive of this variety, with the max IOPs offered, then ran the tests again with this drive as the output. Just to see what would be possible given unlimited budget.

As it turns out, again, no big difference, except the duration of ZSTD HIGH came back in line with the other methods:

While these disks might be capable of better performance for lots of small I/Os, streaming a backup is still limited by the bandwidth constraint of 2,400 MB/s, presumably because of the weakest link: the read side. In order to get better runtimes, I would likely have to have more capable storage on both sides.

While that is unlikely to happen in real life, let’s try it, for science, and just to be thorough. I created two more Hyperdisk Extreme disks, restored the copy of Stack Overflow to one, and ran the tests again with the restore destination set to the other. Results:

With the read bottleneck reduced, all tests fared a lot better in terms of speed (a little better than half the duration), but doubled CPU or more – and, as expected, still resulted in the same compression ratio. Here’s an example from the output:

Clearly SQL Server can work faster under more capable I/O – and would likely do even better if the surrounding hardware would let it.

This is still a win!

You would expect me to be disappointed sharing we were already hitting the limits on our storage, and that the boost from ZSTD won’t ever improve our backup durations on our current hardware. However, from the start, my primary excitement around this new feature was around the disk footprint, not how long a backup takes – we don’t have a dire need to make our daily backups faster, especially at the cost of more extravagant storage options that will be sitting idle most of the time. But it will be quite handy to have fewer bytes to shuffle around and archive.

If it can make this difference on the size of our Stack Overflow backups, once we’re on SQL Server 2025, I’m going to turn it on by default and stop thinking about it. If it has zero – or even negative – impact on the rest of the backups, it will be at worst a wash, because that database is so much bigger than all the others combined. And I’ll be happy going back to our 2.5 hour backups over the network and onto a less capable drive, knowing that they’ll be a significant percentage smaller simply by using a more efficient compression algorithm.

If I can get smaller files in the same amount of time, with only marginal extra CPU cost, and on the hardware we’ve already budgeted, that’s a win for me.