Tabla temporal y variable de tabla más rápidas con optimización para memoriaFaster temp table and table variable by using memory optimization

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Si usa tablas temporales, variables de tabla o parámetros con valores de tabla, tenga en cuenta sus conversiones para aprovechar las tablas optimizadas para memoria y las variables de tabla para mejorar el rendimiento.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. Los cambios de código normalmente son mínimos.The code changes are usually minimal.

En este artículo se explica:This article describes:

  • Escenarios que argumentan en favor de la conversión a en memoria.Scenarios which argue in favor of conversion to In-Memory.
  • Pasos técnicos necesarios para implementar las conversiones a en memoria.Technical steps for implementing the conversions to In-Memory.
  • Requisitos previos antes de la conversión a en memoria.Prerequisites before conversion to In-Memory.
  • Un ejemplo de código que resalta las ventajas de rendimiento de la optimización de memoria.A code sample that highlights the performance benefits of memory-optimization

A.A. Aspectos básicos de las variables de tabla optimizada para memoriaBasics of memory-optimized table variables

Una variable de tabla optimizada para memoria ofrece una gran eficacia al usar la mismas estructuras de algoritmos y datos optimizados para memoria que emplean las tablas optimizadas para memoria.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. La eficacia se maximiza si se accede a la variable de tabla desde un módulo compilado de forma nativa.The efficiency is maximized when the table variable is accessed from within a natively compiled module.

Una variable de tabla optimizada para memoria:A memory-optimized table variable:

  • Solo se almacena en memoria y no tiene ningún componente en el disco.Is stored only in memory, and has no component on disk.
  • No implica ninguna actividad de E/S.Involves no IO activity.
  • No conlleva ningún uso o contención de tempdb.Involves no tempdb utilization or contention.
  • Se puede pasar a un procedimiento almacenado como un parámetro con valores de tabla (TVP).Can be passed into a stored proc as a table-valued parameter (TVP).
  • Debe tener al menos un índice, ya sea hash o no agrupado.Must have at least one index, either hash or nonclustered.

Tipos de objetosObject types

OLTP en memoria proporciona los siguientes objetos que se pueden usar para la optimización de memoria de las tablas temporales y las variables de tabla:In-Memory OLTP provides the following objects that can be used for memory-optimizing temp tables and table variables:

  • Tablas con optimización para memoriaMemory-optimized tables
    • Durability = SCHEMA_ONLYDurability = SCHEMA_ONLY
  • Variables de tabla con optimización para memoriaMemory-optimized table variables
    • Se debe declarar en dos pasos (en lugar de en línea):Must be declared in two steps (rather than inline):
      • CREATE TYPE my_type AS TABLE ...; , entoncesCREATE TYPE my_type AS TABLE ...; , then
      • DECLARE @mytablevariable my_type;.DECLARE @mytablevariable my_type;.

B.B. Escenario: Reemplazar la tabla ##temporal globalScenario: Replace global tempdb ##table

Reemplazar una tabla temporal global por una tabla optimizada para memoria SCHEMA_ONLY es bastante sencillo.Replacing a global temporary table with a memory-optimized SCHEMA_ONLY table is fairly straightforward. El cambio más importante es que hay que crear la tabla durante la implementación, y no durante la ejecución.The biggest change is to create the table at deployment time, not at runtime. La creación de tablas optimizadas para memoria requiere más tiempo que la de las tablas convencionales debido a las optimizaciones del tiempo de compilación.Creation of memory-optimized tables takes longer than creation of traditional tables, due to the compile-time optimizations. Si creáramos y colocáramos tablas optimizadas para memoria como parte de una carga de trabajo en línea, el impacto sobre el rendimiento de la carga de trabajo sería significativo, como también lo sería en el caso del rendimiento de la puesta al día de bases de datos secundarias de AlwaysOn y la recuperación de bases de datos.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.

Imagine que tiene la siguiente tabla temporal global.Suppose you have the following global temporary table.

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

Considere reemplazar la tabla temporal global por la siguiente tabla optimizada para memoria que tiene 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);  

B.1 PasosB.1 Steps

La conversión de temporal global en SCHEMA_ONLY tiene los siguientes pasos:The conversion from global temporary to SCHEMA_ONLY is the following steps:

  1. Cree la tabla dbo.soGlobalB una vez, como haría con cualquier tabla en disco tradicional.Create the dbo.soGlobalB table, one time, just as you would any traditional on-disk table.
  2. En Transact-SQL, quite la instrucción CREATE de la tabla ##tempGlobalB.From your Transact-SQL, remove the create of the ##tempGlobalB table. Es importante crear la tabla optimizada para memoria durante la implementación, y no durante la ejecución, para evitar la sobrecarga de la compilación que implica la creación de tablas.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. En T-SQL, reemplace todas las menciones a ##tempGlobalB por dbo.soGlobalB.In your T-SQL, replace all mentions of ##tempGlobalB with dbo.soGlobalB.

