Ax 2012 R3 CU12 and SQL 2016

[axotnik88]

Всем привет. Есть клиент с 2012 R3 CU 12 и SQL 2012. Есть куча проблем с правильными планами запросов. Пришлось даже написать несколько plan guides. Проблемное место InventSum and InventDim.
Хочу установить SQL 2016. Но многие отговаривают, говорят что будет еще хуже… Cardinality Estimator чудит.

[Logger]

Что мешает включить режим совмесьимости с предыдущей версией?
Это можно сделать только для Cardinality estimator. На форуме была тема.

[Logger]

А что у вас не так?
Опишите.

[axotnik88]

Ситуация такая - inventdim порядка 6 миллионов записей. Очистка настроена в batch + SQL maintenance каждую ночь. Ну любой join c inventdim и SQL рисует не оптимальный план, и запрос отрабатывает больше 5-10 мин. Join c inventsum или whsreserve и другие. Создаем plan guide. Но это не решение.

[fed]

Цитата:

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

Ситуация такая - inventdim порядка 6 миллионов записей. Очистка настроена в batch + SQL maintenance каждую ночь. Ну любой join c inventdim и SQL рисует не оптимальный план, и запрос отрабатывает больше 5-10 мин. Join c inventsum или whsreserve и другие. Создаем plan guide. Но это не решение.

А можно пару примеров запросов ? Не думаю, что я сильно помогу, но мне просто интересно на каких именно запросах оно так подвисает.
А еще интересно - сколько у вас номенклатуры и сколько ходовой номенклатуры (Ну то есть - той которая не просто есть в системе, а по которой операции хотя бы раз в неделю случаются)?

[axotnik88]

Количество номенклатур - 42 000
Количество активных номенклатур - 13 000
Количество активных номенклатур больше 100 проводок в месяц - 3 500
Количество новых записей в inventdim каждый день - +30 000.

Пример запроса
SELECT SUM(T1.AVAILORDERED),
SUM(T1.AVAILPHYSICAL),
SUM(T1.RESERVORDERED),
SUM(T1.RESERVPHYSICAL)
FROM WHSINVENTRESERVE T1
WHERE (((T1.PARTITION=5637144586)
AND (T1.DATAAREAID=N'bb1'))
AND ((T1.ITEMID=N'AS00013021')
AND (T1.HIERARCHYLEVEL=6)))
AND EXISTS
(SELECT 'x'
FROM INVENTDIM T2
WHERE (((T2.PARTITION=5637144586)
AND (T2.DATAAREAID=N'bb1'))
AND (((((((((T2.INVENTDIMID=T1.INVENTDIMID)
AND (T2.configId=N'v1')
AND (T2.INVENTCOLORID=N''))
AND (T2.INVENTSTYLEID=N'2018'))
AND (T2.INVENTSITEID=N'A'))
AND (T2.INVENTLOCATIONID=N'A1'))
AND (T2.INVENTBATCHID=N'0000113748'))
AND (T2.WMSLOCATIONID=N'12A1F0710B'))
AND (T2.LICENSEPLATEID=N'154110671238727282'))
AND (T2.INVENTSTATUSID=N'Blocked')))) OPTION(LOOP JOIN, FORCE ORDER)
SQL использует следующие индексы:
Index seek: InventStatudIdIdx + Key lookup : DiMIdIdx.

Я думаю что проблема в OPTION(LOOP JOIN, FORCE ORDER), но не уверен.
Только приступил к задаче.
Изображение не вставилось ссылка - http://p r n t s c r.com/mbhcza

[fed]

Цитата:

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

Количество номенклатур - 42 000
Количество активных номенклатур - 13 000
Количество активных номенклатур больше 100 проводок в месяц - 3 500
Количество новых записей в inventdim каждый день - +30 000.

Пример запроса
SELECT SUM(T1.AVAILORDERED),
SUM(T1.AVAILPHYSICAL),
SUM(T1.RESERVORDERED),
SUM(T1.RESERVPHYSICAL)
FROM WHSINVENTRESERVE T1
WHERE (((T1.PARTITION=5637144586)
AND (T1.DATAAREAID=N'bb1'))
AND ((T1.ITEMID=N'AS00013021')
AND (T1.HIERARCHYLEVEL=6)))
AND EXISTS
(SELECT 'x'
FROM INVENTDIM T2
WHERE (((T2.PARTITION=5637144586)
AND (T2.DATAAREAID=N'bb1'))
AND (((((((((T2.INVENTDIMID=T1.INVENTDIMID)
AND (T2.configId=N'v1')
AND (T2.INVENTCOLORID=N''))
AND (T2.INVENTSTYLEID=N'2018'))
AND (T2.INVENTSITEID=N'A'))
AND (T2.INVENTLOCATIONID=N'A1'))
AND (T2.INVENTBATCHID=N'0000113748'))
AND (T2.WMSLOCATIONID=N'12A1F0710B'))
AND (T2.LICENSEPLATEID=N'154110671238727282'))
AND (T2.INVENTSTATUSID=N'Blocked')))) OPTION(LOOP JOIN, FORCE ORDER)
SQL использует следующие индексы:
Index seek: InventStatudIdIdx + Key lookup : DiMIdIdx.

Я думаю что проблема в OPTION(LOOP JOIN, FORCE ORDER), но не уверен.
Только приступил к задаче.
Изображение не вставилось ссылка - http://p r n t s c r.com/mbhcza

Да- здесь проблема и в правду скорее всего в Force order. Скорее всего число записей в WHSInventReserve достаточно небольшое (может порядка 20-30-40 тысяч), соответственно система сначала должна по этой табличке отобрать, а потом уже результаты джойнить со всем остальным.

[Logger]

А сколько у вас там в WHSINVENTRESERVE записей с N'AS00013021 ?
И сколько всего записей ?

2. А пример с InventSum ?

Попробуйте отключить FORCE ORDER

[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 будет держать его размер в разумных пределах.

[axotnik88]

Всем спс за помощь, будем пробовать и оптимизировать.
Реальность таковая -
1. Каждый месяц прирост порядка 1 - 1.2 миллиона записей.
2. Стандартные clean up jobs очищают порядка 200-300 тис.
3. После очистки “closed” license plates, стандартные clean up jobs очистить порядка 200-250 т. судя по запросам. Очистить больше нельзя, так как система создаст inventdim запись с тем же batch id но с пустым license plate. Скорее всего что на каком-то этапе стандартные clean up jobs удаляет строку с пустым license plate.
4. В итоге чистый прирост порядка 400 тис – 600 тис, в месяц и на данном этапе шагом номер два можно очистить без прироста порядка 2,5 миллионов записей при общем количестве записей 9 м.