Tabelle temporali con versione gestita dal sistema e tabelle ottimizzate per la memoria

Si applica a: SQL Server 2016 (13.x) e versioni successive di Istanza gestita di SQL di Azure

Le tabelle temporali versionate di sistema per le tabelle ottimizzate in memoria offrono una soluzione conveniente per scenari in cui sono necessari audit dei dati e analisi puntuale oltre ai dati raccolti con carichi di lavoro OLTP in memoria.

Note

Le tabelle temporali ottimizzate per la memoria sono disponibili solo in SQL Server e in Istanza gestita di SQL di Azure. Le tabelle ottimizzate per la memoria e le tabelle temporali sono disponibili in modo indipendente nel database SQL di Azure.

Overview

Le tabelle temporali con controllo delle versioni di sistema mantengono in automatico una cronologia completa delle modifiche ai dati ed espongono comode estensioni di Transact-SQL per l'analisi temporizzata. In uno scenario tipico, la cronologia dei dati viene conservata per molto tempo (diversi mesi, persino anni), anche se non viene interrogata regolarmente.

L'audit dei dati e l'analisi basata sul tempo possono essere richiesti in diversi ambienti, specialmente nei sistemi OLTP che elaborano un numero estremamente elevato di richieste e dove viene utilizzata la tecnologia OLTP in memoria. Tuttavia, l'uso di tabelle ottimizzate per la memoria negli scenari temporali è difficile perché generalmente una quantità enorme di dati cronologici supera il limite della RAM disponibile. Allo stesso tempo non è consigliabile usare la RAM per archiviare dati cronologici di sola lettura a cui si accede con minor frequenza man mano che diventano obsoleti.

Le tabelle temporali con controllo delle versioni gestito dal sistema per le tabelle ottimizzate per la memoria offrono un throughput transazionale elevato e una concorrenza senza blocchi. Puoi memorizzare una grande quantità di dati storici utilizzando tabelle in memoria per memorizzare i dati attuali (la tabella temporale) e tabelle su disco per i dati storici. L'effetto sulle operazioni DML è ridotto grazie all'uso di una tabella di staging ottimizzata per la memoria, interna e generata automaticamente che archivia la cronologia recente e abilita i DML per l'esecuzione da codice compilato in nativo.

Il diagramma seguente illustra questa architettura.

Diagramma dell'architettura temporale in-memory.

Dettagli sull'implementazione

Quando si crea una tabella ottimizzata per la memoria con controllo delle versioni di sistema, occorre tenere presenti le considerazioni seguenti. Per le opzioni di sintassi e per un esempio, vedere CREATE TABLE.

  • Solo le tabelle durevoli ottimizzate per la memoria possono avere il controllo delle versioni gestito dal sistema (DURABILITY = SCHEMA_AND_DATA).

  • La tabella cronologica di una tabella ottimizzata per la memoria con controllo delle versioni gestito dal sistema deve essere basata su disco, sia che venga creata dall'utente o dal sistema.

  • Puoi usare query che influenzano solo la tabella attuale in memoria nei moduli T-SQL compilati nativamente. I moduli compilati nativamente non supportano la FOR SYSTEM TIME clausola, ma query ad hoc e moduli non nativi possono utilizzare la clausola contro tabelle ottimizzate per la memoria.

  • Con SYSTEM_VERSIONING = ON, il sistema crea automaticamente una tabella di staging interna ottimizzata per la memoria per accettare le modifiche più recenti versionate dal sistema, che derivano da operazioni di aggiornamento e cancellazione su una tabella ottimizzata per la memoria corrente.

  • Un compito asincrono di pulizia dati sposta regolarmente i dati dalla tabella di staging ottimizzata per la memoria interna alla tabella della cronologia basata su disco. Questo meccanismo di svuotamento dei dati mantiene i buffer di memoria interni al di sotto del 10% del consumo di memoria dei rispettivi oggetti padre. Puoi monitorare il consumo totale di memoria di una tabella temporale ottimizzata per la memoria e versionata per il sistema interrogando sys.dm_db_xtp_memory_consumers e riassumendo i dati per la tabella di staging ottimizzata per la memoria interna e per la tabella temporale corrente.

  • Per eseguire manualmente un lavaggio dei dati, esegui sp_xtp_flush_temporal_history.

  • Con SYSTEM_VERSIONING = OFF, oppure quando si modifica lo schema di una tabella versionata al sistema aggiungendo, rimuovendo o modificando colonne, l'intero contenuto del buffer interno di staging viene spostato nella tabella della cronologia basata su disco.

  • L'esecuzione di query sui dati cronologici è efficace nel livello di isolamento dello snapshot e restituisce sempre un'unione tra il buffer di gestione in memoria e la tabella basata su disco senza duplicati.

  • ALTER TABLE Le operazioni che modificano internamente lo schema della tabella devono eseguire un lavaggio dei dati, il che potrebbe prolungare l'operazione.

Tabella di staging interna ottimizzata per la memoria

Il sistema crea una tabella di staging interna ottimizzata per la memoria per ottimizzare le operazioni DML.

  • Il nome della tabella utilizza il seguente formato: Memory_Optimized_History_Table_<object_id> dove <object_id> è l'identificatore della tabella temporale corrente.

  • La tabella replica lo schema della tabella temporale corrente più una colonna bigint . Questa colonna aggiuntiva garantisce l'unicità delle righe spostate nel buffer interno di cronologia.

  • La colonna extra presenta il seguente formato nome: Change_ID[<suffix>] dove <suffix> viene aggiunto facoltativamente nel caso in cui la tabella includa già una colonna Change_ID.

  • La dimensione massima della riga per una tabella ottimizzata per la memoria con controllo delle versioni gestito dal sistema è ridotta di 8 byte a causa della colonna aggiuntiva bigint nella tabella di staging. Il massimo ora è di 8.052 byte.

  • La tabella di staging interna ottimizzata per la memoria non appare in Esplora oggetti di SQL Server Management Studio.

  • Puoi trovare i metadati su questa tabella e il suo collegamento con la tabella temporale corrente in sys.internal_tables.

Attività di scaricamento di dati

Il compito di pulizia dei dati viene eseguito regolarmente e verifica se una tabella ottimizzata per la memoria soddisfa una condizione basata sulla dimensione della memoria per il movimento dei dati. Il movimento dei dati inizia quando il consumo di memoria della tabella di staging interna raggiunge l'otto percento del consumo di memoria della tabella temporale corrente.

L'attività di scaricamento di dati viene attivata su base periodica con una pianificazione che varia in base al carico di lavoro esistente. Con un carico di lavoro elevato l'attività viene eseguita con frequenza pari a 5 secondi. Con un carico di lavoro leggero la frequenza aumenta a ogni minuto. Un thread viene generato per ogni tabella di staging interna ottimizzata per la memoria che necessita di pulizia.

Lo scaricamento dei dati consente di eliminare tutti i record dal buffer in-memory interno precedenti alla transazione corrente meno recente al fine di spostare questi record sulla tabella di cronologia basata su disco.

È possibile eseguire uno scaricamento dei dati eseguendo sp_xtp_flush_temporal_history e specificando lo schema e il nome della tabella:

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

Lo stesso processo di spostamento dei dati viene richiamato quando il sistema esegue l'attività di scarico dei dati nella sua pianificazione interna.

Contenuti correlati

  • Last updated on