Улучшение производительности временной таблицы и табличной переменной с помощью оптимизации памятиFaster temp table and table variable by using memory optimization

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

Если вы используете временные таблицы, табличные переменные или возвращающие табличные значения параметры, рекомендуем преобразовать их в оптимизированные для памяти таблицы и табличные переменные с целью повышения производительности.If you use temporary tables, table variables, or table-valued parameters, consider conversions of them to leverage memory-optimized tables and table variables to improve performance. Изменения, которые необходимо внести в код, обычно минимальны.The code changes are usually minimal.

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

  • сценарии, в которых выгоднее преобразование в хранящиеся в памяти объекты;Scenarios which argue in favor of conversion to In-Memory.
  • технические инструкции по реализации преобразования в хранящиеся в памяти объекты;Technical steps for implementing the conversions to In-Memory.
  • предварительные требования для преобразования в хранящиеся в памяти объекты;Prerequisites before conversion to In-Memory.
  • образец кода, демонстрирующий преимущества оптимизации для памяти в плане производительности.A code sample that highlights the performance benefits of memory-optimization

A.A. Основные сведения о табличных переменных, оптимизированных для памятиBasics of memory-optimized table variables

Оптимизированная для памяти табличная переменная позволяет повысить эффективность благодаря использованию тех же алгоритмов и структур данных, которые применяются в оптимизированных для памяти таблицах.A memory-optimized table variable provides great efficiency by using the same memory-optimized algorithm and data structures that are used by memory-optimized tables. Эффективность максимальна в случае, если доступ к табличной переменной осуществляется из модуля, скомпилированного в собственном коде.The efficiency is maximized when the table variable is accessed from within a natively compiled module.

Оптимизированная для памяти табличная переменная:A memory-optimized table variable:

  • хранится только в памяти и не имеет компонента на диске;Is stored only in memory, and has no component on disk.
  • не требует операций ввода-вывода;Involves no IO activity.
  • не требуется использования базы данных tempdb и не создает соответствующих конфликтов;Involves no tempdb utilization or contention.
  • может передаваться в хранимую процедуру как возвращающий табличное значение параметр;Can be passed into a stored proc as a table-valued parameter (TVP).
  • должна иметь по крайней мере один индекс (некластеризованный или хэш-индекс):Must have at least one index, either hash or nonclustered.
    • для хэш-индекса число контейнеров в идеале должно в 1–2 раза превышать предполагаемое число уникальных ключей индекса, но допускается и более значительное превышение (до 10 раз).For a hash index, the bucket count should ideally be 1-2 times the number of expected unique index keys, but overestimating bucket count is usually fine (up to 10X). Дополнительные сведения см. в разделе Индексы для оптимизированных для памяти таблиц.For details see Indexes for Memory-Optimized Tables.

Типы ObjectObject types

Выполняющаяся в памяти OLTP предоставляет следующие объекты, которые можно использовать для оптимизированных для памяти временных таблиц и табличных переменных:In-Memory OLTP provides the following objects that can be used for memory-optimizing temp tables and table variables:

  • Таблицы, оптимизированные для памятиMemory-optimized tables
    • Durability = SCHEMA_ONLYDurability = SCHEMA_ONLY
  • Оптимизированные для памяти табличные переменныеMemory-optimized table variables
    • Должны объявляться в два этапа (а не как встроенные):Must be declared in two steps (rather than inline):
      • CREATE TYPE my_type AS TABLE ...; , а затемCREATE TYPE my_type AS TABLE ...; , then
      • DECLARE @mytablevariable my_type;.DECLARE @mytablevariable my_type;.

Б.B. Сценарий: замена глобальной таблицы tempdb ##tableScenario: Replace global tempdb ##table

Замена глобальной временной таблицы на оптимизированную для памяти таблицы SCHEMA_ONLY достаточно проста.Replacing a global temporary table with a memory-optimized SCHEMA_ONLY table is fairly straightforward. Наиболее существенное изменение состоит в том, что таблица создается во время развертывания, а не во время выполнения.The biggest change is to create the table at deployment time, not at runtime. Создание оптимизированных для памяти таблиц занимает больше времени, чем у традиционных, из-за оптимизации во время компиляции.Creation of memory-optimized tables takes longer than creation of traditional tables, due to the compile-time optimizations. Создание и удаление оптимизированных для памяти таблиц в составе рабочей нагрузки повлияло бы на ее производительность, а также на производительность повтора на серверах-получателях AlwaysOn и восстановления баз данных.Creating and dropping memory-optimized tables as part of the online workload would impact the performance of the workload, as well as the performance of redo on AlwaysOn secondaries and database recovery.

