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

Сегодня хочу затронуть тему регламентного обслуживания баз данных MS SQL. А позже поговорим и про обслуживание баз PostgreSQL.

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

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

Статистики

Статистики — это системные объекты, содержащие метаинформацию о распределении данных в столбцах таблиц и индексов. Они позволяют оптимизатору оценить:

• Сколько строк вернёт условие WHERE;

• Какой тип соединения выбрать (Nested Loops, Hash Match, Merge Join);

• Какой индекс использовать и использовать поиск по нему (seek) или дешевле сделать сканирование (scan);

• Какой порядок обработки таблиц будет оптимальным.

Оптимизатор запросов MS SQL Server использует эту информацию для построения наиболее эффективного плана выполнения запроса. Без актуальных статистик оптимизатор работает «вслепую» и может выбрать неоптимальный план, что может приводить к:

• Неправильному выбору типа соединений таблиц (например, LOOP JOIN вместо HASH JOIN).

• Как следствие, неоправданному потреблению ресурсов (CPU, RAM, I/O).

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

• Резкому падению производительности даже на "простых" запросах.

В мониторинге Perfexpert мы видим такие запросы, которые вдруг ни с того, ни с сего стали выполняться на порядки(!) медленнее.

Вот в качестве примера фрагмент трассы с таким тяжелым запросом, который периодически (не всегда!) плохо себя ведет. Это выражается в количестве логических чтений который он выполняет в ходе выполнения.

На рисунке выше выделены две строки одного и того же запроса, когда количество логический чтений вырастает на порядки. Запрос использует два регистра накопления (ACCUMRG19938 и ACCUMRG19820) с большим количеством строк и несколько временных таблиц. Если посмотреть отчет по изменению статистик базы данных за неделю, то видим, что суммарное количество изменений по таблицам с последнего пересчета статистик имеет тренд на увеличение и даже ночные попытки пересчитывать статистики (падения зеленого графика) выполняются не полностью – график не падает до нуля, и рост изменений на следующий день продолжается. Причины почему так может быть разберем позже, а сейчас просто зафиксируем этот факт.

Самый большой вклад в изменения статистик вносит как раз одна из наших таблиц – _ACCUMRG19938 и ее несвоевременный пересчет привел к выбору неоптимального плана.

Если посмотреть все планы для этого запроса за неделю, то как раз можно увидеть как различаются планы запроса. Интересующий нас «плохой» план в самой верхней строке – выполняет 412 млн. логических чтений.

Оптимизатор ошибается при выборе индекса таблицы «_AccumRg19938». Вот два фрагмента «плохого» и «хорошего» планов выполнения запросов:

В «плохом» плане оптимизатор выбрал некластерный индекс «_AccumRg19938_5», в результате чего выполняется дополнительная операция «Key Lookup». Индекс «_AccumRg19938_5» не содержит всех необходимых полей и оптимизатор вынужден обращаться к основному (кластерному) индексу, чтобы их дополучить – из-за этого такое огромное количество логических чтений.

В «хорошем» плане индекс правильный, и запрос выполняется за несколько секунд.

После пересчета статистик (на зеленом графике есть участок с резким падением) план сразу нормализовался и стал подбираться оптимизатором правильно.

Операция пересчета статистики может проходить в автоматическом режиме (Auto Update Statistics) в параллель пользовательской работе. SQL Server сам решает когда обновлять статистики и запускает эту операцию. Мы не рекомендуем пользоваться этой опцией в больших и высоконагруженных системах, а использовать отдельные скрипты по пересчету статистик, запускаемые по расписанию. Об этом подробнее будет чуть ниже, а пока перейдем к индексам.

Индексы

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

Кластерные индексы (Clustered Index)

• Определяют физический порядок хранения строк в таблице.

• В таблице — только один кластерный индекс.

• Быстрый поиск по ключу – поиск конкретного значения по ключу максимально эффективен.

• Идеальны для диапазонных запросов (BETWEEN, >=, GROUP BY, ORDER BY).

Некластерные индексы (Nonclustered Index)

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

• Хранятся отдельно, содержат копию ключевых столбцов + ссылку на строку (кластерный ключ или RID).

• Ускоряют:

  • поиск по точным значениям

  • соединения (JOIN)

  • условия/фильтрацию (WHERE)

  • сортировки (ORDER BY)

  • группировки (GROUP BY)