C.C. Escenario: Reemplazar la tabla # temporal de sesiónScenario: Replace session tempdb #table

Los preparativos para reemplazar una tabla temporal de sesión implican más T-SQL que para el escenario anterior de tabla temporal global.The preparations for replacing a session temporary table involve more T-SQL than for the earlier global temporary table scenario. Afortunadamente, el T-SQL adicional no significa ningún otro esfuerzo adicional para realizar la conversión.Happily the extra T-SQL does not mean any more effort is needed to accomplish the conversion.

Como en el caso de la tabla temporal global, el cambio más importante es que hay que crear la tabla durante la implementación, y no durante la ejecución, para evitar la sobrecarga de la compilación.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.

Imagine que tiene la siguiente tabla temporal de sesión.Suppose you have the following session temporary table.

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

En primer lugar, cree la siguiente función con valores de tabla para filtrar en **@@spid** .First, create the following table-value function to filter on **@@spid**. Todas las tablas SCHEMA_ONLY que convierta desde tablas temporales de sesión podrán usar la función.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;  

En segundo lugar, cree la tabla SCHEMA_ONLY, además de una directiva de seguridad en la tabla.Second, create the SCHEMA_ONLY table, plus a security policy on the table.

Observe que cada tabla optimizada para memoria debe tener al menos un índice.Note that each memory-optimized table must have at least one index.

  • Para dbo.soSessionC, podría ser mejor un índice de HASH, si se calcula el valor BUCKET_COUNT adecuado.For table dbo.soSessionC a HASH index might be better, if we calculate the appropriate BUCKET_COUNT. Pero en este ejemplo se simplifica con un índice NONCLUSTERED.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  

En tercer lugar, en el código T-SQL general:Third, in your general T-SQL code:

  1. Cambie todas las referencias a la tabla temporal en las instrucciones de Transact-SQL a la nueva tabla optimizada para memoria:Change all references to the temp table in your Transact-SQL statements to the new memory-optimized table:
    • Antiguo: #tempSessionCOld: #tempSessionC
    • Nuevo: dbo.soSessionCNew: dbo.soSessionC
  2. Reemplace las instrucciones CREATE TABLE #tempSessionC en el código por DELETE FROM dbo.soSessionC para garantizar que la sesión no se vea expuesta al contenido de la tabla insertada mediante una sesión anterior con el mismo identificador de sesión.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. Es importante crear la tabla optimizada para memoria durante la implementación, y no durante la ejecución, para evitar la sobrecarga de la compilación que implica la creación de tablas.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. Quite las instrucciones DROP TABLE #tempSessionC del código (opcionalmente, puede insertar una instrucción DELETE FROM dbo.soSessionC, por si el tamaño de la memoria es un posible problema).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.D. Escenario: La variable de tabla puede ser MEMORY_OPTIMIZED=ONScenario: Table variable can be MEMORY_OPTIMIZED=ON

Una variable de tabla tradicional representa una tabla de la base de datos tempdb.A traditional table variable represents a table in the tempdb database. Para un rendimiento mucho más rápido, puede optimizar la memoria de la variable de tabla.For much faster performance you can memory-optimize your table variable.

Este es el T-SQL de una variable de tabla tradicional.Here is the T-SQL for a traditional table variable. Su ámbito finaliza cuando finaliza el lote o la sesión.Its scope ends when either the batch or the session ends.

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

D.1 Convertir en línea en explícitoD.1 Convert inline to explicit

Se dice que la sintaxis anterior crea la variable de tabla en línea.The preceding syntax is said to create the table variable inline. La sintaxis en línea no admite la optimización de memoria.The inline syntax does not support memory-optimization. Por eso se va convertir la sintaxis en línea en sintaxis explícita para TYPE.So let us convert the inline syntax to the explicit syntax for the TYPE.

Ámbito: la definición de TYPE creada por el primer lote delimitado por la instrucción GO persiste incluso después de que el servidor se apague y se reinicie.Scope: The TYPE definition created by the first go-delimited batch persists even after the server is shutdown and restarted. Pero después del primer delimitador GO, la tabla declarada @tvTableC persiste hasta que se alcanza la siguiente instrucción GO y el lote finaliza.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  

D.2 Convertir explícito en disco en optimización para memoriaD.2 Convert explicit on-disk to memory-optimized

Una variable de tabla optimizada para memoria no reside en tempdb.A memory-optimized table variable does not reside in tempdb. La optimización de memoria produce aumentos de velocidad que suelen ser 10 veces más rápidos o más.Memory-optimization results in speed increases that are often 10 times faster or more.

