Как улучшить быстродействие запроса

[Ace of Database]

Привет всем.
AX2012 R2.

По журналу трассировки запросов SQL вижу, что самое нагруженное место в моей Аксапте - это метод findSum на таблице IventSum
Конкретно, вот это место:

            default:
                select
                #inventSumFields
                from inventSum
                    where inventSum.ItemId      == _itemId   &&
                          inventSum.Closed      == NoYes::No
                #inventDimExistsJoin(inventSum.InventDimId,inventDim,_InventDimCriteria,_InventDimParm);

В моменты замедления работы пользователей, запрос в среднем выполняется 0,5 секунды. Но из-за того, что он вызывается многократно, получается приличная задежрка.
Проверил, индексы вроде все в порядке, в SQL Server Management Studio запрос отрабатывает моментально. Actual Execution Plan не показывает, что нужно добавить новые индексы.
По таблицам InventSum и InventDim ежедневно проводится перестроение индексов и обновление статистики.
Может, это из-за кратковременных блокировок получается задержка в 0,5 секунд ?

[Freeangel]

Посмотрите порядок полей в индексе в таблице InventSum и попробуйте поменять порядок полей в условии where согласно индексу.

[Ace of Database]

Цитата:

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

Посмотрите порядок полей в индексе в таблице InventSum и попробуйте поменять порядок полей в условии where согласно индексу.

Проверил, в обеих таблицах последовательность полей в индексах первичного ключа совпадают с условиям where.
Селективность запросов хорошая, всего 30 комбинаций аналитик возвращают количество записей большие чем 100 (максимум - 467 записей). Большинство комбинаций аналитик дают не более 10 записей для каждой комбинации.

[Sada]

А у меня в 12-ке в индексе ClosedItemDimIdx первым полем идет Closed, потом ItemId. Мне кажется это не есть хорошо.

[ice]

возможно стоит создать индекс на InventDim, с наиболее часто выбираемыми полями?

[Ace of Database]

Цитата:

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

А у меня в 12-ке в индексе ClosedItemDimIdx первым полем идет Closed, потом ItemId. Мне кажется это не есть хорошо.

В данном случае должен срабатывать индекс ItemDimIdx,
И меня там действительно смущает, что нет поля closed, но если его добавить, то нельзя оставлять индекс уникальным.
Может, создать новый неуникальный индекс, продублировать там все поля из индекса ItemDimIdx и добавить туда поле Closed ?

[Ace of Database]

Вот запрос:
WHERE (((T1.PARTITION=?) AND (T1.DATAAREAID='?')) AND ((T1.ITEMID='?') AND (T1.CLOSED=?)))
Действительно проблему может решить дублирование индекса ItemDimIdx и добавление в новый индекс поле CLOSED ?

[Ace of Database]

Цитата:

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

возможно стоит создать индекс на InventDim, с наиболее часто выбираемыми полями?

Ок, попробую создать такой индекс: ItemId, Closed, InventDimId. На таблицу InventSum, а не InventDim.
InventDimId нужен для джойна с InventDim ( а может и не нужен, но попробую с ним)

[Михаил Андреев]

Эскалация блокировок включена на таблицах InventSum, InventDim? Я бы отключил.
https://technet.microsoft.com/ru-ru/...=sql.105).aspx
Плюс я бы добавил индекс.

[fed]

Если у вас там батчи/серийные номера используются - скороее всего, индексами делу не поможешь. Попробуй в этот метод НЕНАДОЛГО воткнуть forceliterals и потом посмотри по каким номенклатурам самые тяжелые запросы. (Forceliterals надо откатить потом - это просто способ диангостики).
У меня была похожая ситуация на одном из клиентов. Там было с десяток ключевых номенклатур, по каждой из которых было порядка 10-20 тысяч батчей и, соответственно, порядка 50-100 тысяч записей в inventSum.
Удалось как-то сдвинуть ситуацию только построив за пределами Аксапты index clustered view, полученный запихиванием этого запроса в Database Tuning Advisor. Метод не очень надежный, но у них там уже речь шла не о 0.5 секунды, а о секундах 5-7...

[Alexius]

Цитата:

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

Удалось как-то сдвинуть ситуацию только построив за пределами Аксапты index clustered view, полученный запихиванием этого запроса в Database Tuning Advisor. Метод не очень надежный, но у них там уже речь шла не о 0.5 секунды, а о секундах 5-7...

Сурово Тогда уж лучше, мне кажется, продублировать складские аналитики в InventSum и играться на ней с индексами под различные наборы. Тем более в АХ 2012 можно добавлять Include-поля и творить покрывающие индексы не утыкаясь в ограничение в 16 полей. Вроде допиливать придется не так и много.

[Alexius]

Цитата:

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

А у меня в 12-ке в индексе ClosedItemDimIdx первым полем идет Closed, потом ItemId. Мне кажется это не есть хорошо.

Я его вообще изничтожил, т.к. SQL отказывался его пользовать.
Стандартные неиспользуемые индексы

[Ace of Database]

