В этом посте хотим рассказать о, на наш взгляд, незаслуженно редко используемом методе оптимизации тяжелых запросов к БД Axapta – Plan Guides. Если кратко – это, по сути, механизм «подсказки» оптимизатору SQL правильного плана запроса. В некоторых случаях его использование может быть оправданным, а иногда даже единственным возможным.
Всем привет!
В серии постов хотим поделиться нашим опытом разработки и эксплуатации систем семейства MS Dynamics AX (в прошлом Axapta).
Мы – сравнительно молодая розничная сеть продуктовых супермаркетов «Да!». На момент написания поста у нас чуть больше 100 магазинов. Основные процессы операционной деятельности компании автоматизированы в связке систем MS Dynamics Ax 2012 + MS Dynamics 365 FO. Системы работают в режиме 24\7. Через систему в среднем проходит около миллиона строк чеков и около 70 000 строк заданий на сборку товара в день.
Тюнинг производительности Аксапты можно выполнять разными способами. Наиболее эффективный способ – это, конечно, оптимизация исходного кода приложения. Но бывают ситуации, когда это сделать проблематично. Тогда можно воспользоваться инструментом, который предоставляет СУБД MS SQL Server. Называется он — Plan guide. Появился еще в версии SQL Server 2005. Но по нашим ощущениям в сообществе Аксаптеров (особенно, если в компании нет профессионального DBA), его не всегда знают и используют. Хотя в некоторых случаях его применение может быть очень эффективным.
Вот основные причины, когда стоит посмотреть в сторону этого инструмента:
1. Не консистентные данные в параметрах запроса. Результат выборки (количество записей) для одного и того же критерия выборки (набора полей) отличается в зависимости от значений переменных в критериях выборки. Или, по-простому, когда один и тот же запрос для разных входных параметров дает в результате радикально разное количество записей (иногда одну, а иногда — десять тысяч). Связано это с так называемым “sniffing” параметров запроса. Когда план запроса один раз создается под маску запроса, и в дальнейшем берется из кэша. Не глядя на значения этих самых параметров.
Такое часто бывает с запросами, в которых участвуют таблицы InventSum и InventDim. Например, когда в аналитике Партия для каких-то номенклатур партия всего одна, а для других – несколько тысяч. Первый запрос к базе может пройти для номенклатуры, у которой выключен партионный учет. Под него оптимизатор построит план запроса. И положит его в кэш. Следующий запрос к базе может пройти для номенклатуры, у которой включен партионный учет. И выдать несколько тысяч записей в выборке по InventSum и InventDim. И для такой выборки план из кэша будет неоптимальным.
Один из вариантов решения такой проблемы – использования хинта forceLiterals в тексте запроса. Это дает сигнал движку SQL каждый раз формировать новый план запроса. Но это дает дополнительную ощутимую нагрузку на CPU. И с теми же запросами остатков с использованием InventDim – это не приемлемый вариант. Ну и нужно понимать, что оптимизатор SQL Server не идеален и иногда даже при наличии полной статистики генерит странные планы.
И в этом случае на помощь приходят Plan Guide, с помощью которого можно подобрать план запроса, дающий приемлемую скорость выполнения для любых входных параметров запроса. И прикрепить этот план к маске запроса с помощью Plan Guide.
2. Оптимизатор выбирает индекс, использование которого приводит к длительным блокировкам. С помощью Plan Guide можно «прибить» использование конкретного индекса, который сузит выборку и снизит количество блокировок.
3. Источник (место в коде) проблемного запроса не удается быстро установить, а проблему падения производительности БД нужно оперативно решать.
4. Исходный код приложения по каким-то причинам нельзя изменить (партнерское решение, запросы из ядра и т.п.). Особенно это стало актуально для D365, в которой запрещен оверлейенг.
Подробно описывать пошаговое руководство по созданию Plan Guide я не буду. Есть хорошее описание на сайте вендора (нас интересует тип плана — SQL) И в сети есть море tutorials.
Но важно знать, что есть еще один инструмент SQL Server, который будет вам большим подспорьем, если нужно создать новый Plan Guide. Называется он – Query Store. Появился с 2016-ой версии. Подробное описание его здесь.
Основная идея инструмента в том, что помимо текущего плана запроса в кэше, он хранит всю историю планов, которые формировал оптимизатор за заданное время. Если вы знаете, что раньше проблемный функционал работал «нормально». Не тормозил. Вам остается только найти нужный план в хранилище и сделать на его основе Plan Guide. К сожалению, из-за особенностей Аксапты одной кнопкой «force plan» создать Plan Guide нельзя. Придется скопировать план запроса из хранилища и создать Plan Guide вручную. Но это все равно сильно упрощает задачу.
Также нужно учитывать, что применение Query Store дает небольшой overhead на используемые вычислительные ресурсы сервера СУБД. Но по нашей практике они незначительны, и этим можно пренебречь.
Приведу пару примеров Plan Guide с нашей реальной боевой базы. Обращаю внимание, что это всего лишь примеры, актуальные для наших конкретных бизнес-процессов. Они могут быть не применимы и даже вредны для вашей инсталляции.
1. InventSum
Данный Plan guide решает проблему неоптимальных планов в случае с запросами по номенклатурам с малым количеством записей в таблице InventDim. Использование данного гайда позволяет всегда использовать план, оптимальный для выборки с большим количеством комбинаций InventDim для SKU. Запросы по номенклатурам с малым количеством SKU будут работать чуть медленней. Но это не большая плата за стабильную и предсказуемую скорость для любых комбинаций входных параметров.
Запросы эти в основном генерятся методом InventSum::findSum(). И в зависимости от группировки шаблоны запросов могут немного отличаться. Так что в реальности у нас больше подобных Plan Guide, адаптированных под разные варианты группировок.
2. InventSumDelta
Данный Plan Guide позволяет строить оптимальный план запроса к таблице InventSumDelta, позволяющий избежать ненужных блокировок этой таблицы. Специфика данной таблицы такова, что данные в ней не хранятся. Но очень интенсивно добавляются\удаляются. Это, по сути, таблица-семафор. В связи с этим нормальной статистики по этой таблице не собрать. Поэтому оптимизатор иногда формировал неоптимальные планы, приводящие к блокировкам.
Немного оффтоп – также для этой таблицы нужно отключить страничные блокировки на индексах. Так как выборка из этой таблицы всегда происходит по уникальному ID, эскалация блокировок на уровень страниц здесь не имеет смысла и даже вредна.
Но в общем случае, обращу еще раз внимание, злоупотреблять этим инструментом не стоит. Если код написан оптимально, регулярно обновляется статистика, индексы не сильно фрагментированы – оптимизатор в большинстве случаев сам подберет корректный план. А вот если настроен plan guide, входные критерии запроса могут выпасть такие, что plan guide будет только вредить.
Всем привет!
В серии постов хотим поделиться нашим опытом разработки и эксплуатации систем семейства MS Dynamics AX (в прошлом Axapta).
О нас
Мы – сравнительно молодая розничная сеть продуктовых супермаркетов «Да!». На момент написания поста у нас чуть больше 100 магазинов. Основные процессы операционной деятельности компании автоматизированы в связке систем MS Dynamics Ax 2012 + MS Dynamics 365 FO. Системы работают в режиме 24\7. Через систему в среднем проходит около миллиона строк чеков и около 70 000 строк заданий на сборку товара в день.
Plan guides
Тюнинг производительности Аксапты можно выполнять разными способами. Наиболее эффективный способ – это, конечно, оптимизация исходного кода приложения. Но бывают ситуации, когда это сделать проблематично. Тогда можно воспользоваться инструментом, который предоставляет СУБД MS SQL Server. Называется он — Plan guide. Появился еще в версии SQL Server 2005. Но по нашим ощущениям в сообществе Аксаптеров (особенно, если в компании нет профессионального DBA), его не всегда знают и используют. Хотя в некоторых случаях его применение может быть очень эффективным.
Зачем?
Вот основные причины, когда стоит посмотреть в сторону этого инструмента:
1. Не консистентные данные в параметрах запроса. Результат выборки (количество записей) для одного и того же критерия выборки (набора полей) отличается в зависимости от значений переменных в критериях выборки. Или, по-простому, когда один и тот же запрос для разных входных параметров дает в результате радикально разное количество записей (иногда одну, а иногда — десять тысяч). Связано это с так называемым “sniffing” параметров запроса. Когда план запроса один раз создается под маску запроса, и в дальнейшем берется из кэша. Не глядя на значения этих самых параметров.
Такое часто бывает с запросами, в которых участвуют таблицы InventSum и InventDim. Например, когда в аналитике Партия для каких-то номенклатур партия всего одна, а для других – несколько тысяч. Первый запрос к базе может пройти для номенклатуры, у которой выключен партионный учет. Под него оптимизатор построит план запроса. И положит его в кэш. Следующий запрос к базе может пройти для номенклатуры, у которой включен партионный учет. И выдать несколько тысяч записей в выборке по InventSum и InventDim. И для такой выборки план из кэша будет неоптимальным.
Один из вариантов решения такой проблемы – использования хинта forceLiterals в тексте запроса. Это дает сигнал движку SQL каждый раз формировать новый план запроса. Но это дает дополнительную ощутимую нагрузку на CPU. И с теми же запросами остатков с использованием InventDim – это не приемлемый вариант. Ну и нужно понимать, что оптимизатор SQL Server не идеален и иногда даже при наличии полной статистики генерит странные планы.
И в этом случае на помощь приходят Plan Guide, с помощью которого можно подобрать план запроса, дающий приемлемую скорость выполнения для любых входных параметров запроса. И прикрепить этот план к маске запроса с помощью Plan Guide.
2. Оптимизатор выбирает индекс, использование которого приводит к длительным блокировкам. С помощью Plan Guide можно «прибить» использование конкретного индекса, который сузит выборку и снизит количество блокировок.
3. Источник (место в коде) проблемного запроса не удается быстро установить, а проблему падения производительности БД нужно оперативно решать.
4. Исходный код приложения по каким-то причинам нельзя изменить (партнерское решение, запросы из ядра и т.п.). Особенно это стало актуально для D365, в которой запрещен оверлейенг.
Как?
Подробно описывать пошаговое руководство по созданию Plan Guide я не буду. Есть хорошее описание на сайте вендора (нас интересует тип плана — SQL) И в сети есть море tutorials.
Но важно знать, что есть еще один инструмент SQL Server, который будет вам большим подспорьем, если нужно создать новый Plan Guide. Называется он – Query Store. Появился с 2016-ой версии. Подробное описание его здесь.
Основная идея инструмента в том, что помимо текущего плана запроса в кэше, он хранит всю историю планов, которые формировал оптимизатор за заданное время. Если вы знаете, что раньше проблемный функционал работал «нормально». Не тормозил. Вам остается только найти нужный план в хранилище и сделать на его основе Plan Guide. К сожалению, из-за особенностей Аксапты одной кнопкой «force plan» создать Plan Guide нельзя. Придется скопировать план запроса из хранилища и создать Plan Guide вручную. Но это все равно сильно упрощает задачу.
Также нужно учитывать, что применение Query Store дает небольшой overhead на используемые вычислительные ресурсы сервера СУБД. Но по нашей практике они незначительны, и этим можно пренебречь.
Примеры
Приведу пару примеров Plan Guide с нашей реальной боевой базы. Обращаю внимание, что это всего лишь примеры, актуальные для наших конкретных бизнес-процессов. Они могут быть не применимы и даже вредны для вашей инсталляции.
1. InventSum
Данный Plan guide решает проблему неоптимальных планов в случае с запросами по номенклатурам с малым количеством записей в таблице InventDim. Использование данного гайда позволяет всегда использовать план, оптимальный для выборки с большим количеством комбинаций InventDim для SKU. Запросы по номенклатурам с малым количеством SKU будут работать чуть медленней. Но это не большая плата за стабильную и предсказуемую скорость для любых комбинаций входных параметров.
Запросы эти в основном генерятся методом InventSum::findSum(). И в зависимости от группировки шаблоны запросов могут немного отличаться. Так что в реальности у нас больше подобных Plan Guide, адаптированных под разные варианты группировок.
2. InventSumDelta
Данный Plan Guide позволяет строить оптимальный план запроса к таблице InventSumDelta, позволяющий избежать ненужных блокировок этой таблицы. Специфика данной таблицы такова, что данные в ней не хранятся. Но очень интенсивно добавляются\удаляются. Это, по сути, таблица-семафор. В связи с этим нормальной статистики по этой таблице не собрать. Поэтому оптимизатор иногда формировал неоптимальные планы, приводящие к блокировкам.
Немного оффтоп – также для этой таблицы нужно отключить страничные блокировки на индексах. Так как выборка из этой таблицы всегда происходит по уникальному ID, эскалация блокировок на уровень страниц здесь не имеет смысла и даже вредна.
Выводы
Но в общем случае, обращу еще раз внимание, злоупотреблять этим инструментом не стоит. Если код написан оптимально, регулярно обновляется статистика, индексы не сильно фрагментированы – оптимизатор в большинстве случаев сам подберет корректный план. А вот если настроен plan guide, входные критерии запроса могут выпасть такие, что plan guide будет только вредить.
