AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX: Администрирование
DAX
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 24.01.2019, 00:03   #1  
axotnik88 is offline
axotnik88
Участник
 
82 / 18 (1) ++
Регистрация: 05.06.2012
Ax 2012 R3 CU12 and SQL 2016
Всем привет. Есть клиент с 2012 R3 CU 12 и SQL 2012. Есть куча проблем с правильными планами запросов. Пришлось даже написать несколько plan guides. Проблемное место InventSum and InventDim.
Хочу установить SQL 2016. Но многие отговаривают, говорят что будет еще хуже… Cardinality Estimator чудит.
Старый 24.01.2019, 06:56   #2  
Logger is offline
Logger
Участник
Лучший по профессии 2015
Лучший по профессии 2014
 
3,867 / 3123 (112) ++++++++++
Регистрация: 12.10.2004
Адрес: Москва
Записей в блоге: 2
Что мешает включить режим совмесьимости с предыдущей версией?
Это можно сделать только для Cardinality estimator. На форуме была тема.
Старый 24.01.2019, 08:48   #3  
Logger is offline
Logger
Участник
Лучший по профессии 2015
Лучший по профессии 2014
 
3,867 / 3123 (112) ++++++++++
Регистрация: 12.10.2004
Адрес: Москва
Записей в блоге: 2
А что у вас не так?
Опишите.
Старый 24.01.2019, 12:11   #4  
axotnik88 is offline
axotnik88
Участник
 
82 / 18 (1) ++
Регистрация: 05.06.2012
Ситуация такая - inventdim порядка 6 миллионов записей. Очистка настроена в batch + SQL maintenance каждую ночь. Ну любой join c inventdim и SQL рисует не оптимальный план, и запрос отрабатывает больше 5-10 мин. Join c inventsum или whsreserve и другие. Создаем plan guide. Но это не решение.
Старый 24.01.2019, 12:41   #5  
fed is offline
fed
Moderator
Аватар для fed
Ex AND Project
Соотечественники
Лучший по профессии 2017
Лучший по профессии 2015
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
Лучший по профессии 2011
Лучший по профессии 2009
 
2,890 / 5647 (194) ++++++++++
Регистрация: 13.03.2002
Адрес: Hüfingen,DE
Цитата:
Сообщение от axotnik88 Посмотреть сообщение
Ситуация такая - inventdim порядка 6 миллионов записей. Очистка настроена в batch + SQL maintenance каждую ночь. Ну любой join c inventdim и SQL рисует не оптимальный план, и запрос отрабатывает больше 5-10 мин. Join c inventsum или whsreserve и другие. Создаем plan guide. Но это не решение.
А можно пару примеров запросов ? Не думаю, что я сильно помогу, но мне просто интересно на каких именно запросах оно так подвисает.
А еще интересно - сколько у вас номенклатуры и сколько ходовой номенклатуры (Ну то есть - той которая не просто есть в системе, а по которой операции хотя бы раз в неделю случаются)?
Старый 24.01.2019, 13:36   #6  
axotnik88 is offline
axotnik88
Участник
 
82 / 18 (1) ++
Регистрация: 05.06.2012
Количество номенклатур - 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

Последний раз редактировалось axotnik88; 24.01.2019 в 13:39.
Старый 24.01.2019, 13:46   #7  
Logger is offline
Logger
Участник
Лучший по профессии 2015
Лучший по профессии 2014
 
3,867 / 3123 (112) ++++++++++
Регистрация: 12.10.2004
Адрес: Москва
Записей в блоге: 2
А сколько у вас там в WHSINVENTRESERVE записей с N'AS00013021 ?
И сколько всего записей ?

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

Попробуйте отключить FORCE ORDER
Старый 24.01.2019, 14:31   #8  
axotnik88 is offline
axotnik88
Участник
 
82 / 18 (1) ++
Регистрация: 05.06.2012
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

Нажмите на изображение для увеличения
Название: Screenshot_54.png
Просмотров: 176
Размер:	21.1 Кб
ID:	12186
Старый 24.01.2019, 15:43   #9  
fed is offline
fed
Moderator
Аватар для fed
Ex AND Project
Соотечественники
Лучший по профессии 2017
Лучший по профессии 2015
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
Лучший по профессии 2011
Лучший по профессии 2009
 
