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

2025-05-31

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

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

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

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

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

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

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

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

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

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

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

В. Сценарий. Замена глобальной временной таблицы

Замена глобальной временной таблицы на оптимизированную для памяти таблицы 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);

Этапы

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

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

В. Сценарий. Замена временной таблицы сеанса

Для подготовки к замене временной таблицы сеансов требуется больше кода 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 DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
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 в случае, если размер памяти является потенциальной проблемой.

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

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

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

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

Преобразование встроенного в явное

Предыдущий синтаксис создает так называемую встроеннуютабличную переменную. Встроенный синтаксис не поддерживает оптимизацию памяти. Поэтому давайте преобразуем встроенный синтаксис в явный для 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 AS 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, индекс NONCLUSTERED является хорошей альтернативой.