数据压缩 : 简要

1. 决定压缩哪些对象

通过sp_estimate_data_compression_savings 评估在ROW和PAGE压缩时分别节省的空间量。

表包含如下数据模式时，会有较好的压缩效果：

  • 数字类型的列和固定长度的字符类型数据，但两者的大多数值都不会用到此类型的所有字节。如INT列的值大多数少于1000.
  • 允许为NULL的列有很多NULL值
  • 列值中有很多一样的值或者相同的前缀。

表包含如下数据模式时，压缩效果较差：

  • 数字类型的列和固定长度的字符类型数据，但是两者的大多数值都会用尽此类型的所有字节。
  • 非常少量的重复值
  • 重复值不具有相同的前缀
  • 数据存储在行外
  • FILESTREAM数据

2. 评估应用负载模式

被压缩的页在磁盘和内存都是压缩的。下面两种情况下会被解压缩(不是整页解压缩，只解压缩相关的数据)：

  • 因为查询中的filtering, sorting, joining操作而被读取
  • 被应用程序更新

解压缩会消耗CPU，但是数据压缩会减少物理IO和逻辑IO，同时会提高缓存效率。对于数据扫描操作，减少的IO量非常可观。对于单个的查找操作，减少的IO量较少。

行压缩导致的CPU开销通常不会超过10%。如果当前的系统资源充足，增加10%CPU毫无压力的话，建议所有的表都启用行压缩。

页压缩比行压缩的CPU开销高一些，所以确定是否使用页压缩会困难一些。可以通过一些简单的准则来帮助我们判断：

  • 从那些不常用的表和索引开始
  • 如果系统没有足够的CPU余量，不要使用页压缩
  • 因为 filtering, joins, aggregates和sorting操作使用解压缩后的数据，所以数据压缩对这类查询没有太多帮助。如果工作负载主要由非常复杂的查询(多表JOIN，复杂聚合)组成，页压缩不会提高性能，最主要是节省存储空间。
  • 大型数据仓库系统中，扫描性能是其重点，同时存储设备的成本较高，在CPU性能允许下，建议对所有表使用页压缩。

可以通过两个更细的度量值来帮我们评估使用何种数据压缩方式：

  • U:特定对象(表、索引或者分区)的更新操作占所有操作的百分比。越低越适合页压缩。
  • S:特定对象(表、索引或者分区)的扫描操作占所有操作的百分比。越高越适合页压缩。

通过如下脚本查询数据库所有对象的U:

SELECT o.name AS [Table_Name], x.name AS [Index_Name],

       i.partition_number AS [Partition],

       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],

       i.leaf_update_count * 100.0 /

           (i.range_scan_count + i.leaf_insert_count

            + i.leaf_delete_count + i.leaf_update_count

            + i.leaf_page_merge_count + i.singleton_lookup_count

           ) AS [Percent_Update]

FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

JOIN sys.objects o ON o.object_id = i.object_id

JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

       + i.leaf_delete_count + leaf_update_count

       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND objectproperty(i.object_id,'IsUserTable') = 1

ORDER BY [Percent_Update] ASC

通过如下脚本查询数据库所有对象的S:

SELECT o.name AS [Table_Name], x.name AS [Index_Name],

       i.partition_number AS [Partition],

       i.index_id AS [Index_ID], x.type_desc AS [Index_Type],

       i.range_scan_count * 100.0 /

           (i.range_scan_count + i.leaf_insert_count

            + i.leaf_delete_count + i.leaf_update_count

            + i.leaf_page_merge_count + i.singleton_lookup_count

           ) AS [Percent_Scan]

FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

JOIN sys.objects o ON o.object_id = i.object_id

JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

       + i.leaf_delete_count + leaf_update_count

       + i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND objectproperty(i.object_id,'IsUserTable') = 1

ORDER BY [Percent_Scan] DESC

这两个查询用到了DMV sys.dm_db_index_operational_stats。DMV只是记录上次SQL Server实例启动以来的积累值，所以在实际应用中要选择一个合适的时间来查询。

通常U<20%和S>75%会是比较合理的考虑启用压缩的出发点，但是对于只插入有序数据的流水表，页压缩会比较合适(即使S值很低)。

 

3. 评估资源需求

    使用ALTER TABLE… REBUILD和ALTER INDEX … REBUILD对表和索引启用压缩，其它原理和重建索引是一样的。通常需要的资源包括空间、CPU、IO、空间需求

在压缩过程中，已压缩的表和未压缩表是并存的，只有完成压缩后，未压缩的表才会被删除并释放空间。如果Rebuild是ONLINE的话，则还有Mapping Index需要额外的空间。

