Información general y escenarios de uso de OLTP en memoria

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

OLTP en memoria es la tecnología principal disponible en SQL Server y Azure SQL Database para optimizar el rendimiento de escenarios de datos transitorios, carga de datos, ingesta de datos y procesamiento de transacciones. En este artículo se incluye información general sobre dicha tecnología y se describen escenarios de uso de OLTP en memoria. Use esta información para determinar si OLTP en memoria es adecuado para la aplicación. El artículo concluye con un ejemplo que muestra objetos de OLTP en memoria, hace referencia a una demostración de rendimiento y a recursos que puede usar para los pasos siguientes.

Información general de OLTP en memoria

OLTP en memoria puede proporcionar excelentes ganancias de rendimiento para las cargas de trabajo adecuadas. A pesar de que los clientes han visto un aumento de rendimiento de hasta 30 veces en algunos casos, el que usted obtendrá depende de la carga de trabajo.

¿De dónde proviene esta ganancia de rendimiento? Básicamente, OLTP en memoria mejora el rendimiento del procesamiento de transacciones al hacer que la ejecución de las transacciones y el acceso a los datos sea más eficaz y al quitar la contención de bloqueo y bloqueo temporal entre transacciones que se ejecutan de manera simultánea. OLTP en memoria no es rápido porque se haga en memoria, sino que es rápido porque está optimizado en torno a los datos que están en memoria. Los algoritmos de procesamiento, acceso y almacenamiento de datos se rediseñaron desde el principio para aprovechar las mejoras más recientes en los cálculos de alta simultaneidad y en memoria.

Ahora, solo porque los datos residen en memoria, no significa que los pierda si se produce un error. De manera predeterminada, todas las transacciones son completamente duraderas, lo que significa que tiene las mismas garantías de durabilidad que obtiene para cualquier otra tabla en SQL Server: como parte de la confirmación de transacciones, todos los cambios se escriben en el registro de transacciones en el disco. Si se produce algún error en cualquier momento después de la confirmación de la transacción, los datos se mantienen cuando la base de datos vuelve a estar en línea. Además, OLTP en memoria funciona con todas las funcionalidades de alta disponibilidad y recuperación ante desastres de SQL Server, como grupos de disponibilidad, instancias de clúster de conmutación por error, copia de seguridad y restauración, etc.

Para usar OLTP en memoria en la base de datos, debe usar uno o varios de los siguientes tipos de objetos:

  • Lastablas con optimización para memoria se usan para almacenar datos de usuario. Declara que una tabla es una tabla optimizada para memoria en el momento de su creación.
  • Lastablas no duraderas se usan para los datos transitorios, ya sea para el almacenamiento en caché o para un conjunto de resultados intermedio (que reemplaza las tablas temporales tradicionales). Una tabla no duradera es una tabla optimizada para memoria que se declara con DURABILITY=SCHEMA_ONLY, lo que significa que los cambios en estas tablas no generan ningún E/S. Esto evita que consuman recursos de E/S de registro en los casos donde la durabilidad no sea una preocupación.
  • Los tipos de tablas con optimización para memoria se usan para los parámetros con valores de tabla (TVP), así como conjuntos de resultados intermedios en procedimientos almacenados. Los tipos de tabla optimizados para memoria se pueden usar en lugar de los tipos de tabla tradicionales. Las variables de tabla y los TVP que se declaran con un tipo de tabla optimizada para memoria heredan las ventajas de las tablas no duraderas optimizadas para memoria: acceso eficaz a los datos y no E/S.
  • Losmódulos T-SQL compilados de manera nativa se usan para reducir aún más el tiempo que demora una transacción individual mediante la disminución de los ciclos de CPU que se requieren para procesar las operaciones. Declara un módulo Transact-SQL como compilado de manera nativa en el momento de su creación. En este momento, se pueden compilar de manera nativa los siguientes módulos T-SQL: procedimientos almacenados, desencadenadores y funciones escalares definidas por el usuario.

