Сжатие баз данных 1С: Как оптимизировать хранение и избежать ошибок?

Объемы данных, которые требуются компаниям ежедневно для анализа и прогнозирования своей деятельности, постоянно растут. Поэтому одним из вполне логичных действий является сжатие баз данных, в частности – одного из самых популярных семейств ПО для управления бизнесом и ведения учета – 1С. Подобная процедура позволяет уменьшить размер хранимых баз данных, снизить нагрузку на дисковую подсистему, а также избежать необходимости приобретения новых дисковых емкостей.

Однако на практике, когда 1С работает с базами данных, необходимо приложить дополнительные усилия для компрессии информации. Дело в том, что при реструктуризации новые таблицы и индексы оказываются несжатыми. А реструктуризация может быть вызвана разными операциями – например, обновление на новый релиз конфигурации или проведение полного тестирования и исправления информационной базы. Это связано с тем, что параметр компрессии указывается отдельно для каждого индекса и таблицы.

Решение вопроса для MS SQL Server

Чтобы обеспечить перманентное сжатие всей базы данных, необходимо отследить моменты, когда создается новая таблица или индекс и присвоить данному объекту параметр «сжатие» до начала переноса данных платформой 1С. Для этого необходимо использовать DDL-триггер, причем лучше сразу для всего сервера, а не для отдельной базы данных – это больше соответствует лицензионной политике 1С.

Чтобы выполнить сжатие таблицы нужно выполнить команду

ALTER TABLE [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE). 

В случае с индексом все происходит точно также:

ALTER INDEX [имя индекса] ON [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE). 

Поэтому триггеры будут иметь примерно следующий вид:

CREATE TRIGGER [data_compression]
ON ALL SERVER
AFTER CREATE_TABLE
AS
...текст триггера

CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX
AS
...текст триггера

Пример реализации

Чтобы получить большую гибкость в настройках, можно также создать отдельную базу данных (назовем ее CompressionSettings), которая будет хранить информацию о тех база данных на сервере, которые не подлежат сжатию, а также содержать таблицу с логом для мониторинга работы триггеров. Для этого можно использовать следующий код:

CREATE TABLE [dbo].[Databases](
[name] [nvarchar](100) NULL,
[active] [int] NULL) ON [PRIMARY]
GO
CREATE TABLE [dbo].[trace](
[text] [nvarchar](max) NULL,
[DatabaseName] [nvarchar](max) NULL,
[DateTime] [datetime] NULL) ON [PRIMARY]

Ниже приведены примеры триггеров для индексов и для таблиц, которые уже были проверены в работе:

CREATE TRIGGER [data_compression] ON ALL SERVER
AFTER CREATE_TABLE
AS
DECLARE @SchemaName nvarchar(150),

@ObjectName nvarchar(150),
@DatabaseName nvarchar(150),
@cmd nvarchar(150)

--Получим имя схемы из выполняемой команды CREATE TABLE
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')
--Получим имя таблицы
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')

--Получим имя базы
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
--Сформируем из полученных данных требуемую команду на установку признака сжатия для таблицы
set @cmd = 'ALTER TABLE [' + @DatabaseName + '].[' + @SchemaName + '].[' + @ObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'

--Теперь проверяем настройки – если базы нет в таблице CompressionSetting.dbo.Databases с признаком Active = 1, то выполняем команду, иначе игнорируем
IF NOT EXISTS (SELECT  1 AS Expr1
   FROM CompressionSetting.dbo.Databases AS T
   WHERE (name = @DatabaseName) AND Active = 1)

BEGIN
 INSERT INTO CompressionSetting.dbo.trace (text, DatabaseName, DateTime) SELECT @cmd, @DatabaseName, GETDATE()

 EXEC (@cmd)
END
ELSE
BEGIN
 PRINT 'TEST'

END

CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX

AS
DECLARE @SchemaName nvarchar(150),
@ObjectName nvarchar(150),
@TargetObjectName nvarchar(150),

@DatabaseName nvarchar(150),
@cmd nvarchar(150)
--Получим имя схемы из выполняемой команды CREATE INDEX
SET @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(150)')

--Получим имя индекса
SET @ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(150)')
--Получим имя таблицы
SET @TargetObjectName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(150)')

--Получим имя базы
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(150)')
--Сформируем из полученных данных требуемую команду на установку признака сжатия для индекса
set @cmd = 'ALTER INDEX [' + @ObjectName + '] ON [' + @DatabaseName + '].[' + @SchemaName + '].[' + @TargetObjectName + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'

--Теперь проверяем настройки – если базы нет в таблице CompressionSetting.dbo.Databases с признаком Active = 1, то выполняем команду, иначе игнорируем
IF NOT EXISTS (SELECT  1 AS Expr1
            FROM CompressionSetting.dbo.Databases AS T
            WHERE (name = @DatabaseName) AND Active = 1)

BEGIN
    INSERT INTO CompressionSetting.dbo.trace (text, DatabaseName, DateTime) SELECT @cmd, @DatabaseName, GETDATE()

    EXEC (@cmd)
END
ELSE
BEGIN
    PRINT 'TEST'

END

Сжатие имеющихся баз данных

Как вы понимаете, триггеры ловят момент создания новой таблицы или индекса. А это значит, что они будут эффективны только для новых таблиц и индексов, но для компрессии уже существующих баз данных необходимо применить дополнительные меры. В качестве наиболее оптимального варианта можно рекомендовать заново создать базу данных через dt-файл. При этом она будет минимального размера, а время загрузки БД со сжатием будет практически таким же, как в обычном режиме. Также существует возможность выполнить полную реструктуризацию базы данных или написать скрипт для перебора всех таблиц и индексов, но это занимает длительное время, а также требует последующего применения SHRINK DATABASE для уменьшения раздувшейся базы данных.