AX 2012 R3, SQL 2012, InventSum+InventDim оптимизация

[skycap]

Люди добрые, здравствуйте.

Работаем c R3, с WHS. Работаем давно, практически с момента выхода этой функциональности.

Помогите разобраться с проблемкой. Для начала, если в общем, проблема в том, что периодически (несколько раз в день) стандартный запрос к InventSum в связке с InventDim начинает выполняться долго, точнее, ОЧЕНЬ ДОЛГО, со всеми вытекающими.

InventSum - 3,5 млн записей, InventDim - 35 млн.

Говоря про стандартный запрос я имею ввиду метод findSumQty на таблице InventSum.

select
        #inventSumQtyFields
        from inventSum
            where inventSum.ItemId      == _itemId
               && inventSum.ClosedQty   == NoYes::No
        #inventDimExistsJoin(inventSum.InventDimId,inventDim,_InventDimCriteria,_InventDimParm);
        //#InventDimJoin(inventSum.InventDimId,inventDim,_InventDimCriteria,_InventDimParm);

В стандарте InventDim джойнится к InventSum через exists join. На начальном этапе запуска exists join поменяли на join tableId (вместо макроса #inventDimExistsJoin использовали #InventDimJoin).
И вроде все было хорошо, пока в какой-то момент времени запрос периодически не начал тормозить (не всегда, но бывало). В целях эксперимента вернули стандартный exists join и оно полетело. Довольные своей сообразительностью, оставили стандартный вариант.
Никогда такого не было и вот опять. Начал тормозить уже exists join. Менять обратно на join пока не стали - наблюдаем дальше.
Лечим сбросом кэша - dbcc freeproccache, да, плохо, знаем, но пока других вариантов не придумали.

Вот сам запрос из SQL MS и план запроса, меня настораживает в нем KeyLookup. Запрос взял из SQL MS в тот момент когда было все плохо, таких запросов в статусе SUSPENDED там было бесчисленное множество.

SELECT SUM(T1.POSTEDQTY),
SUM(T1.DEDUCTED),
SUM(T1.RECEIVED),
SUM(T1.RESERVPHYSICAL),
SUM(T1.RESERVORDERED),
SUM(T1.REGISTERED),
SUM(T1.PICKED),
SUM(T1.ONORDER),
SUM(T1.ORDERED),
SUM(T1.ARRIVED),
SUM(T1.QUOTATIONRECEIPT),
SUM(T1.QUOTATIONISSUE),
SUM(T1.AVAILPHYSICAL),
SUM(T1.AVAILORDERED),
SUM(T1.PHYSICALINVENT) FROM INVENTSUM 
T1 WHERE (((T1.PARTITION=@P1) AND (T1.DATAAREAID=N'lm')) 
AND ((T1.ITEMID=@P2)
AND (T1.CLOSEDQTY=@P3))) 
AND EXISTS (SELECT 'x' FROM INVENTDIM T2 
WHERE (((T2.PARTITION=@P4) AND (T2.DATAAREAID=N'lm')) 
AND ((((((((((((((((T2.INVENTDIMID=T1.INVENTDIMID) AND 1=@P5) AND 1=@P6) AND 1=@P7) AND 1=@P8) 
AND (T2.INVENTSITEID=@P9))
AND (T2.INVENTLOCATIONID=@P10))
AND (T2.WMSLOCATIONID=@P11))
AND (T2.LICENSEPLATEID=@P12))
AND (T2.INVENTSTATUSID=@P13))
AND (T2.INVENTGTDID_RU=@P14))
AND (T2.INVENTOWNERID_RU=@P15))
AND (T2.INVENTCULTIVARID=@P16))
AND (T2.INVENTMARKETINGID=@P17))
AND (T2.INVENTCOUNTRYID=@P18))
AND (T2.INVENTTRACKID=@P19))))

Не пойму откуда это взялось

AND 1=@P5) AND 1=@P6) AND 1=@P7) AND 1=@P8)

На таблице InventDim с другого проекта перекочевали индексы, которые у нас не используются, сами индексы кастомизированы, вот 2, которые из плана запроса



В общем конкретного вопроса нет. Расскажите кто как решал такие проблемы. Тапками сразу не бросайте, топики на эту тему на форуме я почитал. Рекомендации разные - использовать join вместо exists join, индексы напильником подпилить.

План обслуживания индексов у нас настроен, выполняется каждую ночь. Периодические операции по очистке неиспользуемых аналитик + очистка записей InventSum+WHSInventReserve выполняются.

[ax_mct]

Цитата:

На таблице InventDim с другого проекта перекочевали индексы, которые у нас не используются,

Отключить их через AOT свойство Disabled?

[skycap]

