Поле DataAreaID в индексах SQL Server

[gkochkin]

Dynamics AX 2009
SQL Server 2008 R2
Возникли разногласия при оптимизации запроса на SQL Server по поводу того можно ли (в качестве исключения для особо критичных задач) перемещать поле DataAreaID на другие позиции в индексе средствами SQL Server, как неселективное?
Тестирование в рамках SQL показывает значительный прирост производительности.
Проблема в том, что при обновлении Аксапты, она вернет все на круги своя.
Как лучше поступить в этом случае?
Буду признателен за помощь.

[Alexius]

В АХ АОТ в описание индекса вставляете вручную (не перетаскиванием) поле DataAreaId и позиционируете его как вам нравится. Ну и синхронизация соответственно.

[gkochkin]

по поводу его позиции есть какие-то противопоказания?

[gkochkin]

Zitat:

Zitat von gkochkin

по поводу его позиции есть какие-то противопоказания?

Я занимаюсь администрированием SQL, в Аксапту имею, но минимальный доступ. И возник этот спор.
Аксаптовики мне говорят, чтобы я забыл про это поле

[Vadik]

Zitat:

Zitat von gkochkin

Аксаптовики мне говорят, чтобы я забыл про это поле

Скажите им, что фикс для parameter sniffing для AX 2009 тоже есть

[SRF]

Есть стандартные таблицы, у которых поле dataAreaId в индексе идет не первым, например SpecTrans, поэтому особых противопоказаний нет, есть индивидуальная не переносимость

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

[Alexius]

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

[ALES]

"Вот так и рождаются не здоровые сенсации" (с)
Ни числа используемых компаний в AX, ни оптимизируемого запроса, ни объема данных в соотв. табличках.. в этом случае действительно лучше ничего не трогать

[AlexeyS]

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

[gkochkin]

Zitat:

Zitat von ALES

"Вот так и рождаются не здоровые сенсации" (с)
Ни числа используемых компаний в AX, ни оптимизируемого запроса, ни объема данных в соотв. табличках.. в этом случае действительно лучше ничего не трогать

Таблица inventdim- 80'000'000 строк.
На данный момент одна компания, но, возможно, будет еще одна.
Запрос следующего вида:

SELECT 
SUM(A.POSTEDQTY),SUM(A.POSTEDVALUE),SUM(A.PHYSICALVALUE),SUM(A.DEDUCTED),SUM(A.RECEIVED),SUM(A.RESERVPHYSICAL),SUM(A.RESERVORDERED),SUM(A.REGISTERED),SUM(A.PICKED),SUM(A.ONORDER),SUM(A.ORDERED),SUM(A.ARRIVED),SUM(A.QUOTATIONRECEIPT),SUM(A.QUOTATIONISSUE),SUM(A.AVAILPHYSICAL),SUM(A.AVAILORDERED),SUM(A.PHYSICALINVENT) 

FROM INVENTSUM A 

WHERE 
((A.DATAAREAID=@P1) AND 
-- поменять местами поля
((A.ITEMID =@P2) AND 
 (A.CLOSED=@P3))) AND 

-- Вопрос: почему не используется индекс ECC_FinDimIdx?
EXISTS (SELECT 'x' 
		FROM INVENTDIM B with(index(I_698ECC_FINDIMIDX))
		WHERE ((B.DATAAREAID=@P4) AND
			((((((B.INVENTDIMID=A.INVENTDIMID) AND 
				(B.INVENTSIZEID =@P5)) AND
				(B.INVENTCOLORID =@P6)) AND
				(B.INVENTLOCATIONID =@P7)) AND
				(B.INVENTBATCHID =@P8)) AND 
				(B.INVENTGTDID_RU =@P9))))

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

Zitat:

-- Вопрос: почему не используется индекс ECC_FinDimIdx?

Почему не работает подсказка оптимизатору - не знаю. Может, физически нет такого индекса. Судя по префиксу - это какая-то кастомизация. Хотя для Axapta использование подсказок оптимизатору - порочная практика, которая не уменьшает, а увеличивает количество проблем.

Кроме того, при оптимизации запросов следует учитывать тот факт, что Axapta обращается к данным SQL не прямыми запросами, а через "обертку" в виде курсоров.

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

Как следствие, план выполнения запросов "внутри" курсоров и в "прямых" запросах может очень сильно отличаться, хотя, казалось бы, запрос один и тот же. Подробности можете посмотреть в этой теме

Проблемы с Exists Join

Вкратце, попробуйте заменить Exists на Inner Join. Поскольку у Вас связь InventSum и InventDim, то в данном случае - это будет корректная замена. И уберите подсказку оптимизатору для индекса

[gkochkin]

Zitat:

Zitat von Владимир Максимов

Почему не работает подсказка оптимизатору - не знаю. Может, физически нет такого индекса. Судя по префиксу - это какая-то кастомизация. Хотя для Axapta использование подсказок оптимизатору - порочная практика, которая не уменьшает, а увеличивает количество проблем.

