SQL Server SP为我们带来了vardecimal功能这项功能使得原来定长的decimal数据在数据文件中以可变长的格式存储据称这项功能可以为典型的数据仓库节省%的空间而SQL Server 在这一基础上又进一步增强了数据压缩功能SQL Server 现在支持行压缩和页面压缩两种选项数据压缩选项可以在以下对象上启用
未创建聚簇索引的表
创建聚簇索引的表
非聚簇索引(对表设置压缩选项不会影响到该表上的非聚簇索引因此聚簇索引的压缩需要单独设置)
索引视图
分区表和分区索引中的单个分区
为什么需要数据压缩
首先可能需要讨论的问题就是为什么在存储成本不断降低的今天微软还要煞费苦心地在SQL Server中实现并且不断改进数据压缩技术呢?
尽管存储成本已经不再是传统意义上的首要考虑因素但是这并不代表数据库尺寸不是一个问题因为数据库尺寸除了会影响到存储成本之外还极大地关联到管理成本和性能问题
首先我们来讨论为什么会有管理成本的问题?因为数据库需要备份数据库的尺寸越大那么备份时间就会越长当然另外一点就是消耗的备份硬件成本也会随之提高(包括需要的备份介质成本和为了满足备份窗口而需要更高级的备份设备带来的采购成本)还有一种管理成本就是数据库的维护成本例如我们经常需要完成的DBCC任务数据库尺寸越大我们就需要更多的时间来完成这些任务
接着我们再看看性能问题SQL Server在扫描磁盘读取数据的时候都是按照数据页为单位进行读取的因此如果一张数据页中包含的数据行数越多SQL Server在一次数据页IO中获得的数据就会越多这样也就带来了性能的提升
最后考虑存储的成本按照原先SQL Server SP中vardecimal的压缩数据为例%的空间节省也就意味着%的存储成本而按照SQL Server 当前放出的测试数据采用新的数据压缩技术可以达到XX的存储率再加上如果企业要考虑容灾而增加的存储空间这样节省的存储硬件成本也将是想当可观的
如何使用数据压缩
SQL Server 中的压缩选项可以在创建表或索引时通过Option进行设置例如 CREATE TABLE TestTable (col int col varchar()) WITH (DATA_COMPRESSION = ROW);
如果需要改变一个分区的压缩选项则可以用以下语句 ALTER TABLE TestTable REBUILD PARTITION = WITH (DATA COMPRESSION = PAGE);
如果需要为分区表的各个分区设置不同的压缩选项可以使用以下的语句(SQL Server 可以对不同的分区使用不同的压缩选项这一点对于数据仓库应用是非常重要的因为数据仓库的事实表通常都会有一个或数个热分区这些分区中的数据经常需要更新为了避免数据压缩给这些分区上的数据更新带来额外的处理载荷可以对这些分区关闭压缩选项) CREATE TABLE PartitionedTable (col int col varchar())
ON PS (col)
WITH (
DATA_COMPRESSION = ROW ON PARTITIONS()
DATA_COMPRESSION = PAGE ON PARTITION( TO ));
如果是为某个索引设置压缩选项的话可以使用
CREATE INDEX IX_TestTable_Col ON TestTable (Col) WITH (DATA_COMPRESSION = ROW);
如果是修改某个索引的压缩选项可以使用 ALTER INDEX IX_TestTable_Col ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);
SQL Server 同时还提供了一个名为sp_estimate_data_compression_savings存储过程帮助DBA估计激活压缩选项后对象尺寸
数据压缩是怎样工作的
对于行压缩SQL Server 采用以下三种方法来节省存储空间
减少了与记录相关联的元数据开销此元数据为有关列列长度和偏移量的信息在某些情况下元数据开销可能大于旧的存储格式
它对于数值类型(例如integerdecimal和float)和基于数值的类型(例如datetime和money)使用可变长度存储格式
它通过使用不存储空字符的可变长度格式来存储定长字符串
对于页面压缩SQL Server 则是在一张数据页面上依次采用
行压缩
前缀压缩
字典压缩
配置数据压缩功能需要注意的
尽管SQL Server 的数据压缩功能非常有价值但是仍然需要注意一些问题
数据压缩功能仅在企业版和开发版中可用
数据压缩可以让一张数据页存储更多的数据行但是并不能改变单行数据最长字节这一限制
在一张已经设置了数据压缩的表上创建聚簇索引时聚簇索引默认继承原表上的压缩选项
在未设置聚簇索引的表上设置页面压缩时只有以下情况才会获得页面压缩的实际效果
数据使用BULK INSERT语法添加到表中
数据使用INSERT INTO WITH (TABLOCK)语法添加到表中
执行带有页面压缩选项的ALTER TABLE REBUILD命令
在未设置聚簇索引的表上更改压缩选项会导致该表上所有非聚簇索引都需要重建因为这些非聚簇索引指向的数据行地址已经都发生了改变
在改变压缩选项时所需要的临时空间大小与创建索引是所需要的空间是一样的因此对于分区表我们可以逐个分区设置压缩选项来减少临时空间的需求压力
由于SQL Server 中数据压缩技术其实是SQL Server SP中vardecimal技术的一个超集因此设置了数据压缩后就没有必要保留vardecimal了当然SQL Server 为了保持向后兼容性在当前版本中仍然保留了vardecimal但是SQL Server 的下一个版本及可能就会弃用vardecimal选项因此做了这些设置的数据库应该尽早改变到数据压缩设置下
SQL Server 的压缩选项是工作在存储引擎层的对于SQL Server的其他部件来说这一特性是透明的因此当我们用BULK LOAD的方式将外面的数据导入SQL Server时会显着的增加CPU的工作载荷同时将以压缩的数据表导出到外部文件时可能会消耗比原来多很多的空间
