Skip to content

Latest commit

 

History

History
 
 

1С-Extended-Database-Settings-Maintenance

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
Database
Database
 
 
Programmability/Procedures
Programmability/Procedures
 
 
Samples
Samples
 
 
Scripts
Scripts
 
 
Server
Server
 
 
Tables
Tables
 
 
Views
Views
 
 
Readme.md
Readme.md
 
 

Readme.md

Инструмент расширенной настройки баз данных 1С

Введение

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

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

Инструмент предназначен только для клиент-серверного варианта работы 1С в связке с Microsoft SQL Server 2012 и выше.

Предоставляемые возможности:

  • Создание неплатформенных индексов произвольной структуры с любыми настройками
  • Изменение настроек платформенных индексов и таблиц
  • Отключение неэффективных платформенных индексов
  • Поддержка сжатия таблиц и индексов
  • Использование файловых групп
  • Секционирование таблиц и индексов
  • Выполнение произвольный SQL-скриптов при создании таблиц и индексов
  • И некоторые другие функции.

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

  1. Нет проблем с производительностью и стабильностью работы
  2. База данных небольшого размера
  3. Все возникающие проблемы можно решить средствами платформы 1С

Сама идея по использованию такого подхода в контексте платформы 1С возникла еще несколько лет назад при прочтении статьи "Using Triggers to Automatically Add Indexes" от Brent Ozar. Но создавать какое-то комплексное решение изначально не планировалось, были лишь точечные применения в рабочих проектах. Ведь что не говори, все же подобное решение является "костылем" и правильным было бы использовать все возможности СУБД через настройки на уровне конфигурации 1С, через метаданные. Но пока, к сожалению, это невозможно. Пройдите по ссылке к статье от Brent Ozar, посмотрите на фотографию. Думаю она хорошо символизирует все, о чем здесь будет идти речь :)

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

Установка

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

  1. Создаст и настроит служебную базу данных (саму базу, таблицы, представления, хранимые процедуры и др.)
  2. Настроит минимальные права доступа к служебной базе и ее объектам
  3. Создаст глобальные триггеры

Перед установкой, возможно, понадобиться подготовить скрипт для вашего окружения:

  • Заменить имя служебной базы данных во всем скрипте
  • Изменить скрипт предоставления прав доступа к служебной базе по своим особым правилам

Дополнительная информация

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

Ссылка Описание Автор
Включение сжатия таблицы или индекса Информация о сжатии таблиц и индексов Microsoft
Секционирование таблиц и индексов Информация о секционировании таблиц и индексов Microsoft
Руководство по проектированию индексов SQL Server Информация по проектированию индексов Microsoft
Using Triggers to Automatically Add Indexes Описание использования DDL-триггеров для автоматического добавления индексов Brent Ozar
Сжатие баз данных 1С:Предприятие в MS SQL Server Использование сжатия таблиц и индексов для баз данных 1С. Предоставлен интересный способ поддержки сжатия даже после реструктуризации таблиц и индексов средствами платформы 1С Алексей Бочков
Давайте забудем о свертке БД? Файловые группы и секции таблиц SQL, сжатие таблиц SQL Хорошее описание механизма секционирования в контексте использования платформы 1С Олег Филлипов
Для чего НЕ нужны индексы Просто и понятно об индексах, а также некоторые полезные советы по работе с ними Олег Филлипов
Про отсутствующие индексы и 1С Доступным языком рассказано про поиск отсутствующих индексов Алексей Лустин
Создание недостающих индексов в MSSQL Пример создания неплатформенных индексов средствами SQL Server Константин Урсалов
Зачем разработчику 1С «индексировать» измерения регистров и реквизиты Доступно в контексте платформы 1С рассказывается о необходимости индексов с ссылками на MSDN Вячеслав Гилёв

Описание использования

Для начала использования нужно обратить внимание на две таблицы служебной базы данных:

  • "CompressionSettingsMaintenance" - настройки сжатия для таблиц и индексов базы данных.
  • "CustomSettingsMaintenance" - произвольные настройки базы данных в виде скриптов, которые выполняются при создании таблиц и индексов.

Таблица "CompressionSettingsMaintenance" имеет следующую структуру (скрыты служебные колонки):

