Сжатие баз данных 1С:Предприятие в MS SQL Server
Тема сжатия баз данных 1С в настоящий момент довольно часто обсуждается. Достоинства сжатия известны – уменьшение размера базы данных, уменьшение нагрузки на дисковую подсистему и некоторое ускорение выполнения тяжелых операций чтения/записи. Из недостатков – небольшое увеличение нагрузки на процессоры сервера СУБД за счет расхода ресурсов на компрессию/декомпрессию данных. Но при использовании в качестве MSSQL и DB2 (за Oracle и PostgreSQL не скажу, т.к. не знаю) есть один «подводный камень» — при выполнении реструктуризации происходит декомпрессия новых таблиц и индексов. Происходить это может как при выполнении обновления конфигурации с изменением структуры метаданных, так и при выполнении тестирования и исправления ИБ (реиндексация пересоздает только индексы, а реструктуризация – и таблицы, и индексы). «Проблема» кроется в том, что признак сжатия устанавливается индивидуально для каждой таблицы и индекса.
- Описание
- Подробнее
Описание
Решение проблемы для MS SQL Server.
В течение долгого времени сжатие было доступно только в Enterprise версиях MS SQL Server, но начиная с SQL2016 Service Pack 1 эта функциональность была включена во всех редакциях, в том числе, бесплатной Express (релиз от 16.11.2016 — https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/).
Поймать момент создания новой таблицы или индекса возможно с помощью DDL-триггера, который вызывается сразу после выполнения CREATE TABLE/INDEX, но до начала переноса данных платформой 1С. Триггер данного типа можно создать как для конкретной ИБ, так и для всего сервера. Создание триггера для ИБ не вписывается в лицензионное соглашение 1С, поэтому лучше создавать для всего сервера, тем более что это позволит обслуживать сразу все базы на данном сервере.
Создание DDL-триггера для операции создания таблицы:
CREATE TRIGGER [data_compression]
ON ALL SERVER
AFTER CREATE_TABLE
AS
—Текст триггера
Создание DDL-триггера для операции создания индекса:
CREATE TRIGGER [index_compression]
ON ALL SERVER
AFTER CREATE_INDEX
AS
—Текст триггера
Для установки признака сжатия страниц для таблицы необходимо выполнить код:
ALTER TABLE [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
Для установки признака сжатия страниц для индекса необходимо выполнить код:
ALTER INDEX [имя индекса] ON [имя базы].[имя схемы].[имя таблицы] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
В общем-то, уже этого достаточно, чтобы создать работоспособный механизм, который не позволит платформе 1С создать новую таблицу или индекс без сжатия, но мне захотелось большей гибкости в настройках :).
Ниже приведу решение в том виде, как оно используется у нас. Это позволило автоматически применить сжатие для всех баз данных на сервере.
Создана служебная база CompressionSetting с двумя таблицами:
1) Databases — для хранения списка баз, которые сжимать НЕ требуется.
CREATE TABLE [dbo].[Databases](
[name] [nvarchar](100) NULL,
[active] [int] NULL
) ON [PRIMARY]
Из ~200 баз на сервере я внес в эту таблицу только одну – tempdb:
2) Trace – для мониторинга работы DDL-триггеров
CREATE TABLE [dbo].[trace](
[text] [nvarchar](max) NULL,
[DatabaseName] [nvarchar](max) NULL,
[DateTime] [datetime] NULL
) ON [PRIMARY]
Далее создал 2 DDL-триггера:
1) Для таблиц
CREATE TRIGGER [data_compression] ON ALL SERVER AFTER CREATE_TABLE AS DECLARE @SchemaName nvarchar(150), @ObjectName nvarchar(150), @DatabaseName nvarchar(150), @cmd nvarchar(500) --Получим имя схемы из выполняемой команды 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
2) Для индексов
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(500) --Получим имя схемы из выполняемой команды 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
Сразу после создания триггеров никаких изменений в размере баз, конечно, не произойдет. Для сжатия уже имеющихся баз можно воспользоваться следующими методами:
1) Написать скрипт для поочередного перебора таблиц и индексов и установки для них признака сжатия. Мне этот вариант не понравился, т.к. на сжатие больших таблиц требуется очень много времени, база при этом раздувается, а потом очень долго уменьшается через SHRINK DATABASE.
2) Выполнить полную реструктуризацию через «Тестирование и исправление» . По времени быстрее, но база также раздувается и ее потом придется уменьшать через SHRINK DATABASE.
3) Самый оптимальный вариант, на мой взгляд – пересоздать базу через dt-файл. При этом готовая база изначально будет минимального размера, а время загрузки базы со сжатием мало отличается от загрузки в обычном режиме.
Относительно допустимости применения подобного метода сжатия я задавал вопрос в партнерской конференции, на что был в итоге получен следующий комментарий Сергея Нуралиева:
«…Можно считать позицией то, что мы считаем, что использование этих возможностей должно быть или запрещено или разрешено, но с адекватной поддержкой (методологической или программной). Вариант использования их без надлежащей поддержки мы считаем неправильным, так как он приводит к проблемам в администрировании.»
Таким образом, не думаю, что предлагаемый мной подход должен использоваться массово.
Администратор, решившийся на это, должен четко понимать что он делает и какие последствия могут быть.
В
