Articolo
09/28/2010

Utilizzo delle risorse di controllo delle versioni delle righe

L'infrastruttura di controllo delle versioni delle righe supporta le funzionalità seguenti disponibili in SQL Server:

Trigger
MARS (Multiple Active Result Set)
Indicizzazione in linea

L'infrastruttura di controllo delle versioni delle righe supporta inoltre i livelli di isolamento delle transazioni basati sul controllo delle versioni delle righe seguenti, disattivati per impostazione predefinita:

Quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON, le transazioni READ_COMMITTED offrono consistenza in lettura a livello di istruzioni tramite il controllo delle versioni delle righe.
Quando l'opzione di database ALLOW_SNAPSHOT_ISOLATION è impostata su ON, le transazioni SNAPSHOT offrono consistenza in lettura a livello di istruzioni tramite il controllo delle versioni delle righe.

I livelli di isolamento basati sul controllo delle versioni delle righe consentono di ridurre il numero di blocchi acquisiti per transazione eliminando l'utilizzo di blocchi condivisi nelle operazioni di lettura. In questo modo, vengono garantite prestazioni di sistema migliori grazie alla riduzione delle risorse utilizzate per la gestione dei blocchi. Le prestazioni risultano inoltre migliorate grazie al minor numero di volte in cui una transazione viene bloccata dai blocchi acquisiti da altre transazioni.

I livelli di isolamento basati sul controllo delle versioni delle righe consentono di ridurre le risorse necessarie per le modifiche dei dati. L'attivazione di tali opzioni comporta il controllo delle versioni di tutte le modifiche dei dati per il database. Una copia dei dati precedente la modifica viene archiviata nel database tempdb anche quando non vi è alcuna transazione attiva che utilizza l'isolamento basato sul controllo delle versioni delle righe. I dati in seguito alla modifica includono un puntatore ai dati a cui è stato applicato il controllo delle versioni archiviati in tempdb. Per oggetti di grandi dimensioni, in tempdb viene copiata solo la parte dell'oggetto modificata.

Spazio utilizzato in tempdb

Per ogni istanza di Motore di database, in tempdb deve essere disponibile una quantità di spazio sufficiente per contenere le versioni di riga generate per ogni database incluso nell'istanza. L'amministratore del database deve fare in modo che in tempdb sia disponibile una quantità di spazio elevata per supportare l'archivio versioni. In tempdb sono inclusi due archivi versioni:

L'archivio versioni per la creazione di indici in linea viene utilizzato per la creazione di indici in linea in tutti i database.
L'archivio versioni comune viene utilizzato per tutte le altre operazioni di modifica dei dati in tutti i database.

Le versioni di riga devono essere archiviate per tutto il tempo necessario a una transazione attiva per accedervi. Una volta al minuto un thread in background rimuove le versioni di riga che non sono più necessarie e libera il relativo spazio utilizzato in tempdb. Una transazione con esecuzione prolungata impedisce che venga liberato lo spazio nell'archivio versioni se si verificano le condizioni seguenti:

Viene utilizzato l'isolamento basato sul controllo delle versioni delle righe.
Vengono utilizzati trigger, MARS o operazioni di creazione di indici in linea.
Vengono generate versioni di riga.

[!NOTA]

Quando viene richiamato un trigger all'interno di una transazione, le versioni di riga create dal trigger vengono mantenute fino alla fine della transazione, anche se le versioni di riga non sono più necessarie al termine del trigger. Questa situazione riguarda inoltre le transazioni Read committed che utilizzano il controllo delle versioni delle righe. Con questo tipo di transazione, una vista del database consistente a livello di transazioni è necessaria solo per ogni istruzione della transazione. Le versioni di riga create per un'istruzione della transazione non sono pertanto più necessarie al termine dell'istruzione. Tuttavia, le versioni di riga create da ogni istruzione della transazione vengono mantenute fino al termine della transazione.

Quando in tempdb viene esaurito tutto lo spazio, Motore di database forza la compattazione degli archivi versioni. Durante il processo di compattazione, le transazioni con esecuzione prolungata che non hanno ancora generato versioni di riga vengono contrassegnate come vittime. Nel log degli errori viene generato un messaggio 3967 per ogni transazione vittima. Se una transazione viene contrassegnata come vittima, non può più leggere le versioni di riga nell'archivio versioni. Quando la transazione tenta di leggere le versioni di riga, viene generato un messaggio 3966 e viene eseguito il rollback della transazione. Se il processo di compattazione ha esito positivo, lo spazio viene reso disponibile in tempdb. In caso contrario, lo spazio di tempdb si esaurisce e si verificano le situazioni seguenti:

