Tabelle temporali con controllo delle versioni di sistema con tabelle ottimizzate per la memoria

  • Articolo

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

Le tabelle temporali con controllo delle versioni di sistema per tabelle ottimizzate per la memoria sono progettate per offrire una soluzione economica per scenari in cui sono necessari controllo dei dati e analisi temporizzate su dati raccolti con carichi di lavoro OLTP in memoria.

Panoramica

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 mantenuta per un lungo periodo di tempo (diversi mesi, anche anni), anche se non viene sottoposta spesso a query.

L'analisi temporizzata di controllo dei dati può essere richiesta in ambienti diversi, in particolare nei sistemi OLTP che elaborano grandi numeri di richieste e negli ambienti in cui viene usata 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 di sistema per le tabelle ottimizzate per la memoria presentano una velocità effettiva transazionale elevata e una concorrenza prova di blocchi. Consentono di archiviare grandi quantità di dati di cronologia utilizzando tabelle in memoria per archiviare i dati correnti (tabelle temporali) e le tabelle basate su disco per i dati cronologici. 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.

Nel diagramma seguente viene illustrata 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 un esempio, si veda CREATE TABLE.

  • Solo le tabelle ottimizzate per la memoria resistenti possono essere tabelle con controllo delle versioni di sistema (DURABILITY = SCHEMA_AND_DATA).

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

  • Le query che interessano solo la tabella in-memory corrente possono essere usate in moduli T-SQL compilati in nativo. Le query temporali che usano la clausola FOR SYSTEM TIME non sono supportate nei moduli compilati in nativo. È supportato invece l'uso della clausola FOR SYSTEM TIME insieme a tabelle ottimizzate per la memoria in query ad hoc e in moduli non nativi.

  • Con SYSTEM_VERSIONING = ON viene creata automaticamente una tabella di staging interna ottimizzata per la memoria per accettare le modifiche più recenti alle versioni di sistema in seguito alle operazioni di aggiornamento ed eliminazione nella tabella ottimizzata per la memoria corrente.

  • I dati della tabella di staging interna ottimizzata per la memoria vengono spostati regolarmente nella tabella di cronologia basata su disco da un’attività di scaricamento asincrono dei dati. Questo meccanismo di scaricamento dei dati mantiene i buffer interni alla memoria inferiori al 10 percento dell'uso della memoria da parte dei relativi oggetti padre. È possibile monitorare il consumo totale della memoria della tabella temporale ottimizzata per la memoria con controllo delle versioni di sistema eseguendo la query sys.dm_db_xtp_memory_consumers, e riepilogando i dati per la tabella di staging interna ottimizzata per la memoria e la tabella temporale corrente.

  • È possibile aggiornare manualmente i dati eseguendo sp_xtp_flush_temporal_history.

  • Con SYSTEM_VERSIONING = OFF o quando viene modificato lo schema della tabella con controllo delle versioni del sistema aggiungendo, eliminando o modificando le colonne, tutti i contenuti del buffer interno di gestione temporanea vengono spostati nella tabella di 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.

  • Le operazioni ALTER TABLE che modificano internamente lo schema della tabella devono eseguire uno scaricamento di dati, che potrebbe prolungare la durata dell'operazione.

Tabella interna di gestione temporanea 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 viene generato nel seguente formato: Memory_Optimized_History_Table_<object_id> dove <object_id> è l'identificatore della tabella temporale corrente.

  • La tabella consente di replicare lo schema della tabella temporale corrente e una colonna di tipo bigint. Questa colonna extra garantisce l'univocità delle righe spostate nel buffer interno della 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.

  • Le dimensioni massime delle righe per una tabella ottimizzata per la memoria con controllo delle versioni di sistema vengono ridotte di 8 byte a causa della colonna bigint extra della tabella di staging. La nuova dimensione massima è ora di 8.052 byte.

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

  • I metadati relativi a questa tabella e la connessione con la tabella temporale corrente sono disponibili in sys.internal_tables.

Attività di scaricamento di dati

Lo scaricamento dei dati è un'attività attivata in modo regolare che controlla se tutte le tabelle ottimizzate per la memoria soddisfano una condizione basato sulle dimensioni della memoria per lo spostamento dei dati. Lo spostamento 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.

Contenuto correlato