Ax 2012 R3 CU12 and SQL 2016

[axotnik88]

1. 150.
2.
SELECT SUM(T1.PHYSICALINVENT),
SUM(T1.RESERVPHYSICAL),
SUM(T1.RESERVORDERED),
SUM(T1.ONORDER),
SUM(T1.QUOTATIONISSUE),
SUM(T1.ARRIVED),
SUM(T1.ORDERED),
SUM(T1.QUOTATIONRECEIPT),
T1.ITEMID,
T2.CONFIGID,
T2.INVENTSIZEID,
T2.INVENTSTYLEID,
T2.INVENTSITEID,
T2.INVENTLOCATIONID,
T2.INVENTBATCHID,
T2.INVENTSTATUSID
FROM INVENTSUM T1
CROSS JOIN INVENTDIM T2
WHERE (((T1.PARTITION=5637144576)
AND (T1.DATAAREAID=N'bb1'))
AND (((T1.CLOSED=0)
AND (T1.CLOSEDQTY=0))
AND (T1.ITEMID=N'AS00013021')))
AND (((T2.PARTITION=5637144576)
AND (T2.DATAAREAID=N'bb1'))
AND (((((((T2.CONFIGID=N'v2')
AND (T2.INVENTSIZEID=N'12'))
AND (T2.INVENTSTYLEID=N'2018'))
AND (T2.INVENTSITEID=N'A'))
AND (T2.INVENTLOCATIONID=N'A1'))
AND (T2.INVENTSTATUSID=N'Blocked'))
AND (T1.INVENTDIMID=T2.INVENTDIMID)))
GROUP BY T1.ITEMID,
T2.CONFIGID,
T2.INVENTSIZEID,
T2.INVENTSTYLEID,
T2.INVENTSITEID,
T2.INVENTLOCATIONID,
T2.INVENTBATCHID,
T2.INVENTSTATUSID
ORDER BY T1.ITEMID,
T2.CONFIGID,
T2.INVENTSIZEID,
T2.INVENTSTYLEID,
T2.INVENTSITEID,
T2.INVENTLOCATIONID,
T2.INVENTBATCHID,
T2.INVENTSTATUSID

[fed]

Цитата:

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

1. 150.
2.
SELECT SUM(T1.PHYSICALINVENT),
SUM(T1.RESERVPHYSICAL),
SUM(T1.RESERVORDERED),
SUM(T1.ONORDER),
SUM(T1.QUOTATIONISSUE),
SUM(T1.ARRIVED),
SUM(T1.ORDERED),
..
T2.INVENTSIZEID,
T2.INVENTSTYLEID,
T2.INVENTSITEID,
T2.INVENTLOCATIONID,
T2.INVENTBATCHID,
T2.INVENTSTATUSID

Ну я бы сказал что здесь план запроса как раз нормальный. Сначала отбирается из InventDim по статусу, потом подгружаются из кластерного индекса обычные поля, потом читается из inventSum записи по данной номнклатуре и джойнятся с выбраными записями из inventDim. Единственное что можно попытаться улучшить - построить индекс по inventDum по всем условиям запроса. (Но надо конечно смотреть- какие у вас там группы складской аналитики используются. Если у вас 20 разных групп с разными комбинациями аналитики, то на каждую группу своего индекса не построишь).

Еще как идея - я аналогичную ситуацию лечил с помощью Clustered Index View,предложенного Data Tuning Advisor. Только мне пришлось после строительства view добавлять еще и индекс дополнительный, потому что запрос, используемый в clustered view,приводил к заметным задержкам по inventSum.update(). Но в итоге - у меня время запроса который при списании номенклатуры проверяет уровень запасов в наличии, упало с 4 секунд где-то до 100ms.

[axotnik88]

Просто как по мне система должна использовать более подходящий индекс.
В первом случае раз у нас OPTION(LOOP JOIN, FORCE ORDER) и данные с первой таблицы счытаются первее, система должна использовать кластерный индекс DimIdIdx и тупо забить на where стейтмент.
Во втором случае, на InventDim есть больше подходящие индексы, чем статус. Срез по статусу дает больше 30000 рекордов.
В системе c стандартных аналитик не используется только InventColorId и InventSerial.Можно попробовать исключить из макроса и с индексов. Если честно думаю полностю пересмотреть индексы на ИнвентДим. Поставить на первое место самый уникальный – например LisensePlate or InventBatchId. Плюс в каждый индекс добавить inventDimId как included column, убрав таким образом key lookup/. Будем пробовать.

[trud]

Цитата:

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

Плюс в каждый индекс добавить inventDimId как included column, убрав таким образом key lookup/. Будем пробовать.

