Кластерный индекс на InventTrans в AX 4.0

[N.D.P.]

При использовании кластерного индекса его поля добавляются на уровне листьев в некластерные индексы. Индекс TransIdIdx включает в себя такие часто востребованные поля, как InventTransId, InventDimId. Благодаря этому ряд запросов, использующих некластрерные индексы работает эффективнее из-за того, что не требуется выполнять дополнительные key lookup'ы (или RID lookup'ы, если кто-то вообще решил отказаться от кластерного индекса).

[Vadik]

Цитата:

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

тот факт что recid в общем случае не возрастающий счетчик - не мешает ?

Речь про несколько инстансов AOS ? Не мешает. При интенсивной вставке образуется непрерывная "горячая область", ее размер можете посчитать самостоятельно из своего количества AOS-ов, размера пула RecId (250), размера записи в InventTrans (около 900 байт) и условного fillfactor-а (50 - 100%). Получается несколько мегабайт на компанию, гарантированно "горячих" (уже находящихся в памяти) которые будут скинуты на диск одной или несколькими последовательными операциями записи (continuous writes), в отличие от множества мелких random writes по всему массиву при обновлении InventDimId. Ну то есть - никак не дороже. Скорее наоборот - у меня сейчас на рабочем инстансе согласно sys.dm_db_index_operational_stats расщеплений страниц на индексе по RecId на порядок (десятичный ) меньше чем расщеплений на TransOriginIdx. Плюс, расщепления по RecId при вставке - "виртуальные", так как по факту страницы как правило только в памяти живут и на диск еще не сброшены

Цитата:

В чем же выигрыш ? За счет меньшего размера ключа ?

• Меньший размер ключа некластерного индекса (а у нас их 9 в стандарте)
• Нет необходимости физически перемещать запись в InventTrans при обновлении InventDimId
• Нет необходимости обновлять записи всех некластерных индексов при обновлении InventDimId

[Kabardian]

Не хочу сейчас погружаться на уровень атомов и прочих элементарных частиц... Подскажите, пожалуйста, какой тест нужно провести, чтобы гарантировано произошло расщепление? Имею ввиду, последовательность действий пользователя в интерфейсе AX: создать заказ на продажу, добавить строки, зарезервировать по таким-то аналитикам и т. п.

Хочется на практике проверить насколько ускорится система если кластерный индекс по TransOriginIdx поменять например на индекс по RecId.

[AndyD]

Зачем резервировать?)
Достаточно просто сплитить проводки (в проводках Функции/Разбиение)

inventTrans.updateSplit()

Создайте проводку на миллион чего-то и разделите в цикле ее по 1шт

[Ivanhoe]

А как просто разбиение повлияет на стандартный индекс? recid будет из конца, а Origin будет один, нет?

[AndyD]

Цитата:

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

А как просто разбиение повлияет на стандартный индекс? recid будет из конца, а Origin будет один, нет?

Да, лот и аналитика будет одна

[Ivanhoe]

И как это повлияет на стандартный кластерный индекс? Kabardian, насколько я понял, хочет зафиксировать эффект, когда в таблице происходит движение строк из-за индекса. И потом сравнить, когда движения нет.

[Kabardian]

Цитата:

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

Зачем резервировать?)
Достаточно просто сплитить проводки (в проводках Функции/Разбиение)

inventTrans.updateSplit()

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

Пока подготовил и прогнал 1 раз на голой стандартной AX 2012 R2 такой сценарий с кластерным индексом по TransOriginIdx (хард: Core I3, SSD 120 GB, 8 GB оперативы):

1. Настроил номенклатуру с выделением номеров партий на каждые 100 шт
2. Создал журнал проводка с 1 строкой для склада А и кол-вом 1 000 000 шт и разнес (примерно 10-15 мин). В результате будет создано 10 000 складских проводок с разными партиями.
3. Настроил пакетную обработку для АОС1 и АОС2 по 32 потока
4. Запланировал в пакетной обработке 4 пакета по созданию заказов на перемещение. Каждый пакет генерит по 1000 задач, каждая задача формирует по 10 заказов. Раскидал по пакеты по времени, чтобы пакеты 1-3 обрабатывались в одно и то же время, а пакет за 40 мин до начала работы пакетов 1-3 чтобы посмотреть как быстро обработается 1 пакет по отдельности и 3 пакета параллельно.
5. Каждый заказ на перемещение создается со склада А на склад Б с включенным авторезервированием, с 3 строками по номенклатуре 001:
   Строка 1 - 1 шт
   Строка 2 - 2 шт
   Строка 3 - 3 шт.

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

