Tabla temporal y variable de tabla más rápidas con optimización para memoria

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Si usa tablas temporales, variables de tabla o parámetros con valores de tabla, considere la posibilidad de realizar conversiones de ellas para usar tablas optimizadas para memoria y variables de tabla para mejorar el rendimiento. Los cambios de código normalmente son mínimos.

En este artículo se describe:

• Escenarios que argumentan en favor de la conversión a en memoria.
• Pasos técnicos necesarios para implementar las conversiones a en memoria.
• Requisitos previos antes de la conversión a en memoria.
• Un ejemplo de código que resalta las ventajas de rendimiento de la optimización de memoria.

A Aspectos básicos de las variables de tabla optimizada para memoria

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. La eficacia se maximiza si se accede a la variable de tabla desde un módulo compilado de forma nativa.

Una variable de tabla optimizada para memoria:

• Solo se almacena en memoria y no tiene ningún componente en el disco.
• No implica ninguna actividad de E/S.
• No supone tempdb utilización ni disputa.
• Se puede pasar a un procedimiento almacenado como un parámetro con valores de tabla (TVP).
• Debe tener al menos un índice, ya sea hash o no agrupado.
  • En el caso de un índice de hash, el número de cubos idealmente debería ser entre una y dos veces el número de claves de índice únicas esperadas, aunque la sobrestimación de este número suele funcionar correctamente (hasta diez veces). Para obtener más información, vea Índices de tablas optimizadas para memoria.

Tipos de objeto

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:

• Tablas optimizadas para memoria
  • Durabilidad = SCHEMA_ONLY
• Variables de tabla con optimización para memoria
  • Se debe declarar en dos pasos (en lugar de en línea):
    • CREATE TYPE my_type AS TABLE ...; , entonces
    • DECLARE @mytablevariable my_type;.

B. Escenario: Reemplazar tabla temporal global

Reemplazar una tabla temporal global por una tabla optimizada para memoria SCHEMA_ONLY es bastante sencillo. El cambio más importante es que hay que crear la tabla durante la implementación, y no durante la ejecución. 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. La creación y eliminación de tablas optimizadas para memoria como parte del flujo de trabajo en línea afectarían al rendimiento del flujo de trabajo, así como al rendimiento de las operaciones de recuperación en los secundarios del grupo de disponibilidad Always On y la recuperación de bases de datos.

Imagine que tiene la siguiente tabla temporal global.

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.

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

Pasos

La conversión de temporal global en SCHEMA_ONLY tiene los siguientes pasos:

1. Cree la tabla dbo.soGlobalB una sola vez, tal como lo haría con cualquier tabla tradicional en disco.
2. En el Transact-SQL (T-SQL), quite la creación de la ##tempGlobalB tabla. Es importante crear la tabla optimizada para memoria en tiempo de implementación, no en tiempo de ejecución, para evitar la sobrecarga de compilación que se incluye con la creación de tablas.
3. En T-SQL, reemplace todas las menciones de ##tempGlobalB por dbo.soGlobalB.

C. Escenario: Reemplazar la tabla temporal de sesión

Los preparativos para reemplazar una tabla temporal de sesión implican más T-SQL que para el escenario anterior de tabla temporal global. Felizmente, el T-SQL adicional no significa que se necesite más esfuerzo para realizar la conversión.

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.

Imagine que tiene la siguiente tabla temporal de sesión.

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

En primer lugar, cree la siguiente función table-value para filtrar en @@spid. La función es utilizable por todas las tablas SCHEMA_ONLY que se transforman a partir de tablas temporales de sesión.

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.

Cada tabla optimizada para memoria debe tener al menos un índice.

• Para dbo.soSessionC, podría ser mejor un índice de HASH, si se calcula el valor BUCKET_COUNT adecuado. Pero en este ejemplo se simplifica con un índice NONCLUSTERED.

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

En tercer lugar, en el código T-SQL general:

1. Cambie todas las referencias a la tabla temporal en las instrucciones de (Transact-SQL) a la nueva tabla optimizada para memoria:
   • Antiguo: #tempSessionC
   • Nuevo: dbo.soSessionC
