Восстановление индексов СУБД

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

"У нас ничего не работает!!"

"База тормозит!!"

Ну что могло случиться на выходных? Все работало и вдруг перестало.

Ознакомившись с работой пользователей, выяснил, что основная масса запросов идет к регистрам с распухшим (для текущей ситуации) количеством записей — четыре регистра накопления с количеством от 1,5 до 46 млн. записей.

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

Ну и как вы наверное уже догадались, для большинства объектов базы 1С в СУБД физически не существовало индексов, что и показала нам MS SQL Studio. В связи с чем, это могло произойти так и не понял. Причем индексов не было на двух базах MSSQL. Есть подозрения, что базы входили в режим восстановления, возможно это и явилось причиной.

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

В связи с этим на свет и появился код для "Инструментов разработчика", коим и поспешил поделиться с сообществом.

//*********************************
// ОБЩИЕ ПЕРЕМЕННЫЕ
ИмяБазыСУБД = "MyBASE";
ВыводитьСтатусОперации = Истина;

// для получения только отсутствующих в СУБД
ТолькоОтсутствующиеВСУБД = Истина;
ИмяСервераСУБД = "MyServer";
ИмяПользователяСУБД = "sa";
ПарольПользователяСУБД = "MyPassword";
//*********************************

Состояние("Получаем структуру хранения базы данных...");
СХД= ПолучитьСтруктуруХраненияБазыДанных(,Истина); // по всем объектам, в терминах СУБД

Состояние("Заполняем таблицу данных для создания индексов...");

ТЗИнд = Новый ТаблицаЗначений;
ТЗИнд.Колонки.Добавить("ИмяИндекса");             // имя индекса из СХД
ТЗИнд.Колонки.Добавить("ИмяТаблицы");             // имя таблицы 1С
ТЗИнд.Колонки.Добавить("ИмяМетаданного");      // метаданные из СХД
ТЗИнд.Колонки.Добавить("ПоляИндекса");        // таблица значений из СХД
ТЗИнд.Колонки.Добавить("ПоляИндексаСтрокой");    // все поля собрали в строку
ТЗИнд.Колонки.Добавить("Кластеризованный");     // кластеризованный или нет
ТЗИнд.Колонки.Добавить("СтрокаСоздания");      // строка для создания индекса в СУБД

Для Каждого СтрокаСХД Из СХД Цикл
Для Каждого СтрокаИндексаСХД Из СтрокаСХД.Индексы Цикл
ИмяМетаданного = СтрокаСХД.ИмяТаблицыХранения;
ИмяИндексаХранения = СтрокаИндексаСХД.ИмяИндексаХранения;

СтрокаИнд = ТЗИнд.Добавить();
СтрокаИнд.ИмяМетаданного = ИмяМетаданного;
СтрокаИнд.ИмяИндекса = ИмяИндексаХранения;
СтрокаИнд.ПоляИндекса = СтрокаИндексаСХД.Поля;
СтрокаИнд.ИмяТаблицы = СтрокаСХД.ИмяТаблицы;

// собрали поля индекса в строку
ПоляИндексаСтрокой = "";
Для Каждого СтрокаИмениПоля из СтрокаИнд.ПоляИндекса Цикл
ПоляИндексаСтрокой = ПоляИндексаСтрокой+?(ПустаяСтрока(ПоляИндексаСтрокой),"",", ")+СтрокаИмениПоля.ИмяПоляХранения;
КонецЦикла;
СтрокаИнд.ПоляИндексаСтрокой = ПоляИндексаСтрокой;

// вид индекса кластерный или нет - из копии оставшей СУБД или по имени индекса
СтрокаИнд.Кластеризованный = 0;
Если Найти(ИмяИндексаХранения,"PK__") > 0 или                   // первичный для всех
Найти(ИмяИндексаХранения,"_IntKeyInd") > 0 или     // для табличных частей документов
Найти(ИмяИндексаХранения,"_ByDataKey_") > 0  или     //
Найти(ИмяИндексаХранения,"_TRN") > 0        // для регистров
Тогда
СтрокаИнд.Кластеризованный = 1;

КонецЕсли;

КонецЦикла;

КонецЦикла;

// получим индексы из СУБД
СоответствиеИндексов = Новый Соответствие;
Если ТолькоОтсутствующиеВСУБД тогда
Состояние("Получаем индексы СУБД ...");

СтрокаСоединения = "Provider=SQLOLEDB; Data Source="""+ИмяСервераСУБД+"""; Initial Catalog="""+ИмяБазыСУБД+"""; User Id="""+ИмяПользователяСУБД+"""; Password="""+ПарольПользователяСУБД+"""";
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = СтрокаСоединения;
Соединение.Open();

Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = "select name, type from sys.indexes where type <> 0";
Данные = Команда.Execute();

Данные.MoveFirst();
Пока НЕ Данные.EOF Цикл
ИмяИндекса = Данные.Fields.Item(0).Value;
СоответствиеИндексов.Вставить(ИмяИндекса,ИмяИндекса);
Данные.MoveNext();
КонецЦикла;
КонецЕсли;

// собираем строку создания индекса на стороне СУБД
// https://docs.microsoft.com/ru-ru/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2024
// Создавайте кластеризованные индексы до создания любых некластеризованных.
// При создании кластеризованного индекса все существующие некластеризованные индексы таблицы перестраиваются.
ТЗИнд.Сортировать("ИмяТаблицы Возр,Кластеризованный Убыв");
КоличествоСтрок = ТЗИнд.Количество();
Ном =1;

Состояние("Получаем финальный скрипт...");
СтрокаСкрипта = "use "+ИмяБазыСУБД+" GO";
Для Каждого СтрокаИнд Из ТЗИнд Цикл
ИмяИндекса  = СтрокаИнд.ИмяИндекса;

ИндексЕстьВСУБД = Ложь;
Если ТолькоОтсутствующиеВСУБД тогда
ИндексЕстьВСУБД = СоответствиеИндексов.Получить(ИмяИндекса) <> Неопределено;
КонецЕсли;

Если ИндексЕстьВСУБД тогда
Продолжить;
КонецЕсли;

СтрокаСоздания = "";
// статус операции для наглядности
Если ВыводитьСтатусОперации тогда
СтрокаСтатуса =  Строка(Ном)+" из "+Строка(КоличествоСтрок) + " ("+Формат(Ном*100/КоличествоСтрок,"ЧЦ=4; ЧДЦ=2; ЧГ=0")+"%)";
СтрокаСоздания = "Print('"+СтрокаСтатуса +". "+СтрокаИнд.ИмяТаблицы+"')"+Символы.ПС;    // выведем имя таблицы 1С
КонецЕсли;
//--

СтрокаСоздания = СтрокаСоздания +"CREATE UNIQUE " + ?(СтрокаИнд.Кластеризованный = 1," CLUSTERED ","")+ " INDEX "           // имя индекса
+ ИмяИндекса+Символы.ПС+" ON ["+ИмяБазыСУБД+"].dbo.["+СтрокаИнд.ИмяМетаданного+"] ("        // имя метаданного SQL
+ СтрокаИнд.ПоляИндексаСтрокой +") "+ "WITH (DROP_EXISTING = OFF) "                                                                   // поля индекса
+ "  ON [PRIMARY] "
+Символы.ПС+"GO";
СтрокаИнд.СтрокаСоздания = СтрокаСоздания;

СтрокаСкрипта = СтрокаСкрипта+?(ПустаяСтрока(СтрокаСкрипта),"", Символы.ПС)+ СтрокаСоздания;

Ном = Ном + 1;
КонецЦикла;

// добавили в скрипт обновление статистики и чистку кэша
СтрокаСкрипта = СтрокаСкрипта + Символы.ПС
+" EXEC sp_updatestats;"+Символы.ПС
+ "DBCC FREEPROCCACHE";

Результат выполнения — строка, которую использовал в MS SQL Studio, чтобы отслеживать прогресс выполнения создания индексов.

UPD 1.

В код добавлена возможность создавать только отсутствующие в СУБД индексы. Для этого используем подключение ADO и анализ имеющихся в СУБД индексов.

Регулируется параметром "ТолькоОтсутствующиеВСУБД " в шапке кода.