На текущий момент результаты тестирования такие:

1. Создание заказов на перемещение с резерированием

Пакет-------Время начала-----------Время окончания
Пакет 1-----18.03.2015 06:09:16----18.03.2015 07:58:09
Пакет 2-----18.03.2015 06:09:16----18.03.2015 07:44:28
Пакет 3-----18.03.2015 06:09:16----18.03.2015 08:05:06
Пакет 4-----18.03.2015 05:30:15----18.03.2015 06:12:28


2. Создано строк в таблицах в AX
Примечание: база была практически пустая, но было около 10 заказов на перемещение, 15 закупок и 20 продаж. Не стал геммороиться и исключать их исключать из подсчета кол-ва созданных строк в таблицах

Table-Name--------------------------------#-Records-Reserved-(KB)-Data-(KB)-Indexes-(KB)-Unused-(KB)
dbo.INVENTSUMLOGTTS-----------------------327287----237056--------112664----123512-------880
dbo.INVENTTRANS---------------------------193141----640048--------228392----408120-------3536
dbo.INVENTTRANSORIGIN---------------------182474----73072---------30664-----42192--------216
dbo.INVENTTRANSFERLINE--------------------91098-----245136--------119464----125120-------552
dbo.INVENTTRANSORIGINTRANSFERSHIP---------91098-----39088---------15080-----23880--------128
dbo.INVENTTRANSORIGINTRANSFERRECEIVE------91098-----38256---------15112-----23120--------24
dbo.INVENTTRANSFERTABLE-------------------30373-----38176---------21128-----16824--------224
dbo.INVENTDIM-----------------------------12176-----14536---------2152------9912---------2472
dbo.INVENTSUM-----------------------------10656-----14944---------7848------6520---------576
dbo.INVENTBATCH---------------------------10036-----3888----------1720------1720---------448
dbo.INVENTSUMDELTA------------------------0---------2176----------8---------160----------2008


