Tablas temporales versionadas por el sistema y tablas optimizadas para memoria

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores de Azure SQL Managed Instance

Las tablas temporales versionadas por sistema para tablas optimizadas en memoria proporcionan una solución rentable para escenarios donde se requiere auditoría de datos y análisis puntual sobre los datos recogidos con cargas de trabajo OLTP en memoria.

Note

Las tablas temporales optimizadas para memoria solo están disponibles en SQL Server y Azure SQL Managed Instance. Las tablas y tablas temporales optimizadas para memoria están disponibles de forma independiente en Azure SQL Database.

Overview

Las tablas temporales versionadas por el sistema mantienen automáticamente un historial completo de los cambios en los datos y ofrecen útiles extensiones de Transact-SQL para el análisis de un momento dado. En un escenario típico, el historial de datos se conserva durante mucho tiempo (varios meses, incluso años), aunque no se consulte regularmente.

La auditoría de datos y el análisis basado en tiempo pueden exigirse en diferentes entornos, especialmente en sistemas OLTP que procesan un número extremadamente grande de solicitudes y donde se utiliza tecnología OLTP en memoria. Pero el uso de tablas optimizadas para memoria en escenarios temporales resulta difícil porque una enorme cantidad de datos históricos generados suele superar el límite de memoria RAM disponible. Al mismo tiempo, la utilización de RAM para almacenar datos históricos de solo lectura a los que se accede como menos frecuencia a medida que son más antiguos no constituye una solución óptima.

Las tablas temporales versionadas por sistema para tablas optimizadas en memoria proporcionan un alto rendimiento transaccional y concurrencia libre de bloqueos. Puedes almacenar una gran cantidad de datos históricos usando tablas en memoria para almacenar datos actuales (la tabla temporal) y tablas basadas en disco para datos históricos. El efecto en las operaciones DML se reduce mediante el uso de una tabla de almacenamiento provisional interna optimizada para memoria y generada automáticamente que almacena el historial reciente y permite que los DML se ejecuten desde código compilado de manera nativa.

En el diagrama siguiente se muestra esta arquitectura.

Diagrama de la arquitectura temporal en memoria.

Detalles de la implementación

Al crear una tabla optimizada para memoria con control de versiones del sistema, tenga en cuenta las consideraciones siguientes. Para obtener opciones de sintaxis y para obtener un ejemplo, vea CREATE TABLE.

  • Solo las tablas duraderas optimizadas para memoria pueden tener control de versiones del sistema (DURABILITY = SCHEMA_AND_DATA).

  • La tabla de historial de una tabla optimizada para la memoria y versión del sistema debe ser basada en disco, tanto si la creas como si la crea el sistema.

  • Puedes usar consultas que afecten solo a la tabla actual en memoria en módulos T-SQL compilados nativamente. Los módulos compilados nativamente no soportan la FOR SYSTEM TIME cláusula, pero las consultas ad hoc y los módulos no nativos pueden usar la cláusula contra tablas optimizadas para memoria.

  • Con SYSTEM_VERSIONING = ON, el sistema crea automáticamente una tabla interna de almacenamiento provisional optimizada para la memoria para aceptar los cambios versionados por el sistema más recientes, que son el resultado de operaciones de actualización y eliminación en una tabla actual optimizada para la memoria.

  • Una tarea de vaciado de datos asíncrona mueve regularmente los datos de la tabla de staging optimizada para memoria interna a la tabla de historial basada en disco. Este mecanismo de vaciado de datos mantiene los búferes de memoria interna a menos del 10 % del consumo de memoria de sus objetos primarios. Puedes rastrear el consumo total de memoria de una tabla temporal optimizada para la memoria y versionada para el sistema consultando sys.dm_db_xtp_memory_consumers y resumiendo los datos para la tabla de etapas optimizada para memoria interna y la tabla temporal actual.

  • Para realizar manualmente un vaciado de datos, ejecuta sp_xtp_flush_temporal_history.

  • Con SYSTEM_VERSIONING = OFF, o cuando se modifica el esquema de una tabla con versiones administradas por el sistema agregando, eliminando o modificando columnas, todo el contenido del búfer de ensayo interno se traslada a la tabla de historial almacenada en disco.

  • La consulta de los datos históricos se realiza efectivamente con el nivel de aislamiento de instantánea y siempre devuelve una unión entre el búfer de ensayo en memoria y la tabla en disco, sin entradas duplicadas.

  • ALTER TABLE Las operaciones que cambian el esquema de la tabla internamente deben realizar un vaciado de datos, lo que podría prolongar la operación.

Tabla interna de ensayo optimizada para memoria

El sistema crea una tabla de ensayo interna optimizada para la memoria para optimizar las operaciones DML.

  • El nombre de la tabla utiliza el siguiente formato: Memory_Optimized_History_Table_<object_id> donde <object_id> es el identificador de la tabla temporal actual.

  • La tabla replica el esquema de la tabla temporal actual más una columna bigint . Esta columna extra garantiza la unicidad de las filas movidas al búfer de historial interno.

  • La columna adicional tiene el siguiente formato de nombre: Change_ID[<suffix>], donde <suffix> se agrega de manera opcional en el caso de que la tabla ya cuente con una columna Change_ID.

  • El tamaño máximo de fila de una tabla temporal versionada por el sistema y optimizada para la memoria se reduce en 8 bytes debido a la columna bigint adicional de la tabla de ensayo. El máximo es ahora de 8.052 bytes.

  • La tabla de ensayo interna optimizada para memoria no aparece en el Explorador de objetos de SQL Server Management Studio.

  • Puedes encontrar metadatos sobre esta tabla y su conexión con la tabla temporal actual en sys.internal_tables.

La tarea de vaciado de datos

La tarea de vaciado de datos se ejecuta regularmente, y eso verifica si alguna tabla optimizada para memoria cumple una condición basada en el tamaño de memoria para el movimiento de datos. El movimiento de datos comienza cuando el consumo de memoria de la tabla de almacenamiento provisional interna alcanza el ocho por ciento del consumo de memoria de la tabla temporal en uso.

La tarea de vaciado de datos se activa periódicamente con una programación que varía según la carga de trabajo existente. Con una carga de trabajo intensiva, la tarea se ejecuta con una frecuencia máxima de hasta 5 segundos. Con una carga de trabajo ligera, la frecuencia aumenta a cada minuto. Se genera un hilo para cada tabla interna de ensayo optimizada para memoria que deba limpiarse.

El vaciado de datos elimina todos los registros del búfer interno en memoria que son posteriores a la transacción más antigua en ejecución en ese momento para mover esos registros a la tabla de historial basada en disco.

Puede ejecutar un vaciado de datos, si ejecuta sp_xtp_flush_temporal_history y especifica el esquema y el nombre de la tabla:

EXEC sys.sp_xtp_flush_temporal_history <schema_name>, <object_name>;

Se invoca el mismo proceso de movimiento de datos que cuando el sistema invoca la tarea de vaciado de datos según su programación interna.

Contenido relacionado

  • Last updated on