事务的空间需求由压缩方式是否是ONLINE(ON or OFF)和数据库的恢复模式决定。

当SORT_IN_TEMPDB=ON时(推荐为ON)，为了实现并发DML操作，会在tempdb中Mapping index的内部结构来映射旧书签和新书签的关系。对于版本化存储的，tempdb的使用量由并发DML操作所涉及的数据量和事务时间长度决定。

通常行压缩操作的CPU开销是重建一个索引的1.5倍左右，页压缩是它的2到5倍。ONLINE模式还需要额外的CPU资源。Rebuild和Compress可以被并行化的，所以还要结合MAXDOP一起考虑。

并行化的注意事项：

  • SQL Server在Create/Rebuild/Compress一个索引时，使用索引首列(最左列)的统计信息确定并行操作在多个CPU间的分布。所以当索引首列的筛选度不高，或者数据倾斜严重使得首列的值很少时，并行化对性能提升的帮助就很少。
  • 使用ONLINE=ON方式Compress/Rebuild堆表是单线程操作。但是压缩和重建前的表扫描操作是并行多线程的。

下表总结对比了压缩和重建一个聚集索的资源开销：

  • X = 压缩或者重建前的页数量
  • P = 压缩后的页数量(P < X)
  • Y = 新增和被更新的页数据 (只适用于ONLINE=ON时并发应用所做修改)
  • M = Mapping index的大小 (基于<TEMPDB Capacity Planning>白皮书的预估值)
  • C = 重建聚集索引所需CPU时间

image

在判断何时和怎么压缩数据时，下面是一些参考点：

  • Online vs. Offline:

        Offline更快，需要的资源也更少，但是压缩操作过程中会锁表。Online自身也会有一些限制。

  • 一次压缩一个table/index/partition vs. 多个操作并发:

        这个由当前资源的余量决定，如果资源很充足，多个压缩操作并行也可以接受的，否则最好一次一个。

  • 表压缩操作的顺序:

        从小表开始，小表压缩需要的资源少，完成快。完成后释放的资源也利于后续表的压缩操作。

  • SORT_IN_TEMPDB= ON or OFF:

        推荐ON。这样可以利用tempdb来存放和完成Mapping index操作，从而也减少用户数据的空间需求。

压缩操作副作用:

  • 压缩操作包括重建操作，所以会移除表或索引上的碎片。
  • 压缩堆表时，如果有非聚集索引存在，则：当ONLINE=OFF，索引重建是串行操作，ONLINE=ON，索引重建是并操作。

4. 维护压缩数据

新插入数据的压缩方式

image

*通过以页压缩方式重建堆表来将行级压缩页转换为页级压缩。

**页压缩中，并不是所有的页都是页压缩的，只有当页压缩节省的空间量超过一个内存阈值时才是。

更新和删除已压缩的行

所有对行压缩表/分区数据行的更新会保持行压缩格式。并不是每次对页压缩表/分区的数据行的更新都会导致列前缀和页字典被重新计算，只有当在上的更新数量超过某个内部阈值时，才会重新计算。

辅助数据结构的行为

Table compression

Transaction log

Mapping index for rebuilding the clustered index

Sort pages for queries

Version store (with SI or RCSI isolation level)

ROW

ROW

NONE

NONE

ROW

PAGE

ROW

NONE

NONE

ROW

页压缩索引的非叶级页是行压缩的

索引的非叶级页相对较小，就算应用页级压缩，节省的空间也不会很显著。而对非叶级页的访问会很频繁，使用行级压缩可减少每次访问时解压缩成本。

5. 回收数据压缩释放的空闲空间

  1. 不回收，留着给将在的数据增长使用。这个不适合分区表(每个分区对应一人不同的文件级)的只读分区，压缩旧的只读分区不会增长，压缩可以节省大量空间。
  2. DBCC SHRINKFILE (或者DBCC SHRINKDATABASE) 。这个操作会带来大量碎片，同时它是一个单线程操作，可能会耗时较长。
  3. 如果压缩了一个文件组上的所有表，则新建一个文件组，然后在压缩时将表和索引移动到新的文件组。数据移动可以通过Create/Recreate聚集索引的方式实现(如，WITH (DATA_COMPRESSION=PAGE, DROP_EXISTING=ON, SORT_IN_TEMPDB=ON) ON [FG_NEW] )。移动完数据之后，删除原来的文件组即可。但是这种方式不能移动LOB_DATA数据到新文件组。
  4. 在新文件组上创建压缩的表，然后将数据导入到这些表。

6. BULK INSERT 和数据压缩

BULK INSERT WITH (TABLOCK)导入数据到已压缩的表，速度最快。很明显，这会锁表。

