Как разбить базу на файлы и не сойти с ума

Разбиение базы данных 1C на файлы и последующее сопровождение. Нюансы, грабли и прочее.

Суть вопроса

Файловые группы В прошлых статьях мы уже говорили о подходах к разработке и обслуживанию базы данных, которые позволяют использовать индексы произвольной структуры и даже секционирование для баз 1С. Казалось бы, тема исчерпана и для высоконагруженных баз может наступить светлое будущее, не смотря на то, что платформа 1С пока так и не поддерживает эти возможности из "коробки".

Однако, хотелось бы остановиться подробнее на такой теме как разбиение базы данных на отдельные файлы с помощью файловых групп. В статье про секционирование файловые группы уже использовались для секций, но там про них был сказано вскользь.

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

Для чего вообще может понадобиться разбивать базу данных на отдельные файлы? Самые распространенные кейсы:

Есть регистр сведений, в котором хранятся двоичные данные файлов. Необходимо вынести хранение файлов на отдельный диск / хранилище, чтобы освободить место на быстрых дисках.
Есть старые архивные таблицы, которые уже редко используются, но удалять данные нельзя. Почему бы такие таблицы также не перенести на отдельные диски, которые для этого и предназначены. Тем более такие файловые группы можно сделать только для чтения.
Ускорить бэкапирование базы, т.к. архивные файловые группы можно не бэкапировать каждый раз. Они ведь не меняются!
Улучшение производительности, за счет распределения файлов базы данных на отдельные носители.

Тему ускорения бэкапирования и производительности сейчас мы рассматривать не будем, но Вы можете прочитать об этом в публикации про секционирование. Сосредоточимся на описании настроек для файловых групп и их сопровождении. Все примеры ниже будут сделаны для SQL Server, но и для PostgreSQL это будет работать с некоторыми модификациями.

Стандартный подход

Любая база, будь то для 1С или любого другого приложения, поддерживает разбиение базы на несколько файлов (конечно, если это поддерживает СУБД). В контексте SQL Server это реализуется с помощью файловых групп.

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

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

Возьмем для примеров демобазу БСП и создадим в ней две новых файловых группы.

-- В примере имя базы данных на сервере СУБД имеет название "bsl"
USE [master]
GO
ALTER DATABASE [bsl] ADD FILEGROUP [FILEGROUP_2]
GO
ALTER DATABASE [bsl] ADD FILEGROUP [FILEGROUP_3]
GO

Но просто добавить файловые группы недостаточно. Еще нужно добавить файлы данных, для которых эти файловые группы будут задействованы.

-- Оставляем стандартные параметры инициализации файлов, для нашего примера это не критично.
-- Все файлы базы данных находятся в каталоге "D:DBs"

