Encuesta de áreas iniciales de OLTP en memoria

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Este artículo está destinado a los desarrolladores que necesitan aprender rápidamente los conceptos básicos de las características de rendimiento de OLTP en memoria de Microsoft SQL Server y Base de datos SQL de Azure.

Para OLTP en memoria, este artículo incluye lo siguiente:

  • Breves explicaciones de las características.
  • Ejemplos de código principal que implementan las características.

SQL Server y Base de datos SQL presentan solo pequeñas variaciones en su compatibilidad con tecnologías en memoria.

En su ámbito, algunos blogueros hacen referencia a las características de OLTP en memoria como Hekaton.

Ventajas de las características en memoria

SQL Server proporciona características en memoria que pueden mejorar considerablemente el rendimiento de muchos sistemas de aplicaciones. En esta sección se describen las consideraciones más sencillas.

Características de OLTP (procesamiento de transacciones en línea)

Los sistemas que deben procesar simultáneamente grandes cantidades de instrucciones INSERT de SQL son los candidatos perfectos para las características de OLTP.

  • Nuestras pruebas comparativas muestran que la velocidad se puede multiplicar entre 5 y 20 veces con la adopción de las características en memoria.

Los sistemas que procesan cálculos intensivos en Transact-SQL son candidatos ideales.

  • Un procedimiento almacenado que se dedica a cálculos intensivos se puede ejecutar hasta 99 veces más rápido.

Más tarde tal vez le interese visitar los siguientes artículos, que ofrecen demostraciones de las mejoras de rendimiento de OLTP en memoria:

Características de análisis operativo

El análisis en memoria hace referencia a instrucciones SELECT de SQL que agregan datos transaccionales, normalmente mediante la inclusión de una cláusula GROUP BY. El tipo de índice denominado columnstore es fundamental para el análisis operativo.

Hay dos escenarios principales:

  • Elanálisis operativo por lotes hace referencia a los procesos de agregación que se ejecutan cuando finaliza el horario laboral o bien en hardware secundario que tiene copias de los datos transaccionales.
  • El análisis operativo en tiempo real hace referencia a los procesos de agregación que se ejecutan en horario laboral y en el hardware principal que se usa para cargas de trabajo transaccionales.

El presente artículo se centra en OLTP y no en el análisis. Para obtener información sobre cómo los índices de almacén de columnas llevan el análisis a SQL, vea:

columnstore

Una secuencia de entradas de blog excelentes explica de manera elegante los índices de almacén de columnas desde varias perspectivas. En la mayoría de las publicaciones se describe más el concepto de análisis operacional en tiempo real, que es compatible con el almacén de columnas. Sunil Agarwal, director de programas en Microsoft, creó estas entradas en marzo de 2016.

análisis operativo en tiempo real

  1. Análisis operativos en tiempo real mediante la tecnología In-Memory
  2. Análisis operativos en tiempo real: información general del índice de almacén de columnas no agrupado (NCCI)
  3. Análisis operativos en tiempo real: ejemplo sencillo usando un índice de almacén de columnas no agrupado (NCCI) en SQL Server 2016
  4. Análisis operativos en tiempo real: las operaciones DML y el índice de almacén de columnas no agrupado (NCCI) en SQL Server 2016
  5. Análisis operativos en tiempo real: índice de almacén de columnas no agrupado filtrado (NCCI)
  6. Análisis operativos en tiempo real: opción de retraso de compresión del índice de almacén de columnas no agrupado (NCCI)
  7. Análisis operativos en tiempo real: opción de retraso de compresión con NCCI y el rendimiento
  8. Análisis operativos en tiempo real: tablas con optimización para memoria e índice de almacén de columnas

Desfragmentar un índice de almacén de columnas.

  1. Desfragmentación del índice de almacén de columnas mediante el comando REORGANIZE
  2. Directiva de combinación del índice de almacén de columnas para REORGANIZE