Рекомендую все же начать с теории, InventDimId и так присутсвует везде
https://www.brentozar.com/archive/20...tered-indexes/

Цитата:

SQL ‘hides’ the columns from the Key of the Clustered Index in Nonclustered Indexes
Since those columns are part of the index, you don’t need to include them in the definition

Plan guides или forceliterals являются единственным решением проблемы в неоднородных данных, с этим ничего не поделать
Думаю в MS тоже с этим столкнулись когда сами начали хостить АХ и и поэтому вернули index hints в новых версиях

https://denistrunin.com/forceliteral...ePlaceholders/

[Polgid]

У одного из клиентов похожая ситуация. Большой InventDim - около 4 млн записей, который из-за новых LicensePlate каждый день растет где-то на 30000 записей.

Из-за этого несколько запросов, где был join с InventDim (с InventSum, InventTrans и даже с InventSumDelta и InventSumDeltaDim) выполнялись очень медленно, в случае если SQL почему то выбирал план выполнения
c Nested Loops и InventDim на верхнем уровне или с Merge join. Причем это не из-за неоднородных данных, пробовали добавлять forceliterals, SQL все равно выбирает не оптимальный план.

SQL2016 и AX2012R3 CU13, так что если установите SQL2016 сильно лучше с планами запросов не будет.

Пока что проблему решили добавлением в этих запросах forceNestedLoop forceSelectOrder и перемещением InventDim в самый низ запроса (в случае если он был не последний).
Во всех этих запросах есть фильтр по определенному ItemId или ttsId, так что верхний уровень запроса отрабатывает быстро.

Интерестно что будет через 2-3 года, когда InventDim будет 20-30 млн записей (go live у клиента был меньше года назад).
Проблему в том, что даже если LicensePlate полностью отгружен и получен и InventSum по нему Closed и удален (стандарной процедурой очистки),
ссылки на LicensePlate остаются в InventTrans и стандартная процедура по очистке InventDim не удаляет строки с этим LicensePlate.

Как вариант думаю написать периодическую операцию, которая будет идти по InventTrans и заменять InventDimId c "закрытым" LicensePlate, на такой же но с пустым LicensePlate.
В функциональном плане, вроде как, смыла хранить ссылки на такие "закрытые" LicensePlate нет. Таким образом, операция по очистке InventDim будет держать его размер в разумных пределах.

[Vadik]

Цитата:

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

Интерестно что будет через 2-3 года, когда InventDim будет 20-30 млн записей (go live у клиента был меньше года назад)

Постепенно придет понимание (и у Вас, и у оптимизатора) того, что в WHS со временем размер InventSum стабилизируется а InvendDim будет за счет партий и LP расти постоянно, и это реальность в которой надо жить, а не бороться с ней (все стандартные для WHS процедуры очистки разумеется используем). У нас в данный момент в InventDim 17M записей, и растет довольно быстро за счет новых компаний

Кластерный индекс по InventDimId и скан по нему конечно зело привлекателен для оптимизатора. Будут пограничные моменты "вчера работало сегодня перестало" когда оптимизатор путается, но со временем статистика выравнивается в пользу того что InventDim все же слишком большой для скана и InventSum отфильтровать по ItemId дешевле . После их прохождения с доработкой напильником можно сказать, что прозводительность стабилизируется. В качестве эксперимента - попробуйте DimIdIdx сделать некластерным - InventDim будет гораздо реже выбираться для ранних стадиях обработки

Есть достаточно много мест в WHS где идет обращение к "финансово открытому" складу (InventSum.Closed) а по-хорошему должно было бы к "незакрытому физически" (InventSum.ClosedQty) - там правили код и дополнительно индексировали (ClosedQty+InventDimId+PhysycalInvent) . Помогало

Цитата:

Хочу установить SQL 2016. Но многие отговаривают, говорят что будет еще хуже

"Хуже" не будет. Просто "летать" _все_ после апгрейда не будет, и обязательно вылезет пара мест где будет хуже, чем до него (по закону Мерфи, или даже в местах вообще не связанных с апгрейдом, просто по времени два события наложатся). Из-за особенностей человеческой психики, большинство улучшений остаются незамеченными или про них очень быстро забывают, а негативные моменты запоминаются надолго. Но это моменты из плоскости управления ожиданиями, не технические

Проблема немного шире и не должна сводиться к версии СУБД (хоть я и за то чтобы работать на актуальной). Новый оптимизатор в чем-то умнее старого, но "продавать" как универсальное решение проблем с производительностью его нельзя. Есть узкие места в приложении вроде того же "финансового закрытия", могут быть дурацкие настройки самого WHS (консультанты например регулярно какой-то трэш устраивают в location directives) - со всем этим приходится разбираться по месту