AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX: Администрирование
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 10.03.2017, 15:43   #1  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
Thumbs up Поле DataAreaID в индексах SQL Server
Dynamics AX 2009
SQL Server 2008 R2
Возникли разногласия при оптимизации запроса на SQL Server по поводу того можно ли (в качестве исключения для особо критичных задач) перемещать поле DataAreaID на другие позиции в индексе средствами SQL Server, как неселективное?
Тестирование в рамках SQL показывает значительный прирост производительности.
Проблема в том, что при обновлении Аксапты, она вернет все на круги своя.
Как лучше поступить в этом случае?
Буду признателен за помощь.
Старый 10.03.2017, 16:06   #2  
Alexius is offline
Alexius
Участник
Аватар для Alexius
 
461 / 248 (9) ++++++
Регистрация: 13.12.2001
В АХ АОТ в описание индекса вставляете вручную (не перетаскиванием) поле DataAreaId и позиционируете его как вам нравится. Ну и синхронизация соответственно.
За это сообщение автора поблагодарили: VORP (2).
Старый 10.03.2017, 16:11   #3  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
по поводу его позиции есть какие-то противопоказания?
Старый 10.03.2017, 16:13   #4  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
Цитата:
Сообщение от gkochkin Посмотреть сообщение
по поводу его позиции есть какие-то противопоказания?
Я занимаюсь администрированием SQL, в Аксапту имею, но минимальный доступ. И возник этот спор.
Аксаптовики мне говорят, чтобы я забыл про это поле
Старый 10.03.2017, 16:27   #5  
Vadik is offline
Vadik
Модератор
Аватар для Vadik
Лучший по профессии 2017
Лучший по профессии 2015
 
3,631 / 1849 (69) ++++++++
Регистрация: 18.11.2002
Адрес: гражданин Москвы
Цитата:
Сообщение от gkochkin Посмотреть сообщение
Аксаптовики мне говорят, чтобы я забыл про это поле
Скажите им, что фикс для parameter sniffing для AX 2009 тоже есть
__________________
-ТСЯ или -ТЬСЯ ?
Старый 10.03.2017, 16:29   #6  
SRF is offline
SRF
Участник
MCBMSS
Axapta Retail User
 
362 / 542 (19) +++++++
Регистрация: 08.08.2007
Записей в блоге: 1
Есть стандартные таблицы, у которых поле dataAreaId в индексе идет не первым, например SpecTrans, поэтому особых противопоказаний нет, есть индивидуальная не переносимость

Если вы меняете какой-либо из существующих индексов, то лучше пообщаться с людьми, которые могут посмотреть в приложении, не будет ли печальных последствий в других местах.
__________________
Sergey Nefedov
Старый 10.03.2017, 16:52   #7  
Alexius is offline
Alexius
Участник
Аватар для Alexius
 
461 / 248 (9) ++++++
Регистрация: 13.12.2001
Основное противопоказание, на мой взгляд, это кластерные индексы, т.к. многие грид-формы в АХ выдают информацию без каких-либо фильтров, кроме скрытого фильтра по компании (DataAreaId) и если это поле утащить с первой позиции, то в них могут начаться тормоза.
Старый 10.03.2017, 19:54   #8  
ALES is offline
ALES
Участник
Злыдни
 
220 / 45 (2) +++
Регистрация: 11.08.2004
"Вот так и рождаются не здоровые сенсации" (с)
Ни числа используемых компаний в AX, ни оптимизируемого запроса, ни объема данных в соотв. табличках.. в этом случае действительно лучше ничего не трогать
За это сообщение автора поблагодарили: Vadik (1).
Старый 12.03.2017, 16:23   #9  
AlexeyS is offline
AlexeyS
Участник
 
404 / 339 (12) ++++++
Регистрация: 15.06.2004
Адрес: москва
можно пойти немного другим путем - вместо изменения существующего индекса добавить еще один с тем-же набором, но другим порядком полей. И если оригинальный индекс перестал использоваться и планы исполнения стали использовать только новый, то можно смело менять исходный. Если нет - нужно искать, где он еще используется.
Старый 13.03.2017, 09:41   #10  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
Цитата:
Сообщение от 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   #11  
Владимир Максимов is offline
Владимир Максимов
Участник
КОРУС Консалтинг
 
1,651 / 1158 (42) ++++++++
Регистрация: 13.01.2004
Записей в блоге: 3
Цитата:
-- Вопрос: почему не используется индекс ECC_FinDimIdx?
Почему не работает подсказка оптимизатору - не знаю. Может, физически нет такого индекса. Судя по префиксу - это какая-то кастомизация. Хотя для Axapta использование подсказок оптимизатору - порочная практика, которая не уменьшает, а увеличивает количество проблем.

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

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

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

Проблемы с Exists Join

Вкратце, попробуйте заменить Exists на Inner Join. Поскольку у Вас связь InventSum и InventDim, то в данном случае - это будет корректная замена. И уберите подсказку оптимизатору для индекса
__________________
- Может, я как-то неправильно живу?!
- Отчего же? Правильно. Только зря...
Старый 13.03.2017, 12:25   #12  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
Цитата:
Сообщение от Владимир Максимов Посмотреть сообщение
Почему не работает подсказка оптимизатору - не знаю. Может, физически нет такого индекса. Судя по префиксу - это какая-то кастомизация. Хотя для Axapta использование подсказок оптимизатору - порочная практика, которая не уменьшает, а увеличивает количество проблем.

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

exec sp_cursoropen
exec sp_cursorfetch
exec sp_cursorclose

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

Проблемы с Exists Join

