axinthefield: SQL Server 2016 CE and Dynamics AX 2012 R3

[Blog bot]

Источник: https://blogs.msdn.microsoft.com/axi...cs-ax-2012-r3/
==============

Hello, everyone!  As many of you are already aware, SQL Server’s Cardinality Estimator received its first update in SQL Server 2014 (this was its first update since SQL Server 7.0), then again in SQL Server 2016.  Many customers on Dynamics AX 2012 R3 who have upgraded to SQL Server 2016 and have changed the compatibility...

Источник: https://blogs.msdn.microsoft.com/axi...cs-ax-2012-r3/

[trud]

о, вот еще один пример вдумчивого и кропотливого анализа. типа что-то не сработало, отключаем.
т.е. логика того, что новый оптимизатор из 100 запросов может улучшить 90(что скорее всего не заметят пользователи, ибо работает и работает), а 10 станут хуже(что они конечно же заметят) и что эти 10 можно как-то подтюнить даже не рассматривается

[Vadik]

Цитата:

Сообщение от trud

т.е. логика того, что новый оптимизатор из 100 запросов может улучшить 90(что скорее всего не заметят пользователи, ибо работает и работает), а 10 станут хуже(что они конечно же заметят) и что эти 10 можно как-то подтюнить даже не рассматривается

Все логично. Включаем, тестируем, применяем на продуктиве. Если там что-то идет не так, откручиваем уровень совместимости обратно, тюним. Далее цикл повторяется

[Logger]

Цитата:

Сообщение от trud

т.е. логика того, что новый оптимизатор из 100 запросов может улучшить 90(что скорее всего не заметят пользователи, ибо работает и работает), а 10 станут хуже(что они конечно же заметят) и что эти 10 можно как-то подтюнить даже не рассматривается

Цитата:

Сообщение от Vadik

Если там что-то идет не так, откручиваем уровень совместимости обратно, тюним.

А вот интересно, кто-нить пробовал вместо включения LEGACY_CARDINALITY_ESTIMATION как советуют в статье
https://web.archive.org/web/20190112...cs-ax-2012-r3/

Цитата:

Hello, everyone! As many of you are already aware, SQL Server's Cardinality Estimator received its first update in SQL Server 2014 (this was its first update since SQL Server 7.0), then again in SQL Server 2016. Many customers on Dynamics AX 2012 R3 who have upgraded to SQL Server 2016 and have changed the compatibility mode to 130 have experienced performance degradation. This is a result of the new Cardinality Estimator. To resolve this issue, instead of changing the compatibility level down to 110 (SQL Server 2012), you can change several options at the database level while leaving the compatibility level at 130:

< set_options > ::=
{
MAXDOP = {<value>}
| LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}
| PARAMETER_SNIFFING = { ON | OFF | PRIMARY}
| QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
}

Please note that in SQL Server 2016, MAXDOP is now a database-level setting. Setting MAXDOP to 1 is still a recommended setting for Dynamics AX.

использовать
QUERY_OPTIMIZER_HOTFIXES
?

[Logger]

Спрашиваю потому что столкнулся с похожей проблемой.
Причем в моем случае QUERY_OPTIMIZER_HOTFIXES также помогает.

Но это я смотрел пока только один запрос. Не факт что поможет в других случаях.

[Logger]

Цитата:

Сообщение от Masel

[B]
По поводу расчета кардинальности На сколько я помню там было очень много странных планов, и пришлось много план гайдов сделать, чтобы снять "пожар" начальный. Больше всего такое удивляло. Есть запрос к таблице в 100 тысяч строк с единственным фильтром по полю из некластерного индекса. Тут даже без статистики понятно, что оптимизатор должен сделать index seek по этому полю и далее по результату lookup на кластерный. Но оптимизатор ищет по кластерному. Пересчитываешь статикику фулсканом, ставишь конкретные значения параметров, все равно кластерный индекс. Поведение такое, как будто там 10 строк. При переключении на старый алгоритм все эти странности пропадают.

Похоже там что-то со статистикой не так, а именно с гистограммами значений.

В моем случае в таблице 3 млн записей.
В индексированном поле по которому идет фильтрация всего 20 записей имеют непустое значение (и все разное)

Пробовал смотреть для индекса статистику командой
DBCC SHOW_STATISTICS
в разделе с гистограммами значений только одно значение после обычного обновления статистики командой UPDATE STATISTICS tablename (думает 7 секунд)
А если
UPDATE STATISTICS tablename WITH FULLSCAN (думает 3 минуты)
то в гистограмме видно все 20 значений и план запроса выправляется

аналогичный эффект имеет
UPDATE STATISTICS tablename WITH FULLSCAN, INDEX (думает 20 секунд)
только работает намного быстрее.

Жаль что в 2012-й отключили index hint
Он бы решил проблему.