Importación masiva de datos

  1. Almacén de columnas agrupadas: carga masiva
  2. Índice de almacén de columnas agrupado: optimizaciones de carga de datos: registro mínimo
  3. Índice de almacén de columnas agrupado: optimizaciones de carga de datos: importación masiva en paralelo

Característica de OLTP en memoria

Veamos las características principales de OLTP en memoria.

Tablas optimizadas para memoria

La palabra clave MEMORY_OPTIMIZED de T-SQL, en la instrucción CREATE TABLE, indica que se crea una tabla en la memoria activa, en lugar de en disco.

Las tablas con optimización para memoria tienen una representación de sí mismas en la memoria activa y una copia secundaria en el disco.

  • La copia del disco es para recuperación rutinaria después de un cierre y reinicio del servidor o de la base de datos. Esta dualidad de disco más memoria está oculta automáticamente y no se ve en el código.

Módulos compilados de forma nativa

La palabra clave NATIVE_COMPILATION de T-SQL, en la instrucción CREATE PROCEDURE, indica que se crea un procedimiento almacenado compilado de forma nativa. Las instrucciones de T-SQL se compilan en el código máquina la primera vez que se usa el procedimiento nativo cada vez que la base de datos se recorre en línea. Las instrucciones de T-SQL ya no aguantan la interpretación lenta de cada instrucción.

  • Hemos visto el resultado de compilación nativa en duraciones de 1/100 de la duración interpretada.

Un módulo nativo solamente puede hacer referencia a tablas optimizadas para memoria, y no a tablas basadas en disco.

Hay tres tipos de módulos compilados de forma nativa:

Disponibilidad en Base de datos SQL de Azure

OLTP en memoria y Almacén de columnas están disponibles en Azure SQL Database. Para detalles, consulte Optimize Performance using In-Memory Technologies in SQL Database (Optimizar el rendimiento con las tecnologías In-Memory en SQL Database).

1. Garantizar un nivel de compatibilidad >= 130

Esta sección es la primera de una serie de secciones numeradas que muestran la sintaxis de Transact-SQL que puede usar para implementar características de OLTP en memoria.

En primer lugar, es importante que la base de datos se establezca en un nivel de compatibilidad de al menos 130. A continuación figura el código de T-SQL para ver el nivel de compatibilidad actual en el que está establecida la base de datos actual.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

A continuación figura el código de T-SQL para actualizar el nivel, si es necesario.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Elevar a SNAPSHOT

Cuando una transacción implica una tabla basada en disco y una tabla optimizada para memoria, la llamamos transacción entre contenedores. En una transacción de este tipo es esencial que la parte optimizada para memoria de la transacción funcione en el nivel de aislamiento de la transacción llamado SNAPSHOT.

Para exigir de forma confiable este nivel para tablas optimizadas para memoria en una transacción entre contenedores, modifique la configuración de la base de datos ejecutando el siguiente código T-SQL.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Crear un grupo de archivos optimizado

En Microsoft SQL Server, antes de poder crear una tabla optimizada para memoria debe crear un grupo de archivos que declara CONTAINS MEMORY_OPTIMIZED_DATA. El grupo de archivos se asigna a la base de datos. Para obtener más información, consulte:

En Azure SQL Database, no es necesario y no se puede crear tal grupo de archivos.

El siguiente ejemplo de script T-SQL habilita una base de datos para OLTP en memoria y configura todos los ajustes recomendados. Funciona con SQL Server y Azure SQL Database: enable-in-memory-oltp.sql.

Tenga en cuenta que no todas las características de SQL Server son compatibles con las bases de datos que tienen un grupo de archivos MEMORY_OPTIMIZED_DATA. Para más información sobre las limitaciones, vea Características de SQL Server no admitidas para OLTP en memoria.

4. Crear una tabla optimizada para memoria