DatabaseName TableName IndexName CompressionType IsActive
Имя базы данных, для которой используется правило сжатия Имя таблицы для сжатия Имя индекса для сжатия. Если имя индекса пустое, то сжатие используется непосредственно для таблицы Тип сжатия (NONE, ROW, PAGE) Активность - признак использования правила сжатия
TestDB _Reference37 _Reference37_2 PAGE 1

Выше также продемонстрирован пример настройки для сжатия индекса "_Reference37_2" таблицы "_Reference37" в базе данных "TestDB" методом "PAGE".

В таблице "CustomSettingsMaintenance" задаются скрипты для запуска по событиям "AFTER CREATE_TABLE" и "AFTER CREATE_INDEX", с помощью которых можно изменять настройки объектов базы данных. Запуск скриптов по событиям позволяет поддерживать настройки даже при реструктуризации базы данных средствами платформы 1С. Таблица настроек имеет следующую структуру (скрыты служебные колонки):

DatabaseName TableName IndexName Command IsActive Description Priority
Имя базы данных Имя таблицы Имя индекса Текст SQL-команды, которая выполняется при возникновении события. Вместо имени таблицы и индекса используются шаблоны '{TableName}' и '{IndexName}' соответственно Активность - признак использования правила Описание правила Приоритет, используется для задания порядка применения правил
TestDB _Reference37 _Reference37_2 ALTER INDEX [{IndexName}] ON [dbo].[{TableName}] DISABLE; 1 Отключение платформенного индекса по коду справочника 1

При выполнении реструктуризации платформой 1С срабатывают глобальные триггеры на уровне инстанса SQL Server "AFTER CREATE_TABLE" и "AFTER CREATE_INDEX", обрабатывающие правила сжатия и произвольные правила из таблиц, описанных выше.

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

Все действия, выполненные по правилам сжатия и произвольным настройка базы данных, записываются в таблицу "EventLog":

DatabaseName TableName IndexName Command Message Severity Period
Имя базы Имя таблицы Имя индекса SQL-команда, выполняемая для правила Содержимое сообщения Уровень события (Информация, Предупреждение, Ошибка) Период возникновения события
TestDB _Reference37 _Reference37_2 ALTER INDEX [{IndexName}] ON [dbo].[{TableName}] DISABLE; Trigger CustomSettingsMaintenanceOnIndexCreate executed! INFORMATIONAL 2018-10-18 00:26:52.233

Кроме того, в служебной базе данных содержатся некоторые объекты, упрощающие решение некоторых задач:

Тип объекта Объект Описание
Представление CustomSettingsMaintenanceCommands Представление позволяет получить список всех произвольных правил с сформированными SQL-скриптами для ручного запуска
Представление CompressionSettingsMaintenanceCommands Представление позволяет получить список всех правил сжатия объектов базы данных со сформированными SQL-скриптами для ручного запуска
Представление InfoLog Просмотр информационных событий логов
Представление WarningLog Просмотр предупреждений событий логов
Представление ErrorLog Просмотр ошибок событий логов

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

Практические примеры

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

Внимание!!!

Все примеры ниже лишь сделаны с целью демонстрации и не являются универсальным решениями.

Новый индекс

В тестовой базе имеем справочник "ТаблицаПростая" следующей структуры:

Поле SQL Поле 1С
_IDRRef Ссылка
_Version ВерсияДанных
_Marked ПомтекаУдаления
_PredefinedID ИмяПредопределенныхДанных
_Code Код
_Description Наименование
_Fld20 РеквизитСтрока
_Fld21 РеквизитЧисло
_Fld22 РеквизитБулево
_Fld23 РеквизитДатаВремя
_Fld24_TYPE РеквизитСсылка (вид значения)
_Fld24_RTRef РеквизитСсылка (тип ссылки)
_Fld24_RRRef РеквизитСсылка (ссылка)

Таблица содержит 3 млн. записей, из которых 100 элементов помечено на удаление. Представим, что в системе выполняются запросы, которые предназначены для следующего:

  • Получение помеченных на удаление элементов
  • Получение полей "РеквизитСтрока", "РеквизитДатаВремя" элементов, у которых "РеквизитЧисло" имеет значение 100. В будущем число для фильтра может изменяться и это стоит учитывать.

Как бы можно было оптимизировать такие запросы средствами платформы 1С?

Создать индекс по полю "ПометкаУдаления" средствами платформы 1С нельзя, такой настройки для стандартного реквизита просто нет.

