{"id":107106,"date":"2025-06-11T03:04:00","date_gmt":"2025-06-11T03:04:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107106"},"modified":"2026-04-08T10:27:35","modified_gmt":"2026-04-08T10:27:35","slug":"sql-server-2025-zstandard-backup-compression","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/sql-server-2025-zstandard-backup-compression\/","title":{"rendered":"SQL Server 2025 ZSTD Backup Compression Guide"},"content":{"rendered":"<p>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 <a href=\"https:\/\/www.red-gate.com\/simple-talk\/collections\/sql-server-2025-preview-posts\/\">this collection<\/a>.<\/p>\n<p>&nbsp;<\/p>\n\n\n\n\n<p><em>SQL Server 2025 introduces Zstandard (ZSTD) backup compression as an alternative to the existing MS_XPRESS algorithm. Use BACKUP DATABASE \u2026 WITH COMPRESSION (ALGORITHM = ZSTD) with optional LOW, MEDIUM, or HIGH levels to control the compression-ratio-to-CPU tradeoff. In testing, ZSTD produces smaller backup files than MS_XPRESS at comparable CPU cost, and it&#8217;s simpler to configure than the Intel QAT hardware acceleration added in SQL Server 2022. You can set ZSTD as the server-wide default using sp_configure &#8216;backup compression algorithm&#8217;, 3.<\/em><\/p>\n\n\n\n<p>Whether you are a bank or a hot dog stand, creating backups is a boring but essential part of managing databases. Compressing backups \u2013 like other types of data compression \u2013 can save time and storage space, at the usually unavoidable cost of CPU. There has been little change in compression throughout SQL Server\u2019s long history, but this year, in SQL Server 2025, there is an exciting change coming. <\/p>\n\n\n\n<p>But first\u2026<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-a-very-brief-history-of-backup-compression\">A very brief history of backup compression<\/h3>\n\n\n\n<p>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 <a href=\"https:\/\/www.red-gate.com\/products\/sql-backup\/\">Redgate SQL Backup<\/a>, <a href=\"https:\/\/www.idera.com\/products\/sql-safe-backup\/\">Idera SQL Safe Backup<\/a>, and <a href=\"https:\/\/www.quest.com\/products\/litespeed-for-sql-server\/\">Quest LiteSpeed<\/a>. 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 <em>some<\/em> compression, you no longer needed a 3<sup>rd<\/sup> party tool.<\/p>\n\n\n\n<p>Microsoft has been using the <code>MS_XPRESS<\/code> compression algorithm, which they openly documented starting in 2011 here:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/learn.microsoft.com\/en-us\/openspecs\/windows_protocols\/ms-xca\/a8b7cb0a-92a6-4187-a23b-5e14273b96f8\">[MS-XCA]: Xpress Compression Algorithm<\/a> <\/li>\n<\/ul>\n<\/div>\n\n\n<p>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.<\/p>\n\n\n\n<p>Nothing much in this landscape really changed until SQL Server 2022, when they added an algorithm called <code>QAT<\/code> (\u201cQuickAssist Technology\u201d), which is most effective when used with specific Intel hardware. I haven\u2019t played with this algorithm, but Glenn Allan Berry has talked about it here:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/glennsqlperformance.com\/2022\/08\/25\/how-to-enable-intel-qat-backup-compression-in-sql-server-2022\/\">How to Enable Intel QAT Backup Compression in SQL Server 2022<\/a> <\/li>\n\n\n\n<li><a href=\"https:\/\/glennsqlperformance.com\/2022\/08\/28\/some-intel-qat-backup-compression-results\/\">Some Intel QAT Backup Compression Results<\/a> <\/li>\n<\/ul>\n<\/div>\n\n\n<p>My quick take: It is complex and disruptive to configure and feels like overkill if you can\u2019t get the full benefit. Which also explains why I haven\u2019t tried it.<\/p>\n\n\n\n<p>And now\u2026<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-enter-zstandard\">Enter Zstandard<\/h3>\n\n\n\n<p>SQL Server 2025 adds the option to use <a href=\"https:\/\/techcommunity.microsoft.com\/blog\/azuresqlblog\/zstd-compression-in-sql-server-2025\/4415418\">Zstandard (\u201cZSTD\u201d) compression<\/a>, which promises to produce better results than <code>MS_XPRESS<\/code> while being easier to configure than <code>QAT<\/code>. <code>ZSTD<\/code> as a technology has been around for nine years, described in detail <a href=\"https:\/\/engineering.fb.com\/2016\/08\/31\/core-infra\/smaller-and-faster-data-compression-with-zstandard\/\">here<\/a> by Facebook\u2019s <a href=\"https:\/\/www.linkedin.com\/in\/yann-collet-5ba1904\">Yann Collet<\/a> and <a href=\"https:\/\/engineering.fb.com\/author\/chip-turner\/\">Chip Turner<\/a>.<\/p>\n\n\n\n<p>Now you will be able to say:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">BACKUP DATABASE \u2026\nWITH COMPRESSION (ALGORITHM = ZSTD);<\/pre><\/div>\n\n\n\n<p>And you can specify the level (<code>LOW<\/code>, <code>MEDIUM<\/code>, or <code>HIGH<\/code>):<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">BACKUP DATABASE \u2026\nWITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH);<\/pre><\/div>\n\n\n\n<p>The scale from <code>LOW<\/code> to <code>HIGH<\/code> being higher compression = smaller size in exchange for more CPU.<\/p>\n\n\n\n<p>You can even <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/view-or-configure-the-backup-compression-algorithm-server-configuration-option?view=sql-server-ver16#view-the-backup-compression-algorithm-option\">set the default<\/a> to use ZSTD, but currently there isn\u2019t a way to set the default level to <code>LOW<\/code>, <code>MEDIUM<\/code>, or <code>HIGH<\/code> \u2013 so by default it will always use <code>LOW<\/code>:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">EXEC sys.sp_configure 'backup compression algorithm', 3;\nRECONFIGURE;<\/pre><\/div>\n\n\n\n<p>But not so fast! If you try this in CTP 2.0, the <code>maximum<\/code> is still hard-coded to 2, so (as <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/sql-server-2025-release-notes?view=sql-server-ver16#known-issues\">the release notes warn<\/a>):<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block\" highlight=\"false\" decode=\"true\">Msg 15129, Level 16, State 1, Procedure sys.sp_configure, Line 172\n'3' is not a valid value for configuration option 'backup compression algorithm'.<\/pre><\/div>\n\n\n\n<p>Surely, they\u2019ll fix that before RTM (and maybe add a way to specify the default compression level, too).<\/p>\n\n\n\n<p>If you use <a href=\"https:\/\/ola.hallengren.com\/sql-server-backup.html\">Ola Hallengren\u2019s backup solution<\/a>, he has already updated them with support for this new algorithm. All you\u2019ll have to change is the interface(s) you use to call his stored procedure.<br><br><strong>Read also: <\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/recovering-data-in-sql-server-without-full-backup\/\">Recovering data without a full backup<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-but-do-i-want-to-switch\">But do I want to switch?<\/h3>\n\n\n\n<p>Of course, you have to be punishing your servers with large enough backups to make the payoff worthwhile and noticeable \u2013 never mind being sure it won\u2019t add substantial CPU overhead. If you don\u2019t have large databases, or you\u2019re not trying to reduce the space or time their backups require, then maybe your current solution is already good enough.<\/p>\n\n\n\n<p>I\u2019ve seen a few posts on this already, and I have to say, they haven\u2019t moved me \u2013 because they don\u2019t really show me anything. Reducing the size and duration of a 640kb <code>WideWorldImporters<\/code> or 13kb <code>AdventureWorks<\/code> backup that takes 2 seconds on modern hardware \u2013 even without compression \u2013 is hardly worth lifting a finger and, as a demo, not particularly exciting.<\/p>\n\n\n\n<p>Luckily, I have access to a large, real-world database that most of you don\u2019t: <strong>Stack Overflow<\/strong>.<\/p>\n\n\n\n<p>So, I decided to take a sanitized copy of this ~8 TB database and put it through the ringer. As a starting point, with <code>MS_XPRESS<\/code> 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\u2019t have any reference for what it takes without compression because we simply don\u2019t ever do that.<\/p>\n\n\n\n<p>I restored the sanitized copy in a VM in GCP with the following characteristics:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><p>VM class<\/p><\/td><td><p>m3-ultramem-128<\/p><\/td><\/tr><tr><td><p>Cores<\/p><\/td><td><p>64 (Intel Ice Lake)<\/p><\/td><\/tr><tr><td><p>Memory (GB)<\/p><\/td><td><p>3,904<\/p><\/td><\/tr><tr><td><p>Backup Disk<\/p><\/td><td><p>Hyperdisk Balanced <br>(20 TB, 20K IOPs, 2,400 MB\/s throughput)<\/p><\/td><\/tr><tr><td><p>Data\/Log Disk<\/p><\/td><td><p>Hyperdisk Balanced <br>(20 TB, 20K IOPs, 2,400 MB\/s throughput)<\/p><\/td><\/tr><tr><td><p>Operating system<\/p><\/td><td><p>Windows Server 2022 21H2 <br>(20348.3692)<\/p><\/td><\/tr><tr><td><p>SQL Server<\/p><\/td><td><p>SQL Server 2025 CTP 2.0 <br>(17.0.700.9)<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p> To approximate what we do in production, I wanted to back up to 32 files, using the default <code>MS_XPRESS<\/code> 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\u2019d have to be dropping and deleting everything I create before starting the next test.<\/p>\n\n\n\n<p>I wrote a stored procedure to help run tests and a table to store the results. This way I didn\u2019t have to watch paint dry or interact with the system after each test. Here is the table:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">CREATE TABLE dbo.BackupTests\n(\n  TestID            int identity(1,1),\n  DatabaseName      sysname,\n  CompressionType   sysname,\n  CompressionLevel  varchar(10),\n  BackupStartTime   datetime,\n  BackupEndTime     datetime,\n  RestoreStartTime  datetime,\n  RestoreEndTime    datetime,\n  SizeMB            decimal(20,4),\n  CompressedSizeMB  decimal(20,4),\n  CONSTRAINT PK_BRT PRIMARY KEY(TestID)\n);<\/pre><\/div>\n\n\n\n<p>And here is the procedure:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">CREATE OR ALTER PROCEDURE dbo.RunBackupTest\n  @DatabaseName     sysname        = N'StackOverflow',\n  @CompressionType  sysname        = N'MS_XPRESS',\n  @CompressionLevel varchar(10)    = null,\n  @OutputPath       nvarchar(4000) = N'M:\\Backups\\',\n  @OutputURL        nvarchar(4000) = null,\n  @RestorePath      nvarchar(4000) = N'K:\\Data\\',\n  @NumberOfFiles    int            = 32,\n  @debug            bit            = 0\nAS\nBEGIN\n  SET NOCOUNT ON;\n\n  DECLARE @OutputFormat   nvarchar(4000),\n          @BackupCommand  nvarchar(max),\n          @FileList       nvarchar(max),\n          @Description    nvarchar(255),\n          @RestoreCommand nvarchar(max),\n          @DropCommand    nvarchar(max),\n          @DeleteCommand  varchar(4000),\n          @TestID         int,\n          @ts char(15) = CONVERT(char(8), getdate(), 112)\n              + '.' + REPLACE(CONVERT(char(8), getdate(), 108), ':', '');\n\n  SET @Description = @DatabaseName\n    + N'.' + @CompressionType\n    + COALESCE(N'.' + @CompressionLevel, N'') + N'.'\n    + @ts;\n\n  SET @OutputFormat = COALESCE(@OutputPath, @OutputURL)\n    + @Description;\n\n  SET @BackupCommand = N'BACKUP DATABASE '\n    + QUOTENAME(@DatabaseName) + N' TO '\n\n  SELECT @FileList = char(13) + char(10) + space(2)\n    + STRING_AGG(CONVERT(nvarchar(max), CONCAT(\n      CASE WHEN @OutputPath IS NOT NULL\n      THEN N'DISK ' ELSE N'URL ' END,\n      N' = ''', @OutputFormat, N'.',\n      RIGHT(CONCAT(N'0', value),2), N'.bak''')), N',\n  ') FROM GENERATE_SERIES(1, @NumberOfFiles);\n\n  SET @BackupCommand = @BackupCommand + @FileList + N'\n    WITH INIT, FORMAT, COMPRESSION '\n    + CASE @CompressionType WHEN N'MS_XPRESS' THEN ''\n      ELSE N'(ALGORITHM = ZSTD, LEVEL = '\n    + COALESCE(@CompressionLevel, N'LOW') + N')' END\n    + N', DESCRIPTION = N''' + @Description + N''';';\n\n  IF @debug = 1 PRINT @BackupCommand;\n  IF @debug = 0\n  BEGIN\n    EXEC master.sys.sp_executesql @BackupCommand;\n\n    INSERT dbo.BackupTests\n    (\n           DatabaseName,\n           CompressionType,\n           CompressionLevel,\n           BackupStartTime,\n           BackupEndTime,\n           SizeMB,\n           CompressedSizeMB\n    )\n    SELECT @Description,\n           @CompressionType,\n           @CompressionLevel,\n           backup_start_date,\n           backup_finish_date,\n           backup_size \/ 1048576.0,\n           compressed_backup_size \/ 1048576.0\n      FROM msdb.dbo.backupset\n     WHERE description = @Description;\n\n    SELECT @TestID = SCOPE_IDENTITY();\n  END\n\n  SET @RestoreCommand = N'RESTORE DATABASE '\n    + QUOTENAME(@Description) + N' FROM '\n    + @FileList + N'\n      WITH REPLACE, RECOVERY, '\n    + char(13) + char(10)\n    +\n    (\n      SELECT STRING_AGG(CONVERT(nvarchar(max), CONCAT(N'MOVE '''\n             + name + N''' TO ''' + @RestorePath\n             + @Description, N'.', file_id, N'.ndf''')), N','\n             + char(13) + char(10))\n        FROM master.sys.master_files\n       WHERE database_id = DB_ID(@DatabaseName)\n    ) + N';';\n\n  IF @debug = 1 PRINT @RestoreCommand;\n  IF @debug = 0\n  BEGIN\n    UPDATE dbo.BackupTests\n       SET RestoreStartTime = getutcdate()\n     WHERE TestID = @TestID;\n\n    EXEC master.sys.sp_executesql @RestoreCommand;\n\n    UPDATE dbo.BackupTests\n       SET RestoreEndTime = getutcdate()\n     WHERE TestID = @TestID;\n  END\n\n  SET @DropCommand = N'ALTER DATABASE '\n    + QUOTENAME(@Description) + N' SET SINGLE_USER\n      WITH ROLLBACK IMMEDIATE;\n      DROP DATABASE ' + QUOTENAME(@Description) + N';';\n\n  SET @DeleteCommand = 'del \"' + @OutputPath + '*' + @ts + '*.bak\"';\n\n  IF @debug = 1 PRINT CONCAT(@DropCommand, char(13), char(10), @DeleteCommand);\n  IF @debug = 0\n  BEGIN\n    EXEC master.sys.sp_executesql @DropCommand;\n    EXEC master.sys.xp_cmdshell @DeleteCommand, NO_OUTPUT;\n  END\nEND<\/pre><\/div>\n\n\n\n<p>There\u2019s a lot to unpack there. I needed to:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li> Create unique names to use for database names, files on disk, and for the backup description to easily identify items from backup\/restore history for each test; <\/li>\n\n\n\n<li> Create a backup command that would output <code>TO DISK<\/code> or <code>TO URL<\/code> the exact number of files I specify, and apply the right compression type and level; <\/li>\n\n\n\n<li> Build a restore command that also accurately indicated the files from the backup, as well as valid <code>WITH MOVE<\/code> options for the database\u2019s data and log files; and, <\/li>\n\n\n\n<li> Drop the database and delete the backup files. <\/li>\n<\/ul>\n<\/div>\n\n\n<p>With the debug argument set to true (and a limited number of files for brevity), the following command\u2026<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">EXEC dbo.RunBackupTest \n     @DatabaseName     = N'StackOverflow',\n     @NumberofFiles    = 4,\n     @CompressionType  = 'ZSTD',\n     @CompressionLevel = 'HIGH',\n     @debug            = 1;<\/pre><\/div>\n\n\n\n<p>\u2026outputs everything it would have run (with a little formatting finesse here from yours truly):<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">BACKUP DATABASE [StackOverflow] TO \n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.01.bak',\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.02.bak',\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.03.bak',\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.04.bak'\nWITH INIT, FORMAT, COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH),\nDESCRIPTION = N'StackOverflow.ZSTD.HIGH.20250529.203745';\n\nRESTORE DATABASE [StackOverflow.ZSTD.HIGH.20250529.203745] FROM\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.01.bak',\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.02.bak',\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.03.bak',\n  DISK  = 'M:\\Backups\\StackOverflow.ZSTD.HIGH.20250529.203745.04.bak'\nWITH REPLACE, RECOVERY,\nMOVE 'DataFile' TO 'K:\\Data\\StackOverflow.ZSTD.HIGH.20250529.203745.1.ndf',\nMOVE 'LogFile'  TO 'K:\\Data\\StackOverflow.ZSTD.HIGH.20250529.203745.2.ndf';\n\nALTER DATABASE [StackOverflow.ZSTD.HIGH.20250529.203745]\nSET SINGLE_USER WITH ROLLBACK IMMEDIATE;\n\nDROP DATABASE [StackOverflow.ZSTD.HIGH.20250529.203745];\n\ndel \"M:\\Backups\\*20250529.203745*.bak\"<\/pre><\/div>\n\n\n\n<p>Then I just needed to write a little harness to run each of the tests:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">DECLARE @tests TABLE\n(\n  CompressionType  sysname,\n  CompressionLevel varchar(10)\n);\n\nINSERT @tests VALUES\n(N'MS_EXPRESS', NULL),\n(N'ZSTD', 'LOW'),\n(N'ZSTD', 'MEDIUM'),\n(N'ZSTD', 'HIGH');\n\nDECLARE @ct sysname, @cl varchar(10), @c cursor;\n\nSET @c = cursor LOCAL FAST_FORWARD READ_ONLY FOR\n  SELECT CompressionType, CompressionLevel FROM @tests;\n\nOPEN @c;\nFETCH NEXT FROM @c INTO @ct, @cl;\n\nWHILE @@FETCH_STATUS = 0\nBEGIN\n  EXEC dbo.RunBackupTest @CompressionType  = @ct,\n                         @CompressionLevel = @cl;\n  FETCH NEXT FROM @c INTO @ct, @cl;\nEND\nGO 5<\/pre><\/div>\n\n\n\n<p>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\u2019t account for average CPU over the course of any operation, but that I can pull from other monitoring.<br><br><strong>Read also: <\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/automated-script-generation-with-powershell-and-smo\/\">PowerShell for automated database administration<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-initial-tests\">Initial Tests<\/h3>\n\n\n\n<p>Here are the results averaged across 5 tests for each compression option:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><p>Test<\/p><\/td><td><p>Backup time (s)<\/p><\/td><td><p>Backup CPU<\/p><\/td><td><p>Backup size<\/p><\/td><td><p>Restore time (s)<\/p><\/td><td><p>Restore CPU<\/p><\/td><\/tr><tr><td><p>MS_XPRESS<\/p><\/td><td><p>3,274<\/p><\/td><td><p>13%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>3,267<\/p><\/td><td><p>6%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; LOW<\/p><\/td><td><p>3,272<\/p><\/td><td><p>13%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>3,271<\/p><\/td><td><p>6%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; MED<\/p><\/td><td><p>3,273<\/p><\/td><td><p>14%<\/p><\/td><td><p>1.68 TB<\/p><\/td><td><p>3,259<\/p><\/td><td><p>7%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; HIGH<\/p><\/td><td><p>3,454<\/p><\/td><td><p>16%<\/p><\/td><td><p>1.59 TB<\/p><\/td><td><p>3,262<\/p><\/td><td><p>8%<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>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.:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"wrap:true lang:tsql highlight:0 decode:true block\">BACKUP DATABASE successfully processed 1002629998 pages in 3273.557 seconds (2392.824 MB\/sec).\nRESTORE DATABASE successfully processed 1002629998 pages in 3264.211 seconds (2399.675 MB\/sec).<\/pre><\/div>\n\n\n\n<p>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\u2019s not likely we\u2019ll 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.<\/p>\n\n\n\n<p>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%).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-what-if-i-enable-trace-flag-3042\">What if I enable trace flag 3042?<\/h3>\n\n\n\n<p>These tests were run without <a href=\"https:\/\/dba.stackexchange.com\/a\/317522\">trace flag 3042<\/a>, 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 &lt; 2 TB.<\/p>\n\n\n\n<p>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\u2019re desperately short on space.<\/p>\n\n\n\n<p>I tried that:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"block lang:tsql\" highlight=\"true\" decode=\"true\">DBCC TRACEON(3042, -1);<\/pre><\/div>\n\n\n\n<p>It made very little difference. Results were identical, down to the byte and second, except for a minor saving in backup time for <code>ZSTD HIGH<\/code>, and slightly faster restores across the board:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><p>Test<\/p><\/td><td><p>Backup time (s)<\/p><\/td><td><p>Backup CPU<\/p><\/td><td><p>Backup size<\/p><\/td><td><p>Restore time (s)<\/p><\/td><td><p>Restore CPU<\/p><\/td><\/tr><tr><td><p>MS_XPRESS<\/p><\/td><td><p>3,274<\/p><\/td><td><p>13%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>3,266<\/p><\/td><td><p>6%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; LOW<\/p><\/td><td><p>3,273<\/p><\/td><td><p>13%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>3,266<\/p><\/td><td><p>6%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; MED<\/p><\/td><td><p>3,274<\/p><\/td><td><p>14%<\/p><\/td><td><p>1.68 TB<\/p><\/td><td><p>3,323<\/p><\/td><td><p>7%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; HIGH<\/p><\/td><td><p>3,430<\/p><\/td><td><p>16%<\/p><\/td><td><p>1.59 TB<\/p><\/td><td><p>3,323<\/p><\/td><td><p>8%<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>I turned the trace flag back off.<\/p>\n\n\n\n<section id=\"my-first-block-block_f14d084e185b570d6ef5a661e179e476\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-what-about-a-better-disk-class\">What about a better disk class?<\/h3>\n\n\n\n<p>As its name implies, the <a href=\"https:\/\/cloud.google.com\/compute\/docs\/disks\/hd-types\/hyperdisk-balanced\">Hyperdisk Balanced<\/a> 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 \u2013 an M3 with 64 cores will be capable of better I\/O to these same disks than a C4 with 8 cores.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p><a href=\"https:\/\/cloud.google.com\/compute\/docs\/disks\/hd-types\/hyperdisk-extreme\">Hyperdisk Extreme<\/a> 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\u2019t spend a lot of time testing this initially because it wasn\u2019t feasible to outfit this disk type across the board. But I\u2019m happy to test it in isolation!<\/p>\n\n\n\n<p>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 <em>possible<\/em> given unlimited budget.<\/p>\n\n\n\n<p>As it turns out, again, no big difference, except the duration of <code>ZSTD HIGH<\/code> came back in line with the other methods:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><p>Test<\/p><\/td><td><p>Backup time (s)<\/p><\/td><td><p>Backup CPU<\/p><\/td><td><p>Backup size<\/p><\/td><td><p>Restore time (s)<\/p><\/td><td><p>Restore CPU<\/p><\/td><\/tr><tr><td><p>MS_XPRESS<\/p><\/td><td><p>3,279<\/p><\/td><td><p>13%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>3,280<\/p><\/td><td><p>6%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; LOW<\/p><\/td><td><p>3,274<\/p><\/td><td><p>13%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>3,266<\/p><\/td><td><p>6%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; MED<\/p><\/td><td><p>3,275<\/p><\/td><td><p>14%<\/p><\/td><td><p>1.68 TB<\/p><\/td><td><p>3,319<\/p><\/td><td><p>8%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; HIGH<\/p><\/td><td><p>3,279<\/p><\/td><td><p>15%<\/p><\/td><td><p>1.59 TB<\/p><\/td><td><p>3,318<\/p><\/td><td><p>8%<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>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 <em>both<\/em> sides.<\/p>\n\n\n\n<p>While that is unlikely to happen in real life, let\u2019s 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:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><p>Test<\/p><\/td><td><p>Backup time (s)<\/p><\/td><td><p>Backup CPU<\/p><\/td><td><p>Backup size<\/p><\/td><td><p>Restore time (s)<\/p><\/td><td><p>Restore CPU<\/p><\/td><\/tr><tr><td><p>MS_XPRESS<\/p><\/td><td><p>1,581<\/p><\/td><td><p>24%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>1,606<\/p><\/td><td><p>14%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; LOW<\/p><\/td><td><p>1,579<\/p><\/td><td><p>25%<\/p><\/td><td><p>1.92 TB<\/p><\/td><td><p>1,569<\/p><\/td><td><p>13%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; MED<\/p><\/td><td><p>1,580<\/p><\/td><td><p>29%<\/p><\/td><td><p>1.68 TB<\/p><\/td><td><p>1,626<\/p><\/td><td><p>15%<\/p><\/td><\/tr><tr><td><p>ZSTD &#8211; HIGH<\/p><\/td><td><p>1,581<\/p><\/td><td><p>34%<\/p><\/td><td><p>1.59 TB<\/p><\/td><td><p>1,637<\/p><\/td><td><p>15%<\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>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 \u2013 and, as expected, still resulted in the same compression ratio. Here\u2019s an example from the output:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"wrap:true lang:tsql highlight:0 decode:true block\">BACKUP DATABASE successfully processed 1002684270 pages in 1578.765 seconds (4961.771 MB\/sec).\nRESTORE DATABASE successfully processed 1002684270 pages in 1566.904 seconds (4999.050 MB\/sec).<\/pre><\/div>\n\n\n\n<p>Clearly <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/sql-server-database-growth-and-autogrowth-settings\/\">SQL Server<\/a> can work faster under more capable I\/O \u2013 and would likely do even better if the surrounding hardware would let it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-this-is-still-a-win\">This is still a win!<\/h3>\n\n\n\n<p>You would expect me to be disappointed sharing we were already hitting the limits on our storage, and that the boost from ZSTD won\u2019t 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 \u2013 we don\u2019t 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.<\/p>\n\n\n\n<p>If it can make this difference on the size of our Stack Overflow backups, once we\u2019re on SQL Server 2025, I\u2019m going to turn it on by default and stop thinking about it. If it has zero \u2013 or even negative \u2013 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\u2019ll be happy going back to our 2.5 hour backups over the network and onto a less capable drive, knowing that they\u2019ll be a significant percentage smaller simply by using a more efficient compression algorithm.<\/p>\n\n\n\n<p>If I can get smaller files in the same amount of time, with only marginal extra CPU cost, and on the hardware we\u2019ve already budgeted, that\u2019s a win for me.<\/p>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: How to use Zstandard backup compression in SQL Server 2025<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you use Zstandard backup compression in SQL Server 2025?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Add WITH COMPRESSION (ALGORITHM = ZSTD) to your BACKUP DATABASE command. Optionally specify a level: LEVEL = LOW (fastest, least compression), LEVEL = MEDIUM, or LEVEL = HIGH (slowest, most compression). To make ZSTD the default for all backups on the instance, run EXEC sp_configure &#8216;backup compression algorithm&#8217;, 3; RECONFIGURE;.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. Is ZSTD compression better than MS_XPRESS in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>ZSTD generally produces smaller backup files than MS_XPRESS at comparable CPU usage. The LOW level is closest to MS_XPRESS in speed while still achieving better compression ratios. HIGH level produces the smallest files but uses more CPU. For most production workloads, ZSTD MEDIUM provides the best balance.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. What backup compression options are available in SQL Server?<\/h3>\n            <div class=\"faq-answer\">\n                <p>SQL Server supports three compression algorithms: MS_XPRESS (available since SQL Server 2008, the long-standing default), Intel QAT (added in SQL Server 2022, requires specific Intel hardware), and ZSTD (new in SQL Server 2025, software-only). MS_XPRESS and ZSTD work on any hardware; QAT requires compatible Intel processors.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server 2025 adds Zstandard (ZSTD) backup compression with LOW, MEDIUM, and HIGH levels. Compare ZSTD vs MS_XPRESS performance, syntax, and configuration options.&hellip;<\/p>\n","protected":false},"author":341115,"featured_media":107107,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527,53,143524],"tags":[159254,159319,159332],"coauthors":[158980],"class_list":["post-107106","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-sql-server","category-featured","category-sql-server","tag-sql-server-2025","tag-sqlserver2025publicpreview","tag-zstandard-backup-compression"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/341115"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=107106"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107106\/revisions"}],"predecessor-version":[{"id":109342,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107106\/revisions\/109342"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107107"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107106"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}