在所有表和索引上启用行级压缩

我刚刚使用SQL Server工具进行压缩后使用a_hardin-splattne脚本进行testing。 testing失败，因为有几个索引没有被压缩。

“sysobjects”视图包括一些但不是全部的索引。 我们需要“sysindexes”来代替。 感谢aspfaq.com上匿名发布的海报，了解这个索引。 我们也想忽略用户定义的function。

 SELECT 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sysobjects WHERE type = 'U' -- all user tables UNION SELECT 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(i.id) + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sysindexes i inner join sysobjects o on o.name = OBJECT_NAME(i.id) WHERE (i.indid BETWEEN 1 AND 254) AND (i.Status & 64)=0 AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 AND NOT o.type in ('TF','FN') 

你可以使用这个简单的SQL脚本来创build另一个脚本来完成这个工作：

  select 'ALTER TABLE ' + name + ' REBUILD WITH (DATA_COMPRESSION = ROW)' + CHAR(13) + CHAR(10) + 'GO' from sysobjects where type = 'U' -- all user tables 

（我没有testing这个，但它应该工作。）

您可以在SQLServerBible站点上find更复杂的脚本（查找“db_compression procs”）。阅读作者的博客文章“整个数据库 – 数据压缩过程” 。

你也许应该看看也处理新表，所以你不需要定期运行这个批处理。 我详细介绍了一个在这篇博文中自动压缩新表的方法。

我还要提到在重build之前应该检查表是否被压缩。

另外，小心使所有的东西都被压缩。 数据在内存中压缩， 每次访问时都解压缩。 对于有很多更改和内存驻留数据的OLTP系统，压缩并不适合，因为您将消耗更多的CPU而无法获得IO。 对于偶尔读取的数据（如数据仓库）来说，这样做更合适，因为您可以针对额外的CPU减lessIO的权衡。 压缩是数据仓库function，而不是OLTPfunction。 不知道这是否适用于你，但值得指出，以及其他人阅读线程。

还有一点 – 可能是你没有从压缩中获得显着的收益，所以这是不值得的。 在使用sp_estimate_data_compression_savings stored-proc之前检查压缩增益的最佳实践。

谢谢

我晚了一点，但是这里有一个使用DMV的版本，而不是弃用的系统表，并允许任意的模式名称。 它启用或禁用当前数据库中所有堆，聚簇索引和非聚簇索引（包括所有分区表）上的行或页面压缩：

 -- Enables or disables compression on all tables in the database DECLARE @Compression NVARCHAR(4) = 'PAGE' -- NONE, ROW or PAGE , @Cmd NVARCHAR(MAX) = ''; -- Clustered indexes, heaps SELECT @Cmd += ' ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');' FROM sys.schemas s INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id] INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id] WHERE p.[data_compression_desc] <> @Compression AND p.index_id IN (0, 1) AND NOT EXISTS ( SELECT 1 FROM sys.partitions d WHERE d.[object_id] = p.[object_id] AND d.index_id = p.index_id AND d.[partition_number] > p.[partition_number] ); -- Nonclustered indexes SELECT @Cmd += ' ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');' FROM sys.schemas s INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id] INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id] INNER JOIN sys.indexes i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id WHERE p.[data_compression_desc] <> @Compression AND p.index_id > 1 AND NOT EXISTS ( SELECT 1 FROM sys.partitions d WHERE d.[object_id] = p.[object_id] AND d.index_id = p.index_id AND d.[partition_number] > p.[partition_number] ); -- Review commands SELECT @Cmd; -- Run commands --EXEC sp_executesql @Cmd;