Как вариант - можно было бы создать, например, регистр сведений с измерениями "ПометкаУдаления" и "Ссылка", а при изменении справочника в событии "ПриЗаписи" дублировать эти значения в этот регистр. Тогда основной кластерный индекс регистра сведений позволял бы выполнять эффективные выборки данных. Плюс такого подхода - все сделано средствами платформы 1С. Но у такого подхода есть и ряд существенных минусов:

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

Что касается оптимизации запросов с фильтром по полю "РеквизитЧисло". Средствами платформы через конфигуратор устанавливаем поле "Индексировать" для реквизита "РеквизитЧисло".

В этом случае выборка уже станет эффективней, но для получения полей "РеквизитСтрока", "РеквизитДатаВремя" СУБД придется делать просмотр кластерного индекса (Key Lookup), что является весьма затратной операцией. То есть созданный платформой индекс не будет покрывающим для запроса.

Но можно пойти дальше, и установить настройку "Индексировать" для полей "РеквизитЧисло", "РеквизитСтрока", "РеквизитДатаВремя" в значение "Индексировать с доп. упорядочиванием". Это позволит создать покрывающий индекс для поля "РеквизитЧисло" следующей структуры:

Поле SQL Поле 1С
_Fld21 РеквизитЧисло
_Description Наименование
_IDRRef Ссылка
_Marked ПометкаУдаления
_Fld23 РеквизитДатаВремя
_Fld20 РеквизитСтрока

То есть средствами платформы мы создали покрывающий индекс. Но есть существенный подводный камень: будут созданы "тяжелые" избыточные индексы для полей "РеквизитСтрока", "РеквизитДатаВремя", т.к. они также будут дополнены другими полями с индексированием доп. упорядочиванием.

Индекс по реквизиту "РеквизитСтрока":

Поле SQL Поле 1С
_Fld20 РеквизитСтрока
_Description Наименование
_IDRRef Ссылка
_Marked ПометкаУдаления
_Fld23 РеквизитДатаВремя
_Fld21 РеквизитЧисло

Индекс по реквизиту "РеквизитДатаВремя":

Поле SQL Поле 1С
_Fld23 РеквизитДатаВремя
_Description Наименование
_IDRRef Ссылка
_Marked ПометкаУдаления
_Fld20 РеквизитСтрока
_Fld21 РеквизитЧисло

В итоге, средствами платформы решить задачу можно, но с серьезными недостатками:

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

Как решить эту задачу средствами SQL Server и предлагаемого инструмента?

Для создания индекса по полю "ПометкаУдаления" с целью выборки помеченных на удаление элементов необходимо выполнить несколько действий:

  1. Определяем какой SQL-командой будем создавать индекс, а именно настройки индекса, структуру и другое.

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

CREATE NONCLUSTERED INDEX [_Marked_Filtered] ON [dbo].[_Reference18]
(
    -- поле "ПометкаУдаления"
	[_Marked] ASC
)
-- В индекс включены только помеченные на удаление элементы
WHERE [_Marked] = 0x01

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

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

Для этого в служебной базе необходимо создать запись в таблице "CustomSettingsMaintenance" со следующими значениями:

DatabaseName TableName IndexName IsActive Command Description EventType Priority
TestDB _Reference18 _Marked_Filtered 1 <см. код ниже таблицы> Фильтрованный индекс по пометке удаления 4 1

В качестве типа события в колонке "EventType" задан код 4. Это предопределенное значение для события "CREATE_TABLE", о чем мы писали ранее.

Отдельно вынесем скрипт в поле "Command":

CREATE NONCLUSTERED INDEX 
        [{IndexName}] -- Шаблон подставновки имени индекса в скрипт
    ON [dbo].[{TableName}] -- Шаблон подстановки имени таблицы в скрипт
([_Marked] ASC) WHERE [_Marked] = 0x01

Это важная особенность при записи команд в правила настройки базы - имя таблицы и индекса в скрипте должны быть подменены шаблонами. Это сделано по той причине, что платформа 1С при реструктуризации сначала выполняет все операции на копии таблицы, добавляя в конец имени таблицы префикс "NG". Чтобы скрипты работали корректно необходимо подменять имена таблиц и индексов динамически.

  • Запускаем скрипт вручную

