Поле DataAreaID в индексах SQL Server - DAX: Администрирование

gkochkin · 10.03.2017, 15:43

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

Alexius · 10.03.2017, 16:06

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

gkochkin · 10.03.2017, 16:11

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

gkochkin · 10.03.2017, 16:13

Цитата:

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

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

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

**Vadik** · 10.03.2017, 16:27

Цитата:

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

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

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

SRF · 10.03.2017, 16:29

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

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

Alexius · 10.03.2017, 16:52

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

ALES · 10.03.2017, 19:54

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

AlexeyS · 12.03.2017, 16:23

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

gkochkin · 13.03.2017, 09:41

Цитата:

Сообщение от 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))))

Владимир Максимов · 13.03.2017, 11:34

Цитата:

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

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

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

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

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

Проблемы с Exists Join

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

gkochkin · 13.03.2017, 12:25

Цитата:

Сообщение от Владимир Максимов

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

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

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

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

Проблемы с Exists Join

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

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

trud · 13.03.2017, 13:37

Цитата:

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

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

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

gkochkin · 13.03.2017, 13:42

Цитата:

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

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

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

trud · 13.03.2017, 13:54

Цитата:

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

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

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

trud · 13.03.2017, 12:40

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

Владимир Максимов · 13.03.2017, 15:23

Извините, а Вы план чего показываете? Выполнения "чистого" запроса или курсора? Вам надо проверять план выполнения вот такой конструкции

Код:

declare @P1 nvarchar(3) = N'dat',
	@P2 nvarchar(20) = N'123456',
	@P3 int			= 0,
	@P4 nvarchar(3) = N'dat',
	@P5 nvarchar(20) = N'12345',
	@P6 nvarchar(20) = N'12345',
	@P7 nvarchar(20) = N'12345',
	@P8 nvarchar(20) = N'12345',
	@P9 nvarchar(20) = N'12345'

DECLARE Test CURSOR FOR
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 

EXISTS (SELECT 'x' 
		FROM INVENTDIM B 
		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)

open test
fetch test  -- Вот на этой команде план смотреть

close test
deallocate test

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

Затем сравните план выполнения, если заменить Exists на Inner Join

Для целей сравнения запишите 2 запроса рядом в одном Query. Ну, т.е. как-то так

Код:

DECLARE Test CURSOR FOR
SELECT ... FROM InventSum Where Exists(...)

DECLARE Test2 CURSOR FOR
SELECT ... FROM InventSum INNER JOIN InventDim (...)

open test
open test2

fetch test  -- Вот на этой команде план смотреть
fetch test2  -- Вот на этой команде план смотреть

PS: Все скобки в WHERE в данном случае можно удалить. Это построитель запроса Axapta их добавляет. Но, поскольку здесь везде объединение по AND, то скобки только мешают анализу

PPS: Напомню, что план выполнения запроса с Exists существенно зависит от объема данных. Поэтому Вы можете получить разные планы выполнения на тестовых данных и на рабочих, если на тесте данных немного

gkochkin · 13.03.2017, 17:03

Цитата:

Сообщение от Владимир Максимов

Извините, а Вы план чего показываете? Выполнения "чистого" запроса или курсора? Вам надо проверять план выполнения вот такой конструкции

Код:

declare @P1 nvarchar(3) = N'dat',
	@P2 nvarchar(20) = N'123456',
	@P3 int			= 0,
	@P4 nvarchar(3) = N'dat',
	@P5 nvarchar(20) = N'12345',
	@P6 nvarchar(20) = N'12345',
	@P7 nvarchar(20) = N'12345',
	@P8 nvarchar(20) = N'12345',
	@P9 nvarchar(20) = N'12345'

DECLARE Test CURSOR FOR
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 

EXISTS (SELECT 'x' 
		FROM INVENTDIM B 
		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)

open test
fetch test  -- Вот на этой команде план смотреть

close test
deallocate test

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

Затем сравните план выполнения, если заменить Exists на Inner Join

Для целей сравнения запишите 2 запроса рядом в одном Query. Ну, т.е. как-то так

Код:

DECLARE Test CURSOR FOR
SELECT ... FROM InventSum Where Exists(...)

DECLARE Test2 CURSOR FOR
SELECT ... FROM InventSum INNER JOIN InventDim (...)

open test
open test2

fetch test  -- Вот на этой команде план смотреть
fetch test2  -- Вот на этой команде план смотреть

PS: Все скобки в WHERE в данном случае можно удалить. Это построитель запроса Axapta их добавляет. Но, поскольку здесь везде объединение по AND, то скобки только мешают анализу

PPS: Напомню, что план выполнения запроса с Exists существенно зависит от объема данных. Поэтому Вы можете получить разные планы выполнения на тестовых данных и на рабочих, если на тесте данных немного

планы прикладываю с использованием курсора (варианты exists и inner join)

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
emeadaxsupport: AX Performance - Analyzing key SQL Server configuration and database settings	Blog bot	DAX Blogs	0	28.09.2015 14:11
emeadaxsupport: AX Performance Troubleshooting Checklist Part 1A [Introduction and SQL Configuration]	Blog bot	DAX Blogs	0	05.09.2014 21:11
emeadaxsupport: How to perform a data center change (change of the physical location) where a SQL server 2008 R 2 cluster installation and MS Dynamics AX 4.0 is involved?	Blog bot	DAX Blogs	0	21.06.2014 19:19
dynamicsaxbi: Better together: Microsoft Dynamics AX 2012 R2 and SQL Server Power View	Blog bot	DAX Blogs	0	12.12.2012 13:11