La palabra clave de Transact-SQL fundamental es la palabra clave MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Las instrucciones INSERT y SELECT de Transact-SQL en una tabla optimizada para memoria son las mismos que para una tabla normal.

ALTER TABLE para tablas con optimización para memoria

ALTER TABLE... ADD/DROP puede agregar o quitar una columna de una tabla optimizada para memoria o un índice.

Planear sus índices y tablas optimizadas para memoria

5. Crear un procedimiento almacenado compilado de forma nativa (procedimiento nativo)

La palabra clave fundamental es NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

La palabra clave SCHEMABINDING significa que las tablas a las que se hace referencia en el procedimiento nativo no se puede quitar a menos que dicho procedimiento se quite primero. Para obtener detalles, vea Crear procedimientos almacenados compilados de forma nativa.

Tenga en cuenta que no es necesario crear un procedimiento almacenado compilado de forma nativa para tener acceso a una tabla optimizada en memoria. También puede hacer referencia a tablas optimizadas en memoria desde procedimientos almacenados tradicionales y desde lotes ad hoc.

6. Ejecutar el procedimiento nativo

Rellene la tabla con dos filas de datos.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Después seguirá una llamada EXECUTE al procedimiento almacenado compilado de forma nativa.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

Esta es la salida de PRINT real:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Guía de la documentación y pasos siguientes

Los ejemplos anteriores sin formato constituyen una base para aprender las características más avanzadas de OLTP en memoria. Las secciones siguientes constituyen una guía a las consideraciones especiales que convendría conocer y dónde puede encontrar los detalles sobre cada una de ellas.

¿Cómo funcionan las características de OLTP en memoria tan rápido?

Las siguientes subsecciones describen brevemente cómo funcionan las características de OLTP en memoria internamente para proporcionar un rendimiento mejorado.

¿Cómo funcionan las tablas optimizadas para memoria más rápido?

Doble naturaleza: Una tabla optimizada para memoria tiene una doble naturaleza: una representación en memoria activa y la otra en el disco duro. Cada transacción se confirma con ambas representaciones de la tabla. Las transacciones funcionan en la representación memoria activa mucho más rápida. Las tablas con optimización para memoria se benefician de la mayor velocidad de la memoria activa en comparación con el disco. Además, la mayor agilidad de la memoria activa hace práctica una estructura de tabla más avanzada que se optimiza para velocidad. La estructura avanzada tampoco tiene páginas, por lo que evita la sobrecarga y la contención de bloqueos temporales y bloqueos por subproceso.

No hay bloqueos: La tabla optimizada para memoria se basa en un enfoque optimista de los objetivos de la competencia de integridad de datos frente a la simultaneidad y el alto rendimiento. Durante la transacción, la tabla no coloca bloqueos en ninguna versión de las filas de datos actualizadas. Esto puede reducir considerablemente la contención en algunos sistemas de gran volumen.

Versiones de fila: En lugar de bloqueos, la tabla optimizada para memoria agrega una nueva versión de una fila actualizada en la propia tabla, no en tempdb. La fila original se mantiene hasta que se confirma la transacción. Durante la transacción, otros procesos pueden leer la versión original de la fila.

  • Cuando se crean varias versiones de una fila para una tabla basada en disco, las versiones de fila se almacenan temporalmente en tempdb.

Menos tareas de registro: Las versiones anterior y posterior de las filas actualizadas se mantienen en la tabla optimizada para memoria. El par de filas proporciona gran parte de la información que tradicionalmente se escribe en el archivo de registro. Esto permite al sistema escribir menos información y con menos frecuencia en el registro. Aún así, la integridad transaccional está garantizada.

¿Cómo funcionan los procedimientos nativos más rápido?

Convertir un procedimiento almacenado interpretado normal en un procedimiento almacenado compilado de forma nativa, reduce considerablemente el número de instrucciones que se ejecutan en tiempo de ejecución.

Ventajas e inconvenientes de las características en memoria

