Сжатие данных в базе данных средствами СУБД

На Инфостарте есть статья https://infostart.ru/public/114634/ про то, как навесить триггеры на создание таблиц, чтобы новые таблицы в БД создавались сжатыми. Для существующих баз предлагается загрузить базу из ДТ или сделать ТиИ с реструктуризацией. Предлагаю вашему вниманию скрипт, который сделает это для существующей БД средствами SQL.

Категория: Публикации 1С

Подробнее

Описание
Подробнее

Описание

Про плюсы и минусы сжатия можно почитать в оригинальной статье //sale.itcity.ru/public/114634/ или на msdn https://msdn.microsoft.com/ru-ru/library/cc280449(v=sql.120).aspx основным плюсом идет экономия ввода вывода и места на диске, основным минусом — существенное увеличение регламентных операций СУБД и небольшое увеличение нагрузки на процессор.

В management studio правой кнопкой мыши на базе, new query, вставляем текст, нажимаем execute (или F5):

DECLARE @Table_catalog NVARCHAR(128) 
DECLARE @Table_schema NVARCHAR(128) 
DECLARE @Table_name NVARCHAR(128) 
DECLARE @Index_Name NVARCHAR(128) 

DECLARE @cmd VARCHAR(4000) 

-- включение сжатия для таблиц
DECLARE TableNameCursor CURSOR
FOR
	SELECT Table_catalog, Table_schema, Table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
	ORDER BY Table_catalog, Table_schema, Table_name

OPEN TableNameCursor

FETCH NEXT FROM TableNameCursor INTO @Table_catalog, @Table_schema, @Table_name
WHILE @@fetch_status = 0
	BEGIN 
				
		PRINT @Table_catalog + '.' + @Table_schema +  '.' + @Table_name
		SET @cmd = 'ALTER TABLE [' + @Table_catalog + '].[' + @Table_schema + '].[' + @Table_name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
		EXEC (@cmd) 

		FETCH NEXT FROM TableNameCursor INTO @Table_catalog, @Table_schema, @Table_name 
	END 
CLOSE TableNameCursor 
DEALLOCATE TableNameCursor 

-- включение сжатия для индексов
DECLARE IndexCursor CURSOR
FOR
    SELECT  DB_NAME(), schemas.name, tables.name, indexes.name
    FROM sys.schemas as schemas inner join sys.tables as tables inner join sys.indexes as indexes on tables.object_id = indexes.object_id on schemas.schema_id = tables.schema_id
	ORDER BY schemas.name, tables.name, indexes.name;

OPEN IndexCursor

FETCH NEXT FROM IndexCursor INTO @Table_catalog, @Table_schema, @Table_name, @Index_Name
WHILE @@fetch_status = 0
	BEGIN 
				
		PRINT @Table_catalog + '.' + @Table_schema + '.' + @Table_name +  ': ' + @Index_Name

		SET @cmd = 'ALTER INDEX [' + @Index_Name + '] ON [' + @Table_catalog + '].[' + @Table_schema + '].[' + @Table_name + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
		EXEC (@cmd) 

		FETCH NEXT FROM IndexCursor INTO @Table_catalog, @Table_schema, @Table_name, @Index_Name
	END 
CLOSE IndexCursor 
DEALLOCATE IndexCursor 

-- делаем шринк базы - возвращаем свободное место на диск
SELECT @cmd=(
SELECT 'DBCC SHRINKDATABASE('''+ DB_NAME() + ''')'
)
EXEC (@cmd)

Скрипт выводит в раздел messages ход работы

Для того, чтобы результаты сохранились после реструктуризации, следует добавить триггеры из оригинальной статьи.

Для выключения сжатия надо запустить скрипт, заменив в нем DATA_COMPRESSION = PAGE на DATA_COMPRESSION = NONE

Каталог решений - Сжатие данных в базе данных средствами СУБД