L'esecuzione delle operazioni di scrittura prosegue, ma non vengono generate versioni. Nel log degli errori viene visualizzato un messaggio informativo (3959), ma la transazione che scrive i dati non subisce alcun effetto.
Le transazioni che tentano di accedere alle versioni di riga non generate a causa di un rollback completo di tempdb vengono terminate con un errore 3958.

Spazio utilizzato nelle righe di dati

Ogni riga del database può utilizzare fino a 14 byte alla fine della riga per le informazioni relative al controllo delle versioni delle righe. Tali informazioni contengono il numero di sequenza della transazione che ha eseguito il commit della versione e il puntatore alla riga di cui è stato eseguito il controllo delle versioni. Questi 14 byte vengono aggiunti alla prima modifica della riga oppure quando viene inserita una nuova riga se si verifica una delle condizioni seguenti:

L'opzione READ_COMMITTED_SNAPSHOT o ALLOW_SNAPSHOT_ISOLATION è impostata su ON.
La tabella include un trigger.
Viene utilizzato MARS (Multiple Active Results Set).
Nella tabella sono in esecuzione operazioni di creazione di indici in linea.

I 14 byte vengono rimossi dalla riga del database alla prima modifica della riga quando si verificano tutte le condizioni seguenti:

Le opzioni READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION sono impostate su OFF.
Il trigger non è più presente nella tabella.
Non si utilizza MARS.
Le operazioni di creazione di indici in linea non sono in esecuzione.

Se si utilizza una qualsiasi delle funzionalità di controllo delle versioni delle righe, potrebbe essere necessario allocare spazio su disco sufficiente per includere i 14 byte per ogni riga del database . L'aggiunta delle informazioni relative al controllo delle versioni delle righe può provocare la divisione delle pagine di indice o l'allocazione di una nuova pagina di dati se nella pagina corrente non è disponibile spazio sufficiente. Se la lunghezza media della riga, ad esempio, è 100 byte, i 14 byte aggiuntivi provocano un aumento del 14 percento della tabella esistente.

La riduzione del fattore di riempimento potrebbe impedire o ridurre la frammentazione di pagine di indice. Per visualizzare informazioni sulla frammentazione dei dati e degli indici per una tabella o vista specificata, è possibile utilizzare DBCC SHOWCONTIG.

Spazio utilizzato in oggetti di grandi dimensioni

Motore di database di SQL Server supporta sei tipi di dati in grado di includere stringhe estese di un massimo di 2 gigabyte (GB) in lunghezza: nvarchar(max), varchar(max), varbinary(max), ntext, text e image. Le stringhe estese per cui vengono utilizzati questi tipi di dati vengono archiviate in una serie di frammenti di dati collegati alla riga di dati. Le informazioni relative al controllo delle versioni delle righe vengono archiviate in ogni frammento utilizzato per archiviare tali stringhe estese. I frammenti di dati sono una raccolta di pagine dedicate a oggetti di grandi dimensioni in una tabella.

Man mano che nel database vengono aggiunti nuovi valori di grandi dimensioni, questi valori vengono allocati utilizzando un massimo di 8040 byte di dati per frammento. Nelle versioni precedenti di Motore di database vengono archiviati fino a 8080 byte di dati ntext, text o image per frammento.

I dati LOB (Large Object) esistenti ntext, text e image non vengono aggiornati per lasciare spazio alle informazioni relative al controllo delle versioni delle righe quando un database viene aggiornato a SQL Server da una versione precedente di SQL Server. La prima volta che i dati LOB vengono modificati, tuttavia, vengono aggiornati dinamicamente per consentire l'archiviazione delle informazioni relative al controllo delle versioni. Questa operazione viene eseguita anche se non vengono generate versioni di riga. In seguito all'aggiornamento dei dati LOB, il numero massimo di byte archiviati per frammento viene ridotto da 8080 byte a 8040 byte. Il processo di aggiornamento equivale all'eliminazione del valore LOB e al reinserimento dello stesso valore. I dati LOB vengono aggiornati anche se viene modificato un solo byte. Si tratta di un'operazione occasionale per ogni colonna ntext, text o image, ma ogni operazione può generare una quantità elevata di allocazioni di pagina e un'ingente attività di I/O, a seconda delle dimensioni dei dati LOB. Tali operazioni possono inoltre generare un'attività di registrazione elevata se la modifica viene registrata completamente. Viene eseguita la registrazione minima delle operazioni WRITETEXT e UPDATETEXT se la modalità di recupero del database non è impostata su FULL.