Como es habitual en informática, las mejoras de rendimiento que ofrecen las características en memoria son una solución de compromiso. Las mejores características ofrecen beneficios que son más valiosos que los costos adicionales de la característica. Puede encontrar instrucciones completas sobre los compromisos en:

En el resto de esta sección se enumeran algunas de las consideraciones principales de planeación y compromiso.

Ventajas e inconvenientes de las tablas optimizadas para memoria

Calcular la memoria: Debe calcular la cantidad de memoria activa que consumirá la tabla optimizada para memoria. El equipo debe tener la capacidad de memoria suficiente para hospedar una tabla optimizada para memoria. Para obtener más información, consulte:

Dividir la tabla grande: Una manera de satisfacer la demanda de grandes cantidades de memoria activa es dividir la tabla grande en partes en memoria que almacenen filas de datos recientes calientes frente a otras partes en el disco que almacenen filas heredadas frías (por ejemplo, pedidos de ventas que se han enviado y completado totalmente). Esta división es un proceso manual de diseño e implementación. Vea:

Ventajas e inconvenientes de los procedimientos nativos

  • Un procedimiento almacenado compilado de forma nativa no puede acceder a una tabla basada en disco. Un procedimiento nativo solo puede acceder a tablas optimizadas en memoria.
  • Cuando se ejecuta un procedimiento nativo por primera vez después de que el servidor o base de datos vuelve a estar en línea, el procedimiento nativo se debe compilar de nuevo una vez. Esto provoca un retraso antes de que el procedimiento nativo empieza a ejecutarse.

Consideraciones avanzadas sobre tablas optimizadas para memoria

Losíndices de tablas con optimización para memoria difieren en algunos aspectos de los índices de las tablas en disco tradicionales. Los índices de hash solo están disponibles en las tablas optimizadas para memoria.

Debe tener un plan para asegurarse de que haya suficiente memoria activa para la tabla optimizada para memoria planeada y sus índices. Vea:

Es posible declarar una tabla optimizada para memoria con DURABILITY = SCHEMA_ONLY:

  • Esta sintaxis indica al sistema que descarte todos los datos de la tabla optimizada para memoria cuando la base de datos se desconecta. Solo se conserva la definición de tabla.
  • Cuando la base de datos vuelva a estar en línea, la tabla optimizada para memoria se vuelve a cargar en la memoria activa, vacía de datos.
  • Las tablas SCHEMA_ONLY pueden ser una mejor alternativa a las tablas temporales en tempdb, cuando hay implicados varios miles de filas.

Las variables de tabla también pueden declararse como optimizadas para memoria. Vea:

Consideraciones avanzadas para módulos compilados de forma nativa

Los tipos de módulos compilados de forma nativa disponibles a través de Transact-SQL son:

Una función definida por el usuario (UDF) compilada de forma nativa se ejecuta más rápido que una UDF interpretada. Debe tener en cuenta lo siguiente con respecto a las UDF:

  • Cuando una instrucción SELECT de T-SQL usa una UDF, siempre se llama una vez a la UDF por cada fila devuelta.
    • Las UDF nunca se ejecutan en línea, sino que siempre se llaman.
    • La distinción compilada es menos significativa de lo que es la sobrecarga de llamadas repetidas que es inherente a todas las UDF.
    • A pesar de ello, la sobrecarga de llamadas a UDF suele ser aceptable en la práctica.

Para obtener datos de prueba y una explicación del rendimiento de UDF nativas, vea:

Guía de documentación para tablas optimizadas para memoria

Consulte estos otros artículos que tratan consideraciones especiales para tablas optimizadas para memoria:

Guía de documentación para procedimientos nativos

El siguiente artículo, y los artículos secundarios en la tabla de contenido (TOC), explican los detalles sobre los procedimientos almacenados compilados de forma nativa.

Los artículos siguientes incluyen código para demostrar las mejoras de rendimiento que se pueden lograr con el uso de OLTP en memoria: