Тормоза на ровном месте при инициализации формы резервирования

[Saber]

Люди добрые! Помогите!!!
Не могу поймать кота за хвост.
Уже продолжительное время не могу вычислить причину "самопроизвольного" увеличения времени исполнения запросов при инициализации или рефреше формы резервирования товара.

Исходные данные такие:
Axapta 2.5 RU SP5
База данных: Microsoft SQL Server 2005
Железо новое и нормальное. Проблем с ним нет.
Переиндексация - раз в неделю.
Сбор статистики - 2 раза в неделю.

Симптомы.
После некоторого промежутка времени с нормальной работой (от нескольких дней до нескольких часов) форма резервирования начинает жутко тормозить. А через время опять все становиться нормально.

Практически постоянно мониторю ситуацию. Наблюдения показывают, что загрузка сервера к развитию событий отношения не имеет.
Во время тормозов через PerfMon вижу резкий скачек IndexSearch. Остальные параметры в норме.
Трассировка запросов средствами Аксапты показывает что наибольшее время исполнения имеют 6 одинаковых запросов:

Цитата:

Расчет времени: 5126 мс на 'EXECUTE+FETCH (execute, first chunk of data)'
SQL запрос: SELECT SUM(A.COSTAMOUNTPOSTED),SUM(A.COSTAMOUNTADJUSTMENT),SUM(A.QTY) FROM INVENTTRANS A,INVENTDIM B WHERE ((A.DATAAREAID=?) AND ((((A.ITEMID=?) AND (A.CONFIGID=?)) AND (A.STATUSISSUE=?)) AND (A.STATUSRECEIPT=?))) AND ((B.DATAAREAID=?) AND ((((B.INVENTDIMID=A.INVENTDIMID) AND (B.INVENTLOCATIONID=?)) AND (B.INVENTBATCHID=?)) AND (B.INVENTGTDID_RU=?))) OPTION(FAST 5,LOOP JOIN) [Идентификатор=9885, Использовано повторно=Да]

Есть смутное подозрение, что данный глюк как-то связан со статистикой, но принудительный сбор статистики во время работы по таблицам INVENTTRANS и INVENTDIM моментального результата не дает.

Может вопрос и глупый, но я уже не знаю на что еще нужно обратить внимание что бы разобраться с проблемой.

[fed]

Если используются placeholders (то есть вопросики, а не конкретные значения), статистика по большей части просто не используется. Например - если у тебя в запросе стоит значение statusReceipt==3, система может посмотреть в статистике гистограмму распределения значений и оценить что по этому условию отбирается всего 1-2% таблицы inventTrans. А вот если в запросе стоит statusReceipt==?, то система смотрит что всего поле statusReceipt может принимать 7 возможных значений, соответственно - по этому условию будет отобрана 1/7 от выборки.

Поэтому прежде чем чего-либо делать дальше, рекомендую в этом запросе тупо подставить forceLiterals и посмотреть чего будет...

[Wamr]

Цитата:

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

Если используются placeholders (то есть вопросики, а не конкретные значения), статистика по большей части просто не используется

Не согласен с данным утверждением. При первом вызове такого запроса статистика используется и строится корректный план исполнения, а вот при повторном использовании план сохраняется, что и может приводить к подобным эффектам.
Например, сначала вы искали проводки по партии (использовался индекс по партиям), а потом стали искать по ГТД (и опять используется индекс по партиям).

Цитата:

прежде чем чего-либо делать дальше, рекомендую в этом запросе тупо подставить forceLiterals и посмотреть чего будет

а с этим согласен

[Alexius]

1. Попробуйте убрать из запроса в Аксапте forceNestedLoop
2. Проанализируйте в Managment Studio план выполнения этого запроса в "быстром"/"медленном" варианте и если они различаются, то пропишите в запрос хинт с индексом из "быстрого" плана.
3. В качестве "лома" можно попробовать покрывающий индекс на InventTrans:
- ItemId
- StatusIssue
- StatusReceipt
- ConfigId
- InventDim
- Qty
- CostAmountPosted
- CostAmountAdjustment
Можно дополнить имеющийся индекс, главное использовать такую последовательность полей.

PS. Начинать лучше всего, на мой взгляд, с анализа планов выполнения запроса.
PS2. А так ли нужна вам себестоимость в форме резервирования, может выкинуть ее?

[fed]

Цитата:

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