2,890 / 5647 (194) ++++++++++
Регистрация: 13.03.2002
Адрес: Hüfingen,DE
Цитата:
Сообщение от 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 (1), Logger (3).
Старый 24.01.2019, 15:54   #10  
fed is offline
fed
Moderator
Аватар для fed
Ex AND Project
Соотечественники
Лучший по профессии 2017
Лучший по профессии 2015
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
Лучший по профессии 2011
Лучший по профессии 2009
 
2,890 / 5647 (194) ++++++++++
Регистрация: 13.03.2002
Адрес: Hüfingen,DE
Цитата:
Сообщение от 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 тысяч), соответственно система сначала должна по этой табличке отобрать, а потом уже результаты джойнить со всем остальным.
Старый 24.01.2019, 16:02   #11  
axotnik88 is offline
axotnik88
Участник
 
82 / 18 (1) ++
Регистрация: 05.06.2012
Просто как по мне система должна использовать более подходящий индекс.
В первом случае раз у нас OPTION(LOOP JOIN, FORCE ORDER) и данные с первой таблицы счытаются первее, система должна использовать кластерный индекс DimIdIdx и тупо забить на where стейтмент.
Во втором случае, на InventDim есть больше подходящие индексы, чем статус. Срез по статусу дает больше 30000 рекордов.
В системе c стандартных аналитик не используется только InventColorId и InventSerial.Можно попробовать исключить из макроса и с индексов. Если честно думаю полностю пересмотреть индексы на ИнвентДим. Поставить на первое место самый уникальный – например LisensePlate or InventBatchId. Плюс в каждый индекс добавить inventDimId как included column, убрав таким образом key lookup/. Будем пробовать.
Старый 25.01.2019, 03:05   #12  
trud is offline
trud
Участник
Лучший по профессии 2017
 
1,038 / 1629 (57) ++++++++
Регистрация: 07.06.2003
Записей в блоге: 1
Цитата:
Сообщение от 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/
За это сообщение автора поблагодарили: axotnik88 (1).
Старый 25.01.2019, 09:13   #13  
Polgid is offline
Polgid
Участник
 
11 / 28 (1) +++
Регистрация: 07.05.2010
У одного из клиентов похожая ситуация. Большой 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 будет держать его размер в разумных пределах.
За это сообщение автора поблагодарили: AlGol (2), trud (2), Logger (3), axotnik88 (1).
Старый 25.01.2019, 15:13   #14  
Vadik is offline
Vadik
Модератор
Аватар для Vadik
Лучший по профессии 2017
Лучший по профессии 2015
 
3,631 / 1849 (69) ++++++++
Регистрация: 18.11.2002
Адрес: гражданин Москвы
Цитата:
Сообщение от 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) - со всем этим приходится разбираться по месту
__________________
-ТСЯ или -ТЬСЯ ?

Последний раз редактировалось Vadik; 25.01.2019 в 20:25.
За это сообщение автора поблагодарили: AlGol (2), fed (4), trud (5), sukhanchik (8), Polgid (1), axotnik88 (1).
Старый 31.01.2019, 00:45   #15  
axotnik88 is offline
axotnik88
Участник
 
82 / 18 (1) ++
Регистрация: 05.06.2012
Всем спс за помощь, будем пробовать и оптимизировать.
Реальность таковая -
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 м.

Последний раз редактировалось axotnik88; 31.01.2019 в 00:59.
Теги
ax2012r3, sql server 2016, план запроса, производительность

 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
yetanotherdynamicsaxblog: Error when installing Reporting Extensions for AX2012 R3 on SQL Server 2016 Blog bot DAX Blogs 0 14.09.2017 13:11
dynamicsaxse: July 2017 release – Dynamics AX 2012 R3 Blog bot DAX Blogs 0 25.07.2017 20:11
dynamicsaxse: January release – Dynamics AX 2012 R3 Blog bot DAX Blogs 0 12.01.2017 00:12
dynamicscpm: Management Reporter 2012 CU 10 Now Available! Blog bot DAX Blogs 0 01.10.2014 02:11
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 15:28.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.