AX 2012 ускорение синхронизации базы в 3-5 раз

[Logger]

Добрый день.
Коллеги, хотел поделиться опытом ускорения синхронизации базы в 2012-й аксапте. Как известно, на стандартном приложении она длится >=20 минут какой бы мощности сервер ни взять. Достаточно долго получается. (имеется в виду полная синхронизация базы в случае когда никаких изменений нет)

Я задумался, а что там в этот момент происходит. Запустил SQL профайлер.
Оказалось, что у меня синхронизация длится 22 минуты 49 секунд.

При этом большую часть времени сервер выполняет такие запросы:
1.

select name, change_tracking_state_desc from sys.columns AS Cols inner join sys.fulltext_index_columns AS FTSCols inner join sys.fulltext_indexes as FTS on FTSCols.object_id = FTS.object_id on Cols.object_id = FTSCols.object_id where Cols.column_id = FTSCols.column_id and Cols.object_id = object_id('TableName')

где TableName – SQL имя таблицы

выполняется примерно 8713 *2 = 17426 запросов длительностью по 50-70 миллисекунд, что дает от 15 до 20 минут. (Такое огромное число запросов получается потому что в базе 8713 таблиц и при синхронизации выполняется 2 прохода)


2.

exec [sys].sp_pkeys N'TableName',NULL,NULL

… также примерно 8713 *2 = 17426 запросов длительностью по 10 миллисекунд, что дает 3 минуты ожидания.

Итого в сумме из 23 минут эти 2 запроса выполнялись примерно от 18 до 23 минут.
На первый взгляд не очень эффективно получается.

Большую часть времени сервер приложения делает запросы к системных объектам SQL сервера, а мы ждем. Причем, получается, что разработчики ядра аксапты проигнорировали базовые правила выполнения SQL запросов и делают десятки тысяч запросов там, где можно было обойтись одним запросом, разобрать его результаты и уже использовать их. Это дало бы значительное ускорение.
Но попробуем улучшить то, что имеем.

Запустил первый запрос без фильтров. Т.е. убрал условие
Cols.object_id = object_id('TableName')

Результат – всего навсего 25 записей !!!

Т.е. при синхронизации большую часть времени аос без конца выполняет запрос к системным вьюхам, а по факту обрабатывает конечную выборку из 25 записей. Совсем интересно стало:
sys.columns содержит 172 000 записей.
sys.fulltext_indexes содержит 19 записей.
sys.fulltext_index_columns содержит 25 записей.

Примерно понятно куда девается время.

Попробуем ускорить.
Сперва попытались в лоб, применив что-то типа этого :
Попробовал создать материализованный View так чтобы оптимизатор SQL использовал его при построении плана запроса. Оказалось, что в SQL Server нельзя применять Schema binding к системным представлениям. (Была идея создать представление, проиндексировать его, чтобы оптимизатор вместо нашего джоина реально выполнял запрос по индексу на view в котором лежит всего навсего 25 записей.)