• Могут включать дополнительные столбцы через INCLUDE — чтобы избежать Key Lookup (покрывающие индексы). Невозможность создавать на стороне платформы 1С некластерные индексы с инклюзивными полями – это, кстати, одна из серьезных проблем до недавнего времени. В 26-й версии платформы появилась наконец-таки такая возможность, но на практике мы пока не сталкивались. Если у кого есть опыт в этой части, поделитесь впечатлениями.

При создании любого индекса (кластерного или некластерного) SQL Server автоматически создаёт статистику по его ключевым столбцам.

Приоритеты обслуживания БД

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

Почему статистики — в первую очередь?

• Они напрямую влияют на выбор плана выполнения. Даже самый быстрый индекс бесполезен, если оптимизатор его не выберет.

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

• Обновление статистик – достаточно лёгкая операция по сравнению с перестроением индексов: мало блокировок, мало нагрузки на журнал транзакций.

Индексы — во вторую очередь

• Фрагментация накапливается медленно, особенно если включена автоматическая реорганизация.

• Перестроение индексов – более тяжёлая операция: требует места в tempdb, вызывает блокировки на схеме SCH-M.

Стратегия пересчета статистик, индексов и типичные ошибки

Рекомендуемый подход для регламентного обслуживания

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

Рекомендуем:

• Отключить AUTO_UPDATE_STATISTICS в базе 1С.

• Настроить ежедневное задание на целевой пересчёт статистик – в регламентное окно или в период наименьшей пользовательской активности (например, 02:00).
  В первую очередь пересчитывать статистики, которые чаще всего используются, с наибольшим количеством изменений и те, которые давно не обновлялись. Как правило ежедневного пересчета статистик хватает, чтобы не выполнять его во время рабочего дня.

• Использовать многопоточность при пересчете – 4-8 потоков и даже больше, если позволяет CPU. Ну и не забывайте распределять таблицы по разным потокам, чтобы они (потоки) не блокировали друг друга.

• В каждом потоке использовать параллелизм сервера SQL (MAXDOP >1), опять же с оглядкой на достаточность процессорных ресурсов.

• Отслеживать состояние фрагментации индексов с помощью любой программы мониторинга (мы, естественно, используем Perfexpert) и не чаще чем раз в неделю в регламентное окно запускать перестроение индексов (rebuild) для тех, где фрагментация более, например, 30%. По индексам это довольно общая рекомендация для скоростных дисковых систем на SSD, которая подходит почти всем. В этой статье погружаться детальнее не планировал, но скорее всего уже в следующей приведу более подробные выкладки и выводы.

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

Основные ошибки

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

• Выключают опцию AUTO_CREATE_STATISTICS. По невнимательности, скорее всего, т.к. по умолчанию она включена.

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

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

• Использование автоматического обновления статистик. Эта ошибка требует более развернутого ответа, поэтому о ней далее.

Опасности автоматического обновления статистик

Для высоконагруженных и больших баз данных включенная настройка AUTO_UPDATE_STATISTICS – это плохая идея. Как минимум по двум причинам:

1) Автообновление срабатывает только после изменения некоторого количества строк в таблице. Величина порога адаптивная и зависит от размера таблицы. Регулируется флагом trace flag 2371, который начиная с SQL 2016 включен по умолчанию, иначе порог срабатывания становился ~20% и очень неэффективным для больших таблиц. Например, у вас в регистре 100 млн. строк. Соответственно требуется изменение по меньшей мере 20 млн. строк, чтобы сработало автообновление статистик, а до этого момента оптимизатор будет использовать устаревшую статистику и может выбирать «плохой» план.

2) Обратная ситуация. Адаптивный порог срабатывания становится слишком низким и условно любое групповое перепроведение документов может вызвать автообновление статистик в неподходящий момент времени. Хотя обновление статистик и является онлайн операцией, тем не менее, она вызывает блокировку схемы на последнем этапе замены старой статистики на пересчитанную и приводит к блокировке всех запросов к таблице на этот промежуток времени. На рисунке ниже показан пример такой блокировки на автообновлении статистики:

 У пользователя SPID 144 выполняется длительный запрос. Оптимизатор запросов вдруг принимает решение о том, что нужно обновить статистики, т. к. они устарели. Запускается фоновый процесс со SPID 18, который обновляет статистику параллельно с активностью других соединений, и в самом конце ему нужно заменить старую статистику на новую. Накладывается блокировка схемы, и системное соединение ждёт монопольного доступа. Появляется первая блокировка между SPID 18 и 144. Теперь любой другой запрос к данной таблице будет заблокирован и завершён по таймауту. В дереве блокировок таким заблокированным сеансом является соединение SPID 154.