OLTP en memoria está integrado en SQL Server y SQL Database. Dado que estos objetos tienen un comportamiento similar al de sus homólogos tradicionales, a menudo puedes obtener ventajas de rendimiento aplicando solo cambios mínimos en la base de datos y la aplicación. Además, puede tener tablas optimizadas para memoria y tablas basadas en discos tradicionales en la misma base de datos, y ejecutar consultas entre ambas. Consulte el script de Transact-SQL de ejemplo para cada uno de estos tipos de objetos más adelante en este artículo.

Escenarios de uso de OLTP en memoria

OLTP en memoria no es un botón rápido mágico y no funciona en todas las cargas de trabajo. Por ejemplo, las tablas optimizadas para memoria no disminuyen realmente el uso de la CPU si la mayoría de las consultas ejecutan la agregación en grandes intervalos de datos. Son los índices de almacén de columnas los que ayudan en ese escenario.

Precaución

Problema conocido: en el caso de las bases de datos con tablas optimizadas para memoria, realizar una copia de seguridad del registro transaccional sin recuperación y, posteriormente, ejecutar una restauración del registro de transacciones con recuperación, puede dar lugar a un proceso de restauración de base de datos que no responde. Este problema también puede afectar a la funcionalidad de trasvase de registros. Para solucionar este problema, se puede reiniciar la instancia de SQL Server antes de iniciar el proceso de restauración.

A continuación, puede ver una lista de escenarios y patrones de aplicaciones en los que se han visto buenos resultados con OLTP en memoria.

Procesamiento de transacciones de baja latencia y alto rendimiento

Este es el escenario central para el que compilamos OLTP en memoria: se admiten grandes volúmenes de transacciones con una baja latencia coherente para transacciones individuales.

Los escenarios de carga de trabajo comunes son: comercialización de instrumentos financieros, apuestas deportivas, juegos móviles y publicación de anuncios. Otro patrón común es un "catálogo" que se lee o actualiza con frecuencia. Un ejemplo es cuando tiene archivos de gran tamaño, cada uno de ellos distribuido sobre varios nodos de clúster, y se cataloga la ubicación de la partición de cada archivo en una tabla optimizada para memoria.

Consideraciones sobre la implementación

Use las tablas optimizadas para memoria para sus tablas de transacciones principales, es decir, las tablas con las transacciones con rendimiento más crítico. Use los procedimientos almacenados compilados de manera nativa para optimizar la ejecución de la lógica asociada con la transacción comercial. Cuanta más lógica pueda insertar en los procedimientos almacenados de la base de datos, más ventajas obtendrá gracias a OLTP en memoria.

Para empezar a trabajar en una aplicación existente:

  1. Use el informe de análisis de rendimiento de transacciones para identificar los objetos que desea migrar.
  2. Usa el asesor de optimización en memoria y el asistente de compilación nativa para obtener ayuda en la migración.

Ingesta de datos, incluida IoT (Internet de las cosas)

OLTP en memoria es excelente en la ingesta de grandes volúmenes de datos desde distintos orígenes al mismo tiempo. Además, la ingesta de datos en una base de datos de SQL Server suele ser beneficiosa en comparación con otros destinos, porque SQL Server permite ejecutar las consultas de datos rápido y, además, le permite obtener información en tiempo real.

Patrones comunes de aplicación:

  • Ingerir eventos y lecturas de sensores y permitir las notificaciones, además del análisis del historial.
  • La administración de actualizaciones por lotes, incluso desde varios orígenes, mientras se minimiza el impacto en la carga de trabajo de lectura simultánea.

Consideraciones sobre la implementación

Use una tabla optimizada para memoria para la ingesta de datos. Si la ingesta consta principalmente de inserciones (en lugar de actualizaciones) y el espacio que ocupa el almacenamiento de OLTP en memoria de los datos es una preocupación, puede hacer una de las acciones siguientes:

  • Use un trabajo para descargar por lote de manera regular los datos en una tabla basada en discos con un índice de almacén de columnas en clúster, a través de un trabajo que hace INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; o bien,
  • Use una tabla temporal optimizada para memoria para administrar los datos históricos; en este modo, los datos históricos residen en el disco y es el sistema el que administra el movimiento de los datos.