Но, к сожалению, это оказалось невозможно в отличие от оракла. Плюс это все работает только для Enterprise версии. Т.е. для Standart edition все равно не сработало бы.
(у меня SQL 2016 SP1 Standart Edition. Ax 2013 build 6.3.6000.8144 (самый свежий на текущий момент)

Попробовал модифицировать описанный выше способ.
Создал свои таблички
sysZcolumns
sysZfulltext_indexes
sysZfulltext_index_columns

и заполнил их также как системные представления

Затем при помощи утилиты HEX Editor Neo
https://www.hhdsoftware.com/Downloads/free-hex-editor

пропатчил ax32Serv.exe так, что он стал отправлять запрос к нашим табличкам вместо системных :

select name, change_tracking_state_desc from sysZcolumns AS Cols inner join sysZfulltext_index_columns AS FTSCols inner join sysZfulltext_indexes as FTS on FTSCols.object_id = FTS.object_id on Cols.object_id = FTSCols.object_id where Cols.column_id = FTSCols.column_id and Cols.object_id = object_id('TableName')

Время проблемного запроса упало с 50-70 до 5 миллисекунд.
Время полной синхронизации теперь заняло 8:04. Ускорение примерно в 3 раза
Хорошо, но недостаточно.

Подошел чуть радикальнее.
Создал табличку sysZFTSC
Поля:
object_id
column_id
nam
change_tracking_state_des

Заполнил на основании системных представлений. Табличка содержит 25 записей.
Подправил ax32serv.exe так чтобы уходил запрос такого вида.

select nam , change_tracking_state_des  from sysZFTSC    AS Cols 
where 
 1 = 1  and Cols.object_id = object_id('TableName')

Т.е. джоин из 3 системных вьюх превратился в запрос по табличке из 25 записей.
Длительность запроса упала до 0,1-2 миллисекунд. (В начале синхронизации идут запросы длительностью 2 миллисекунды, а затем время падает до 0,1. Почему SQL повел себя так, до конца непонятно). Это уже лучше. Хотя сильно на время синхронизации это не повлияло. Возможно надо было сделать больше измерений, набрав статистику, но нет времени на это.

Что еще можно сделать. Как-то исправить 2-й запрос

exec [sys].sp_pkeys

- не получается. Там системная хранимка работает.

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

Попробовал перепроверить конфигурацию в 2012-й и поотключал все, что можно, включил только необходимый минимум как в 4-ке.
Таблички в базе при этом не удаляются, но удаляются ряд индексов. Время упало до 6:35

Еще я заметил что если DEL_ табличка отключена ключом SysDeletedObjects* то при синхронизации табличка совсем удаляется из базы. И ряд запросов к системных вьюхам и хранимкам с именем этой таблички не выполняется. Например, наш 2-й запрос.

На этом можно сэкономить. У нас от стандарта используется стандартный набор: расчеты с клиентами, поставщиками, управление запасами. Много табличек в базе лежат пустые и в принципе не нужны.

Сейчас в базе 8713 таблиц и 1370 view.

Проверил конфигурацию, отключил все что не нужно.
На каждом отключенном конфключе джобом создал дочерний ключ

SysDeltedObjects_XXX и прописал его в соответствующую табличку. (Оказывается если создать ключ с именем соответствующем шаблону SysDeletedObjects* то ядро по особому обрабатывает такой ключ – оно удаляет физически из базы таблички с таким ключом)

После синхронизации число табличек уменьшилось до 4364. View – до 667. Т.е. примерно в 2 раза.

Время полной синхронизации упало до 4:28 минут.

Т.е. получили ускорение в 5,1 раз.

Интересно что время выполнения запроса

exec [sys].sp_pkeys N'TableName',NULL,NULL

стало уже не 10 миллисекунд а 4-5 миллисекунды. Что неудивительно, так как стало меньше записей в системных табличках сиквела.

sys.columns раньше содержал 172 тысячи записей, а теперь 90 тысяч.

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

Класс по заполнению таблички sysZFTSC и табличка – во вложении. Поставил заполнение sysZFTSC перед и до super() в appl.dbsynchronise()



Вопрос к участникам :

1. Кто-нибудь что-то подобное делал ? Есть еще способы ускорения синхронизации? Какие риски такого подхода ?


2. Хотелось бы еще ускорить работы с базой модели.
Потому что при первом открытии некоторые формы думают по 6-10 секунд загружая определения методов таблиц, полей, и EDT (также как и при синхронизации делаются тысячи запросов, например, каждый метод таблички грузится отдельным запросом ). А потом еще выполняют запрос к БД для получения данных. Получение данных из БД мы можем оптимизировать (вылизали так, что отклик идет мгновенно), а работу с моделью – похоже нет.Хотя там используются хранимые процедуры, поэтому можно их допилить.

Но может кому-то удавалось.


3. Как с этим обстоят дела в D365 ? Там описанные проблемы актуальны?


4. Какие риски такого подхода вы видите ?

[Logger]

Вложения забыл добавить.
Исправляюсь.

В \Classes\Application\dbSynchronize
дописать до super такой код

    if (!syncAsNeeded)
    {
        PrepareSync_MRC::fillTables(true);
    }

а в конце

    if (!syncAsNeeded)
    {
        PrepareSync_MRC::fillTables(false);
    }

подсмотрел тут:
Долгая синхронизация

А здесь на sql.ru обсуждение
https://www.sql.ru/forum/1314237/ind...ror-kak-oboyti

[sukhanchik]

Цитата:

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

3. Как с этим обстоят дела в D365 ? Там описанные проблемы актуальны?

Дело в том, что в D365 обновления накатываются по принципу - "нажал кнопку, дождался, пока отработает". Т.е. конечно 30-45 минут простоя не есть здорово, но процедура обновления не разделяется на собственно синхронизацию, обновление кода и еще ряд процедур. Т.е. конечно в недрах Powershell-скриптов разделение есть, но для конечного пользователя - его нет

Цитата:

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

4. Какие риски такого подхода вы видите ?

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

[Logger]

Да, это конечно проблема.

Но модификаций в X++ не завязана на ядро. Т.е. если exe-ник изменится, то просто уйдет ускорение связанное с обращениями к метаданным SQL и все. Все продолжит работать. Ничего не сломается.
Ускорения из-за отключения табличек в конфигурации останется.

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

По-хорошему, надо добиваться от MS исправления ax32Serv.exe
Там и кодировать то немного. Например, перед синхронизацией заполнить времянку со структурой как в моем примере. И поменять текст запроса который читает определение полнотекстовых индексов. Это десяток строк кода всего. Зато получается 3-кратное ускорение.

[Logger]

А чтение данных из модели никто не пробовал оптимизировать ?
Там такие же безумные тысячи запросов через вызовы хранимых процедур.
Хранимки нам доступны, можно переписать как душе угодно.

Например при запросе к [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt]
(а именно эта хранимка самая популярная при начитке узлов AOT)
в случае обращения к EDT или полям таблички передается параметр LoadType = 273
т.е. начитывается заодно и исходный текст узла, которого в данном случае нет по определению, т.е. лишний outer join идет. Можно его убрать. Ну и.т.д.

Все можно пофиксить, поправив текст времянки, делая в зависимости от типа объекта разный запрос.

Правда начитка EDT - это мелочи по сравнению с начитыванием методов таблиц при открытии форм. Вот на них и идут основные запросы. Каждый табличный метод отдельным вызовом типа такого (похоже начитываются все методы таблички, даже если они не используются) :

exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'buf2con',15,5,1,273

А для всей таблички так:

exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'buf2con',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'canSubmitToWorkflow',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'caption',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'clear',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'con2buf',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'defaultField',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'defaultRow',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'equal',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'fieldAccessRight',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'fieldBufferAccessRight',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'getAllowRedefault',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'getDefaultingDependencies',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'getExtension',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'getPresenceFieldData',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'getSQLStatement',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'helpField',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'initValue',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'isFormDataSource',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'modifiedField',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'modifiedFieldValue',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'postCacheLoad',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'postLoad',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'preRemoting',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'setSQLTracing',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'setXDSContext',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'tableAccessRight',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'tableBufferAccessRight',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'takeOwnershipOfTempDBTable',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'toolTipField',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'toolTipRecord',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'useExistingTempDBTable',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'validateDelete',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'validateField',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'validateFieldValue',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'validateWrite',15,5,1,273
exec [AXJW12_TEST_model].[dbo].[XU_ReadHeaderExt] 12,30441,0,N'xml',15,5,1,273

и это только начитка методов. (Без учета полей, индексов, групп полей, EDT в полях)
А если там 100 методов ? То будет 100 запросов.
А табличка там не одна. В некоторых стандартных формах по десятку и больше датасорсов нагромождено.

Правила построения запросов SQL, когда за один запрос лучше получать всю выборку ?
Не, не слышали такого


Правда справедливости ради скажу, что когда трассировал открытие формы то из 6 секунд время выполнения этих запросов было порядка 1,5 секунд. Остальные 4,5 секунды аос что-то думал внутри себя. возможно раскладывал это все в кешах. Но есть вероятность того что профайлер не совсем точно время посчитал. Тем более что длительность каждого запроса была 0,120 - 0,180 миллисекунд.

Сразу добавлю, что времена привожу по начитке формы из AOT т.е. из базы модели.
Запрос самих данных в БД - это отдельная тема, время на это сюда не включено и там все было нормально.

[Logger]

Про версию SQL опечатался
стоит
Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) - 13.0.5081.1 (X64) Jul 20 2018 22:12:40