La conversión a una tabla optimizada para memoria se consigue en un solo paso.The conversion to memory-optimized is achieved in only one step. Mejore la creación de TYPE explícita para que sea la siguiente, que agrega:Enhance the explicit TYPE creation to be the following, which adds:

  • Un índice.An index. De nuevo, cada tabla optimizada para memoria debe tener al menos un índice.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);  

Listo.Done.

E.E. FILEGROUP como requisito previo para SQL ServerPrerequisite FILEGROUP for SQL Server

En Microsoft SQL Server, para usar las características de optimización para memoria, la base de datos debe tener un FILEGROUP declarado con 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.

  • La base de datos SQL de Azure no necesita crear este FILEGROUP.Azure SQL Database does not require creating this FILEGROUP.

Requisito previo: el siguiente código Transact-SQL para un FILEGROUP es un requisito previo para los ejemplos de código largos de T-SQL en secciones posteriores de este artículo.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. Debe usar SSMS.exe u otra herramienta que pueda enviar T-SQL.You must use SSMS.exe or another tool that can submit T-SQL.
  2. Pegue el código T-SQL de FILEGROUP de ejemplo en SSMS.Paste the sample FILEGROUP T-SQL code into SSMS.
  3. Edite el T-SQL para cambiar sus nombres y rutas de acceso a directorios concretos a su gusto.Edit the T-SQL to change its specific names and directory paths to your liking.
  • Todos los directorios del valor FILENAME deben existir de antemano, excepto el último.All directories in the FILENAME value must preexist, except the final directory must not preexist.
  1. Ejecute el T-SQL editado.Run your edited T-SQL.
  • No hay necesidad de ejecutar el T-SQL de FILEGROUP más de una vez, aunque ajuste repetidamente y vuelva a ejecutar el T-SQL de comparación de velocidad de la siguiente subsección.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  

El script siguiente crea el grupo de archivos y configura los valores recomendados de la base de datos: enable-in-memory-oltp.sqlThe following script creates the filegroup for you and configures recommended database settings: enable-in-memory-oltp.sql

Para obtener más información sobre ALTER DATABASE ... ADD para FILE y FILEGROUP, vea:For more information about ALTER DATABASE ... ADD for FILE and FILEGROUP, see:

F.F. Prueba rápida para demostrar la mejora de velocidadQuick test to prove speed improvement

En esta sección se proporciona código Transact-SQL que se puede ejecutar para probar y comparar el aumento de velocidad de INSERT-DELETE con el uso de una variable de tabla optimizada para memoria.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. El código se compone de dos mitades que son casi iguales, salvo que en la primera mitad el tipo de la tabla sea optimizada para memoria.The code is composed of two halves that are nearly the same, except in the first half the table type is memory-optimized.

La comparación dura unos 7 segundos.The comparison test lasts about 7 seconds. Para ejecutar el ejemplo:To run the sample:

  1. Requisito previo: ya debe haber ejecutado el T-SQL FILEGROUP de la sección anterior.Prerequisite: You must already have run the FILEGROUP T-SQL from the previous section.
  2. Ejecute el siguiente script de T-SQL INSERT-DELETE.Run the following T-SQL INSERT-DELETE script.
  • Observe la instrucción “GO 5001”, que vuelve a enviar el T-SQL 5001 veces.Notice the 'GO 5001' statement, which resubmits the T-SQL 5001 times. Puede ajustar el número y volver a ejecutar.You can adjust the number and rerun.

Al ejecutar el script en una base de datos SQL de Azure, asegúrese de ejecutar desde una máquina virtual en la misma región.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.G. Predecir el consumo de memoria activaPredict active memory consumption

Puede aprender a predecir las necesidades de memoria activa de las tablas optimizadas para memoria con los siguientes recursos:You can learn to predict the active memory needs of your memory-optimized tables with the following resources:

En variables de tabla más grandes, los índices no agrupados usan más memoria que con las tablas optimizadas para memoria.For larger table variables, nonclustered indexes use more memory than they do for memory-optimized tables. Cuanto mayor sea el recuento de filas y la clave de índice, más aumentará la diferencia.The larger the row count and the index key, the more the difference increases.

Si se accede a la variable de tabla optimizada para memoria solo con un valor de clave exacto por acceso, un índice de hash puede ser una opción mejor que un índice no agrupado.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. Pero si no puede calcular el valor BUCKET_COUNT adecuado, un índice NONCLUSTERED es una buena segunda opción.However, if you cannot estimate the appropriate BUCKET_COUNT, a NONCLUSTERED index is a good second choice.

H.H. Vea tambiénSee also