在 SQL Server 中为 Workflow Manager 工作空间使用数据压缩

使用数据压缩

利用表和索引的行与页面压缩,可以通过减少数据库大小来节省存储空间。数据压缩的缺点是会增加 CPU 使用率,因为必须对数据进行压缩并在访问时解压缩。不能将数据压缩用于系统表,而且只有企业版和开发版的 SQL Server 2012 支持数据压缩。

可针对下列内容配置数据压缩:

可用于 SQL Server 2012 的数据压缩形式有三种:行级压缩、Unicode 压缩和页面级压缩。

有关详细信息,请参阅MSDN 库中的堆(不含聚类索引的表)

行级压缩

行级压缩通过为长度固定的数据使用更为有效的存储格式来实现。行级压缩使用下列策略节省空间:

虽然没有减少数据使用的空间量,但行级压缩不会带来页面级压缩的存储改善。行级压缩的优势在于它需要的 CPU 使用率比页面级压缩更少。可使用以下语法通过行级压缩来压缩表:

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

例如,要通过行压缩来重新构建 WMXDB 数据库的 wmx.TableA 表的所有分区,可使用以下查询:

USE [WMXDB]
ALTER TABLE [wmx].[TableA] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)

使用以下语法通过行级压缩来配置索引:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

有关行级压缩的详细信息,请参阅 MSDN 库中的行压缩实现

Unicode 压缩

利用 Unicode 压缩,数据库引擎可以压缩存储在页面压缩对象或行压缩对象中的 unicode 值。可将 Unicode 压缩用于长度固定的 nchar(n) 和 nvarchar(n) 数据类型。在启用行和页面压缩后,会在适当时自动使用 Unicode 压缩。

有关 Unicode 压缩的详细信息,请参阅 MSDN 库中的Unicode 压缩实现

页面级压缩

页面级压缩通过对重复值和通用前缀仅存储一次,然后在表中其他位置参考这些值的方法来压缩数据。当将页面压缩应用于表时,也将应用行压缩技术。页面级压缩使用以下策略:

页面压缩的效益取决于所压缩数据的类型。涉及许多重复值的数据相比于由更多唯一值进行填充的数据,前者的压缩程度更高。使用以下常用语法来应用页面级压缩:

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)

例如,要通过页面压缩来重新构建 WMXDB 数据库的 wmx.TableA 表的所有分区,可使用以下查询:

USE [WMXDB]
ALTER TABLE [wmx].[TableA] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)

使用以下语法通过页面级压缩来配置索引:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

有关页面级压缩的详细信息,请参阅 MSDN 库中的页面压缩实现

如果表或索引已分区,则可基于每个分区配置压缩。如果使用 ALTER PARTITION 语句拆分分区,则新分区将继承原始分区的数据压缩属性。如果对两个分区进行合并,则生成的分区将具有目标分区的压缩属性。虽然通过压缩的确可以在页面中存储更多的行,但并不会更改表或索引的最大行大小。如果最大行大小和压缩开销超出 8,060 字节,则无法启用表以进行压缩。索引的默认压缩设置为“无”,并且必须在创建索引时指定索引压缩属性。非聚类索引不会继承表的压缩属性,但堆中创建的聚类索引会继承该堆的压缩状态。数据压缩仅在源中应用,因此在从压缩源中导出数据时,SQL Server 将以未压缩的行格式输出数据。将未压缩的数据导入到启用了压缩的目标表中,会对数据进行压缩。

有关数据压缩的详细信息,请参阅 MSDN 库中的数据压缩

可通过使用之前的 Transact-SQL 语句或者在 SQL Server Management Studio 中通过对表或索引使用数据压缩向导来配置压缩。可使用数据压缩向导添加和移除压缩。要使用数据压缩向导更改表和索引的压缩设置,请执行以下步骤:

步骤:
  1. 在 SQL Server Management Studio 中,右键单击要压缩的表或索引,单击存储,然后单击管理压缩
  2. 欢迎使用数据压缩向导 页面中单击下一步
  3. 选择压缩类型 页面中,可以选择对所有分区使用相同的压缩类型,也可以基于每个分区在“行”、“页面”和“无”中进行选择。单击计算以确定当前空间使用率和压缩后使用率之间的差值。
  4. 选择输出选项 页面中,选择是创建脚本、立即执行操作还是根据计划执行选项。单击下一步,然后单击完成完成向导。

有关数据压缩向导的详细信息,请参阅 MSDN 库中的数据压缩向导帮助

估算压缩程度

确定某一对象压缩效益的最佳方法是使用 sp_estimate_data_compression_savings 存储过程。压缩效益取决于诸如数据唯一性等因素。sp_estimate_data_compression_savings 存储过程仅在企业版 SQL Server 2012 中可用。

存储过程的语法如下:

sp_estimate_data_compression_savings[ @schema_name = ] 'schema_name', [ @object_name = ]
'object_name', [@index_id = ] index_id,[@partition_number = ] partition_number,
[@data_compression = ] 'data_compression'

例如,要配置对 WMXDB 数据库中的 wmx.TableA 表进行行压缩的压缩效益的预估值,可执行以下 Transact-SQL 语句:

USE WMXDB;
GO
EXEC sp_estimate_data_compression_savings 'wmx', 'TableA', NULL, NULL,
'ROW';
GO

要配置对该表进行页面压缩的压缩效益的预估值,可执行以下 Transact-SQL 语句:

USE WMXDB;
GO
EXEC sp_estimate_data_compression_savings 'wmx', 'TableA', NULL, NULL,
'PAGE';
GO

有关如何估计压缩节约值的详细信息,请参阅 MSDN 库中的 sp_estimate_data_compression_savings (Transact-SQL)。

可将以下 SQL 脚本用于检查器用户表和索引,也可用此脚本来生成用于为这些表格和索引设置页面压缩的 SQL 语句。

/*---------------------------------------------------------------------
-- Verify WMX Schema Storage
---------------------------------------------------------------------*/
USE [wmxdb]
GO
/*---------------------------------------------------------------------
--List all tables
---------------------------------------------------------------------*/
SELECT USER_NAME(o.uid) [owner], o.name,o.id,o.type,o.status
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
/*---------------------------------------------------------------------
--List all indexes
---------------------------------------------------------------------*/
SELECT USER_NAME(o.uid) [owner], OBJECT_NAME(i.id) [table], i.name [index],o.type [type]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

/*---------------------------------------------------------------------
--Table page compression
--Example:
/*
ALTER TABLE WMX.JTX_JOBS_AOI 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
*/
---------------------------------------------------------------------*/
--Generate script to set table page compression:
SELECT 'ALTER TABLE ' + USER_NAME(o.uid) + '.' + o.name + ' REBUILD WITH (DATA_COMPRESSION = PAGE);' [TXTSQL]
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO

/*---------------------------------------------------------------------
--Index page compression
--Example:
/*
ALTER INDEX R125_pk 
ON WMX.JTX_JOBS_AOI
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
*/
---------------------------------------------------------------------*/
--Generate script to set index page compression:
SELECT 'ALTER INDEX ' + i.name + ' ON ' + USER_NAME(o.uid) + '.' + OBJECT_NAME(i.id) +
       ' REBUILD WITH ( DATA_COMPRESSION = PAGE );' [TXTSQL]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'wmx'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

压缩和 TDE

数据库文件的加密是在页面级别执行的。已加密数据库中的页面将在写入磁盘之前进行加密,并在读取到内存时进行解密。TDE 不会增加已加密数据库的大小。

有关详细信息,请参阅 MSDN 库中的 SQL Server 2012 - 透明数据加密 (TDE)

加密后的数据压缩要明显少于等效的未加密数据。如果使用 TDE 加密数据库,则备份压缩无法对备份存储进行显著压缩。

有关详细信息,请参阅 MSDN 库中的 SQL Server 2012 - 数据压缩

5/10/2014