Кроме того, при оптимизации запросов следует учитывать тот факт, что Axapta обращается к данным SQL не прямыми запросами, а через "обертку" в виде курсоров.

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

Как следствие, план выполнения запросов "внутри" курсоров и в "прямых" запросах может очень сильно отличаться, хотя, казалось бы, запрос один и тот же. Подробности можете посмотреть в этой теме

Проблемы с Exists Join

Вкратце, попробуйте заменить Exists на Inner Join. Поскольку у Вас связь InventSum и InventDim, то в данном случае - это будет корректная замена. И уберите подсказку оптимизатору для индекса

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

[trud]

а почему вы хотите его использовать? т.е. для условных значений B.INVENTBATCHID ="без партии и B.INVENTGTDID_RU ="пусто"(т.е. комбинации значений которых много в базе) использование любого индекса по INVENTDIM может привести к большим проблемам. Предположу что SQL рассуждает в таком же духе, раз автоматом не использует индекс

[trud]

Zitat:

Zitat von gkochkin

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

если планы одинаковые, то порядок то тут не причем. просто ваш результирующий индекс по видимому имеет другой Fill factor(т.е. получается страницы расположены более плотно раз кол-во логических чтений меньше - тут я подразумеваю что дефрагментацию вы выполнили перед тестом).
Это все приведет к увеличению кол-ва расщеплений этих страниц при обновлении-вставке данных(а для InventSum это критично), что в конечном итоге потенциально замедлит всю систему. так что вполне аргументировано не дают переносить

[gkochkin]

Zitat:

Zitat von trud

если планы одинаковые, то порядок то тут не причем. просто ваш результирующий индекс по видимому имеет другой Fill factor(т.е. получается страницы расположены более плотно раз кол-во логических чтений меньше - тут я подразумеваю что дефрагментацию вы выполнили перед тестом).
Это все приведет к увеличению кол-ва расщеплений этих страниц при обновлении-вставке данных(а для InventSum это критично), что в конечном итоге потенциально замедлит всю систему. так что вполне аргументировано не дают переносить

Планы не одинаковые. План просто при выполнении запроса не использует нами добавленный индексI_698ECC_FINDIMIDX.
С хинтом соответственно использует.
Если поле DataAreaID перенести в конец - оптимизатор хватает наш индекс I_698ECC_FINDIMIDX и количество чтений (и процессорное время) значительно меньше, чем без использования этого индекса (I_698ECC_FINDIMIDX).

[trud]

Zitat:

Zitat von gkochkin

С хинтом соответственно использует.

А в индексе что за поля? все 5 полей запроса из InventDim?
просто на практике я видел ситуации когда создание подобных индексов приводило к остановкам системы, из за того, что собственно для ряда значений поиск остатков через InventDim приводил к полному скану этой таблицы.
т.е. надо проверить что не используются всякие обобщенные партии и гтд

[gkochkin]

Zitat:

Zitat von trud

А в индексе что за поля? все 5 полей запроса из InventDim?
просто на практике я видел ситуации когда создание подобных индексов приводило к остановкам системы, из за того, что собственно для ряда значений поиск остатков через InventDim приводил к полному скану этой таблицы.
т.е. надо проверить что не используются всякие обобщенные партии и гтд

dataareaid
inventdimid
inventsizeid
inventcolorid
inventlocationid
inventbatchid
inventgtdid_ru

[trud]

О..
этот индекс и не должен использоваться. т.е. какой у вас план то - найти сначала номенклатуру(из InventSum) и InventDimId, а потом проверить все комбинации аналитик? для проверки комбинаций SQL считает что лучше использовать кластерный индекс по InventDimId.
тут можно попробовать создать вот такой(ниже) - искать сразу InventDim и потом по нему уже InventSum, но опять же надо смотреть на ваши данные, т.е. это будет работать если нет обобщенных партий-гтд и нет других аналитик
dataareaid
inventsizeid
inventcolorid
inventlocationid
inventbatchid
inventgtdid_ru

[gkochkin]

Zitat:

Zitat von trud

О..
этот индекс и не должен использоваться. т.е. какой у вас план то - найти сначала номенклатуру(из InventSum) и InventDimId, а потом проверить все комбинации аналитик? для проверки комбинаций SQL считает что лучше использовать кластерный индекс по InventDimId.
тут можно попробовать создать вот такой(ниже) - искать сразу InventDim и потом по нему уже InventSum, но опять же надо смотреть на ваши данные, т.е. это будет работать если нет обобщенных партий-гтд и нет других аналитик
dataareaid
inventsizeid
inventcolorid
inventlocationid
inventbatchid
inventgtdid_ru

SQL использует поиск по некластерному, а потом добирает данные в кластерном.
Если поставить DataAreaID в вышеуказанном мной индексе на 4 место, то статистические данные запроса становятся лучше.
Такой индекс sql использует без подсказок.
Согласны?

[trud]

ну т.е. у вас сначала ищутся все InventDimId с заданной партией, потом уже выбираются остатки.
т.е. индекс который я предложил однозначно ускорит этот процесс(за счет доп фильтрации по тому же складу)
а с вашим индексом какой план?