数据压缩（Data Compression）

最新推荐文章于 2024-04-02 09:38:31 发布

coujiyu6562

最新推荐文章于 2024-04-02 09:38:31 发布

阅读量659

点赞数

SQL Server 2008数据压缩是对存储和性能优势的加强。减少数据库占用的磁盘空间量将减少整体数据库文件存储空间，在下列方面增加吞吐量。

更好的IO利用率，因为每个页面可以读取更多数据。
更好的内存利用率，因为缓冲区缓存中可以保存更多数据
减少页面闩锁，因为每个页面上可以包含更多数据。

磁盘空间变得越来越便宜。然后，实现高性能的数据库系统需要高性能的磁盘系统或者SAN或者NAS，而它们并不便宜。另外，还需要额外存储具有高可用性、备份、QA和测试环境。总之，这降低了SQL Server 2008的总开销，让它更有竞争力。

在实际数据压缩之前，必须考虑I/O和CPU之间的平衡。压缩和解压缩数据需要CPU处理工具，因此不推荐与CPU绑定的系统。然而，它会受益于与I/O绑定的系统。第二，请记住，访问压缩数据需要CPU处理工作，如果数据是高度选择的，就会出现CPU系能补偿。数据压缩对于旧数据和不经常查询的数据更有意义。例如，有一个很大的分区表，可以考虑不经常查询的旧分区，而不压缩高度易失的分区。用户可以压缩易失数据，但必须综合考虑成本与对数据库工作负责的好处，以及硬件支持它实现最大吞吐量这个目标的性能。

注意：数据压缩只在SQL Server 2008企业版和开发人员版中可用。

可以对下列数据库对象进行数据压缩

表（但不是系统表）
群集索引
非群集索引
索引视图
分区表和索引，其中每个分区都可以有不同的压缩设置

行压缩

SQL Server 2008实现行数据压缩和页面数据压缩。数据压缩率取决于架构和数据分布，压缩以可变格式存储固定值数据类型——也就是说，包含1B值的4B列可以压缩为1B。包含1B值的1B列不能压缩，虽然NULL或者0值不占字节。压缩的确需要几位元数据系统开销来存储每个值。例如，保存1B值的Interger数据类型列的压缩率为75%。要使用行压缩创建一个新的压缩表，可以使用CREATE TABLE 命令，如下所示。

USE AdventureWorks
GO
CREATE TABLE dbo.AddressType_Compression_Row
(
AddressTypeID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(10) NOT NULL,
Rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
ModifiedDate DATETIME NOT NULL
)WITH(DATA_COMPRESSION=ROW)
GO

要改变表的压缩设置，可以使用ALTER TABLE 命令。

ALTER TABLE dbo.AddressType_Compression_Row REBUILD
WITH(DATA_COMPRESSION=ROW)

页面压缩

页面压缩包括行压缩，并实现其他两种压缩操作。

前缀压缩：对于每个页面和每列来说，前缀值是相同的，它可用来减少存储需求。这个值存储在每个页面的压缩信息（CI）结构中。重复的前缀值由对存储在CI中的前缀的引用所取代。
字典压缩：搜索页面中的重复值，这被对CI的引用所取代。

为新表启动页面压缩时，将对新插入的行执行行压缩，直到页面满为止，然后执行页面压缩。如果之后页面中还有空间可以保存其他新插入的行，就可以插入其他行并压缩它们：如果没有，新插入的行就会转到下一个页面上。压缩填充表时，将重构索引。使用页面压缩时，使用行压缩来压缩索引的非叶子页。要使用页面压缩创建新的压缩表，可以使用下面的命令。

CREATE TABLE dbo.AddressType_Compression_Page
(
AddressTypeID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(10) NOT NULL,
Rowguid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
ModifiedDate DATETIME NOT NULL
)WITH(DATA_COMPRESSION=Page)
GO

要改变表的压缩设置，可以使用ALTER TABLE命令。
ALTER TABLE dbo.AddressType_Compression_Page REBUILD
WITH(DATA_COMPRESSION=Page)

而且，在分区表或者索引中，可以对单个分区进行压缩或者改变压缩。下面的代码是对分区表和索引进行压缩的实例。

USE AdventureWorks
GO
CREATE PARTITION FUNCTION TableCompression(INT)
AS RANGE RIGHT
FOR VALUES(1,10000,20000,40000,50000)
GO

CREATE PARTITION SCHEME KeyRangePS
AS
PARTITION TableCompression
TO([DEFAULT],[DEFAULT],[DEFAULT],[DEFAULT],[DEFAULT],[DEFAULT])
GO

CREATE TABLE PartitionTable
(
KeyID int,
[Description] varchar(30)
)
ON KeyRangePS(KeyID)
WITH(DATA_COMPRESSION=ROW ON PARTITIONS(1,2),DATA_COMPRESSION=Page ON PARTITIONS(3 TO 6))

GO

CREATE INDEX IX_PartTabKeyID
ON PartitionTable(KeyID)
WITH(DATA_COMPRESSION=ROW ON PARTITIONS(1),DATA_COMPRESSION=PAGE ON PARTITIONS(2 TO 6))
GO

压缩分区表上的表分区操作有如下行为：

划分分区：这两个分区都继承原来的分区设置。
合并分区：合成分区继承目标分区的压缩设置
切换分区：分区和要切换的表的压缩设置必须匹配。
删除分区群集索引：表保留压缩设置。

另外，通过选项要进行数据压缩的表或者索引，可以再SQL SERVER 2008 Management Studio的”对象资源管理器“中管理数据压缩。

如下步骤来完成：

产生出来的脚本如下：

USE [AdventureWorks]
ALTER TABLE [Person].[CountryRegion] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)

估计节省的空间

在启动数据压缩之前，可以估计压缩开销节省的空间。例如，如果有一行超过4KB，并且整个值精度总是使用数据类型，就没有节省多少压缩空间。sp_estimate_data_compression_savings存储过程在tempdb中创建一个示例数据，估计压缩节省的空间，返回估计的表和示例节省的空间。它可以评估表、群集索引、非聚集索引、索引视图以及表和索引分区的页面压缩或者行压缩节余。而且，这个存储过程还可以评估压缩表、索引或者分区在非压缩状态下的大小。该存储过程实现的开销节余计算，与以上图所示的”数据压缩向导"在单击“计算”按钮时执行的计算是相同的。

select * from sys.dm_db_index_operational_stats (DB_ID('AdventureWorks'),OBJECT_ID('arinvt01'),default,default)

返回结果信息说明:

index_id 标示对象

监控数据压缩

对于在SQL Server 2008实例级监控数据压缩来说，在SQL Server：Access Method 对象中有两个计数器可用：

Page Compression attempts/sec计算每秒进行页面压缩尝试的次数。
Page Compressed/sec 计算每秒压缩的页面数。

而且，sys.dm_db_index_operational_stats 动态管理功能包含page_compression_attempt_count 和page_compression_success_count。另外，sys.dm_db_index_physical_stats动态管理功能包含compressed_page_count,它显示每个对象和每个分区压缩的页面数。

要标识数据库中压缩的对象，可以查看sys.Partitions 目录视图的data_compression 列（0=无，1=行，2=页面）。另外，从SQL Server 2008 Management Studio 的'对象资源管理器"中，选择表并右击，然后从弹出菜单中选择“存储”|“管理压缩”，打开“数据压缩向导”对话框。关于数据压缩的更多信息，请参考SQL Server 2008联机丛书中的“创建压缩表和索引”这一主题。

数据压缩需要考虑的事项