3. Статистика по расщеплениям из в sys.dm_db_index_operational_stats сразу после проведения тестирования
Примечание: статистика может быть немного искажена сводным планированием, которое я запустил чтобы очистить данные в InventSumLogTts и примерно через 20 мин отключил его.
object_nm-----------------------------index_id----partition_number-----------leaf_allocation_count------nonleaf_allocation_count
INVENTSUMDELTA------------------------1-----------1--------------------------102912---------------------1220
INVENTSUMLOGTTS-----------------------1-----------1--------------------------18512----------------------241
INVENTTRANSFERLINE--------------------1-----------1--------------------------15315----------------------246
INVENTSUMDELTA------------------------3-----------1--------------------------11208----------------------28
INVENTSUMDELTA------------------------2-----------1--------------------------11152----------------------28
INVENTSUMDELTA------------------------4-----------1--------------------------10654----------------------8
INVENTSUMLOGTTS-----------------------2-----------1--------------------------8046-----------------------134
INVENTSUMLOGTTS-----------------------3-----------1--------------------------6579-----------------------92
INVENTSUMLOGTTS-----------------------4-----------1--------------------------5310-----------------------21
INVENTTRANSORIGIN---------------------1-----------1--------------------------3946-----------------------17
BATCH---------------------------------1-----------1--------------------------3488-----------------------13
INVENTTRANSORIGIN---------------------2-----------1--------------------------3422-----------------------41
INVENTTRANSFERTABLE-------------------1-----------1--------------------------2645-----------------------32
INVENTTRANSFERLINE--------------------5-----------1--------------------------2322-----------------------27
INVENTTRANSFERLINE--------------------2-----------1--------------------------2319-----------------------27
INVENTTRANSFERLINE--------------------6-----------1--------------------------2286-----------------------27
INVENTTRANSFERLINE--------------------3-----------1--------------------------2281-----------------------27
INVENTTRANSORIGINTRANSFERRECEIVE------1-----------1--------------------------1890-----------------------30
INVENTTRANSORIGINTRANSFERSHIP---------1-----------1--------------------------1882-----------------------30
INVENTTRANSORIGIN---------------------3-----------1--------------------------1800-----------------------20
INVENTTRANSORIGINTRANSFERSHIP---------2-----------1--------------------------1758-----------------------11
INVENTTRANSFERLINE--------------------7-----------1--------------------------1722-----------------------34
INVENTTRANSORIGINTRANSFERRECEIVE------2-----------1--------------------------1668-----------------------10
INVENTTRANSFERLINE--------------------8-----------1--------------------------1627-----------------------30
INVENTTRANSFERLINE--------------------4-----------1--------------------------1508-----------------------25
INVENTTRANSORIGINTRANSFERSHIP---------3-----------1--------------------------1178-----------------------5
INVENTTRANSORIGINTRANSFERRECEIVE------3-----------1--------------------------1176-----------------------5
INVENTTRANSFERLINE--------------------9-----------1--------------------------1170-----------------------5
INVENTTRANSFERTABLE-------------------3-----------1--------------------------438------------------------6
INVENTTRANSFERTABLE-------------------4-----------1--------------------------434------------------------6
INVENTTRANSFERTABLE-------------------5-----------1--------------------------434------------------------6
INVENTTRANSFERTABLE-------------------2-----------1--------------------------409------------------------5
INVENTTRANSFERTABLE-------------------6-----------1--------------------------305------------------------0
INVENTSUMDELTADIM---------------------1-----------1--------------------------170------------------------3
BATCH---------------------------------5-----------1--------------------------116------------------------0
BATCH---------------------------------2-----------1--------------------------83-------------------------2
BATCH---------------------------------3-----------1--------------------------68-------------------------0
INVENTDIM-----------------------------1-----------1--------------------------64-------------------------0
INVENTDIM-----------------------------3-----------1--------------------------17-------------------------0
INVENTDIM-----------------------------4-----------1--------------------------13-------------------------0
INVENTDIM-----------------------------6-----------1--------------------------10-------------------------0
INVENTDIM-----------------------------2-----------1--------------------------9--------------------------0
INVENTDIM-----------------------------5-----------1--------------------------9--------------------------0
sysobjvalues--------------------------1-----------1--------------------------6--------------------------0
INVENTDIM-----------------------------8-----------1--------------------------4--------------------------0
INVENTDIM-----------------------------9-----------1--------------------------4--------------------------0
INVENTDIM-----------------------------10----------1--------------------------4--------------------------0
INVENTDIM-----------------------------11----------1--------------------------4--------------------------0
INVENTDIM-----------------------------7-----------1--------------------------3--------------------------0
BATCHJOBHISTORY-----------------------1-----------1--------------------------1--------------------------0
INVENTTRANS---------------------------1-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------2-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------3-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------4-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------5-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------6-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------7-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------8-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------9-----------1--------------------------0--------------------------0
INVENTTRANS---------------------------10----------1--------------------------0--------------------------0


upd: забыл задать вопрос... пока рано конечно делать какие-либо выводы, т. к. не провел такие же тесты с индексом по RecId. Но хотя бы предварительно по п.3 скажите, достаточно много ли много расщеплений по индексу TransOrigin случилось? Могли ли они как-то существенно повлиять на производительность или чтобы это отразилось на производительности должны быть цифры другого порядка?
upd: добавил статистику по InventTrans в п.3, оказывается по индексам таблицы не было вообще.

[Ivanhoe]

Много букв Вывод какой?

[Kabardian]

Цитата:

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

И как это повлияет на стандартный кластерный индекс? Kabardian, насколько я понял, хочет зафиксировать эффект, когда в таблице происходит движение строк из-за индекса. И потом сравнить, когда движения нет.

Именно, хотел понять как протестировать и потом проверить эффект.

[Kabardian]

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

[Vadik]

Цитата:

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

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

• Расщепление по RecId - параллельная вставка в InventTrans с нескольких инстансов AOS
• Расщепление по TransOriginIdx - расщепление "старых" (ранее созданных) лотов, обновление аналитик в "старых" проводках по лоту

[Logger]

Цитата:

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

• Расщепление по RecId - параллельная вставка в InventTrans с нескольких инстансов AOS

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

[Kabardian]

Vadik, Logger, спасибо за подсказку. Расщеплю их сегодня ночью, полностью расщеплю. А завтра с утра результаты проверю.

Еще интересный момент - для чистоты экспериментов желательно ли делать бэкап-восстановление базы чтобы восстановаить первончальное состояние системы или вполне допустимо по горячему менять индексы на таблице и проверять результаты сначала с RecId затем с TransOrigin?