压缩数据时，BULK INSERT和创建聚集索引的顺序考虑：

序号

方式

比较

1

BULK INSERT导入数据到未压缩的堆表，然后再 CREATE CLUSTERED INDEX WITH (DATA_COMPRESSION = PAGE).

所需时间：1<2<3

2

BULK INSERT导入数据到页压缩的堆表，然后再  CREATE CLUSTERED INDEX

所需空间：1>2>3

3

BULK INSERT导入数据到页压缩的聚集索引

  

7. 数据压缩和分区表维护

1. Switch操作要求目标分区(或目标表)与源分区的压缩方式相同。

2. Split后的分区继承原分区的压缩方式。

3. Merger操作，被删除的分区称为源分区,接收数据的分区称为目标分区：

目标分区的压缩方式

数据合并到目标分区的方式

NONE

在Merger期间，数据会被解压缩到目标分区

ROW

在Merger期间，数据会被转换成行压缩格式

PAGE

-堆表: 在Merger期间，数据会被转换成行压缩格式

- 聚集索引: 在Merger期间，数据会被转换成页压缩格式

分区表Merger操作规则

1. LEFT RANGE时，删除边界值所在的分区，保留"左"侧的分区，并向其移动数据

2. RIGHT RANGE时，删除边界值所在的分区，保留"右"分区，并向其移动数据

8. 数据压缩和透明数据加密(TDE)

TDE是当数据页写入磁盘时加密，从磁盘中读出页放入到内存时解密。而数据压缩/解压缩操作是对内存中的页执行的，所以数据压缩/解压缩总是用到解密后的页。因此两者之前的相互影响很小。

----20160725 添加以下内容----

9. 数据压缩和复制

如果将数据压缩与复制一起使用，则应注意以下事项：

  • 当快照代理生成初始架构脚本时，新架构将对表及其索引使用相同的压缩设置。 不能仅对表启用压缩，而不对索引启用压缩。

  • 对于事务复制，项目架构选项决定了必须对哪些依赖对象和属性编写脚本。 有关详细信息，请参阅 sp_addarticle。

    分发代理在应用脚本时，不对下级订阅服务器进行检查。 如果选择了压缩的复制，则在下级订阅服务器上创建表将会失败。 在混合拓扑中，不启用压缩的复制。

  • 对于合并复制，发布兼容级别优先于架构选项，并决定了将编写脚本的架构对象。

    在混合拓扑中，如果不是必须支持新的压缩选项，则发布兼容级别应设置为下级订阅服务器版本。 否则，应在创建表后在订阅服务器上压缩表。

下表列出了在复制期间控制压缩的复制设置。

User intent
 Replicate partition scheme for a table or index
Replicate compression settings
Scripting behavior
复制分区方案并在该分区上的订阅服务器上启用压缩。 True True 对分区方案和压缩设置均编写脚本。
复制分区方案，但不压缩订阅服务器上的数据。 True False 对分区方案编写脚本，但不对分区的压缩设置编写脚本。
不复制分区方案，也不压缩订阅服务器上的数据。 False False 不对分区和压缩设置编写脚本。
如果发布服务器上的所有分区均压缩，则压缩订阅服务器上的表，但不复制分区方案。 False True 检查是否对所有分区均启用了压缩。

在表级别对压缩编写脚本。
 
10. 压缩对其他 SQL Server 组件的影响

压缩发生在存储引擎中，数据以未压缩状态呈现给 SQL Server 的其他大部分组件。 这决定了其他组件上的压缩效果仅限于以下方面：

  • 大容量导入和导出操作

    导出数据时，即使采用本机格式，数据也以未压缩的行格式输出。 这会导致导出的数据文件的大小比源数据要大得多。

    导入数据时，如果已对目标表启用压缩，则存储引擎会将数据转换为压缩的行格式。 这样所使用的 CPU 资源会比将数据导入未压缩表时使用的 CPU 资源多。

    如果以大容量方式将数据导入具有页压缩设置的堆，则在插入数据时，大容量导入操作会尝试使用页压缩来压缩数据。

  • 压缩对备份和还原没有影响。

  • 压缩对日志传送没有影响。

  • 数据压缩与稀疏列不兼容。 因此，无法压缩包含稀疏列的表，也不能将稀疏列添加到压缩表。

  • 启用压缩可以导致查询计划更改，因为数据是用不同的页数和每页不同的行数存储的。

 

总结

1. 本文来基于白皮书<Data Compression: Strategy, Capacity Planning and Best Practices>的简译和总结。此白皮书是基于SQL Server 2008的。

2. 数据压缩是一个被低估SQL Server技术，个人认为很有必要将之做为标准化最佳实践之一。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Joe-T/p/5666756.html