Про отсутствующие индексы и 1С

Хотите чтобы MS SQL подсказывал Вам, в каком объекте вы строите не оптимальные запросы или неверно спроектировали метаданные - тогда спросите у него это.

Про отсутствующие индексы и 1С

Вместо вступления:

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

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

Итак перед началом ознакомления со статьей хочу заметить, что на моем пути встречались следующие категории людей по типу отношения к индексам СУБД:

1. Бог его знает что такое индекс - отличительная особенность: круглые, удивленные глаза, как реакция на фразу “Запрос не попадает в индекс”.
2. Каждое поле таблицы (реквизит объекта) необходимо проанализировать на предмет необходимости индексации- отличительная особенность: постоянный просмотр планов запроса, даже в случае выборки одной строки из таблицы с гарантированным размером не более 10 строк.
3. Вы слишком сильно уповаете на индексы - отличительная особенность: гарантированная реакция с ярко выраженным апломбом, в виде “Оно вам не поможет”, при попытке коллеги просмотра административных представлений MS SQL илиплана выполнения сложного запроса в терминах SQL
4. С индексами в каждом конкретном случае надо включать голову - компромиссный тип людей, отличительная особенность - знает что такое селективность, и понимает почему JOIN можно заменить на UNION и в чем будет выйгрыш.

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

Тут ответ прост - скорее к последнему типу, по причинам:

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

 Для работы нам понадобиться:

• 1С Предприятие 8.2 - http://users.v8.1c.ru -  я использую версию 1С:Предприятие 8.2 (8.2.13.205)
• Тестовая конфигурация  - режим совместимости 8.1, архитектура: 1С Сервер + MS SQL 2005 и выше (я использую Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64))
• GameWithFire - http://main.1c-ei.ru/Articles/gamewithfire скачать можно по адресу http://dev.citykirov.ru/ или в составеEnterprise Integrator
• любую Консоль запросов 1С

Отсутствующие индексы

Как не странно еще со времен 1С 7.7, мое желание запустить ее именно под MS SQL 2005 было связано, помимо всего прочего, еще и с тем фактом что сервер СУБД собирает статистику о выполнении запросов и предоставляет мне доступ к этой информации.

С появлением 1С 8.* ничего собственно не изменилось (за исключением того что теперь нет необходимости учить 1С работать с MS SQL 2005) - архитектура метаданных может оказаться не оптимальной, а запросы на языке 1С тоже - что вполне естественно: идеальных систем не существует в принципе.

Так вот - MS SQL хранит эту самую статистику и данная информация может быть очень даже полезной. Что это за статистика можно ознакомиться в относительно серьезной статье “Открытие скрытых данных для оптимизации производительности приложений”

Однако мы с Вами попробуем разобраться с одним из административных представлений MS SQL - а именно сsys.dm_db_missing_index_*

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

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

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

Сейчас попробуем на довольно простом примере убедиться что это очень просто:

Тестовый пример

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

Развертывать необходимо в клиент-серверном режиме с использованием сервера СУБД MS-SQL версии 2005 и выше.

Метаданные в данном случае совершенно простейшие:

Однако обращаю внимание - реквизит ДокументОснование - НЕ проиндексирован. Я про него забыл ;-).

 При запуске конфигурации будет запущен генератор документов - он создаст Вам сколько угодно документов. По умолчанию я выставляю 10000 документов

Генератор документов создает последовательно документы - в котором каждый следующий документ имеет основанием предыдущий по МоментуВремени документ такого же типа.

В конфигурацию встроена обработка “Отсутствующие индексы SQL” .

И сейчас она будет показывать пустую информацию (или ошибку если Вы забыли проинсталировать GameWithFire.dll)

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

 Открываем консоль запросов (причем я всем последнее время помимо UCR советую также Консоль запросов с трассировкой), и выполняем набор простых запросов.

И снова запускаем обработку “Отсутствующие индексы SQL”

А вот теперь в итоге посмотрите на запросы которые вы выполнили 
и на ту информацию которую Вам сообщил SQL:

Итак Ваше приложение выполняло запросы которые:

• осуществляли работу с основной таблицей документ с ИД “Документ1”
• вложенность не использовали (поле Состав предлагаемого индекса пустое)
• накладывали фильтры:
  • на равенство (поле Поиск) - по реквизиту ДокументОснование
  • на больше/меньше/НЕ равно - по реквизиту Проведен

• затраты на такие запросы сводно составили по этому документу 2683 “попугая” = [средняя стоимость одного запроса * средний процент  выйгрыша * (количество пользовательских операций поиска + количество пользовательских операций сканирования)]
• если бы вы создали подобный индекс вы бы сократили свои издержки на 99%

Так как поле Проведен недоступно для индексирования из 1С - Вы легко сделаете вывод сами, я надеюсь - как исправить данную ситуацию.

 Отдельно хотел бы заметить - после  того как Вы вы выставите Индексацию у реквизита ДокументОснование - при выполнении тех же самых запросов - SQL уже не будет Вам сигнализировать о том “что кажется Вы забыли проиндексировать поля таблицы”.

В качестве эпилога:

Данная статья - это:

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

• написана в качестве “ночного скетча”, вместе с тестовой конфигурацией
• фактически приглашение Вас к дискуссии и как минимум к прочтению статей MSDN и других технических ресурсов по работе с СУБД (если Вы конечно это еще не сделали).