Все настроено, но индекса так и не появилось :-) И это логично, ведь мы не выполняли реструктуризации и не запускали скрипт вручную. Давайте сгенерируем скрипты по тому правилу, что мы добавили выше, а после запустим его. Для этого сделаем запрос к представлению "CustomSettingsMaintenanceCommands". Для нашего правила в колонке "CreateIndexCommand" мы увидим SQL-скрипт для создания индекса:

/*
		  Будет создан индекс '_Marked_Filtered' для таблицы '_Reference18'
		  Описание: Фильтрованный индекс по пометке удаления
*/
IF NOT EXISTS(
			SELECT TOP 1
    1
FROM SYS.INDEXES i
where i.Name = '_Marked_Filtered'
    and I.Object_id = OBJECT_ID('_Reference18'))	
			BEGIN
    CREATE NONCLUSTERED INDEX [_Marked_Filtered] ON [dbo].[_Reference18]
(
    -- поле "ПометкаУдаления"
	[_Marked] ASC
)
-- В индекс включены только помеченные на удаление элементы
WHERE [_Marked] = 0x01
END
GO

Скопируем его в SSMS и запустим. После этого индекс будет добавлен в базу данных.

  • Проверяем работу инструмента, сделав реструктуризацию

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

По завершению посмотрим на список индексов таблицы "_Reference18" скриптом

SELECT
	OBJECT_NAME(object_id) [Имя объекта]
	,[name] [Имя индекса]
	,[type_desc] [Тип индекса]
	,[has_filter] [Фильтрованный]
FROM sys.indexes inds
WHERE OBJECT_NAME(object_id) = '_Reference18'

Результат будет следующий:

Имя таблицы Имя индекса Тип индекса Фильтрованный
_Reference18 PK___Referen__AC8ED0C4A13A8D79 CLUSTERED 0
_Reference18 _Marked_Filtered NONCLUSTERED 1
_Reference18 _Reference18_1 NONCLUSTERED 0
_Reference18 _Reference18_2 NONCLUSTERED 0
_Reference18 _Reference18_3 NONCLUSTERED 0

В таблице с логами "EventLog" также можно найти запись, что команда создания индекса отработала успешно.

Задачу с индексом для реквизита "РеквизитЧисло" решаем тем же способом, только SQL-скрипт будет другим:

CREATE NONCLUSTERED INDEX [_CustomIndex] ON [dbo].[_Reference18]
(
	[_Fld21] ASC -- поле "РеквизитЧисло"
)
-- Включенные столбцы для создания покрывающего запрос индекса
INCLUDE (
	[_Fld20], -- поле "РеквизитСтрока"
	[_Fld23]  -- поле "РеквизитДатаВремя"
	)

В итоге мы имеем эффективные SQL-индексы, которые средствами платформы 1С создать было бы нельзя.

Изменение платформенного индекса

С созданием индексов разобрались, но как быть, если нужно изменить индекс, который платформа 1С создает самостоятельно? Возьмем для демонстрации ту же таблицу "_Reference18" и предположим, что индекс по коду справочника ("_Reference18_2") не используется и нам нужно его отключить.

В этом случае действуем по той же схеме, что и при создании нового индекса, за некоторыми исключениями:

  1. Тип события ("EventType") должно иметь значение 5 - это идентификатор события "INDEX_CREATE".
  2. Будет отличаться и команда для отключения индекса, которая с учетом шаблонов будет выглядеть так:
ALTER INDEX [{IndexName}] ON [dbo].[{TableName}] DISABLE;

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

Подобным образом можно менять и другие настройки индексов: коэффициент заполнения (fill factor), сортировку в TempDB, разрешение на блокировки строк и страниц и многое другое.

Сжатие данных

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

Предположим, что нам нужно включить сжатие методом PAGE для таблицы "_Reference18" и всех ее индексов, а также включить сжатие методом ROW для отдельного индекса "_Reference19_1" таблицы "_Reference19". Для этого нужно внести следующие настройки в таблицу "CustomSettingsMaintenanceCommands":

DatabaseName TableName IndexName CompressionType IsActive
TestDB _Reference18 '' 3 1
TestDB _Reference19 _Reference19_1 2 1

Особенностью является то, что если в качестве имени индекса указать пустую строку, то правило сжатия будет применено как для таблицы, так и для всех ее индексов. Доступные значения поля "CompressionType" это:

  • None (1)
  • Row (2)
  • Page (3)

При реструктуризации эти правила конвертируются в скрипты вида:

ALTER TABLE [dbo].[{TableName}] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = Page);
ALTER INDEX [{IndexName}] ON [dbo].[{TableName}] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = Row)

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

Использование файловых групп

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

-- Исходные параметры
DECLARE @tableNameForChange SYSNAME = '{TableName}',
		@indexNameForChange SYSNAME = '{IndexName}',
		@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 [NewFileGroupName]');

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

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

Секционирование

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

CREATE UNIQUE CLUSTERED INDEX [{IndexName}] ON [dbo].[{TableName}]
(
	[_Fld2683] ASC,
	[_Fld5588] ASC,
	[_Fld5589] ASC,
	[_Fld5590] ASC,
	[_Fld5591] ASC
	-- В параметрах пересоздания индекса обязательно нужно добавить "DROP_EXISTING = ON",
	-- чтобы старый индекс был удален. Иначе можно получить ошибку об уже существующем индексе.
)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 [StorageImageRepositoryScheme]
-- И в качестве параметра имя поля секционирования,
-- которое должно удовлетворять схеме
([_Fld5590])

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

Обработка ошибок при реструктуризации

Стоп, стоп! Но что, если что-то пойдет не так? Что если я ошибся в скрипте и при применении правила возникнет ошибка? Неужели база "упадет" и нет шансов ее восстановить?

Подождите, не все так плохо!

Предположим, вы добавили правило по событию "TABLE_CREATE" для таблицы "_Reference18". Скрипт правила выглядит следующим образом:

ERROR!!! ALTER INDEX [{IndexName}] ON [dbo].[{TableName}] DISABLE;

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

Посмотрев таблицу с логами "EventLog" можно увидеть все ошибки обработки правил. Для нашего случая будет присутствовать следующая запись (показана только часть колонок):

DatabaseName TableName IndexName Message Severity
TestDB _Reference18NG _Reference18_3NG Trigger CustomSettingsMaintenanceOnIndexCreate failed! Error: Неправильный синтаксис около конструкции "ERROR". ERROR 
USE TestDB; ERROR!!! ALTER INDEX [_Reference18_3NG] ON [dbo].[_Reference18NG] DISABLE;

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

Для мониторинга, отслеживания подобных ошибок можно сделать Job, который при возникновении нештатных ситуаций будет делать рассылку ответственным разработчикам / администраторам по логам. Также для этих целей и отладки правил рекомендую использовать сессию Exteded Events со следующими настройками:

CREATE EVENT SESSION [_ErrorsLog] ON SERVER 
ADD EVENT sqlserver.error_reported 
    ( 
        ACTION  (package0.collect_system_time,
				package0.last_error,
				sqlserver.client_app_name,
				sqlserver.client_hostname,
				sqlserver.database_id,
				sqlserver.database_name,
				sqlserver.nt_username,
				sqlserver.plan_handle,
				sqlserver.query_hash,
				sqlserver.session_id,
				sqlserver.sql_text,
				sqlserver.tsql_frame,
				sqlserver.tsql_stack,
				sqlserver.username)
        WHERE    ([severity]>10) 
    ) 
ADD TARGET package0.event_file (SET filename=N'D:\SQLTraces\_ErrorsLog.xel') 
WITH (STARTUP_STATE=OFF) 
GO

ALTER EVENT SESSION [error_trap] ON SERVER 
STATE = START; 
GO

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

Отказ от ответственности

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

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

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

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

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

Таким образом, игнорируя это правило лицензионного соглашения, Вы можете получить отказ в технической поддержке со стороны фирмы "1С". Учитывайте это, прежде чем начать использовать неплатформенные возможности. Взвесьте все "ЗА" и "ПРОТИВ".

Вместо заключения

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

  1. Имеются проблемы стабильности и производительности, которые нет возможности решить средствами платформы 1С:Предприятие, при этом возможности SQL Server могут эффективно решить возникающие вопросы.
  2. Вы ясно понимаете, что, как и для чего изменять на стороне базы данных SQL Server для достижения результатов, а также есть понимание возможных проблем.
  3. Есть понимание всех возможных последствий после нарушения лицензионного соглашения фирмы "1С".

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