在《SQL Server 透明数据加密（TDE）的影响》一文中，我们给出启用数据库TDE的备份时，不启用压缩（COMPRESSION ）选项建议（有些片面，现在看来需要加上一些限定条件）。因为在SQL Server 2016版本以前，对TDE数据库备份时使用COMPRESSION，不但不能减少数据库备份文件占用空间，而且备份的时间、CPU都会显著升高。

从 SQL Server 2016开始，启用TDE 的数据库，使用COMPRESSION选项进行备份，其压缩效果和未启用TDE之前有类似的效果。但是有个前提，要在BACKUP命令中设置参数MAXTRANSFERSIZE值大于65536（默认值，64KB）的整数倍，在维护计划→维护计划向导→备份数据库→选项，可以设置“最大传输大小”，如下图：

sql server 2022 中文企业版_备份压缩

值得注意的是，如果您并没有使用维护计划进行备份时，您可能还没有发现这个新的变化。在SQL Server 2016，直至SQL Server 2019版本，我们在SSMS资源管理器中，右击数据库→任务→备份数据库，在备份选项中均没有“最大传输大小”可选。

sql server 2022 中文企业版_TDE_02

那么这个参数在备份中如何使用呢？使用这个参数会对备份过程有哪些影响呢？本文将使用试验的方式给出这些问题的答案。

试验结果

  • 未启用TDE，备份时【未】使用ENCRYPTION加密，压缩，无论是否使用MAXTRANSFERSIZE选项，压缩效果基本相同；但无论是总耗时还是CPU时间上来看不加MAXTRANSFERSIZE都要远优于增加此选项
  • 启用TDE时，是否使用MAXTRANSFERSIZE，对CPU和备份文件大小有显著影响。如果备份策略安排在低谷期（应用对CPU需求较低），可以考虑将MAXTRANSFERSIZE设置为默认值的2倍（维护计划中MAXTRANSFERSIZE 只能设置为65536的非0正整数倍，脚本中虽然可以将其值设置为65536加任意整数，如65537，实质上系统的传输还是会按照向上取一个EXTENT，即64KB的整数倍的数值），从这个试验中可以看到，直接设置65536的整数倍在耗时上优于增加任意整数65537约18%，CPU消耗仅仅增加2%
  • 启用TDE时，不同系统设置MAXTRANSFERSIZE 对性能的影响不同，需要测试去确定设置多大是最优的。
  • 日志的大小对全备的大小影响较小（无压缩备份的文件大小仅仅比mdf文件大181KB，而log文件有4GB多）

 

sql server 2022 中文企业版_透明加密_03

sql server 2022 中文企业版_压缩性能_04

试验准备

在这里我们仍然使用《SQL Server 透明数据加密（TDE）的影响》一文中的test库作为测试数据库，SQL Server 版本为SQL Server 2016企业版。

关闭实例中备份压缩选项

关闭实例中备份压缩选项，这将允许BACKUP命令显式地指定是否应该使用备份压缩。

SSMS资源管理器中，右击实例名→属性→数据库设置→压缩备份左侧的多选框未勾选，如下图：

sql server 2022 中文企业版_透明加密_05

 

我们也可以使用如下T-SQL语句查看实例备份压缩选项的状态：

SELECT * FROM sys.sysconfigures
WHERE comment LIKE '%Enable compression of backups by default%'
EXEC sp_configure 'backup compression default'

sql server 2022 中文企业版_MAXTRANSFERSIZE_06

当然，也可以使用下面的命令禁用SQL服务器实例上的备份压缩设置。

EXEC sys.sp_configure N'backup compression default', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

dbcc showfilestats ：可查看数据库中数据文件的整体使用情况（按区统计）

DBCC SHOWFILESTATS WITH TABLERESULTS

sql server 2022 中文企业版_压缩性能_07

数据库使用的Extent和总的分配的Extent还是比较接近的。

可以使用sp_helpdb查看数据库及数据库文件、日志文件的大小，可以用于备份后文件大小的比较。

EXEC sp_helpdb test

sql server 2022 中文企业版_备份压缩_08

试验样例

服务器性能的不同，SQL  Server 版本不同等，都可能影响测试的结果，最终性能的影响，取决于您的环境。