Пока перечитывал тему анализировал параллельно таблицу аналитик и увидел, что
в таблице InventDim в индексе DimIdx нет аналитик (это продуктовые аналитики), которые указаны в плане запроса в KeyLookup.
INVENTCULTIVARID
INVENTMARKETINGID
INVENTCOUNTRYID

Следует ли из этого что эти аналитики нужно добавить в индекс DimIdx?

Более того эти аналитики мы не используем в принципе. Может удалить их вообще тогда если от этого будет легче.

[skycap]

Цитата:

Отключить их через AOT свойство Disabled?

Да, они уже отключены. Спасибо.

[kgksoft]

InventSum - 47 млн
InventDim - 76 млн
WHSINVENTRESERVE - 43 млн

У себя решаем:
1.Регулярным (каждый день) пересчетом статистики по InventSum, InventDim, WHSINVENTRESERVE + FREEPROCCACHE + FREESYSTEMCACHE
2. Прибиванием планов запросов через PlanGuide
3. ExistJoin -> InnerJoin
4. Написал новый класс для работы с остатками через WHSINVENTRESERVE+ InventDim - без использования страшной stored-procedure. Для кастомных расчетов все летает. + Как плюшки остатки (физ.+доступное кол-во) возвращаю как массив с указанной группировкой InventDim

[trud]

Цитата:

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

У себя решаем:
1.Регулярным (каждый день) пересчетом статистики по InventSum, InventDim, WHSINVENTRESERVE + FREEPROCCACHE + FREESYSTEMCACHE

А статистику зачем пересчитывать? т.е. распределение данных при таких объемах вряд-ли меняется часто

Цитата:

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

4. Написал новый класс для работы с остатками через WHSINVENTRESERVE+ InventDim - без использования страшной stored-procedure. Для кастомных расчетов все летает. + Как плюшки остатки (физ.+доступное кол-во) возвращаю как массив с указанной группировкой InventDim

Why Don't You Contribute to Open Source?
https://www.yegor256.com/2015/12/22/...en-source.html

По теме - вам нужно начать с того, чтобы понять какие данные вызывают эти проблемы, т.е. что скрывается под этими @P11.. и почему это занимает много времени(одна то строчка должна выбираться быстро - при условии конечно что сервер не перегружен)

Если это parameters sniffing то можно или прописывать планы PlanGuides или поставить forceliterals если на сервере есть ресурсы для этого
https://denistrunin.com/forceliteral...ePlaceholders/

[dech]

Иногда можно чистить InventDim:

Цитата:

Inventory and warehouse management > Periodic > Clean up > Inventory dimensions cleanup

Хотя бы избавитесь от нескольких млн записей, которые не используются в системе.

[skycap]

Цитата:

Иногда можно чистить InventDim

И это делаем. Периодические операции по очистке неиспользуемых аналитик + очистка записей InventSum+WHSInventReserve выполняются.

[skycap]

Цитата:

Если это parameters sniffing то можно или прописывать планы PlanGuides или поставить forceliterals если на сервере есть ресурсы для этого
https://denistrunin.com/forceliteral...ePlaceholders/

Спасибо, почитаем.

[Alexius]

Цитата:

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

Пока перечитывал тему анализировал параллельно таблицу аналитик и увидел, что
в таблице InventDim в индексе DimIdx нет аналитик (это продуктовые аналитики), которые указаны в плане запроса в KeyLookup.
INVENTCULTIVARID
INVENTMARKETINGID
INVENTCOUNTRYID

Следует ли из этого что эти аналитики нужно добавить в индекс DimIdx?

Более того эти аналитики мы не используем в принципе. Может удалить их вообще тогда если от этого будет легче.

Удалить !!!

[Владимир Максимов]

Цитата:

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

Не пойму откуда это взялось

AND 1=@P5) AND 1=@P6) AND 1=@P7) AND 1=@P8)

Связь и фильтры по InventDim - это из макроса #InventDimExistsJoin или вложенного в него макроса #InventDimDevelop. Вот и смотрите, что у Вас в них написано. Вероятно, какая-то модификация сделана

[БАХ43]

Сталкивался с подобным. Когда запрос выдает пустое значение - он тормозит безбожно.
Я нашел в классе InventUpd_Financial метод protected CostAmountSecCur_RU updateFinancialIssue(CostAmount costAmountMST, CostAmountSecCur_RU _costAmountSecCur)
подправил в этом месте:

            inventTrans.update();
//BAH250 16.08.2018 ->
            if (movement.inventModelType().stdCostBased())  //BAH250 16.08.2018 <-
                this.updateInventOnhandFinancialCache(inventTrans);

[nnrvsn]

Пару месяцев назад решал подобную проблему.

Пробовали кастомные планы, freeprocchache итд, но рано поздно (рано) все равно начинает тормзить. Вылечилось добавлением forceLiterals в селектах в InventSum::findSum.

Еще можно попробовать бубны из блога:
https://blogs.msdn.microsoft.com/axs...eserve-tables/