Цитата:

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

Если у вас там батчи/серийные номера используются - скороее всего, индексами делу не поможешь. Попробуй в этот метод НЕНАДОЛГО воткнуть forceliterals и потом посмотри по каким номенклатурам самые тяжелые запросы. (Forceliterals надо откатить потом - это просто способ диангостики).
У меня была похожая ситуация на одном из клиентов. Там было с десяток ключевых номенклатур, по каждой из которых было порядка 10-20 тысяч батчей и, соответственно, порядка 50-100 тысяч записей в inventSum.
Удалось как-то сдвинуть ситуацию только построив за пределами Аксапты index clustered view, полученный запихиванием этого запроса в Database Tuning Advisor. Метод не очень надежный, но у них там уже речь шла не о 0.5 секунды, а о секундах 5-7...

У меня по одной номенклатуре не более 400 партий. И то таких не больше 10- штук. В основном не больше 10 партий на одну номенклатуру.
Посчитал : в среднем 72 партии (уникальных комбинаций аналитик) на одну номенклатуру.

[Freeangel]

Создайте свой индекс и добавьте index hint для этого запроса. И посмотрите, что получится.

[Ace of Database]

Цитата:

Сообщение от Михаил Андреев

Эскалация блокировок включена на таблицах InventSum, InventDim? Я бы отключил.
https://technet.microsoft.com/ru-ru/...=sql.105).aspx
Плюс я бы добавил индекс.

Спасибо, попробую. И индекс добавлю. Надо сначала морально подготовиться, а потом сделать. Заметил, что лучше работает тогда, когда делаешь не сразу после принятия решения, а через несколько дней.
Тем более, что пока особенно народ не жалуется.

[fed]

Цитата:

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

Сурово Тогда уж лучше, мне кажется, продублировать складские аналитики в InventSum и играться на ней с индексами под различные наборы. Тем более в АХ 2012 можно добавлять Include-поля и творить покрывающие индексы не утыкаясь в ограничение в 16 полей. Вроде допиливать придется не так и много.

Так проблема не в том что на джойн время много уходит, проблема в том что суммировать дофига записей. То есть - там сам джойн процентов 15-20 времени запроса занимает. А вот суммировать 100000 записей - дело не быстрое. Так что добавлением аналитик в таких ситуациях не поможешь. А cluster indexed view и используется тогда, когда надо заранее подбить суммы по каким-то сочетаниям полей. При этом если в исходном запросе аггрегатные функции не использются, то этот cluster indexed view и не даст особого ускорения. К слову сказать, по моему Database Tuning Advisor выдает рекомендации по cluster indexed view ТОЛЬКО для запросов с аггрегированием.
Кстати - как топикстартер верно заметил - это точно не его случай. У него там что-то другое происходит.

[Alexius]

Если запросы тормозит не всегда, а после какого-то времени или время от времени, то можно поэкспериментировать с процедурным кэшем. Для начала попробовать его сбросить DBCC FREEPROCCACHE.
Еще на БД есть свойство PARAMETERIZATION, если его установить в FORCED, то сервер не будет кэшировать планы для каждого набора параметров запросов.
Ну и на закуску возможно неплохо бы отключить параллелизм на сервере, если в MS SQL 2012 я пока не встречал "паразитных" распараллеливаний запросов, то на более древних версиях они могли укладывать не самые хилые дисковые подсистемы.

[trud]

Цитата:

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

А вот суммировать 100000 записей - дело не быстрое. Так что добавлением аналитик в таких ситуациях не поможешь. А cluster indexed view и используется тогда, когда надо заранее подбить суммы по каким-то сочетаниям полей.

кстати имел негативный опыт играясь с cluster indexed view как раз для такого запроса - т.е. сам запрос заметно ускорялся но при этом вставка в InventDim и InventSum замедлялась на такое же время
из текста кстати непонтяно что за запрос - какие поля выбираются, еще из универсальных советов можно попробовать почистить InventSum от нулевых значений(closed=1)

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

[fed]

Цитата:

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

кстати имел негативный опыт играясь с cluster indexed view как раз для такого запроса - т.е. сам запрос заметно ускорялся но при этом вставка в InventDim и InventSum замедлялась на такое же время

Я на эту граблю тоже наступал и достаточно легко ее обошел. Надо выловить из кэша запросов план того самого тормозящего запроса с update по inventSum и построить индекс, который в этом самом плане рекомендован. (То есть - даже Tuning Advisor запускать не надо). Единственное know how - порядок полей в индексе надо строго соблюдать, засовывая dataAreaId на второе или третье место. В противном случае - индекс не срабатывает.
Ну и да - согласен, в исходном вопросе не хватает данных. Не понятно что за запрос и с какой целью идет. В идеале, надо было бы его выловить в кэше запросов на SQL Server и выложить сюда план. Странно что запрос так медленно работает при таких скромных объемах.

[Maxim Gorbunov]

Быстрее всего работает запрос, который вообще не надо выполнять. Может в findSum() приделать кэширование?