备份压缩 (SQL Server)Backup Compression (SQL Server)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题介绍 SQL ServerSQL Server 备份的压缩,包括限制、压缩备份时的性能折中、备份压缩的配置以及压缩率。This topic describes the compression of SQL ServerSQL Server backups, including restrictions, performance trade-off of compressing backups, the configuration of backup compression, and the compression ratio. 以下 SQL Server 2017SQL Server 2017 版本支持备份压缩:企业版、标准版和开发人员版。Backup compression is supported on SQL Server 2017SQL Server 2017 editions: Enterprise, Standard, and Developer. SQL Server 2008SQL Server 2008 的每个版本和更高版本都可以还原已压缩的备份。Every edition of SQL Server 2008SQL Server 2008 and later can restore a compressed backup.

优势Benefits

  • 因为相同数据的压缩的备份比未压缩备份小,所以压缩备份所需的设备 I/O 通常较少,因此通常可大大提高备份速度。Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

    有关详细信息,请参阅本主题后面的 压缩备份的性能影响For more information, see Performance Impact of Compressing Backups, later in this topic.

限制Restrictions

压缩的备份具有以下限制条件:The following restrictions apply to compressed backups:

  • 压缩的备份和未压缩的备份不能共存于一个介质集中。Compressed and uncompressed backups cannot co-exist in a media set.

  • 早期版本的 SQL ServerSQL Server 无法读取压缩的备份。Previous versions of SQL ServerSQL Server cannot read compressed backups.

  • NTbackup 无法共享包含压缩的 SQL ServerSQL Server 备份的磁带。NTbackups cannot share a tape with compressed SQL ServerSQL Server backups.

压缩备份的性能影响Performance Impact of Compressing Backups

默认情况下,压缩会显著增加 CPU 的使用,并且压缩进程所消耗的额外 CPU 可能会对并发操作产生不利影响。By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. 因此,你可能需要在会话中创建低优先级的压缩备份,其 CPU 使用率受资源调控器限制。Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited byResource Governor. 有关详细信息,请参阅本主题后面的 使用资源调控器限制备份压缩的 CPU 使用量 (Transact-SQL)限制。For more information, see Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).

若要很好地了解备份 I/O 的性能表现,可以通过评估以下类型的性能计数器来分别考察进入设备或来自设备的备份 I/O:To obtain a good picture of your backup I/O performance, you can isolate the backup I/O to or from devices by evaluating the following sorts of performance counters:

有关 Windows 计数器的信息,请参阅 Windows 帮助。For information about Windows counters, see Windows help. 有关如何使用 SQL Server 计数器的信息,请参阅 使用 SQL Server 对象For information about how to work with SQL Server counters, see Use SQL Server Objects.

计算压缩的备份的压缩率Calculate the Compression Ratio of a Compressed Backup

若要计算备份的压缩率,请使用 backupset 历史记录表的 backup_size 列和 compressed_backup_size 列中有关此备份的值,如下所示:To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_sizebackup_size:compressed_backup_size

例如,3:1 的压缩率表明您可以节省大约 66% 的磁盘空间。For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space. 若要查询这些列,可以使用以下 Transact-SQL 语句:To query on these columns, you can use the following Transact-SQL statement:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;  

压缩备份的压缩率取决于所压缩的数据。The compression ratio of a compressed backup depends on the data that has been compressed. 有多种因素会影响所获得的压缩率。A variety of factors can impact the compression ratio obtained. 主要因素包括:Major factors include:

  • 数据类型。The type of data.

    字符数据的压缩率要高于其他类型的数据。Character data compresses more than other types of data.

  • 页面上各行间数据的一致性。The consistency of the data among rows on a page.

    通常,如果某页包含多个行,而其中的某个字段包含相同的值,则该值可获得较大的压缩。Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. 相反,对于包含随机数据或者每页只有一个很大的行的数据库,压缩备份的大小几乎与未压缩的备份相同。In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.

  • 数据是否加密。Whether the data is encrypted.

    与未加密数据相比,同样的加密数据的压缩率要小得多。Encrypted data compresses significantly less than equivalent unencrypted data. 如果使用透明数据加密来加密整个数据库,则压缩备份不会将数据库大小减小很多,甚至根本不会减小。If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.

  • 数据库是否压缩。Whether the database is compressed.

    如果压缩数据库,则压缩备份不会将大小减小很多,甚至根本不会减小。If the database is compressed, compressing backups might not reduce their size by much, if at all.

为备份文件分配空间Allocation of Space for the Backup File

对于压缩的备份,最终备份文件的大小取决于数据可压缩程度,这在备份操作完成之前是未知的。For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. 因此,默认情况下,在使用压缩备份数据库时,数据库引擎将预先分配算法用于备份文件。Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. 此算法为备份文件预先分配数据库大小的预定义的百分比。This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. 如果在备份操作过程中需要更多空间,则数据库引擎会增大该文件。If more space is needed during the backup operation, the Database Engine grows the file. 如果最终大小小于分配的空间,则在备份操作结束时,数据库引擎会将该文件收缩到备份的实际的最终大小。If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.

若要允许备份文件仅在需要时增大以便达到其最终大小,则使用跟踪标志 3042。To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. 跟踪标志 3042 导致备份操作绕过默认的备份压缩预先分配算法。Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. 如果您需要仅分配压缩的备份所需的实际大小以便节约空间,则此跟踪标志将很有用。This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. 但是,使用此跟踪标志可能会导致轻微的性能损失(在备份操作期间损失可能会增加)。However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

相关任务Related Tasks

另请参阅See Also

备份概述 (SQL Server) Backup Overview (SQL Server)
跟踪标志 (Transact-SQL)Trace Flags (Transact-SQL)