[Logger]

Еще лучше вот эту галку снять
InventParameters.addTransact

чтобы автоматом не объединяла проводки.

[Kabardian]

Цитата:

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

• Расщепление по TransOriginIdx - расщепление "старых" (ранее созданных) лотов, обновление аналитик в "старых" проводках по лоту

На всякий случай уточню, для расщепления достаточно будет такого теста?

1. Создать заказ на перемещение и физ. зарезервировать строку по нему
2. Создать как минимум еще 300 складских проводок (любых)
3. Снять резерв с заказа на перемещение и физ. зарезервировать по другой складской аналитике

Цитата:

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

Еще лучше вот эту галку снять
InventParameters.addTransact

чтобы автоматом не объединяла проводки.

Спасибо, возьму на заметку.

[Kabardian]

Ничего не понимаю..

В проводке обновился InventDimId (снял резерв и установил его заново), это должно было привести к расщеплению страниц по индексу TransOriginIdx в таблице InventTrans:
------------------------PARTITION---DATAAREAID---INVENTTRANSORIGIN----INVENTDIMID---RECID
ДО-ОБНОВЛЕНИЯ-----------5637145327--001----------5637404275-----------082167--------5637145327
ПОСЛЕ-ОБНОВЛЕНИЯ--------5637145327--001----------5637404275-----------002057--------5637145327

Но, расщепление по индексу TransOriginIdx не происходит:
object_nm-------index_id---index_name---------------------------partition_number---leaf_allocation_count-nonleaf_allocation_coun
INVENTTRANS-----4----------I_177DIMIDIDX------------------------1------------------2---------------------0
INVENTTRANS-----5----------I_177OPENITEMIDX---------------------1------------------0---------------------0
INVENTTRANS-----6----------I_177GOODSINROUTESALESIDX_RU---------1------------------0---------------------0
INVENTTRANS-----7----------I_177GROUPREFIDX_RU------------------1------------------0---------------------0
INVENTTRANS-----8----------I_177OPENSECCURITEMIDX_RU------------1------------------0---------------------0
INVENTTRANS-----9----------I_177GOODSINROUTETRANSITIDX_RU-------1------------------0---------------------0
INVENTTRANS-----10---------I_177GOODSINROUTEDELIVERYIDX_RU------1------------------0---------------------0
INVENTTRANS-----1----------I_177TRANSORIGINIDX------------------1------------------0---------------------0
INVENTTRANS-----2----------I_177ITEMIDX-------------------------1------------------0---------------------0
INVENTTRANS-----3----------I_177STATUSITEMIDX-------------------1------------------0---------------------0

Что я делаю не так?

[Kabardian]

Нашел хорошую статью по SQL Server 2005, в которой сравнивается производительность работы с кластерными индексами и некластерными, много графиков и количественных показателей: SQL Server Best Practices Article.

[AndyD]

Цитата:

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

Ничего не понимаю..

В проводке обновился InventDimId (снял резерв и установил его заново), это должно было привести к расщеплению страниц по индексу TransOriginIdx в таблице InventTrans:
------------------------PARTITION---DATAAREAID---INVENTTRANSORIGIN----INVENTDIMID---RECID
ДО-ОБНОВЛЕНИЯ-----------5637145327--001----------5637404275-----------082167--------5637145327
ПОСЛЕ-ОБНОВЛЕНИЯ--------5637145327--001----------5637404275-----------002057--------5637145327

Но, расщепление по индексу TransOriginIdx не происходит
Что я делаю не так?

А почему, по-вашему, должно было что-то "расщепиться"?

Если у вас проводка только одна на этот лот, то она никуда не сдвинется)
И даже если их несколько, то изменение InventDimId может привести только к перемещению записей между смежными страницами (и то, при условии, что порядок сортировки по InventDimId внутри InventTransOrigin изменится), но никак не их разделение
Ну и опять же, не забываем о FillFactor

Я поэтому и предлагал делать разбиение проводок - так очень просто можно увидеть, что происходит при разделении страниц.

[Alexius]

Цитата:

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

Еще интересный момент - для чистоты экспериментов желательно ли делать бэкап-восстановление базы чтобы восстановаить первончальное состояние системы ...?

Вместо этого лучше использовать операции Отсоединить/Присоединить (sp_detach_db/sp_attach_db), они гораздо шустрее.