Предположим, что у вас есть приведенная ниже глобальная временная таблица.Suppose you have the following global temporary table.

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

Рассмотрите возможность замены глобальной временной таблицы на приведенную ниже таблицу, оптимизированную для памяти, с параметром DURABILITY = SCHEMA_ONLY.Consider replacing the global temporary table with the following memory-optimized table that has 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. ЭтапыB.1 Steps

Чтобы преобразовать глобальную временную таблицу в таблицу с параметром SCHEMA_ONLY, выполните указанные ниже действия.The conversion from global temporary to SCHEMA_ONLY is the following steps:

  1. Однократно создайте таблицу dbo.soGlobalB так же, как любую традиционную таблицу на диске.Create the dbo.soGlobalB table, one time, just as you would any traditional on-disk table.
  2. В коде Transact-SQL удалите инструкцию для создания таблицы ##tempGlobalB.From your Transact-SQL, remove the create of the ##tempGlobalB table. Важно создавать оптимизированную для памяти таблицу во время развертывания, а не во время выполнения, чтобы избежать дополнительных временных затрат при компиляции, связанных с созданием таблицы.It is important to create the memory-optimized table at deployment time, not at runtime, to avoid the compilation overhead that comes with table creation.
  3. В коде T-SQL замените все упоминания таблицы ##tempGlobalB на dbo.soGlobalB.In your T-SQL, replace all mentions of ##tempGlobalB with dbo.soGlobalB.

В.C. Сценарий: замена таблицы сеансов tempdb #tableScenario: Replace session tempdb #table

Для подготовки к замене временной таблицы сеансов требуется больше кода T-SQL, чем в предыдущем сценарии с глобальной временной таблицей.The preparations for replacing a session temporary table involve more T-SQL than for the earlier global temporary table scenario. К счастью, больший объем кода T-SQL не означает, что для преобразования потребуется больше усилий.Happily the extra T-SQL does not mean any more effort is needed to accomplish the conversion.

Как и в случае с глобальной временной таблицей, самым значительным изменением является создание таблицы во время развертывания, а не выполнения, позволяющее избежать дополнительной нагрузки при компиляции.As with the global temp table scenario, the biggest change is to create the table at deployment time, not runtime, to avoid the compilation overhead.

Предположим, что у вас есть приведенная ниже временная таблица сеансов.Suppose you have the following session temporary table.

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

Сначала создайте приведенную ниже функцию, возвращающую табличное значение, для фильтрации по @@spid.First, create the following table-value function to filter on @@spid. Эту функцию смогут использовать все таблицы SCHEMA_ONLY, преобразованные из временных таблиц сеансов.The function will be usable by all SCHEMA_ONLY tables that you convert from session temporary tables.

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, а также политику безопасности для нее.Second, create the SCHEMA_ONLY table, plus a security policy on the table.

Обратите внимание на то, что каждая оптимизированная для памяти таблица должна содержать как минимум один индекс.Note that each memory-optimized table must have at least one index.

  • Для таблицы dbo.soSessionC, возможно, лучше подойдет хэш-индекс, если вычислить соответствующее значение BUCKET_COUNT.For table dbo.soSessionC a HASH index might be better, if we calculate the appropriate BUCKET_COUNT. Но для простоты в этом примере мы используем некластеризованный индекс.But for this sample we simplify to a NONCLUSTERED index.
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 сделайте следующее:Third, in your general T-SQL code:

  1. Измените все ссылки на временную таблицу в инструкциях Transact-SQL, чтобы они указывали на новую таблицу, оптимизированную для работы в памяти.Change all references to the temp table in your Transact-SQL statements to the new memory-optimized table:
    • Старое имя: #tempSessionCOld: #tempSessionC
    • Новое имя: dbo.soSessionCNew: dbo.soSessionC
  2. Замените инструкции CREATE TABLE #tempSessionC в своем коде на DELETE FROM dbo.soSessionC, чтобы сеанс не обращался к содержимому таблицы, добавленному в предыдущем сеансе с тем же идентификатором session_id.Replace the CREATE TABLE #tempSessionC statements in your code with DELETE FROM dbo.soSessionC, to ensure a session is not exposed to table contents inserted by a previous session with the same session_id. Важно создавать оптимизированную для памяти таблицу во время развертывания, а не во время выполнения, чтобы избежать дополнительных временных затрат при компиляции, связанных с созданием таблицы.It is important to create the memory-optimized table at deployment time, not at runtime, to avoid the compilation overhead that comes with table creation.
  3. Удалите из кода инструкции DROP TABLE #tempSessionC. Если есть опасения относительно размера используемой памяти, вы можете добавить инструкцию DELETE FROM dbo.soSessionC.Remove the DROP TABLE #tempSessionC statements from your code - optionally you can insert a DELETE FROM dbo.soSessionC statement, in case memory size is a potential concern

