Улучшение производительности временной таблицы и табличной переменной с помощью оптимизации памяти

Статья
05/30/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

В этой статье рассматриваются следующие вопросы:

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

А. Основные сведения о табличных переменных, оптимизированных для памяти

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

Оптимизированная для памяти табличная переменная:

хранится только в памяти и не имеет компонента на диске;
не требует операций ввода-вывода;
не требуется использования базы данных tempdb и не создает соответствующих конфликтов;
может передаваться в хранимую процедуру как возвращающий табличное значение параметр;
должна иметь по крайней мере один индекс (некластеризованный или хэш-индекс):
- для хэш-индекса число контейнеров в идеале должно в 1–2 раза превышать предполагаемое число уникальных ключей индекса, но допускается и более значительное превышение (до 10 раз). Дополнительные сведения см. в разделе Индексы для оптимизированных для памяти таблиц.

Типы объектов

Выполняющаяся в памяти OLTP предоставляет следующие объекты, которые можно использовать для оптимизированных для памяти временных таблиц и табличных переменных:

Оптимизированные для памяти таблицы
- Durability = SCHEMA_ONLY
Переменные таблицы, оптимизированные для памяти
- Должен быть объявлен двумя шагами (а не встроенными):
  - CREATE TYPE my_type AS TABLE ...; , а затем
  - DECLARE @mytablevariable my_type;.

B. Сценарий. Замена глобальной базы данных tempdb ##table

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

Предположим, что у вас есть приведенная ниже глобальная временная таблица.

CREATE TABLE ##tempGlobalB  
    (  
        Column1   INT   NOT NULL ,  
        Column2   NVARCHAR(4000)  
    );

Рассмотрите возможность замены глобальной временной таблицы на приведенную ниже таблицу, оптимизированную для памяти, с параметром DURABILITY = SCHEMA_ONLY.

CREATE TABLE dbo.soGlobalB  
(  
    Column1   INT   NOT NULL   INDEX ix1 NONCLUSTERED,  
    Column2   NVARCHAR(4000)  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
        DURABILITY        = SCHEMA_ONLY);

Б.1. Этапы

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

Однократно создайте таблицу dbo.soGlobalB так же, как любую традиционную таблицу на диске.
Из Transact-SQL удалите создание таблицы ##tempGlobalB . Важно создавать оптимизированную для памяти таблицу во время развертывания, а не во время выполнения, чтобы избежать дополнительных временных затрат при компиляции, связанных с созданием таблицы.
В T-SQL замените все упоминания ##tempGlobalB на dbo.soGlobalB.

В. Сценарий. Замена #table tempdb сеанса

Для подготовки к замене временной таблицы сеансов требуется больше кода T-SQL, чем в предыдущем сценарии с глобальной временной таблицей. К счастью, больший объем кода T-SQL не означает, что для преобразования потребуется больше усилий.

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

Предположим, что у вас есть приведенная ниже временная таблица сеансов.

CREATE TABLE #tempSessionC  
(  
    Column1   INT   NOT NULL ,  
    Column2   NVARCHAR(4000)  
);

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

CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid;

Затем создайте таблицу SCHEMA_ONLY, а также политику безопасности для нее.

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

Для таблицы dbo.soSessionC, возможно, лучше подойдет хэш-индекс, если вычислить соответствующее значение BUCKET_COUNT. Но для простоты в этом примере мы используем некластеризованный индекс.

CREATE TABLE dbo.soSessionC  
(  
    Column1     INT         NOT NULL,  
    Column2     NVARCHAR(4000)  NULL,  

    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH  
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),  
        
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_ONLY);  
go  
  
  
CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.soSessionC  
    WITH (STATE = ON);  
go

Наконец, в общем коде T-SQL сделайте следующее:

Измените все ссылки на временную таблицу в инструкциях Transact-SQL на новую таблицу, оптимизированную для памяти:
- Старый: #tempSessionC
- Новое имя: dbo.soSessionC
Замените инструкции CREATE TABLE #tempSessionC в своем коде на DELETE FROM dbo.soSessionC, чтобы сеанс не обращался к содержимому таблицы, добавленному в предыдущем сеансе с тем же идентификатором session_id. Важно создавать оптимизированную для памяти таблицу во время развертывания, а не во время выполнения, чтобы избежать дополнительных временных затрат при компиляции, связанных с созданием таблицы.
Удалите из кода инструкции DROP TABLE #tempSessionC. Если есть опасения относительно размера используемой памяти, вы можете добавить инструкцию DELETE FROM dbo.soSessionC.

D. Сценарий: табличная переменная может иметь параметр MEMORY_OPTIMIZED=ON

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

Ниже приведен код T-SQL для традиционной табличной переменной. Ее область действия завершается, когда заканчивается пакет или сеанс.

DECLARE @tvTableD TABLE  
    ( Column1   INT   NOT NULL ,  
      Column2   CHAR(10) );

Г.1. Преобразование встроенной переменной в явную

Предыдущий синтаксис создает так называемую встроеннуютабличную переменную. Встроенный синтаксис не поддерживает оптимизацию для памяти. Поэтому давайте преобразуем встроенный синтаксис в явный для TYPE.

