Tabla temporal y variable de tabla más rápidas con optimización para memoria
Se aplica a: SQL Server
Azure SQL Database
Azure SQL Managed Instance
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. 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 conlleva ningún uso o contención de tempdb.
- 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 detalles, vea Indexes for Memory-Optimized Tables (Índices de tablas con optimización 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
- Durability = 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 ...;
, entoncesDECLARE @mytablevariable my_type;
.
- Se debe declarar en dos pasos (en lugar de en línea):
B. Escenario: Reemplazar la tabla tempdb 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. 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 grupos de disponibilidad AlwaysOn 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);
B.1 Pasos
La conversión de temporal global en SCHEMA_ONLY tiene los siguientes pasos:
- Cree la tabla dbo.soGlobalB una vez, como haría con cualquier tabla en disco tradicional.
- En Transact-SQL, quite create de la tabla ##tempGlobalB. 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.
- En T-SQL, reemplace todas las menciones a ##tempGlobalB por dbo.soGlobalB.
C. Escenario: Reemplazar la tabla tempdb 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. Afortunadamente, el T-SQL adicional no significa ningún otro esfuerzo adicional 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 con valores de tabla para filtrar en @@spid. Todas las tablas SCHEMA_ONLY que convierta desde tablas temporales de sesión podrán usar la funció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.
Observe que 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 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:
- 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
- Reemplace las instrucciones
CREATE TABLE #tempSessionC
en el código porDELETE 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. 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. - Quite las instrucciones
DROP TABLE #tempSessionC
del código (opcionalmente, puede insertar una instrucciónDELETE FROM dbo.soSessionC
, por si el tamaño de la memoria es un posible problema).
D. Escenario: Una variable de tabla puede ser MEMORY_OPTIMIZED=ON
Una variable de tabla tradicional representa una tabla de la base de datos tempdb. Para un rendimiento mucho más rápido, puede optimizar la memoria de la variable de tabla.
Este es el T-SQL de 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) );
D.1 Convertir en línea en 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.
Á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. 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 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. Mejore la creación de TYPE explícita para que sea la siguiente, que agrega:
- 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 las características de optimización para memoria, la base de datos debe tener un FILEGROUP declarado con MEMORY_OPTIMIZED_DATA.
- La base de datos SQL de Azure no necesita crear este 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.
- Debe usar SSMS.exe u otra herramienta que pueda enviar T-SQL.
- Pegue el código T-SQL de FILEGROUP de ejemplo en SSMS.
- 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.
- Ejecute el T-SQL editado.
- 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.
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:
- Opciones File y Filegroup de ALTER DATABASE (Transact-SQL)
- El grupo de archivos con optimización 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:
- Requisito previo: ya debe haber ejecutado el T-SQL FILEGROUP de la sección anterior.
- Ejecute el siguiente script de T-SQL INSERT-DELETE.
- Observe la instrucción “GO 5001”, 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
El conjunto de resultados es el siguiente:
---- 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 de las tablas con optimización para memoria: cálculo de ejemplo
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. Pero si no puede calcular el valor BUCKET_COUNT adecuado, un índice NONCLUSTERED es una buena segunda opción.
H. Consulte también
Definición de la durabilidad de los objetos con optimización para memoria
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)
- En Versiones de compilación de SQL Server 2016 se proporciona toda la información de las versiones, Service Pack y actualizaciones acumulativas.
- Estos errores ocasionales incorrectos no aparecen en la edición Enterprise de SQL Server.