-- Добавляем файлы для файловых групп
ALTER DATABASE [bsl]
ADD FILE ( NAME = N'bsl_fg_2', FILENAME = N'D:DBssl_fg_2.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [FILEGROUP_2]
GO
ALTER DATABASE [bsl]
ADD FILE ( NAME = N'bsl_fg_3', FILENAME = N'D:DBssl_fg_3.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [FILEGROUP_3]
GO

Отлично, у нас есть две файловые группы "FILEGROUP_2" и "FILEGROUP_3", осталось их задействовать. Есть несколько основных вариантов:

Мы можем вручную изменить основную файловую группу базы и сделать реструктуризацию средствами 1С.
Мы можем пересоздать кластерный или другие индексы средствами T-SQL, указав для использования нужную файловую группу.
Ничего не делать.

По третьему варианту написано довольно много примеров в сети, поэтому рассмотрим только первые два пункта. Все примеры будем делать на регистре сведений "История адресных объектов", который на стороне базы представлен таблицей "_InfoRg4683" с несколькими индексами.

Вперед через реструктуризацию

И так, для начала установим основную файловой группой — одну из тех, что добавили выше.

Установка основной файловой группы скриптом

То же самое можно сделать через T-SQL.

USE [bsl]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'FILEGROUP_2')
ALTER DATABASE [bsl] MODIFY FILEGROUP [FILEGROUP_2] DEFAULT
GO

Кому как больше нравится.

Теперь нужно сделать реструктуризацию средствами платформы. Нормального способа вызвать ее для нашего случая нет, но мы можем:

Добавить временно реквизит в таблицу, а потом запустить реструктуризацию.
Полностью реструктуризировать базу через "Тестирование и исправление".

Оба варианта выглядят "не очень", и Вам повезет, если необходимость реструктуризации появится как-раз в этот момент для других задач. При использовании инструмента "Тестирование и исправление" Вы вообще переведете все таблицы и индексы в установленную файловую группу, поэтому этот случай мы вообще рассматривать не будем. А вот пример с добавлением временного реквизита — пожалуйста.

С помощью этих скриптов можно узнать как изменилась структура базы в части использования файловых групп. Вот какие изменения мы получили.

Таблица	Индекс	Файловая группа	Файл
_InfoRg4683	_InfoRg4683_ByDims_NNNNNNNNNNBN	FILEGROUP_2	D:DBssl_fg_2.mdf
_InfoRg4683	_InfoRg4683_ByResource4705_SNNNNNNNNNNBN	FILEGROUP_2	D:DBssl_fg_2.mdf
_InfoRg4683	_InfoRg4683_ByResource4706_SNNNNNNNNNNBN	FILEGROUP_2	D:DBssl_fg_2.mdf
_InfoRg4683	_InfoRg4683_ByMainFilter_NNNNNNNNNNNB	FILEGROUP_2	D:DBssl_fg_2.mdf

Таким образом, мы перевели таблицу регистра сведений "История адресных объектов" и все ее индексы в файловую группу "FILEGROUP_2".

Подведем итог по данному способу.

Плюсы:

Простота в настройке

Минусы:

Нужен вызов платформенной реструктуризации, что не всегда оптимально.
После реструктуризации надо обратно настраивать основную файловую группу.
Необходимость проводить реструктуризацию таблиц в разных файловых группах отдельно друг от друга, что не всегда возможно.

Вообщем, способ неэффективный, но требует минимальных действий на стороне СУБД.

Скриптуем

Более эффективный и гибкий подход — это перенос таблиц и индексов в другую файловую группу с помощью скриптов. Вот так будет выглядеть скрипт для переноса всех индексов регистра сведений "История адресных объектов" в третью файловую группу.

Перенос всех индексов таблицы в другую файловую группу

Фактически, нам нужно пересоздать индексы с указанием новой файловой группы. При этом, когда мы пересоздаем кластерный индекс таблицы, то все данные в ней переносятся в новую файловую группу.

USE [bsl]
GO

CREATE UNIQUE CLUSTERED INDEX [_InfoRg4683_ByDims_NNNNNNNNNNBN] ON [dbo].[_InfoRg4683]
(
[_Fld4684] ASC,
[_Fld4685] ASC,
[_Fld4686] ASC,
[_Fld4687] ASC,
[_Fld4688] ASC,
[_Fld4689] ASC,
[_Fld4690] ASC,
[_Fld4691] ASC,
[_Fld4692] ASC,
[_Fld4693] ASC,
[_Fld4694] ASC,
[_Fld4695] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [FILEGROUP_3] -- Задаем имя новой файловой группы
GO

CREATE NONCLUSTERED INDEX [_InfoRg4683_ByMainFilter_NNNNNNNNNNNB] ON [dbo].[_InfoRg4683]
(
[_Fld4684] ASC,
[_Fld4685] ASC,
[_Fld4686] ASC,
[_Fld4687] ASC,
[_Fld4688] ASC,
[_Fld4689] ASC,
[_Fld4690] ASC,
[_Fld4691] ASC,
[_Fld4692] ASC,
[_Fld4693] ASC,
[_Fld4695] ASC,
[_Fld4694] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [FILEGROUP_3] -- Задаем имя новой файловой группы
GO

CREATE UNIQUE NONCLUSTERED INDEX [_InfoRg4683_ByResource4705_SNNNNNNNNNNBN] ON [dbo].[_InfoRg4683]
(
[_Fld4697] ASC,
[_Fld4684] ASC,
[_Fld4685] ASC,
[_Fld4686] ASC,
[_Fld4687] ASC,
[_Fld4688] ASC,
[_Fld4689] ASC,
[_Fld4690] ASC,
[_Fld4691] ASC,
[_Fld4692] ASC,
[_Fld4693] ASC,
[_Fld4694] ASC,
[_Fld4695] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [FILEGROUP_3] -- Задаем имя новой файловой группы
GO

CREATE UNIQUE NONCLUSTERED INDEX [_InfoRg4683_ByResource4706_SNNNNNNNNNNBN] ON [dbo].[_InfoRg4683]
(
[_Fld4698] ASC,
[_Fld4684] ASC,
[_Fld4685] ASC,
[_Fld4686] ASC,
[_Fld4687] ASC,
[_Fld4688] ASC,
[_Fld4689] ASC,
[_Fld4690] ASC,
[_Fld4691] ASC,
[_Fld4692] ASC,
[_Fld4693] ASC,
[_Fld4694] ASC,
[_Fld4695] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [FILEGROUP_3] -- Задаем имя новой файловой группы

Для того, чтобы сгенерировать скрипты создания индексов, можно воспользоваться стандартными возможностями SQL Managment Studio по созданию скриптов для базы данных.

Все, теперь мы счастливые обладатели регистра сведений, который находится в дополнительной файловой группе. Смотрим итог с помощью этих скриптов.

Таблица	Индекс	Файловая группа	Файл
_InfoRg4683	_InfoRg4683_ByDims_NNNNNNNNNNBN	FILEGROUP_3	D:DBssl_fg_3.mdf
_InfoRg4683	_InfoRg4683_ByResource4705_SNNNNNNNNNNBN	FILEGROUP_3	D:DBssl_fg_3.mdf
_InfoRg4683	_InfoRg4683_ByResource4706_SNNNNNNNNNNBN	FILEGROUP_3	D:DBssl_fg_3.mdf
_InfoRg4683	_InfoRg4683_ByMainFilter_NNNNNNNNNNNB	FILEGROUP_3	D:DBssl_fg_3.mdf

Как итог, определим плюсы и минусы.

Плюсы:

Быстрый и эффективный способ работы с файловыми группами.
Нет необходимости каких-либо действий на стороне 1С.

Минусы:

Нет связи с платформой 1С, даже призрачной как в прошлом примере.

Конечно, предпочтительнее использовать этот способ, если Вы бережете время, нервы и деньги.

Сложности для 1С

Все выглядит просто, но есть нюансы.

Во-первых, лицензионное соглашение 1С запрещает так работать с СУБД, т.к. эти возможности недокументированы. Начиная использовать файловые группы, Вы должны осознавать риски нарушения этого соглашения. Минимальные последствия — это отказ в технической поддержке решений на платформе 1С.

Лицензионное соглашение

В пункте 65 лицензионного соглашения сказано следующее:

Лицензионное соглашение не позволяет использовать недокументированные фирмой "1С" средства для построения решений на платформе "1С:Предприятие". Это означает, что средства СУБД (или любые другие внесистемные средства) можно использовать только в том случае, если документация по продуктам линейки "1С:Предприятие" (включая 1С:ИТС) содержит явную рекомендацию использовать данное средство для решения данной задачи.

Во всех остальных случаях лицензионное соглашение позволяет использовать для построения решений только штатные средства платформы. В частности, можно обращаться к данным информационной базы только при помощи объектов "1С:Предприятия", специально предназначенных для работы с данными (запросы, справочники, документы и т. д.). Нельзя обращаться к данным информационной базы напрямую, минуя уровень объектов работы с данными "1С:Предприятия", например при помощи средств СУБД или при помощи внешних компонент, которые реализуют прямой доступ к СУБД. Это ограничение распространяется на любые действия с данными, в том числе на изменение их структуры, а так же на чтение или изменение самих данных информационной базы или служебных данных "1С:Предприятия".

Данное ограничение необходимо для обеспечения стабильности работы механизмов системы, осуществления поддержки и возможности перехода на новые версии "1С:Предприятия".

Вы должны четко понимать плюсы и минусы данного шага. Все, что Вы сделаете будет на Вашей совести!

Во-вторых, это усложнение сопровождения, т.к. при обновлении базы данных необходимо учитывать тот факт, что некоторые таблицы находятся в других файловых группах или дисковых носителях.

Зачем это учитывать? Например, у Вас в базе есть регистр сведений "Присоединенные файлы" (в базе представлен таблицей "_InfoRg2133"), в котором хранятся двоичные данные разнотипных документов. Для экономии места в основном хранилище данных был выполнен перенос этих документов в отдельную файловую группу. Файл данных для нее находится на отдельном диске.

Перенос данных документов в отдельную файловую группу

Платформа 1С хранит двоичные данные документов в LOB-типах данных (image или varbinary(max) в зависимости от версии платформы). Для переноса LOB-данных в отдельную файловую группу не обязательно переносить всю таблицу и индексы. Достаточно перенести только сами LOB-данные, указав основную файловую группу для таких типов. Именно так мы и сделаем в примере ниже.

-- Создаем таблицу с новыми настройками хранения LOB-данных,
-- при этом структура таблица полностью поторяет структуру исходной
-- таблицы регистра сведений "Присоединенные файлы"
CREATE TABLE dbo.Tmp__InfoRg2133
(
_Fld2134_TYPE binary(1) NOT NULL,
_Fld2134_RTRef binary(4) NOT NULL,
_Fld2134_RRRef binary(16) NOT NULL,
_Fld2135 image NOT NULL,
_Fld2683 numeric(7, 0) NOT NULL
)  ON [PRIMARY]
-- !!! Именно здесь необходимо указать файловую группу,
-- !!! в которой будут храниться двоичные данные документов
TEXTIMAGE_ON FILEGROUP_3
GO

-- Для оптимизации устанавливаем уровень эскалации блокировок = Таблица
ALTER TABLE dbo.Tmp__InfoRg2133 SET (LOCK_ESCALATION = TABLE)
GO

-- Переносим данные из старой таблицы в новую
IF EXISTS(SELECT * FROM dbo._InfoRg2133)
EXEC('INSERT INTO dbo.Tmp__InfoRg2133 (_Fld2134_TYPE, _Fld2134_RTRef, _Fld2134_RRRef, _Fld2135, _Fld2683)
SELECT _Fld2134_TYPE, _Fld2134_RTRef, _Fld2134_RRRef, _Fld2135, _Fld2683 FROM dbo._InfoRg2133 WITH (HOLDLOCK TABLOCKX)')
GO

-- Удаляем старую таблицу
DROP TABLE dbo._InfoRg2133
GO

-- Новой таблице присваиваем то же самое имя, что было у исходной
EXECUTE sp_rename N'dbo.Tmp__InfoRg2133', N'_InfoRg2133', 'OBJECT'
GO

-- Далее создаем недостающие индексы. Все аналогично исходной таблице
CREATE UNIQUE CLUSTERED INDEX _InfoRg2133_ByDims_R ON dbo._InfoRg2133
(
_Fld2683,
_Fld2134_TYPE,
_Fld2134_RTRef,
_Fld2134_RRRef
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Теперь все LOB-данные перенесены в файловую группу "FILEGROUP_3". При необходимости основной файл данных, где ранее хранились перемещенные документы, можно уменьшить операцией Shrink. В нашем случае мы это рассматривать не будем.

P.S. Не забудьте сделать бэкап перед такими операциями.

P.P.S. Этот скрипт не является готовым решением. Его можно улучшить за счет различных проверок, транзакций и т.д.

Все отлично сработало, мы освободили 1 ТБ данных в основном хранилище. НО! В один прекрасный день разработчики 1С внесли изменения в систему, добавив новый ресурс к регистру сведений "Присоединенные файлы". В тестовых базах все проверено, ведь там никто не держит полную копию рабочей базы. Изменение ушло в релиз, но при развертывании возникли следующие проблемы:

В хранилище, где находится основной файл базы данных, не хватило места при выполнении реструктуризации. Ведь в процессе платформа создает таблицу заново в основной файловой группе базы, то есть в "PRIMARY". Получается, что платформа создала таблицу и "перегоняла" в нее данные из файловой группы "FILEGROUP_3", пока не заполнила диск.
Из-за прерванной с ошибкой реструктуризации в базе данных (в файловой группе "PRIMARY") останется таблица "_InfoRg2133NG", в которую платформа и "переливала" данные. Чтобы исправить ситуацию и освободить место ее нужно будет удалить вручную. Не будем останавливаться на описании процесса реструктуризации, лишь отметим, что платформа добавляет к пересоздаваемым таблицам постфикс "NG". Так Вы можете в базе найти таблицы, которые появились при "битой" реструктуризации базы данных.
Исправить последствия попытки реструктуризации может быть не просто, ведь для этого нужно выполнить шринк основного файла данных, а это может быть очень длительной и ресурсоемкой операцией. Да, в прошлом пункте мы удалили таблицу, но файл данных от этого не уменьшился в размере. Мы лишь освободили место в самом файле данных.

В итоге, если такая ситуация произойдет и добрые администраторы не смогут выделить дополнительное место да дисках, то может произойти остановка работы системы. Но это не точно и полностью зависит от Вашей инфраструктуры!

Но есть ли способ избавиться от такой проблемы? Да, есть! Вот несколько рекомендаций:

Проверять перечень таблиц для реструктуризации перед каждым релизом.
В случае, если изменения затронули тяжелые таблицы, для которых применены нестандартные файловые группы, то один из вариантов:
- Отказаться от изменения на этой таблице. Вместо этого использовать внешние таблицы. Например, вместо добавления реквизита в справочник можно добавить его как доп. свойство или в дополнительный регистр сведений. Включите воображение!
- Если изменения все же очень нужны, то необходимо делать реструктуризацию в "ручном режиме". Подробнее останавливаться на этом сейчас не будем, но на ИС уже об этом писали. Причем, чем больше изменений, тем и сложнее будет сделать это вручную.
Максимально автоматизировать настройку файловых групп для таблиц и индексов базы, а также сделать заглушки для тех таблиц, где реструктуризация автоматически проходить не должна. Об этом будет ниже.

В этом и кроются основные причины усложнения сопровождения. Поэтому стоит 7 раз подумать, прежде чем начать такое у себя использовать.

Автоматизируй это!

Выше мы упомянули про автоматизацию настроек файловых групп для таблиц и индексов. На самом деле здесь ничего нового нет и используется тот же самый подход по созданию произвольных индексов и применению настроек сжатия, что был в статье "Создаем свои индексы для баз 1С. Со своей структурой и настройками!". Он заключается в создании глобальных триггеров, в которых мы отлавливаем события создания таблицы или индекса и встраиваем свою логику для настройки базы данных.

CREATE TRIGGER [CustomSettingsMaintenance_OnIndexCreate]
ON ALL SERVER
AFTER CREATE_INDEX
AS
BEGIN
SET NOCOUNT ON

DECLARE @SchemaName SYSNAME,
@TableName SYSNAME,
@DatabaseName SYSNAME,
@IndexName SYSNAME;

SELECT @TableName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','SYSNAME')
SELECT @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')
SELECT @IndexName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
SELECT @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME');

-- Здесь выполняем необходимые действия.
--  Например, возможные варианты:
--      1. Пересоздаем индекс с учетом новой файловой группы
--      2. Вызываем ошибку, если реструктуризация на этой таблице
--      не должна проходить автоматически. В этом случае
--      обновление базы будет доступно после отключения глобальных триггеров,
--      зато не будет непредвиденных падений информационной системы.
--      3. Ничего не делаем, если платформенная реструктуризация работает как надо.

END

Как пересоздать индекс с учетом новой файловой группы? Например, у нас есть таблица "_InfoRg4683" и индекс "_InfoRg4683_ByDims_NNNNNNNNNNBN" (это из примера с регистром сведений "История адресных объектов"), при этом основная файловая группа в базе это "PRIMARY". Имея уже такие данные мы можем написать такой скрипт.

Универсальный (почти) скрипт пересоздания индекса с новой файловой группой

Скрипт генерирует команду "CREATE INDEX" для уже существующего индекса, а в ней мы просто подменяем имя файловой группы.

-- Исходные параметры
DECLARE @tableNameForChange SYSNAME = '_InfoRg4683',
@indexNameForChange SYSNAME = '_InfoRg4683_ByDims_NNNNNNNNNNBN',
@CreateIndexSQL nvarchar(max);

-- Запрос генерирует команду создания индекса
SELECT @CreateIndexSQL =
(' CREATE ' +
CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +
I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +
I.name  + ' ON '  +
Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +
KeyColumns + ' )  ' +
ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +
ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +
CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +
'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +
-- default value
'SORT_IN_TEMPDB = OFF '  + ','  +
CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +
CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +
-- default value
' DROP_EXISTING = ON '  + ','  +
-- default value
' ONLINE = OFF '  + ','  +
CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +
CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +
DS.name + '] ')
FROM sys.indexes I
JOIN sys.tables T ON T.Object_id = I.Object_id
JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid
JOIN (SELECT * FROM (
SELECT IC2.object_id , IC2.index_id ,
STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END
FROM sys.index_columns IC1
JOIN Sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column = 0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY IC1.object_id,C.name,index_id
ORDER BY MAX(IC1.key_ordinal)
FOR XML PATH('')), 1, 2, '') KeyColumns
FROM sys.index_columns IC2
WHERE IC2.Object_id = object_id(@tableNameForChange)
GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4
ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id
JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id
JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id
LEFT JOIN (SELECT * FROM (
SELECT IC2.object_id , IC2.index_id ,
STUFF((SELECT ' , ' + C.name
FROM sys.index_columns IC1
JOIN Sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column = 1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY IC1.object_id,C.name,index_id
FOR XML PATH('')), 1, 2, '') IncludedColumns
FROM sys.index_columns IC2
WHERE IC2.Object_id = object_id(@tableNameForChange)
GROUP BY IC2.object_id ,IC2.index_id) tmp1
WHERE IncludedColumns IS NOT NULL ) tmp2
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0
AND I.Object_id = object_id(@tableNameForChange)
AND I.name = @indexNameForChange

-- Заменяем исходную файловую группу на необходимую
SET @CreateIndexSQL = REPLACE(@CreateIndexSQL, 'ON [PRIMARY]', 'ON [FILEGROUP_3]');

-- Пересоздаем индекс
exec sp_executesql @CreateIndexSQL;

Параметр "DROP_EXISTING = ON" позволяет избежать ошибки, что такой индекс уже существует. В этом случае СУБД удалит старый индекс и создаст новый.

Также есть несколько нюансов при пересоздании индексов с новыми файловыми группами:

При пересоздании кластерного индекса с новой файловой группой, все остальные индексы таблицы будут также созданы с этой файловой группой.
Для большей универсальности имя новой и старой файловой группы можно получать динамически, вместо явного указания в скрипте.
Пользователь СУБД, от имени которого выполняется реструктуризация, должен иметь необходимые привилегии для выполнения запросов.

А что на счет остановки реструктуризации, если она начинается на таблице, где этого происходить не должно?

Ты не пройдешь, реструктуризация!

В триггере проверяем имя таблицы и/или индекса и если он попадает под запрет, то выполняем:

CREATE TRIGGER [StopUpdateInfobase_OnIndexCreate]
ON ALL SERVER
AFTER CREATE_INDEX
AS
BEGIN
SET NOCOUNT ON

DECLARE @SchemaName SYSNAME,
@TableName SYSNAME,
@DatabaseName SYSNAME,
@IndexName SYSNAME;

SELECT @TableName = EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]','SYSNAME')
SELECT @SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')
SELECT @IndexName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
SELECT @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME');

IF(@TableName LIKE '_InfoRg4683' OR @TableName LIKE '_InfoRg4683NG')
BEGIN
DECLARE @msg nvarchar(max) = 'Реструктуризация таблицы ' + @TableName + ' запрещена!';
RAISERROR (@msg, 10, 1);

THROW 51000, 'Обнаружен запрет реструктуризации таблицы базы данных!', 1;
END

END

При попытке запуска обновления информационной базы получим ошибку.

Запрет реструктуризации Вот такой страшный запрет!

Можно пойти дальше и не ограничиваться отдельными скриптами, а вынести все подобные ограничения и настройки в отдельный инструмент, как это было сделано здесь. Всё!

Послесловие

На первый, второй и третий взгляд все это может показаться настоящим монстром, особенно для сопровождения. Что ж, так оно и есть! Остается надеяться, что наступят светлые времена, когда платформа 1С позволит использовать возможности СУБД без таких костылей. А пока на этом все!

Удачи в бою!

Другие ссылки

30 Comments

user-z99999 06.04.2019 at 10:13

В Вот это поворотхранилище, где находится основной файл базы данных, не хватило места при выполнении реструктуризации. Ведь в процессе платформа создает таблицу заново в основной файловой группе базы, то есть в «PRIMARY». Получается, что платформа создала таблицу и «перегоняла» в нее данные из файловой группы «FILEGROUP_3», пока не заполнила диск.

Нужно на тестовой базе проверять предварительно.

Если таблица будет изменена, а в ней хранилище файлов находится, тогда делаем следущее:

Переименовываем её в своё уникальное имя, а для 1с создаем пустую таблицу (копия по структуре).

1С делает реструктуризацию и изменяет таблицу, и обновление выполнено.

Теперь нужно нашу копию обратно переименовать в таблицу 1С, изменив структуру.

Да, поддержка немного усложняется.

Reply ↓
user-z99999 06.04.2019 at 10:13

Исправить последствия попытки реструктуризации может быть не просто, ведь для этого нужно выполнить шринк основного файла данных

Это вы рассказывайте как совершили ошибку и её исправляете.

А при правильной работе этого не будет. Не нужно делать шнинк базы данных и файлов — при правильной работе.

Т.к. нарушается цепочка Регистрационные номера транзакций (LSN) для бэкапов при восстановлении базы.

Reply ↓
YPermitin 06.04.2019 at 10:20

(1) все так. Не стал усложнять, т.к. трюки с реструктуризациями это отдельная тема.

Но за комментарий спасибо!

Reply ↓
YPermitin 06.04.2019 at 10:21

(2) я против шринков практически в любом виде, это просто пример.

Причем против не только из-за LSN, но и из-за влияния на производительность и бессмысленности в большинстве ситуаций.

Reply ↓
lunjio 06.04.2019 at 10:34

Если весь сыр-бор только из-за внешних файлов допустим, которые 1ТБ, то достаточно сделать хранение внешним и не заморачиваться вот так, другое дело, тоже самое со всякими версионированиями, изначально нужно делать с упором на внешнее хранение, единственное остаются таблицы которые содержат в себе информацию по себестоимости, в УПП таблицы производственного учета и расчета себестоимости налогового учета весят очень много, но на моей практике в базе 300 гб, 8 переделов себестоимости, самая жирная таблица 18 ГБ, НалоговыйУчет. В целом спасибо за статью.

Reply ↓
YPermitin 06.04.2019 at 10:48

(5) по поводу внешних хранилищ согласен.

Иногда бывают случаи, когда проблема возникает «внезапно», ведь раньше все работало, вот и не трогали 🙂 В этом случае может быть сделано временное решение. Ну а что временно, то….

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

Свертку в этих случаях было нельзя делать из-за различных особенностей.

Reply ↓
acanta 06.04.2019 at 10:55

Интересно, как организуют длительные обновления при 24/7 и маленьких технологических окнах.

Reply ↓
YPermitin 06.04.2019 at 11:04

(7)

длительные обновления

Даже реструктуризации можно оптимизировать. Например, включить параллелизм или…не делать изменения, которые эти реструктуризации вызывает 🙂 Плюс можно делать реструктуризацию частями, так сказать, выпускать подготовительные релизы.

Хотя если устанавливаешь типовые обновления, например те же с переходом на ставку НДС 20% как это было с УТ, то тут очень сложно все заранее подготовить.

Reply ↓
lunjio 06.04.2019 at 11:06

(8) Еще один из вариантов оптимизации реструктуризации, это временное удаление всей регистрации с предварительной выгрузкой ее, или полное удаление если по плану обмена не актуальные и просто болтаются изменения, затем возвращение после реструктуризации.

По поводу периода расчитанных итогов, имете ввиду простым пересчетом итогов на дату, на которую граница запрета установлена ?

Reply ↓
YPermitin 06.04.2019 at 11:27

(9) имел ввиду уменьшение периода рассчитанных итогов.

Например, в регистре данные с 2010 до 2019 год. Итоги рассчитаны также с 2010 по 2019. Отчеты по регистру строятся только за 18 и 19 года.

В этом случае можно установить период рассчитанных итогов с 2018 года, тем самым уменьшив размер таблиц итогов.

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

Но тут надо смотреть а не делает ли кто еще запросов к прошлым периодам, а то они могут стать очень тяжелыми.

Reply ↓
ellavs 06.04.2019 at 17:47

Спасибо, добавила в закладки.

Reply ↓
YPermitin 06.04.2019 at 18:34

(11) спасибо!

Reply ↓
3vs 07.04.2019 at 09:04

Всё круто, «Как лампочка горит вижу, а как керосин по проводам течёт не понимаю…»

Юрий, завидую Вашим светлым мозгам!

Вопрос, ежели Вы, не дай Бог, конечно, вдруг внезапно померли, или решили всё бросить и уехать в глушь, в Саратов, заниматься сельским хозяйством,

что со всем этим будут делать ваши клиенты?

Кто сможет подхватить упавшее знамя?

Вы этот случай как-то предусматриваете?

Reply ↓
YPermitin 07.04.2019 at 09:20

(13) спасибо на добром слове 🙂

Все что здесь и в некоторых предыдущих публикациях написано — это не для всех. Не зря же это в разделе «Highload». Большинству клиентов это просто не надо, а тем кому надо — обычно в штате есть специалисты, которые в этом разбираются. И не один.

Мое внезапное сельское хозяйство в глуши Саратовской области не помешает бизнесу =D

Плюс ко всему, все это описано в инструкциях или местных Вики, так сказать для будущих поколений разработчиков и администраторов.

Reply ↓
3vs 07.04.2019 at 09:48

(14)Ну, тогда это другое дело! 🙂

Согласитесь, 1С вполне себе справедливо «Лицензионное соглашение не позволяет использовать недокументированные фирмой «1С» средства для построения решений на платформе «1С:Предприятие».» не позволяет работать с базой не штатными средствами, поди разберись, если что-то пошло не так, где

грабли платформы а где «самодеятельности»! 🙂

1С справедливо прикрывает себе зад, отказываясь от поддержки решений, использующих недокументированные трюки.

Как-то за державу обидно, столько в стране людей с мозгами, способными

сделать не хуже 1С, но все мозги в секте 1С!

Деньги-деньги, платная подписка, платные сервисы…

Была в своё время «Высший сорт бухгалтерия» однопользовательская бухгалтерия раздавалась бесплатно, но в 2015 году разорились и от пиратства и от нашего государства…

Сейчас и сайт-то http://www.v-sort.ru уже не существует — «Домен продается»… 🙁

Reply ↓
YPermitin 07.04.2019 at 10:05

(15) нет разницы какая информационная система используется в энтерпрайзе. Будь то 1С, SAP, Dynamics или свои самописные системы на .NET/JAVA или на других крутых словах. Проблемы все одни и те же у всех.

Конечно, можно не выходить из экосистемы 1С, считать что платформа сама все оптимизирует, что SQL Server / PG работают и сами отлично из коробки и т.д. Но это будет самообман. Все же лучше разбираться что и как работает, тогда и жизнь будет проще (или не будет…). Поэтому лицензионное соглашение фирмы «1С» это вы правильно назвали — «прикрывает себе зад». Но даже оно никогда не защитит от некомпенентности или ошибок администраторов и разработчиков, так что этот их пункт полностью бесполезен.

Что касается альтернатив — они есть, но они дороже и специфичны для особых отраслей. А 1С пошла в массы со всеми вытекающими. Конкурировать с ней тяжело просто потому, что она «у всех» и поддержку для нее найти проще. А то что дешевле и проще, то и распространяется быстрее.

P.S. Это не для спора или холивара, просто мысли в коммент 🙂

Reply ↓
3vs 07.04.2019 at 10:32

(16)Согласен! 🙂

А то что дешевле и проще, то и распространяется быстрее.

В настоящее время, к 1С, по моему, это уже не относится…

Reply ↓
YPermitin 07.04.2019 at 10:41

(17) относительно других продуктов пока все еще дешевле, вроде как.

Но вот для крупных внедрений недавно лицензия КОРП подорожала. Возможно, это сигнал.

Reply ↓
3vs 07.04.2019 at 10:51

(18)Это уже не сигнал, а свершившийся факт!

Логика развития, видимо, диктует…

Reply ↓
ufedor 07.04.2019 at 11:19

За статью спасибо, тоже начинаю об этом думать. У нас файлы хранятся в томах, а самой большой таблицей оказались версии объектов — пользователи не хотят отказываться от истории. Буду пытаться переносить ее на медленный диск.

Насчет лицензионного соглашения позвольте не согласиться. Насколько я понимаю, речь идет о том что 1с не рекомендует работать с ДАННЫМИ (чтение/изменение) в обход стандартных средств 1с. То есть когда вы делаете удаление регистра сведений используя truncate table это уже нарушение. Тут есть разделение — администратору СУБД сами данные не нужны, а 1С не нужна структура хранения.

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

Reply ↓
YPermitin 07.04.2019 at 11:37

(20) спасибо за интерес к публикации!

По поводу лицензии, то тут спорно. Там в соглашении сказано про использование недокументированных возможностей. А на партнерском форуме вроде как офф. позиция также против сжатия данных средствами СУБД, хотя это влияет лишь на способ их хранения.

Сама фирма «1С» вроде как не спешит устанавливать границы разрешенных действий с базой. Формулировки в основном общие.

Reply ↓
Aletar 08.05.2019 at 04:32

Спасибо за статью. Я хочу попробовать перенести на другой диск (более быстрый) системные таблицы (Config, ConfigSave, …) своей базы для разработки, есть ли там какие-то нюансы при обслуживании, а то развалится база не ко времени?

Reply ↓
DBOdin_Lab 30.07.2019 at 09:51

Добрый день!

Интересная публикация для меня, так как недавно завершил секционирование базы с разделением данных (fresh, типовая бухгалтерия).

В моем случае схема секционирования назначает каждой области данных свою файловую группу.

Не знаете, что будет при реструктуризации новым способом (v2)?

Насколько я понимаю она не создаёт новые таблицы и добавляет колонки используя Alter table. Возможно при таким способе реструктуризации у нас не будет необходимости исправлять файловые группы или схемы секционирования?

Reply ↓
YPermitin 30.07.2019 at 11:13

(23) интересный опыт у Вас будет.

К сожалению, я не пользовался плотно новым механизмом реструктуризации, т.к. до этого были с ним некоторые проблемы. Но , по идее, если новых таблиц не создается, то все должно быть ок.

Возможны вопросы с невыровненными по секциям индексами, т.к. не понятно к какой файловой группе их относить. Но нужно эксперементировать.

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

Reply ↓
3vs 30.07.2019 at 11:42

«Остается надеяться, что наступят светлые времена, когда платформа 1С позволит использовать возможности СУБД без таких костылей.»

Это только если Юрий перейдёт работать в 1С! 🙂

Главным по СУБД! 🙂

Reply ↓
YPermitin 30.07.2019 at 11:56

(25) я думаю спецы в фирме «1С» получше меня, просто они статьи на ИС не публикуют 🙂

Reply ↓
3vs 30.07.2019 at 14:24

(26)Ну, ежели костыли используются, то это вряд ли… 🙂

Reply ↓
Dorosh 01.08.2019 at 12:41

Более эффективный и гибкий подход — это перенос таблиц и индексов в другую файловую группу с помощью скриптов. Вот так будет выглядеть скрипт для переноса всех индексов регистра сведений «История адресных объектов» в третью файловую группу.

Не проще ли заставить менеджмент студию заскриптовать создание индекса, а потом в полученном скрипте подпилить файловую группу?

Reply ↓
A_Max 06.08.2019 at 11:54

В Вот это поворотхранилище, где находится основной файл базы данных, не хватило места при выполнении реструктуризации. Ведь в процессе платформа создает таблицу заново в основной файловой группе базы, то есть в «PRIMARY».

Новые платформы вроде уже делают alter вместо drop/create ?

Reply ↓
user1284652 15.10.2019 at 15:09

Или отдать сисадмину на разбивку или схождение с ума или через обновление как вариант.

Если коротко то так

Как он отработает?

1. Для любой таблицы будет выполнена инструкция alter table. Выполняется мгновенно. Без копирования строк из старой таблицы в новую. Будет добавлена колонка в таблицу БД с указанным Вами типом. Если тип, к примеру, ссылочный — значение колонки во всех строках будет вида «ПустаяСсылка» (16-ричное число 0x00000000000000000000000000000000).

2. Для добавления индекса сразу будет запущена инструкция create index. Без копирования строк из старой таблицы в новую. Если индексов несколько — команды create выполняются параллельно. На таблице 100 млн строк индексы по 3 реквизитам добавились за 30 минут (реальный живой тест не на самом мощном ПК с обычным жестким диском, не SSD).

ну а дальше все становится более понятным и перегружать страницу не хочется

Reply ↓