Вкратце, попробуйте заменить Exists на Inner Join. Поскольку у Вас связь InventSum и InventDim, то в данном случае - это будет корректная замена. И уберите подсказку оптимизатору для индекса
Тестирование показало, что хинт работает, но запрос с хинтом работает хуже. Если индекс поменять - поле DataAreaID поставить не на первую позицию - оптимизатор самостоятельно подхватывает индекс (без хинта) и в этом случае статистика показывает меньшее кол-во логических чтений и процессорного времени.
На тестовой системе я все это сделал, но в продакшн это не хотят пускать, прикрываясь тем, что поле DataAreaID определяет рамки компании и всегда ставится аксаптой на первое место.
Старый 13.03.2017, 13:37   #13  
trud is offline
trud
Участник
Лучший по профессии 2017
 
1,038 / 1629 (57) ++++++++
Регистрация: 07.06.2003
Записей в блоге: 1
Цитата:
Сообщение от gkochkin Посмотреть сообщение
в этом случае статистика показывает меньшее кол-во логических чтений и процессорного времени.
На тестовой системе я все это сделал, но в продакшн это не хотят пускать, прикрываясь тем, что поле DataAreaID определяет рамки компании и всегда ставится аксаптой на первое место.
если планы одинаковые, то порядок то тут не причем. просто ваш результирующий индекс по видимому имеет другой Fill factor(т.е. получается страницы расположены более плотно раз кол-во логических чтений меньше - тут я подразумеваю что дефрагментацию вы выполнили перед тестом).
Это все приведет к увеличению кол-ва расщеплений этих страниц при обновлении-вставке данных(а для InventSum это критично), что в конечном итоге потенциально замедлит всю систему. так что вполне аргументировано не дают переносить
Старый 13.03.2017, 13:42   #14  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
Цитата:
Сообщение от trud Посмотреть сообщение
если планы одинаковые, то порядок то тут не причем. просто ваш результирующий индекс по видимому имеет другой Fill factor(т.е. получается страницы расположены более плотно раз кол-во логических чтений меньше - тут я подразумеваю что дефрагментацию вы выполнили перед тестом).
Это все приведет к увеличению кол-ва расщеплений этих страниц при обновлении-вставке данных(а для InventSum это критично), что в конечном итоге потенциально замедлит всю систему. так что вполне аргументировано не дают переносить
Планы не одинаковые. План просто при выполнении запроса не использует нами добавленный индексI_698ECC_FINDIMIDX.
С хинтом соответственно использует.
Если поле DataAreaID перенести в конец - оптимизатор хватает наш индекс I_698ECC_FINDIMIDX и количество чтений (и процессорное время) значительно меньше, чем без использования этого индекса (I_698ECC_FINDIMIDX).
Старый 13.03.2017, 13:54   #15  
trud is offline
trud
Участник
Лучший по профессии 2017
 
1,038 / 1629 (57) ++++++++
Регистрация: 07.06.2003
Записей в блоге: 1
Цитата:
Сообщение от gkochkin Посмотреть сообщение
С хинтом соответственно использует.
А в индексе что за поля? все 5 полей запроса из InventDim?
просто на практике я видел ситуации когда создание подобных индексов приводило к остановкам системы, из за того, что собственно для ряда значений поиск остатков через InventDim приводил к полному скану этой таблицы.
т.е. надо проверить что не используются всякие обобщенные партии и гтд
Старый 13.03.2017, 12:40   #16  
trud is offline
trud
Участник
Лучший по профессии 2017
 
1,038 / 1629 (57) ++++++++
Регистрация: 07.06.2003
Записей в блоге: 1
а почему вы хотите его использовать? т.е. для условных значений B.INVENTBATCHID ="без партии и B.INVENTGTDID_RU ="пусто"(т.е. комбинации значений которых много в базе) использование любого индекса по INVENTDIM может привести к большим проблемам. Предположу что SQL рассуждает в таком же духе, раз автоматом не использует индекс
Старый 13.03.2017, 15:23   #17  
Владимир Максимов is offline
Владимир Максимов
Участник
КОРУС Консалтинг
 
1,651 / 1158 (42) ++++++++
Регистрация: 13.01.2004
Записей в блоге: 3
Извините, а Вы план чего показываете? Выполнения "чистого" запроса или курсора? Вам надо проверять план выполнения вот такой конструкции

Код:
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 существенно зависит от объема данных. Поэтому Вы можете получить разные планы выполнения на тестовых данных и на рабочих, если на тесте данных немного
__________________
- Может, я как-то неправильно живу?!
- Отчего же? Правильно. Только зря...

Последний раз редактировалось Владимир Максимов; 13.03.2017 в 15:32.
За это сообщение автора поблагодарили: alex55 (1).
Старый 13.03.2017, 17:03   #18  
gkochkin is offline
gkochkin
Участник
 
29 / 7 (1) +
Регистрация: 10.03.2017
Цитата:
Сообщение от Владимир Максимов Посмотреть сообщение
Извините, а Вы план чего показываете? Выполнения "чистого" запроса или курсора? Вам надо проверять план выполнения вот такой конструкции

Код:
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)
Миниатюры
Нажмите на изображение для увеличения
Название: exists.jpg
Просмотров: 385
Размер:	93.9 Кб
ID:	11258   Нажмите на изображение для увеличения
Название: inner join (production).jpg
Просмотров: 250
Размер:	92.7 Кб
ID:	11259  

Теги
axapta, dynamics ax, sql server, tuning

 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
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
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра
Комбинированный вид Комбинированный вид

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 09:02.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.