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

我刚刚使用 Works With SQL Server Tool 在使用 a_hardin-splattne 脚本压缩后进行了测试。测试失败，因为几个索引没有被压缩。

“sysobjects”视图包括一些但不是全部的索引。我们需要“sysindexes”来代替。感谢aspfaq.com上的匿名发帖人提供此索引见解。我们还想忽略用户定义的函数。

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 脚本来创建另一个应该完成这项工作的脚本：

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

（我没有对此进行测试，但它应该可以工作。）

您可以在SQLServerBible 站点上找到更复杂的脚本（查找“db_compression procs”。）阅读作者的博客文章“Whole Database - Data Compression Procs”。

顺便说一句，要小心使所有内容都被压缩。数据在内存中被压缩，每次访问时都会解压缩。对于具有大量更改和内存驻留数据的 OLTP 系统，压缩是不合适的，因为您将消耗更多 CPU 而不会增加 IO。对于偶尔读取的数据，例如数据仓库，它更合适，因为您可以在减少 IO 与额外 CPU 之间做出很大的权衡。压缩是一种数据仓库功能，而不是 OLTP 功能。不确定这是否适用于您，但值得指出以防万一，以及其他阅读该主题的人。

另一点 - 可能是您没有从压缩中获得显着收益，因此不值得转出。在启用使用 sp_estimate_data_compression_savings 存储过程之前检查压缩增益的最佳实践。

谢谢

您可能还应该考虑处理新表，因此您不需要定期运行此批处理。我在这篇博文中详细介绍了一种自动压缩新表的方法。

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

我参加聚会有点晚了，但这是一个使用 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;