[Masel]

Интересная тема, никогда не пробовал смотреть что там делается при синхронизации. Я посмотрел первый запрос, который по 70 мс у вас выполняется. Если его запускать, то любой повторный запуск у меня отрабатывает 0мс на тестовой базе. Это наталкивает на мысль, что все время там тратится на парсинг и построение плана. Так происходит потому, что запрос с литералами, а именно название таблицы туда передается. Я попробовал убрать литералы через план гайд, т.е. сделать параметризованный запрос, тогда времени на него почти не тратится. Делается это простым скриптом:

PHP код:

DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'select name, change_tracking_state_desc from sys.columns AS Cols inner join sys.fulltext_index_columns AS FTSCols inner join sys.fulltext_indexes as FTS on FTSCols.object_id = FTS.object_id on Cols.object_id = FTSCols.object_id where Cols.column_id = FTSCols.column_id and Cols.object_id = object_id(''SYSINETCSS'')',  
    @stmt OUTPUT,   
    @params OUTPUT;  
select @stmt;
EXEC sp_create_plan_guide   
    N'SyncTemplateGuide',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)'; 


На медленной тестовой базе это ускорило синхронизацию с 28 до 7 минут примерно.
Попробуйте у себя. Плюс в том что не нужно патчить exe.

[Logger]