--测试1：未启用TDE，不压缩，不使用备份加密选项
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithOutCom.bak'
WITH STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_透明加密_09

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 126.102 秒(20.357 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 92 毫秒，占用时间 = 126409 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试2：未启用TDE，使用COMPRESSION选项,使用默认最大传输大小
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithCom65536.bak'
WITH COMPRESSION
       ,STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_透明加密_10

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 60.227 秒(42.624 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 173 毫秒，占用时间 = 60551 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试2-1：未启用TDE，使用COMPRESSION选项,使用默认最大传输大小
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithCom65536.bak'
WITH COMPRESSION
       ,MAXTRANSFERSIZE=65536
       ,STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_备份压缩_11

 

SQL Server 分析和编译时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 207.799 秒(12.353 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 719 毫秒，占用时间 = 208160 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试3：未启用TDE，使用COMPRESSION选项，最大传输大小设置为65537
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithCom65537.bak'
WITH COMPRESSION
       ,MAXTRANSFERSIZE=65537
       , STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_MAXTRANSFERSIZE_12

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 206.892 秒(12.408 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 688 毫秒，占用时间 = 207270 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试4：未启用TDE，使用COMPRESSION选项，最大传输大小设置为131072
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithCom131072.bak'
WITH COMPRESSION
       ,MAXTRANSFERSIZE=131072
       , STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_备份压缩_13

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 119.628 秒(21.459 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 468 毫秒，占用时间 = 119980 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试5：未启用TDE，使用ENCRYPTION选项
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithEncrypt.bak'
WITH ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = TestBackOption
   )
       , STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_透明加密_14

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 123.385 秒(20.805 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 235 毫秒，占用时间 = 123709 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试6：未启用TDE，使用ENCRYPTION选项
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithEncryptWithComDefault.bak'
WITH ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = TestBackOption
   )
   ,COMPRESSION
       , STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_TDE_15

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 61.778 秒(41.554 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 157 毫秒，占用时间 = 62138 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试7：未启用TDE，使用ENCRYPTION选项，最大传输大小值为65537
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithEncryptWithCom65537.bak'
WITH ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = TestBackOption
   )
       ,COMPRESSION
       ,MAXTRANSFERSIZE=65537
       , STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_透明加密_16

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 204.448 秒(12.556 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 843 毫秒，占用时间 = 204873 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试8：未启用TDE，使用ENCRYPTION选项，最大传输大小值为131072
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_DisableTDEWithEncryptWithCom131072.bak'
WITH ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = TestBackOption
   )
       ,COMPRESSION
       ,MAXTRANSFERSIZE=131072
       , STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_TDE_17

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 122.020 秒(21.038 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 438 毫秒，占用时间 = 122384 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试9：启用TDE
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_EnableTDEWithoutCom.bak'
WITH  STATS = 10
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_TDE_18

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 122.271 秒(20.995 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 142 毫秒，占用时间 = 122549 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试10：启用TDE,压缩备份，最大传输大小为默认值
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_EnableTDEWithCom.bak'
WITH  STATS = 10
       ,COMPRESSION
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_TDE_19

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 125.862 秒(20.396 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 219 毫秒，占用时间 = 126112 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试11：启用TDE,压缩备份，最大传输大小为65537
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_EnableTDEWithCom65537.bak'
WITH  STATS = 10
       ,COMPRESSION
       ,MAXTRANSFERSIZE=65537
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_TDE_20

SQL Server 分析和编译时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 128.927 秒(19.911 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 33766 毫秒，占用时间 = 129279 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)

--测试12：启用TDE,压缩备份，最大传输大小为131072
DECLARE @bdatetime AS DATETIME= GETDATE()
SET STATISTICS IO ON
SET   STATISTICS TIME ON
BACKUP DATABASE test
TO DISK='D:\Test_EnableTDEWithCom131072.bak'
WITH  STATS = 10
       ,COMPRESSION
       ,MAXTRANSFERSIZE=131072
SET STATISTICS IO OFF
SET   STATISTICS TIME OFF
SELECT  @bdatetime, GETDATE(), DATEDIFF(millisecond, @bdatetime, GETDATE())
GO

sql server 2022 中文企业版_透明加密_21

已处理百分之 10。

已处理百分之 20。

已处理百分之 30。

已处理百分之 40。

已处理百分之 50。

已处理百分之 60。

已处理百分之 70。

已处理百分之 80。

已处理百分之 90。

已为数据库 'test'，文件 'test' (位于文件 1 上)处理了 328592 页。

已处理百分之 100。

已为数据库 'test'，文件 'test_log' (位于文件 1 上)处理了 2 页。

BACKUP DATABASE 成功处理了 328594 页，花费 105.379 秒(24.360 MB/秒)。

 SQL Server 执行时间:

   CPU 时间 = 34499 毫秒，占用时间 = 105796 毫秒。

 SQL Server 执行时间:

   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)