El repositorio de ejemplos de SQL Server contiene una aplicación de cuadrícula inteligente que usa una tabla temporal optimizada para memoria, un tipo de tabla optimizada para memoria y un procedimiento almacenado compilado de manera nativa para acelerar la ingesta de datos, mientras que se administra el espacio de almacenamiento de OLTP en memoria de los datos del sensor:

Almacenamiento en caché y estado de sesión

La tecnología de OLTP en memoria hace que el motor de base de datos en SQL Server o bases de datos de Azure SQL sea una plataforma atractiva para mantener el estado de sesión (por ejemplo, para una aplicación ASP.NET) y para el almacenamiento en caché.

El estado de sesión de ASP.NET es un caso de uso de éxito de OLTP en memoria. Con SQL Server, un cliente estuvo a punto de lograr 1,2 millones de solicitudes por segundo. Mientras tanto, comenzó a usar OLTP en memoria para cumplir con las necesidades de almacenamiento en caché de todas las aplicaciones de nivel intermedio de la empresa. Información: Cómo bwin usa OLTP en memoria de SQL Server 2016 (13.x) para obtener un rendimiento y escala sin precedentes

Consideraciones sobre la implementación

Puede usar tablas no duraderas optimizadas para memoria como un almacén de clave-valor simple mediante el almacenamiento de un BLOB en una columna varbinary(max). De manera alternativa, puede implementar una memoria caché semiestructurada con compatibilidad de JSON en SQL Server y SQL Database. Por último, puede crear una caché completamente relacional a través de tablas no duraderas con un esquema completamente relacional, incluidas diversas restricciones y tipos de datos.

Comience con un estado de sesión de ASP.NET optimizado para memoria usando los scripts publicados en GitHub para reemplazar los objetos creados por el proveedor de estado de sesión de SQL Server integrado: aspnet-session-state.

Caso práctico del cliente

reemplazo de objeto tempdb

Use las tablas no duraderas y los tipos de tabla optimizada para memoria para reemplazar las estructuras tradicionales basadas en tempdb, como las tablas temporales, las variables de tabla y los parámetros con valores de tabla (TVP).

Las tablas no duraderas y las variables de tabla con optimización para memoria normalmente reducen la CPU y quitan completamente E/S del registro en comparación con la tabla #temp y las variables de tabla tradicionales.

Consideraciones sobre la implementación

Para comenzar: Mejora del rendimiento de la tabla temporal y de variable de tabla mediante optimización de memoria.

Caso práctico del cliente

ETL (extracción, transformación, carga)

A menudo, los flujos de trabajo de ETL incluyen cargar datos en una tabla provisional, transformaciones de los datos y su carga en las tablas finales.

Use tablas no duraderas optimizadas para memoria para el almacenamiento provisional de datos. Estas tablas quitan completamente E/S y hacen que el acceso a los datos sea más eficaz.

Consideraciones sobre la implementación

Si hace transformaciones en la tabla de almacenamiento provisional como parte del flujo de trabajo, puede usar procedimientos almacenados compilados de manera nativa para acelerar estas transformaciones. Si puede hacer estas transformaciones en paralelo, obtendrá ventajas adicionales de escalabilidad a partir de la optimización de memoria.

Script de ejemplo

Antes de que pueda comenzar a usar OLTP en memoria, debe crear un grupo de archivos MEMORY_OPTIMIZED_DATA. Además, se recomienda usar el nivel 130 (o superior) de compatibilidad de base de datos y establecer en ON la opción MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT de base de datos.

Puede usar el script que se encuentra en la siguiente ubicación para crear el grupo de archivos en la carpeta de datos predeterminada y configurar los valores recomendados:

El script de ejemplo siguiente ilustra los objetos de OLTP en memoria que puede crear en la base de datos.

En primer lugar, configure la base de datos para OLTP en memoria.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Puede crear tablas con durabilidad diferente:

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

Puede crear un tipo de tabla como una tabla en memoria.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

Para crear un procedimiento almacenado compilado de forma nativa. Para obtener más información, consulte Llamar a procedimientos almacenados compilados de forma nativa desde aplicaciones de acceso a datos.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

Contenido relacionado