Dynamics 365. Нам действительно нужны index hint (forceLiterals против forcePlaceholder)?

Index hint

Microsoft Dynamics AX 2009 и ранние версии поддерживали выражение INDEX HINT в X++. Однако, оно было выключено в Dynamics AX 2012 и интерпретатор запроса стал игнорировать данное выражение.

Index hint в X++ снова доступен

Одна из основных причин заключалась в том, что ошибочный индекс мог ухудшить запрос и мало что можно было сделать, пока запрос не будет исправлен. Теперь, увидев тысячи запросов от сотен клиентов и, что SQL предлагает менее оптимальные планы для некоторых простых запросов, Finance and Operations вернул hint X++. Однако подсказки следует использовать с осторожностью.
Был добавлен новый API для табличного курсора, который называется allowIndexHint, по умолчанию значение установлено False. Разработчики могут использовать параметр, чтобы включить поддержку выражения index hint в выражении select.
Чтобы в существующем запросе начал работать index hint, требуется только использовать новый API, без изменения самого запроса, как в пример ниже.

public void testIndexHintRegularTable()
{
   SysDataAccessDBLogTestTable tbl1;

   tbl1.allowIndexHint(true); // New API

   select generateonly tbl1
       index hint PrimaryKeyIdx // Send index hint to SQL server
       where tbl1.description == '';
   ...
}

Режим ForcePlaceholders

По умолчанию запрос выполняется в данном режиме. В этом случае каждое SQL выражение, которое написано в X++, обрабатывается в 2 шага:
1. Выполняется команда sp_prepare с заполнителями вместо фактических значений и, как результат команды, получаем кешированный план.
2. Команда sp_cursorexecute выполняет запрос используя этот план и фактические значения.
Для одного SQL выражения с различными значениями план рассчитывается один раз.
Как SQL сервер определяет, какой план сгенерировать в sp_prepare, если для некоторых значений один план может быть более эффективным, чем для других? Для новых неизвестных планов используется функция “parameters sniffing”. Когда сервер получает команду sp_prepare для нового выражения, он не пытается вычислить его немедленно, вместо этого он ожидает первого вызова sp_cursorexecute и использует первые переданные значения для расчета плана.

Режим ForceLiterals

В этом случае, текущее SQL выражение (с фактическими значениями) отправляется в SQL сервер и выполняется как есть. Недостатком является то, что для каждого набора значений вам нужно будет проанализировать этот оператор и составить новый план SQL.

Parameters sniffing

Функция “Parameters sniffing” нередко является причиной проблем связанных с производительностью. Например, имеется склад, где большинство номенклатур имеет уникальный номер партии, но несколько партий номенклатур имеют значения по умолчанию (такие как “Нет партии”, “Пусто”). В этой ситуации актуальный план будет зависить от первых значений запроса. Если запрос содержит для партии значения “по умолчанию”, SQL сервер создает план, который начинает выполнение с номенклатуры, поскольку значение для партии не является выборочным, в данном случае. Но для большинства номенклатур это будет неверно и приведет огромных накладным расходам, т.к. для большинства случаев, партия является уникальным значением.

Накладные расходы на использование ForceLiterals

Давайте проверим это. Для стендов используется средняя конфигурация для АХ 2012 и D365 8.1
Для тестирования используется следующий джоб, который рассчитывает физическое количество товара на складе.

class testForceLiterals
{
    public static void main(Args _args)
    {
        InventTable         inventTable;
        InventDim           inventDim;
        InventSum           inventSum;
        InventLocation      inventLocation;
        real                timeMs;         
        int                 curCount, maxCount = 5000;
        System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
        
        stopwatch.Start();
        while select inventTable
        {
            while select inventLocation
            {
                //select forcePlaceholders  //case 1
                select forceLiterals        //case 2
                    sum(AvailPhysical) from inventSum
                group by ItemId
                where inventSum.ItemId == inventTable.ItemId &&
                      inventSum.Closed == false
                exists join inventDim
                    where inventDim.inventDimId      == inventSum.InventDimId &&
                          inventDim.InventLocationId == inventLocation.InventLocationId &&
                          inventDim.InventSiteId     == inventLocation.InventSiteId;

                curCount++;
                if (curCount > maxCount) break;
            }
            if (curCount > maxCount) break;
        }
        stopwatch.Stop();
        timeMs = stopwatch.get_ElapsedMilliseconds();
        info(strFmt("Time for %1 lines: %2 sec", curCount, timeMs / 1000));
    }
}

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

DBCC FREEPROCCACHE
GO

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

SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

Результаты ForcePlaceholders
Первый запуск: 8,51 сек.
Второй запуск: 8,51 сек.
Потребление памяти:

Результаты ForceLiterals
Первый запуск: 66,57 сек.
Второй запуск: 9,60 сек.
Потребление памяти:

В течение выполнения загрузка одного ядра процессора была 100%

Представим значения в виде графа:

Потребление памяти около 100Kb для сохранненого плана, тогда как для forceLiterals требовалось 500MB памяти для 5k SQL выражения. Большой объем памяти используется только для кеша плана.

Заключение

Создание плана до сих пор является сложной задачей, которая потребляет много процессорных ресурсов. Эта разница в 1 минуту между первыми запусками forceLiterals и forcePlaceholders использует ядро процессора на 100%. В случае forceLiterals, время построения плана в 6 раз больше, чем время на получение фактических данных. Также обратим внимание на разницу между вторым запуском forceLiterals и forcePlaceholder, примерно 1 секунда — это время, которое сервер тратит на лексический анализ выражения SQL.
Для отдельных SQL выражений (или выражений, которые выполняются один раз для заголовка документа) forceLiterals может дать некоторые преимущества. Но если имеется большое количество пользователей, которые работают с разными элементами и разными измерениями, использование forceLiterals для частых запросов может повлиять на общую производительность SQL сервера (как по потреблению памяти, так и по процессору). Поэтому нужно избегать использования forceLiterals на уровне «на строку». И использование index hint может в этом помочь.

source

 

Comments

So empty here ... leave a comment!

Добавить комментарий

Sidebar