☰

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

tag-icon • tag-icon sql-server-2008 compression
对所有表和索引启用行级压缩

Works With SQL Server 2008 测试的 (可选) 要求之一是在所有表和索引上启用行级压缩。我们有一个现有数据库，其中已创建了许多表和索引。有没有一种简单的方法可以在所有这些表和索引上启用压缩？

这是我根据 splattne 的建议最终制作的脚本。

select 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'   
from   sysobjects   where  type = 'U' -- all user tables
UNION
select 'ALTER INDEX [' + k.name + '] ON [' + t.name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'
from   sysobjects k
join sysobjects t on k.parent_obj = t.id
   where  k.type = 'K' -- all keys
    AND t.type = 'U' -- all user tables

答案1

我刚刚用 Works With SQL Server 工具测试了使用 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')

答案2

您可以使用这个简单的 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”。）阅读作者的博客文章“整个数据库 - 数据压缩过程”。

答案3

另外，启用压缩功能时要小心。数据在内存中压缩，然后解压缩每一个访问时。对于具有大量更改和内存驻留数据的 OLTP 系统，压缩并不合适，因为您会消耗更多 CPU 而没有获得 IO 增益。对于偶尔读取的数据（如数据仓库），它更合适，因为您会在减少 IO 和增加 CPU 之间做出很大的权衡。压缩是数据仓库功能，而不是 OLTP 功能。不确定这是否适用于您，但值得指出以防万一，也适用于阅读该帖子的其他人。

还有一点——压缩可能不会给你带来显著的收益，因此不值得这么做。最佳做法是在启用 sp_estimate_data_compression_savings 存储过程之前检查压缩增益。

谢谢

答案4

我来晚了，但这里有一个使用 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;

相关内容