I tipi di dati nvarchar(max), varchar(max) e varbinary(max) non sono disponibili nelle versioni precedenti di SQL Server. Per questo motivo, tali tipi dati non sono interessati da problemi di aggiornamento.

Per soddisfare questo requisito, è necessario allocare spazio su disco sufficiente.

Controllo delle versioni delle righe e archivio versioni

Per il controllo delle versioni delle righe, l'archivio versioni e i processi di isolamento dello snapshot per le prestazioni e i problemi, SQL Server offre strumenti in forma di viste a gestione dinamica e contatori delle prestazioni di Windows System Monitor.

Viste a gestione dinamica

Le seguenti viste a gestione dinamica forniscono informazioni sullo stato attuale del sistema di tempdb e l'archivio versioni, nonché delle transazioni che utilizzano il controllo delle versioni delle righe.

sys.dm_db_file_space_usage. Restituisce informazioni sull'utilizzo dello spazio per ogni file nel database. Per ulteriori informazioni, vedere sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage. Restituisce informazioni relative alle attività di allocazione e deallocazione delle pagine per sessione per il database. Per ulteriori informazioni, vedere sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage. Restituisce informazioni relative alle operazioni di allocazione e deallocazione per attività per il database. Per ulteriori informazioni, vedere sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators. Restituisce una tabella virtuale per gli oggetti indicante la maggior parte delle versioni nell'archivio versioni. Raggruppa le 256 lunghezze superiori dei record di aggregazione per database_id e rowset_id. Utilizzare questa funzione per trovare i maggiori consumer dell'archivio delle versioni. Per ulteriori informazioni, vedere sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store. Restituisce una tabella virtuale in cui vengono visualizzati tutti i record di versione nell'archivio versioni comune. Per ulteriori informazioni, vedere sys.dm_tran_version_store (Transact-SQL).

[!NOTA]

sys.dm_tran_top_version_generators e sys.dm_tran_version_store possono essere funzioni molto costose da eseguire, poiché entrambe inviano query all'intero archivio versioni, che potrebbe avere dimensioni estremamente elevate.

sys.dm_tran_active_snapshot_database_transactions. Restituisce una tabella virtuale per tutte le transazioni attive in tutti i database dell'istanza di SQL Server che utilizzano il controllo delle versioni delle righe. Le transazioni di sistema non vengono visualizzate in questa vista a gestione dinamica. Per ulteriori informazioni, vedere sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot. Restituisce una tabella virtuale in cui vengono visualizzati gli snapshot creati da ogni transazione. Lo snapshot contiene il numero di sequenza delle transazioni attive che utilizzano il controllo delle versioni delle righe. Per ulteriori informazioni, vedere sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction. Restituisce una singola riga in cui vengono visualizzate le informazioni sullo stato correlate al controllo delle versioni delle righe per la transazione nella sessione corrente. Per ulteriori informazioni, vedere sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot. Restituisce una tabella virtuale in cui vengono visualizzate tutte le transazioni attive al momento in cui è stata avviata la transazione di isolamento dello snapshot corrente. Se la transazione corrente utilizza l'isolamento dello snapshot, questa funzione non restituisce alcuna riga. sys.dm_tran_current_snapshot è simile a sys.dm_tran_transactions_snapshot, ad eccezione del fatto che restituisce solo le transazioni attive per lo snapshot corrente. Per ulteriori informazioni, vedere sys.dm_tran_current_snapshot (Transact-SQL).

Contatori delle prestazioni

I contatori delle prestazioni di SQL Server includono le informazioni sulle prestazioni del sistema influenzate dai processi di SQL Server. I contatori delle prestazioni seguenti consentono di monitorare tempdb e l'archivio versioni, nonché le transazioni che utilizzano il controllo delle versioni delle righe. I contatori delle prestazioni sono inclusi nell'oggetto prestazione SQLServer:Transactions.