Не согласен с данным утверждением. При первом вызове такого запроса статистика используется и строится корректный план исполнения, а вот при повторном использовании план сохраняется, что и может приводить к подобным эффектам.
Например, сначала вы искали проводки по партии (использовался индекс по партиям), а потом стали искать по ГТД (и опять используется индекс по партиям).

а с этим согласен

Угу. Это вроде бы как раз в MS SQL 2005 появилось. План строится при первой подстановке значений в параметры, а не при компиляции исходного запроса. И обычно вот такое вот поведение из серии "обычно нормально работает но иногда ни с того ни с сего тормозит" и объясняется тем, что при первом исполнении были какие-то сильно нетипичные значения параметров подставлены. Просто я обычно при рассказе о пользе forceLiterals про это ленюсь рассказывать Вообще - когда такие вопросы задают, почти всегда в ветке появляется масса доброхотов с советами построить какие-нить магические индексы, подставить хинт, постучать по дереву, покропить сервер куриной кровью и тп А про то что SQL Server не такой уж тупой сам по себе и кривой план на простых запросах строит не от дурости, а по каким-то внешним причинам никто не задумывается...

[Saber]

Спасибо всем.
Действительно, пока попробую повторно внимательно проанализировать планы запросов при нормальной работе и с "тормозами".

[Wamr]

Обращаю внимение, что имеет смысл анализировать планы запросов полученные MS SQL Profiler-ом, а не Аксаптой

[Logger]

Цитата:

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

Обращаю внимение, что имеет смысл анализировать планы запросов полученные MS SQL Profiler-ом, а не Аксаптой

А что, они могут отличаться ?

[Logger]

Цитата:

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

3. В качестве "лома" можно попробовать покрывающий индекс на InventTrans:
- ItemId
- StatusIssue
- StatusReceipt
- ConfigId
- InventDim
- Qty
- CostAmountPosted
- CostAmountAdjustment
Можно дополнить имеющийся индекс, главное использовать такую последовательность полей.

Как бы нам этим ломом не убить другие запросы. Индекс уж очень тяжелый получается

[Alexius]

Цитата:

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

Как бы нам этим ломом не убить другие запросы. Индекс уж очень тяжелый получается

С запросами вставки/обновления/удаления в Аксапте ничего страшного не произойдет, а для запросов на чтение индекс действительно добавит остроты в "драке" за оперативную память

[Logger]

Цитата:

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

С запросами вставки/обновления/удаления в Аксапте ничего страшного не произойдет,

А как же перестройка ключа индекса при обновлениях ? Дополнительные затраты ресурсов сервера на это дело?

Особенно вот эти поля настораживают.
- Qty
- CostAmountPosted
- CostAmountAdjustment

[Alexius]

Цитата:

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

А как же перестройка ключа индекса при обновлениях ? Дополнительные затраты ресурсов сервера на это дело?

Операции вставки/обновления/удаления производятся для одной записи и накладные расходы в них по перестройке ключей индексов незначительны.

Цитата:

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

Особенно вот эти поля настораживают.
- Qty
- CostAmountPosted
- CostAmountAdjustment

А без них не получить покрывающего индекса В идеале нужно их воткнуть в INCLUDE-поля индекса, но Аксапта это делать не умеет

[Logger]

Цитата:

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

А без них не получить покрывающего индекса В идеале нужно их воткнуть в INCLUDE-поля индекса, но Аксапта это делать не умеет

Да я понимаю что не получить, но напрягают как-то индексы которые на любое изменение данных перестраиваются.

[Logger]

А INCLUDE поля - это чо за зверь ?
Чем они в данной ситуации полезны ?

[Alexius]

Цитата:

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

А INCLUDE поля - это чо за зверь ?
Чем они в данной ситуации полезны ?

Эта опция появилась в MS SQL 2005. В крации - это поля, включенные в индекс, по которым не производится упорядочивание данных. Подробности в BOL: CREATE INDEX.

[Wamr]

Цитата:

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

А что, они могут отличаться ?

Да, так как профайлер покажет используемый план, а Аксапта расчетный.

[Logger]

Цитата:

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

Да, так как профайлер покажет используемый план, а Аксапта расчетный.

Но ведь Аксапта сама запрашивает план запроса у БД. Почему БД будет его разным строить ? Если настройки БД не менялись то и планы должны по идее одинаковые получаться.