2. Reemplace las CREATE TABLE #tempSessionC instrucciones del código por DELETE FROM dbo.soSessionC, para asegurarse de que una sesión no se expone al contenido de la tabla insertado por una sesión anterior con el mismo session_id. Es importante crear la tabla optimizada para memoria en tiempo de implementación, no en tiempo de ejecución, para evitar la sobrecarga de compilación que se incluye con la creación de tablas.
3. Quite las DROP TABLE #tempSessionC sentencias del código. Opcionalmente, puede insertar una DELETE FROM dbo.soSessionC sentencia en caso de que el tamaño de la memoria sea una posible preocupación.

D. Escenario: Una variable de tabla puede ser MEMORY_OPTIMIZED=ON

Una variable de tabla tradicional representa una tabla de la tempdb base de datos. Para un rendimiento mucho más rápido, puede optimizar la memoria de la variable de tabla.

Este es el T-SQL para una variable de tabla tradicional. Su ámbito finaliza cuando finaliza el lote o la sesión.

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

Convertir en línea a explícito

Se dice que la sintaxis anterior crea la variable de tabla en línea. La sintaxis en línea no admite la optimización de memoria. Por eso se va convertir la sintaxis en línea en sintaxis explícita para TYPE.

Alcance: La definición de tipo creada por el primer lote delimitado por el comando 'go' persiste incluso después de que el servidor sea apagado y reiniciado. 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.

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

D.2 Convertir explícito en disco en optimización para memoria

Una variable de tabla optimizada para memoria no reside en tempdb. La optimización de memoria produce aumentos de velocidad que suelen ser 10 veces más rápidos o más.

La conversión a una tabla optimizada para memoria se consigue en un solo paso. Mejora la creación explícita de TYPE como se indica a continuación, que añade lo siguiente:

• Un índice. De nuevo, cada tabla optimizada para memoria debe tener al menos un índice.
• MEMORY_OPTIMIZED = ON.

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

Listo.

E. FILEGROUP como requisito previo para SQL Server

En Microsoft SQL Server, para usar características optimizadas para memoria, la base de datos debe tener un FILEGROUP declarado con MEMORY_OPTIMIZED_DATA.

• Azure SQL Database no requiere la creación de este GRUPO DE ARCHIVOS.

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.

1. Debe usar SSMS.exe u otra herramienta que pueda enviar T-SQL.
2. Pegue el código T-SQL de FILEGROUP de ejemplo en SSMS.
3. Edite el T-SQL para cambiar sus nombres y rutas de acceso a directorios concretos a su gusto.

• Todos los directorios del valor FILENAME deben existir de antemano, excepto el último.

1. Ejecute el T-SQL editado.

• No es necesario ejecutar la sentencia FILEGROUP T-SQL más de una vez, aunque modifique y ejecute nuevamente la sentencia de comparación de velocidad T-SQL en la siguiente subsección.

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.sql

Para obtener más información sobre ALTER DATABASE ... ADD para FILE y FILEGROUP, vea:

• ALTER DATABASE Opciones de archivo y grupo de archivos (Transact-SQL)
• El grupo de archivos optimizado para memoria

F. Prueba rápida para demostrar la mejora de velocidad

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. 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.

La comparación dura unos 7 segundos. Para ejecutar el ejemplo:

1. Requisito previo: ya debe haber ejecutado el T-SQL FILEGROUP de la sección anterior.
2. Ejecute el siguiente script de T-SQL INSERT-DELETE.

• Observe la GO 5001 instrucción , que vuelve a enviar el T-SQL 5001 veces. Puede ajustar el número y volver a ejecutar.

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.

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

Este es el conjunto de resultados.

---- 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. Predecir el consumo de memoria activa

Puede aprender a predecir las necesidades de memoria activa de las tablas optimizadas para memoria con los siguientes recursos:

• Estimar los requisitos de memoria para las tablas con optimización para memoria
• Tamaño de tabla y fila en tablas optimizadas para memoria

En variables de tabla más grandes, los índices no agrupados usan más memoria que con las tablas optimizadas para memoria. Cuanto mayor sea el recuento de filas y la clave de índice, más aumentará la diferencia.

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. Sin embargo, si no puede calcular el BUCKET_COUNT adecuado, un índice NONCLUSTERED es una buena segunda opción.

Contenido relacionado

• Base de datos de muestra para OLTP en memoria
• Definición de la durabilidad de los objetos de Memory-Optimized
• Cumulative Update to eliminate chance of improper Out Of Memory errors, announced in blog September 2017. (Actualización acumulativa para eliminar la posibilidad de errores de memoria insuficiente incorrectos, anunciada en el blog de septiembre de 2017)
• KB3177312: versiones de compilación de SQL Server 2016