Предвижу комментарии, что нужно было делать пересчет в асинхронном режиме. Так вот, настройка асинхронного автообновления в этом примере была включена: AUTO_UPDATE_STATISTICS_ASYNC = ON.

Чуть подробнее о блокировках на обновлении статистик можно прочитать в другой нашей статье «Записки оптимизатора 1C (часть 7). «Нелогичные» блокировки MS SQL для систем 1С: Предприятие».

Выводы

Обслуживание базы данных — не формальность и не «разовая настройка при запуске системы», а непрерывный, осознанный процесс, напрямую влияющий на стабильность, предсказуемость и производительность всей ИТ-системы.

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

1. Приоритет №1 — это статистики. Оптимизатор SQL Server крайне чувствителен к актуальности статистик. Их своевременный пересчет — наиболее эффективный способ предотвратить выбор катастрофически неоптимальных планов запросов. Ежедневное обновление статистик в тех. окно или в период минимальной нагрузки должно стать обязательным ритуалом.

2. Автоматизируйте обслуживание свой базы для максимальной эффективности. Просто запустить sp_updatestats — недостаточно. Точно также не стоит полагаться на встроенное автообновление статистик (AUTO_UPDATE_STATISTICS) – в высоконагруженных системах это почти гарантированно приведет к блокировкам в пиковые моменты. Необходимо:

• использовать многопоточные скрипты, распределяющие нагрузку по разным таблицам;

• учитывать результаты предыдущих запусков (не пересчитывать то, что уже актуально);

• контролировать время выполнения, чтобы не выйти за рамки технологического окна;

• мониторить метрики (логические чтения, планы запросов, блокировки), чтобы подтверждать эффективность проведённых работ.

3. Обслуживание индексов требует гибкого подхода. Еженедельное перестроение индексов — хорошая отправная точка, но не догма. Используйте мониторинг для принятия решений о перестроении индексов. Аналогично пересчету статистик используйте многопоточность и для перестроения индексов. Это в разы сократит длительность регламента.

Продолжение следует…

Ссылки на остальные части Записок оптимизатора 1С:

1. Записки оптимизатора 1С (ч.1). Странное поведение MS SQL Server 2019: длительные операции TRUNCATE

2. Записки оптимизатора 1С (ч.2). Полнотекстовый индекс или как быстро искать по подстроке

3. Записки оптимизатора 1С (ч.3). Распределенные взаимоблокировки в 1С системах

4. Записки оптимизатора 1С (ч.4). Параллелизм в 1С, настройки, ожидания CXPACKET

5. Записки оптимизатора 1С (ч.5). Ускорение RLS-запросов в 1С системах

6. Записки оптимизатора 1С (ч.6). Логические блокировки MS SQL Server в 1С: Предприятие

7. Записки оптимизатора 1С (ч.7). «Нелогичные» блокировки MS SQL для систем 1С предприятия

8. Записки оптимизатора 1С (ч.8). Нагрузка на диски сервера БД при работе с 1С. Пора ли делать апгрейд?

9. Записки оптимизатора 1С (ч.9). Влияние сетевых интерфейсов на производительность высоконагруженных ИТ-систем

10. Записки оптимизатора 1С (ч.10): Как понять, что процессор — основная боль на вашем сервере MS SQL Server?

11. Записки оптимизатора 1С (ч.11). Не всегда очевидные проблемы производительности на серверах 1С.

12. Записки оптимизатора 1С (ч.12).  СрезПоследних в 1C:Предприятие на PostgreSQL. Почему же так долго?

13. Записки оптимизатора 1С (ч.13). Что не так в журнале регистрации 1С в формате SQLitе?

14. Записки оптимизатора 1С (ч.14.1). Любите свою базу данных и не забывайте обслуживать