数据压缩:简要

1.决定压缩哪些对象

创新互联建站于2013年创立，先为株洲等服务建站，株洲等地企业，进行企业商务咨询服务。为株洲企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

通过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的大小 (基于白皮书的预估值)

• C = 重建聚集索引所需CPU时间

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

• Online vs. Offline:

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

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

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

• 表压缩操作的顺序:

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

• SORT_IN_TEMPDB= ON or OFF:

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

压缩操作副作用:

• 压缩操作包括重建操作，所以会移除表或索引上的碎片。

• 压缩堆表时，如果有非聚集索引存在，则：当ONLINE=OFF，索引重建是串行操作，ONLINE=ON，索引重建是并操作。

4.维护压缩数据

新插入数据的压缩方式

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

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

更新和删除已压缩的行

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

辅助数据结构的行为   

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

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

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和创建聚集索引的顺序考虑：

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

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

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

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

PS：分区表Merger操作规则

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

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

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

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

总结

1. 本文来基于白皮书的简译和总结。此白皮书是基于SQL Server 2008的。

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