Г.D. Сценарий: Табличная переменная может задаваться с MEMORY_OPTIMIZED=ONScenario: Table variable can be MEMORY_OPTIMIZED=ON

Традиционная табличная переменная представляет таблицу в базе данных tempdb.A traditional table variable represents a table in the tempdb database. Чтобы значительно повысить производительность, можно оптимизировать табличную переменную для памяти.For much faster performance you can memory-optimize your table variable.

Ниже приведен код T-SQL для традиционной табличной переменной.Here is the T-SQL for a traditional table variable. Ее область действия завершается, когда заканчивается пакет или сеанс.Its scope ends when either the batch or the session ends.

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

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

Предыдущий синтаксис создает так называемую встроенную табличную переменную.The preceding syntax is said to create the table variable inline. Встроенный синтаксис не поддерживает оптимизацию для памяти.The inline syntax does not support memory-optimization. Поэтому давайте преобразуем встроенный синтаксис в явный для TYPE.So let us convert the inline syntax to the explicit syntax for the TYPE.

Область действия. Определение TYPE, созданное первым пакетом, отделенным командой GO, сохраняется даже после завершения работы и перезапуска сервера.Scope: The TYPE definition created by the first go-delimited batch persists even after the server is shutdown and restarted. Но после первого разделителя GO объявленная таблица @tvTableC сохраняется только до тех пор, пока не будет достигнут следующий разделитель GO и пакет не завершится.But after the first go delimiter, the declared table @tvTableC persists only until the next go is reached and the batch ends.

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. Преобразование явной таблицы на диске в оптимизированную для памяти таблицуD.2 Convert explicit on-disk to memory-optimized

Оптимизированная для памяти табличная переменная не хранится в базе данных tempdb.A memory-optimized table variable does not reside in tempdb. Оптимизация для памяти приводит к повышению скорости работы до 10 раз и более.Memory-optimization results in speed increases that are often 10 times faster or more.

Преобразование таблиц в оптимизированные для памяти производится в один шаг.The conversion to memory-optimized is achieved in only one step. Оптимизируйте явное создание TYPE следующим образом. При этом добавляются:Enhance the explicit TYPE creation to be the following, which adds:

  • Индекс.An index. Еще раз напомним, что каждая оптимизированная для памяти таблица должна содержать как минимум один индекс.Again, each memory-optimized table must have at least one index.
  • MEMORY_OPTIMIZED = ON.MEMORY_OPTIMIZED = ON.
CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL   INDEX ix1,  
        Column2  CHAR(10)  
    )  
    WITH  
        (MEMORY_OPTIMIZED = ON);  

Готово.Done.

Д.E. Файловая группа, необходимая для SQL ServerPrerequisite FILEGROUP for SQL Server

Для использования оптимизированных для памяти функций в Microsoft SQL Server база данных должна иметь файловую группу, объявленную с параметром MEMORY_OPTIMIZED_DATA.On Microsoft SQL Server, to use memory-optimized features, your database must have a FILEGROUP that is declared with MEMORY_OPTIMIZED_DATA.

  • База данных SQL Azure не требует создания такой файловой группы.Azure SQL Database does not require creating this FILEGROUP.

