Включить сжатие на уровне строк для всех таблиц и индексов

Я только что использовал инструмент 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".) Прочтите сообщение в блоге автора «Вся база данных - процессы сжатия данных».

Кроме того, будьте осторожны с возможностью сжатия всего. Данные сжимаются в памяти и распаковываются. каждый раз, когда к нему обращаются. Для системы OLTP с большим количеством изменений и резидентными данными в памяти сжатие не подходит, поскольку вы сжигаете больше ЦП без увеличения количества операций ввода-вывода. Для данных, которые время от времени читаются, например, хранилища данных, он гораздо более подходит, потому что вы получаете большой компромисс между сокращением операций ввода-вывода и дополнительным процессором. Сжатие - это функция хранилища данных, а не функция 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;