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 el sistema para tablas optimizadas para memoria proporcionan una solución económica para escenarios en los que se requiere auditoría de datos y análisis en un momento dado sobre los datos recopilados mediante cargas de trabajo de OLTP en memoria.

Información general

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, se conserva el historial de datos durante un periodo largo (varios meses, incluso años) aunque no se consulte con regularidad.

Puede que se exijan auditorías de datos y análisis basados en el tiempo en distintos entornos, especialmente en sistemas OLTP que procesen un número de solicitudes muy elevado y donde se utilice la 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 el sistema para tablas con optimización para memoria proporcionan alto rendimiento transaccional y concurrencia sin bloqueos. Ofrecen la capacidad de almacenar una gran cantidad de datos del historial mediante 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.

El diagrama siguiente muestra esta arquitectura.

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 se puede usar el control de versiones del sistema en tablas duraderas optimizadas para memoria (DURABILITY = SCHEMA_AND_DATA).

• La tabla de historial de una tabla versionada por el sistema optimizada para memoria debe estar basada en disco, tanto si la ha creado el usuario final como si la ha creado el sistema.

• Las consultas que afectan solo a la tabla actual en memoria se pueden usar en módulos T-SQL compilados de forma nativa. No se admiten consultas temporales con la cláusula FOR SYSTEM TIME en módulos compilados de forma nativa. Se admite el uso de la cláusula FOR SYSTEM TIME con tablas optimizadas para memoria en consultas ad hoc y módulos no nativos.

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

• Una tarea de vaciado de datos asincrónica mueve periódicamente los datos de la tabla de almacenamiento provisional interna optimizada para memoria 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. Puede hacer un seguimiento del consumo de memoria total de la tabla temporal con control de versiones del sistema y optimizada para memoria si realiza una consulta a sys.dm_db_xtp_memory_consumers y resume los datos de la tabla de almacenamiento provisional interna optimizada para memoria y la tabla temporal actual.

• Puede ejecutar manualmente un vaciado de datos si ejecuta sp_xtp_flush_temporal_history.

• Con SYSTEM_VERSIONING = OFF, o cuando se modifica el esquema de una tabla versionada por el sistema al agregar, eliminar o modificar columnas, todo el contenido del búfer de almacenamiento provisional interno se mueve a la tabla de historial en disco.

• La consulta de los datos de historial se produce en el nivel de aislamiento de instantánea y siempre devuelve una unión entre el búfer de almacenamiento provisional en memoria y la tabla basada en disco sin duplicados.

• Las operaciones ALTER TABLE que cambian el esquema de tabla internamente deben realizar un vaciado de datos, lo que podría prolongar la duración de 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 se genera en el formato siguiente: 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 adicional 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 optimizada para memoria con control de versiones del sistema se reduce en 8 bytes debido a la columna bigint adicional de la tabla de almacenamiento provisional. Ahora, el nuevo máximo es 8052 bytes.

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

• Los metadatos sobre esta tabla, así como su conexión con la tabla temporal actual, se pueden encontrar en sys.internal_tables.

La tarea de vaciado de datos

El vaciado de datos es una tarea que se activa con regularidad y que comprueba si la tabla optimizada para memoria cumple una condición basada en el tamaño de la memoria para el movimiento de los datos. El movimiento de los datos empieza cuando el consumo de memoria de la tabla de almacenamiento provisional interna alcanza el 8 % del de la tabla temporal actual.

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 subproceso para cada tabla de almacenamiento provisional optimizada para memoria que necesite limpieza.

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

• Tablas temporales
• Introducción a las tablas temporales con control de versiones del sistema
• Escenarios de uso de tablas temporales
• Comprobaciones de coherencia del sistema de la tabla temporal
• Creación de particiones con tablas temporales
• Consideraciones y limitaciones de las tablas temporales
• Seguridad de la tabla temporal
• Administrar la retención de datos históricos en las tablas temporales con versiones del sistema
• Funciones y vistas de metadatos de la tabla temporal