В обычный погожий денек, любимый всеми понедельник, не прошло и 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-2017
// Создавайте кластеризованные индексы до создания любых некластеризованных.
// При создании кластеризованного индекса все существующие некластеризованные индексы таблицы перестраиваются.
ТЗИнд.Сортировать("ИмяТаблицы Возр,Кластеризованный Убыв");
КоличествоСтрок = ТЗИнд.Количество();
Ном =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 и анализ имеющихся в СУБД индексов.
Регулируется параметром "ТолькоОтсутствующиеВСУБД " в шапке кода.