Область действия. Определение TYPE, созданное первым пакетом, отделенным командой GO, сохраняется даже после завершения работы и перезапуска сервера. Но после первого разделителя GO объявленная таблица @tvTableC сохраняется только до тех пор, пока не будет достигнут следующий разделитель GO и пакет не завершится.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL ,  
        Column2  CHAR(10)  
    );  
go  
        
SET NoCount ON;  
DECLARE @tvTableD dbo.typeTableD  
;  
INSERT INTO @tvTableD (Column1) values (1), (2)  
;  
SELECT * from @tvTableD;  
go

Г.2. Преобразование явной таблицы на диске в оптимизированную для памяти таблицу

Оптимизированная для памяти табличная переменная не хранится в базе данных tempdb. Оптимизация для памяти приводит к повышению скорости работы до 10 раз и более.

Преобразование таблиц в оптимизированные для памяти производится в один шаг. Оптимизируйте явное создание TYPE следующим образом. При этом добавляются:

Индекс. Еще раз напомним, что каждая оптимизированная для памяти таблица должна содержать как минимум один индекс.
MEMORY_OPTIMIZED = ON.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL   INDEX ix1,  
        Column2  CHAR(10)  
    )  
    WITH  
        (MEMORY_OPTIMIZED = ON);

Готово.

Е. Файловая группа, необходимая для SQL Server

Для использования оптимизированных для памяти функций в Microsoft SQL Server база данных должна иметь файловую группу, объявленную с параметром MEMORY_OPTIMIZED_DATA.

База данных SQL Azure не требует создания такой файловой группы.

Предварительное требование. Приведенный ниже код Transact-SQL для файловой группы необходим для развернутых образцов кода T-SQL в дальнейших подразделах этого раздела.

Необходимо использовать SSMS.exe или другое средство, позволяющее отправлять код T-SQL.
Вставьте образец кода T-SQL для файловой группы в среду SSMS.
Отредактируйте код T-SQL, изменив имена и пути к каталогам по своему желанию.

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

Выполните отредактированный код T-SQL.

Запускать файловую группу T-SQL несколько раз не нужно, даже если вы повторно настраиваете и перезапускаете скрипт T-SQL для сравнения скорости в следующем подразделе.

ALTER DATABASE InMemTest2  
    ADD FILEGROUP FgMemOptim3  
        CONTAINS MEMORY_OPTIMIZED_DATA;  
go  
ALTER DATABASE InMemTest2  
    ADD FILE  
    (  
        NAME = N'FileMemOptim3a',  
        FILENAME = N'C:\DATA\FileMemOptim3a'  
                    --  C:\DATA\    preexisted.  
    )  
    TO FILEGROUP FgMemOptim3;  
go

Следующий скрипт создает файловую группу и настраивает рекомендованные параметры базы данных: enable-in-memory-oltp.sql

Дополнительные сведения о ALTER DATABASE ... ADD для FILE и FILEGROUP см. в следующих разделах:

F. Небольшой тест для проверки повышения быстродействия

В этом разделе приводится код Transact-SQL, с помощью которого можно протестировать и оценить прирост скорости выполнения операций INSERT-DELETE при использовании табличной переменной, оптимизированной для памяти. Код состоит из двух половин, которые почти одинаковы за тем исключением, что в первой половине используется таблица оптимизированного для памяти типа.

Сравнительный тест длится примерно 7 секунд. Запуск примера:

Предварительное требование. Вы уже должны были выполнить код T-SQL для файловой группы из предыдущего подраздела.
Выполните приведенный ниже скрипт T-SQL INSERT-DELETE.

Обратите внимание на инструкцию GO 5001, которая повторно отправляет код T-SQL 5001 раз. Вы можете изменить это число и перезапустить тест.

В базе данных SQL скрипт следует запускать из виртуальной машины, находящейся в вашем регионе.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.  
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!  

INSERT INTO @tvTableC (Column1)
VALUES (1), (2);

INSERT INTO @tvTableC (Column1)
VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.  
-------------------------------------------------  
---- Start traditional on-disk.  
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.  
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!  

INSERT INTO @tvTableC (Column1)
VALUES (1), (2);

INSERT INTO @tvTableC (Column1)
VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

Вот результирующий набор.

---- Next, memory-optimized, faster. ----  
2016-04-20 00:26:58.033  = Begin time, _mem.  
Beginning execution loop  
Batch execution completed 5001 times.  
2016-04-20 00:26:58.733  = End time, _mem.  

---- Next, tempdb based, slower. ----  
2016-04-20 00:26:58.750  = Begin time, _tempdb.  
Beginning execution loop  
Batch execution completed 5001 times.  
2016-04-20 00:27:05.440  = End time, _tempdb.  
---- Tests done. ----

G. Прогнозирование потребления активной памяти

Чтобы узнать, как прогнозировать потребность оптимизированных для памяти таблиц в активной памяти, обратитесь к следующим ресурсам:

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

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

H. См. также

Таблицы, оптимизированные для памяти.
Определение устойчивости для оптимизированных для памяти объектов.
Накопительное обновление, позволяющее исключить вероятность возникновения неправильных ошибок нехватки памяти, объявленное в блоге в сентябре 2017 г.
- В статье Версии сборки SQL Server 2016 приводятся подробные сведения о выпусках, пакетах обновления и накопительных обновлениях.
- Эти случайные неправильные ошибки не возникали в выпуске Enterprise для SQL Server.

Поделиться через