Цитата:

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

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

О! Классно.

А в моем случае план строился просто, так как есть одна табличка с кластерным индексом и все. У оптимизатора особо нет вариантов.

Вы проверили на примере одной таблички

Цитата:

Cols.object_id = object_id(''SYSINETCSS'')'

А штатно в базе их около 9 тысяч. Т.е. нужно для каждой таблички такой скрипт сделать и запустить ? Производительность CBO от этого не просядет ?

[Masel]

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

select name , change_tracking_state_desc from sys . columns as Cols inner join sys . fulltext_index_columns as FTSCols inner join sys . fulltext_indexes as FTS on FTSCols . object_id = FTS . object_id on Cols . object_id = FTSCols . object_id where Cols . column_id = FTSCols . column_id and Cols . object_id = object_id ( @0 )

Этот шаблон используется потом для создания план гайда

[Logger]

А какая у вас версия SQL ?

[Masel]

MS SQL 2016 13.0.5233.0

[Logger]

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

[Logger]

Ну вообще, получается что можно обойтись без патча Exe-ника.
Это очень круто.

Ура !

[Masel]

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

[sukhanchik]

Спасибо! А рестарт SQL Server-а приведет к необходимости выполнять этот скрипт заново?

[Masel]

Цитата:

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

Спасибо! А рестарт SQL Server-а приведет к необходимости выполнять этот скрипт заново?

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

[Logger]

Интересно, что если запускать синхронизацию с такими параметрами

appl.dbSynchronize(0, false, true, true, [0], false)

(вызов отсюда \Classes\SysCheckListItem_CreateTablesUpgrade\main)

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

exec [sys].sp_tables N'TableName',N'%',NULL,NULL,@fUsePattern=1

и длится он 150 миллисекунд (в 2-3 раза дольше чем запрос который мы ускоряли).
Ускорить его выполнение через коррекцию плана запроса тоже невозможно.

Т.е. в этом случае синхронизация длится почти час!

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

[trud]

Потестил на нескольких базах, скрипт творит чудеса - на быстрой машине с 18 до 6 минут сократилось время синхронизации. на медленной - с 37 до 26(время в основном уходит на 2 этап)
Жалко что такие скрипты не появились раньше

[Logger]

Цитата:

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

Потестил на нескольких базах, скрипт творит чудеса - на быстрой машине с 18 до 6 минут сократилось время синхронизации. на медленной - с 37 до 26(время в основном уходит на 2 этап)
Жалко что такие скрипты не появились раньше

Спасибо за проверку.

Самое главное, что применяя скрипт, нет нужды трогать Exe-ник. Т.е. риски что-то повредить в аксапте сведены к нулю.

[Vadik]

перенес ветку в Базу знаний