Spazio disponibile in tempdb (KB). Esegue il monitoraggio della quantità, in kilobyte (KB), di spazio libero nel database tempdb. In tempdb deve essere disponibile una quantità di spazio sufficiente per gestire l'archivio versioni che supporta l'isolamento dello snapshot.

Nella formula seguente viene eseguito un calcolo approssimativo delle dimensioni dell'archivio versioni. Per le transazioni con esecuzione prolungata, può essere utile monitorare la frequenza di generazione ed eliminazione per stimare le dimensioni massime dell'archivio versioni.

[dimensioni dell'archivio versioni comune] = 2 * [dati dell'archivio versioni generati al minuto] * [tempo massimo (in minuti) di esecuzione della transazione]

Il tempo massimo di esecuzione delle transazioni non deve includere operazioni di creazione di indici in linea. Poiché queste operazioni possono richiedere una quantità di tempo prolungata nelle tabelle di dimensioni molto elevate, le operazioni di creazione di indici in linea utilizzano un archivio versioni distinto. Le dimensioni approssimative dell'archivio versioni per le operazioni di creazione di indici in linea equivale alla quantità di dati modificati nella tabella, inclusi tutti gli indici, quando è attiva la creazione di indici in linea.

Dimensioni archivio versioni (KB). Esegue il monitoraggio delle dimensioni in KB di tutti gli archivi versioni. Queste informazioni consentono di determinare la quantità di spazio necessario nel database tempdb per l'archivio versioni. Il monitoraggio di questo contatore per un certo periodo di tempo offre una stima utile dell'ulteriore spazio necessario per tempdb.

Frequenza generazione versioni (KB/s). Esegue il monitoraggio della frequenza di generazione delle versioni in KB al secondo in tutti gli archivi versioni.

Frequenza eliminazione versioni (KB/s). Esegue il monitoraggio della frequenza di eliminazione delle versioni in KB al secondo in tutti gli archivi versioni.

[!NOTA]

Le informazioni incluse in Frequenza generazione versioni (KB/s) e Frequenza eliminazione versioni (KB/s) possono essere utilizzate per prevedere i requisiti di spazio del database tempdb.

Conteggio unità archivio versioni. Esegue il monitoraggio del conteggio delle unità dell'archivio versioni.

Creazione unità archivio versioni. Esegue il monitoraggio del numero totale di unità dell'archivio versioni create per archiviare le versioni di riga dal momento in cui è stata avviata l'istanza.

Troncamento unità archivio versioni. Esegue il monitoraggio del numero totale di unità dell'archivio versioni troncate dal momento in cui è stata avviata l'istanza. Un'unità dell'archivio versioni viene troncata quando in SQL Server viene determinato che nessuna delle righe di versione archiviata nell'unità è necessaria per eseguire transazioni attive.

Percentuale conflitti aggiornamento. Esegue il monitoraggio del rapporto tra le transazione snapshot di aggiornamento in cui sono presenti conflitti di aggiornamento e il numero totale di transazioni snapshot di aggiornamento.

Tempo massimo esecuzione transazione. Esegue il monitoraggio del tempo massimo di esecuzione in secondi di qualsiasi transazione che utilizza il controllo delle versioni delle righe. Queste informazioni possono essere utilizzate per determinare se alcune transazioni vengano eseguite in una quantità di tempo eccessiva.

Transazioni. Esegue il monitoraggio del numero totale di transazioni attive. Non sono incluse le transazioni di sistema.

Transazioni snapshot. Esegue il monitoraggio del numero totale di transazioni snapshot attive.

Transazioni snapshot di aggiornamento. Esegue il monitoraggio del numero totale di transazioni snapshot attive tramite cui vengono eseguite operazioni di aggiornamento.

Transazioni di versione non snapshot. Esegue il monitoraggio del numero totale di transazioni non snapshot attive che generano record di versione.

[!NOTA]

La somma dei valori indicati in Transazioni snapshot di aggiornamento e Transazioni di versione non snapshot rappresenta il numero totale di transazioni coinvolte nella generazione delle versioni. La differenza tra i valori indicati in Transazioni snapshot e Transazioni snapshot di aggiornamento rappresenta il numero di transazioni snapshot di sola lettura.