Предварительное требование. Приведенный ниже код Transact-SQL для файловой группы необходим для развернутых образцов кода T-SQL в дальнейших подразделах этого раздела.Prerequisite: The following Transact-SQL code for a FILEGROUP is a prerequisite for the long T-SQL code samples in later sections of this article.

  1. Необходимо использовать SSMS.exe или другое средство, позволяющее отправлять код T-SQL.You must use SSMS.exe or another tool that can submit T-SQL.
  2. Вставьте образец кода T-SQL для файловой группы в среду SSMS.Paste the sample FILEGROUP T-SQL code into SSMS.
  3. Отредактируйте код T-SQL, изменив имена и пути к каталогам по своему желанию.Edit the T-SQL to change its specific names and directory paths to your liking.
  • Все каталоги в значении FILENAME уже должны существовать, за исключением последнего каталога, который не должен существовать.All directories in the FILENAME value must preexist, except the final directory must not preexist.
  1. Выполните отредактированный код T-SQL.Run your edited T-SQL.
  • Запускать файловую группу T-SQL несколько раз не нужно, даже если вы повторно настраиваете и перезапускаете скрипт T-SQL для сравнения скорости в следующем подразделе.There is no need to run the FILEGROUP T-SQL more than one time, even if you repeatedly adjust and rerun the speed comparison T-SQL in the next subsection.
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.sqlThe following script creates the filegroup for you and configures recommended database settings: enable-in-memory-oltp.sql

Дополнительные сведения о ALTER DATABASE ... ADD для FILE и FILEGROUP см. в следующих разделах:For more information about ALTER DATABASE ... ADD for FILE and FILEGROUP, see:

Е.F. Небольшой тест для проверки повышения быстродействияQuick test to prove speed improvement

В этом разделе приводится код Transact-SQL, с помощью которого можно протестировать и оценить прирост скорости выполнения операций INSERT-DELETE при использовании табличной переменной, оптимизированной для памяти.This section provides Transact-SQL code that you can run to test and compare the speed gain for INSERT-DELETE from using a memory-optimized table variable. Код состоит из двух половин, которые почти одинаковы за тем исключением, что в первой половине используется таблица оптимизированного для памяти типа.The code is composed of two halves that are nearly the same, except in the first half the table type is memory-optimized.

Сравнительный тест длится примерно 7 секунд.The comparison test lasts about 7 seconds. Запуск примера:To run the sample:

  1. Предварительное требование. Вы уже должны были выполнить код T-SQL для файловой группы из предыдущего подраздела.Prerequisite: You must already have run the FILEGROUP T-SQL from the previous section.
  2. Выполните приведенный ниже скрипт T-SQL INSERT-DELETE.Run the following T-SQL INSERT-DELETE script.
  • Обратите внимание на инструкцию GO 5001, которая повторно отправляет код T-SQL 5001 раз.Notice the 'GO 5001' statement, which resubmits the T-SQL 5001 times. Вы можете изменить это число и перезапустить тест.You can adjust the number and rerun.

В базе данных SQL скрипт следует запускать из виртуальной машины, находящейся в вашем регионе.When running the script in an Azure SQL Database, make sure to run from a VM in the same region.

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  

/*** Actual output, SQL Server 2016:  

---- 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. Прогнозирование потребления активной памятиPredict active memory consumption

Чтобы узнать, как прогнозировать потребность оптимизированных для памяти таблиц в активной памяти, обратитесь к следующим ресурсам:You can learn to predict the active memory needs of your memory-optimized tables with the following resources:

В случае с большими табличными переменными некластеризованные индексы потребляют больше памяти, чем в случае с _таблицами*, оптимизированными для памяти.For larger table variables, nonclustered indexes use more memory than they do for memory-optimized _tables*. Чем больше число строк и ключ индекса, тем сильнее эта разница.The larger the row count and the index key, the more the difference increases.

Если в каждой операции доступа к оптимизированной для памяти табличной переменной используется только одно точное значение ключа, хэш-индекс может быть предпочтительнее некластеризованного индекса.If the memory-optimized table variable is accessed only with one exact key value per access, a hash index might be a better choice than a nonclustered index. Однако, если вы не можете оценить подходящее значение BUCKET_COUNT, можно использовать и некластеризованный индекс.However, if you cannot estimate the appropriate BUCKET_COUNT, a NONCLUSTERED index is a good second choice.

З.H. См. также разделSee also