Pianificazione delle capacità per tempdb
In questo argomento vengono fornite le linee guida per determinare la quantità appropriata di spazio su disco necessaria per tempdb. Vengono inoltre forniti suggerimenti su come configurare tempdb per ottenere prestazioni ottimali in ambiente di produzione, nonché informazioni su come monitorare l'utilizzo dello spazio di tempdb.
Modalità di utilizzo di tempdb
Il database di sistema tempdb è una risorsa globale disponibile a tutti gli utenti connessi a un'istanza di SQL Server. Il database tempdb viene utilizzato per memorizzare gli oggetti utente, gli oggetti interni e gli archivi delle versioni.
Oggetti utente
Gli oggetti utente vengono creati esplicitamente dall'utente. Tali oggetti possono trovarsi nell'ambito di una sessione utente o nell'ambito della routine in cui viene creato l'oggetto. Una routine è una stored procedure, un trigger o una funzione definita dall'utente. Gli oggetti utente rientrano in una delle categorie seguenti:
Tabelle e indici definiti dall'utente
Tabelle e indici di sistema
Tabelle e indici temporanei globali
Tabelle e indici temporanei locali
Variabili di tabella
Tabelle restituite da funzioni con valori di tabella
Oggetti interni
Gli oggetti interni vengono creati in base alle necessità dal Motore di database di SQL Server per l'elaborazione delle istruzioni di SQL Server. Tali oggetti vengono creati ed eliminati all'interno dell'ambito di un'istruzione e rientrano in una delle categorie seguenti:
Tabelle di lavoro per le operazioni di spooling o di cursore e per le archiviazioni temporanee connesse alle operazioni su oggetti di grandi dimensioni (LOB, Large Object) .
File di lavoro per operazioni hash join o hash aggregate.
Risultati intermedi dell'ordinamento per operazioni quali la creazione o la ricompilazione di indici (se SORT_IN_TEMPDB è specificato) o per alcune query GROUP BY, ORDER BY o UNION.
Ogni oggetto interno utilizza un minimo di nove pagine: una pagina IAM e un'estensione di otto pagine. Per ulteriori informazioni sulle pagine e sulle estensioni, vedere Informazioni su pagine ed extent.
Archivi delle versioni
Un archivio delle versioni è una raccolta di pagine di dati che mantiene righe di dati utilizzate dalle caratteristiche che supportano il controllo delle versioni delle righe. Vengono utilizzati due archivi delle versioni: uno comune e uno per la compilazione di indici online. Gli archivi delle versioni contengono:
Le versioni delle righe generate dalle transazioni di modifica dei dati in un database che utilizza snapshot o Read committed con utilizzo dei livelli di isolamento basati sul controllo delle versioni delle righe.
Versioni delle righe generate dalle transazioni che modificano i dati per caratteristiche quali operazioni sugli indici online, MARS (Multiple Active Result Sets) e trigger AFTER.
Nella tabella seguente vengono elencate le caratteristiche di SQL Server che creano oggetti utente, oggetti interni o versioni delle righe in tempdb. Quando possibile, vengono anche indicati i metodi per la stima dell'utilizzo di spazio su disco.
Caratteristica |
Utilizzo di tempdb |
Informazioni aggiuntive |
|---|---|---|
Operazioni bulkload con trigger abilitati |
Quando i trigger sono abilitati, è possibile ottimizzare le importazioni bulk. In SQL Server, per i trigger che aggiornano o eliminano le transazioni viene utilizzato il controllo delle versioni delle righe. Una copia di ogni riga eliminata o aggiornata viene aggiunta all'archivio delle versioni. Vedere "Trigger" di seguito in questa tabella. |
|
Query delle espressioni di tabella comune |
Un'espressione di tabella comune può essere considerata come un set di risultati temporaneo definito all'interno dell'ambito di esecuzione di una singola istruzione SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Quando il piano di query per una query delle espressioni di tabella comune utilizza un operatore spool per salvare i risultati delle query intermedie, Motore di database crea una tabella di lavoro in tempdb per supportare questa operazione. |
|
Cursori |
I cursori gestiti da keyset e i cursori statici utilizzano tabelle di lavoro che vengono compilate nel database tempdb. I cursori gestiti da keyset archiviano in queste tabelle il set di chiavi che identifica le righe nel cursore, mentre i cursori statici vi archiviano l'intero set dei risultati del cursore stesso. L'utilizzo di spazio su disco può variare in base al piano di query adottato. Se il piano di query è uguale a quello delle versioni precedenti di SQL Server, l'utilizzo di spazio su disco sarà quasi identico. |
|
Posta elettronica database |
Vedere "Service Broker" di seguito in questa tabella. |
|
DBCC CHECKDB |
DBCC CHECKDB utilizza le tabelle di lavoro di tempdb per mantenere i risultati intermedi e per le operazioni di ordinamento. Per determinare i requisiti di spazio su disco di tempdb per l'operazione, eseguire DBCC CHECKDB WITH ESTIMATEONLY. |
|
Notifiche di eventi |
Vedere "Service Broker" di seguito in questa tabella. |
|
Indici |
Quando si crea o si ricompila un indice (offline oppure online) e si imposta l'opzione SORT_IN_TEMPDB su ON, si indica al Motore di database di utilizzare tempdb per l'archiviazione dei risultati intermedi dell'ordinamento utilizzati per la compilazione dell'indice. Quando la specifica di SORT_IN_TEMPDB produce un ordinamento, è necessario che tempdb disponga di spazio su disco tale da contenere l'indice più grande, più ulteriore spazio pari al valore dell'opzione index create memory. Per ulteriori informazioni, vedere Esempio di spazio su disco per gli indici. Le tabelle e gli indici possono essere partizionati. Per gli indici partizionati, se l'opzione SORT_IN_TEMPDB è specificata e l'indice è allineato alla tabella di base, tempdb deve disporre di spazio sufficiente a contenere i risultati dell'ordinamento intermedio della partizione più grande. Se l'indice non è allineato, tempdb deve disporre di spazio sufficiente a contenere i risultati dell'ordinamento intermedio di tutte le partizioni. Per ulteriori informazioni, vedere Linee guida specifiche per gli indici partizionati. Le operazioni sull'indice online utilizzano il controllo delle versioni delle righe per isolare l'operazione sull'indice dagli effetti delle modifiche eseguite da altre transazioni. In questo modo si evita di dover richiedere blocchi di condivisione sulle righe che sono state lette. Le operazioni utente simultanee di aggiornamento ed eliminazione durante le operazioni sugli indici online richiedono spazio per i record di versione in tempdb. Quando le operazioni sugli indici online utilizzano SORT_IN_TEMPDB e producono un ordinamento, è necessario che tempdb disponga di ulteriore spazio su disco, come descritto in precedenza per i risultati di ordinamento intermedi. Le operazioni sugli indici online che consentono di creare, eliminare o ricompilare un indice cluster richiedono spazio su disco aggiuntivo per la compilazione e il mantenimento di un indice di mapping temporaneo. Le operazioni CREATE e UPDATE STATISTICS possono utilizzare tempdb per ordinare l'esempio di righe per la compilazione di statistiche. Per ulteriori informazioni, vedere Requisiti di spazio su disco per operazioni DLL sugli indici. |
tempdb e creazione dell'indice Linee guida specifiche per gli indici partizionati Requisiti di spazio su disco per operazioni DLL sugli indici |
Variabili e parametri di tipo LOB (Large Object) |
I tipi di dati LOB sono varchar(max), nvarchar(max), varbinary(max)text, ntext, image e xml. Tali tipi possono avere dimensioni fino a 2 GB e possono essere utilizzati come variabili o parametri nelle stored procedure, nelle funzioni definite dall'utente, nei batch o nelle query. Quando contengono valori piccoli, le variabili e i parametri definiti come tipo di dati LOB vengono archiviati nella memoria principale. I valori più grandi vengono invece archiviati in tempdb. Le variabili e i parametri LOB archiviati in tempdb vengono gestiti come oggetti interni. È possibile eseguire una query sulla vista a gestione dinamica sys.dm_db_session_space_usage per ottenere un report delle pagine allocate agli oggetti interni per una data sessione. Alcune funzioni per i valori stringa, come SUBSTRING o REPLICATE, archiviano dati temporanei intermedi in tempdb quando operano con valori LOB. Allo stesso modo, quando nel database è abilitato un livello di isolamento delle transazioni basato sul controllo delle versioni e vengono apportate modifiche a oggetti di grandi dimensioni, il frammento modificato dell'oggetto LOB viene copiato nell'archivio delle versioni in tempdb. |
|
MARS (Multiple Active Result Set) |
È possibile ottenere più set di risultati attivi in una sola connessione, solitamente noti come MARS (Multiple Active Result Set). Se una sessione MARS genera un'istruzione di modifica di dati (ad esempio INSERT, UPDATE o DELETE) in un momento in cui è presente un set di risultati attivo, le righe interessate dalla modifica vengono inserite nell'archivio delle versioni in tempdb. Vedere "Controllo delle versioni delle righe" di seguito in questa tabella. |
|
Notifiche delle query |
Vedere "Service Broker" di seguito in questa tabella. |
|
Query |
Le query che contengono istruzioni SELECT, INSERT, UPDATE e DELETE possono utilizzare oggetti interni per archiviare risultati intermedi di operazioni di ordinamento, hash join o hash aggregate. Quando un piano di esecuzione della query è memorizzato nella cache, vengono memorizzate nella cache anche le tabelle di lavoro utilizzate dal piano. Quando una tabella di lavoro viene memorizzata nella cache, la tabella viene troncata e nove pagine restano nella cache per un riutilizzo successivo. Vengono così incrementate le prestazioni della successiva esecuzione della query. Se la memoria del sistema tende a esaurirsi, il Motore di database può rimuovere il piano di esecuzione ed eliminare le relative tabelle di lavoro. |
Memorizzazione nella cache e riutilizzo del piano di esecuzione |
Controllo delle versioni delle righe |
Il controllo delle versioni delle righe è un framework generale utilizzato a supporto delle caratteristiche seguenti.
Le versioni delle righe vengono mantenute nell'archivio delle versioni di tempdb per tutto il tempo in cui una transazione attiva deve accedervi. Il contenuto dell'archivio delle versioni corrente viene restituito in sys.dm_tran_version_store. Le pagine dell'archivio delle versioni vengono tracciate a livello di file in quanto risorse globali. Per visualizzare la dimensione corrente dell'archivio delle versioni, è possibile utilizzare la colonna version_store_reserved_page_count in sys.dm_db_file_space_usage. È necessario considerare l'operazione di pulizia dell'archivio delle versioni come la transazione con esecuzione più lunga che deve accedere alla versione precedente. Per individuare la transazione con esecuzione più lunga relativa alla pulizia dell'archivio delle versioni, visualizzare la colonna elapsed_time_seconds in sys.dm_tran_active_snapshot_database_transactions. I contatori Spazio disponibile in tempdb (KB) e Dimensioni archivio versioni (KB) nell'oggetto Transactions possono essere utilizzati per monitorare la dimensione e la velocità di crescita dell'archivio delle versioni delle righe contenuto in tempdb. Per ulteriori informazioni, vedere Oggetto Transactions di SQL Server. Per operare una stima dello spazio da destinare al controllo delle versioni delle righe in tempdb, è innanzitutto necessario considerare che nell'archivio delle versioni verranno memorizzate tutte le modifiche eseguite durante una transazione attiva. Una transazione snapshot avviata in seguito potrà pertanto accedere alle versioni precedenti. Se inoltre c'è una transazione snapshot attiva, dovranno essere mantenuti anche tutti i dati generati per l'archivio delle versioni dalle transazioni attive nel momento in cui ha inizio lo snapshot. Di seguito è riportata una formula di base: [Size of Version Store] = 2 * [Version store data generated per minute] * [Longest running time (minutes) of your transaction] |
Informazioni sui livelli di isolamento basati sul controllo delle versioni delle righe Utilizzo delle risorse di controllo delle versioni delle righe |
Service Broker |
Service Broker consente agli sviluppatori di compilare applicazioni asincrone a regime di controllo libero ("loosely-coupled") in cui componenti indipendenti concorrono all'esecuzione di un'attività. Tali componenti delle applicazioni si scambiano messaggi contenenti informazioni necessarie per il completamento dell'attività. Service Broker utilizza in modo esplicito tempdb per mantenere il contesto del dialogo esistente che non può rimanere in memoria. La dimensione è di circa 1 KB per dialogo. Service Broker utilizza inoltre in modo implicito tempdb memorizzando nella cache gli oggetti nel contesto di esecuzione delle query, come le tabelle di lavoro che vengono utilizzate per gli eventi timer e le conversazioni recapitate in background. La posta elettronica database, le notifiche degli eventi e le notifiche delle query utilizzano Service Broker in modo implicito. |
|
Stored procedure |
Le stored procedure possono creare oggetti utente quali le tabelle globali o locali e gli indici, le variabili o i parametri relativi. Gli oggetti temporanei delle stored procedure possono essere memorizzati nella cache per ottimizzare le operazioni che li eliminano e li creano. Questo può accrescere i requisiti di spazio su disco di tempdb. Per ogni oggetto temporaneo vengono archiviate fino a nove pagine per un utilizzo successivo. Vedere "Tabelle temporanee e variabili table" di seguito in questa tabella. |
|
Tabelle temporanee e variabili table
|
Le tabelle temporanee e le variabili table vengono archiviate in tempdb. I requisiti di spazio su disco per gli oggetti tabelle temporanee sono uguali a quelli delle versioni precedenti di SQL Server. Il metodo per la stima della dimensione di una tabella temporanea è uguale a quello utilizzato per la stima della dimensione di una tabella standard. Per ulteriori informazioni, vedere Stima delle dimensioni di una tabella. Le variabili table funzionano in modo analogo alle variabili locali. Una variabile table è di tipo table e viene principalmente utilizzata per l'archiviazione temporanea di un set di righe restituite come set di risultati di una funzione con valori di tabella. Lo spazio su disco necessario per mantenere una variabile table dipende dalla dimensione della variabile dichiarata e dal valore archiviato nella variabile. Le variabili e le tabelle temporanee locali vengono memorizzate nella cache quando vengono soddisfatte le condizioni seguenti:
Quando una tabella temporanea o una variabile table viene memorizzata nella cache, l'oggetto temporaneo non viene eliminato al termine dell'utilizzo, bensì viene troncato. Fino a nove pagine vengono archiviate e riutilizzate in seguito, quando l'oggetto chiamante viene eseguito di nuovo. La memorizzazione nella cache consente un'esecuzione molto più veloce delle operazioni di eliminazione e creazione degli oggetti e riduce la contesa per l'allocazione delle pagine. Per prestazioni ottimali, è consigliabile calcolare lo spazio su disco richiesto per le variabili table o per le tabelle temporanee locali memorizzate nella cache in tempdb utilizzando la formula seguente: 9 page per temp table * number of average temp tables per procedure * number of maximum simultaneous executions of the procedure |
|
Trigger |
Le tabelle inserted e deleted, che vengono utilizzate nei trigger AFTER, vengono create in tempdb. Viene pertanto eseguito il controllo delle versioni delle righe aggiornate o eliminate dal trigger. Sono tali tutte le righe che vengono modificate dall'istruzione che ha attivato il trigger. Non viene eseguito il controllo delle versioni delle righe inserite dal trigger. I trigger INSTEAD OF utilizzano tempdb in modo analogo alle query. L'utilizzo di spazio su disco per i trigger INSTEAD OF è uguale a quello delle versioni precedenti di SQL Server. Vedere "Query" più indietro in questa tabella. Quando si esegue il caricamento bulk con i trigger abilitati, all'archivio delle versioni viene aggiunta una copia di ogni riga eliminata o aggiornata. |
Ottimizzazione delle prestazioni dell'importazione bulk Utilizzo delle risorse di controllo delle versioni delle righe |
Funzioni definite dall'utente |
Le funzioni definite dall'utente possono creare oggetti utente temporanei quali le tabelle globali o locali e gli indici, le variabili o i parametri relativi. La tabella restituita di una funzione con valori di tabella viene ad esempio archiviata in tempdb. I tipi di dati consentiti per i parametri e i valori restituiti delle funzioni scalari e delle funzioni con valori di tabella includono la maggior parte dei tipi di dati LOB. Un valore restituito può ad esempio essere di tipo xml o varchar(max). Vedere "Variabili e parametri di tipo LOB (Large object)" più indietro in questa tabella. Gli oggetti temporanei delle funzioni definite dall'utente con valori di tabella possono essere memorizzati nella cache per ottimizzare le operazioni necessarie per eliminarli e crearli. Vedere "Tabelle temporanee e variabili table" più indietro in questa tabella. |
|
XML |
Le variabili e i parametri di tipo xml possono contenere fino a 2 GB. Quando contengono valori piccoli, vengono archiviati nella memoria principale. I valori più grandi vengono invece archiviati in tempdb. Vedere "Variabili e parametri di tipo LOB (Large object)" più indietro in questa tabella. La stored procedure di sistema sp_xml_preparedocument crea una tabella di lavoro in tempdb. Il parser MSXML utilizza la tabella di lavoro per archiviare il documento XML analizzato. I requisiti di spazio su disco di tempdb sono orientativamente proporzionali alla dimensione del documento XML specificato quando la stored procedure viene eseguita. |
Implementazione di XML in SQL Server |
Pianificazione della capacità per gli aggiornamenti a SQL Server
La determinazione delle dimensioni appropriate per tempdb in un ambiente di produzione dipende da molti fattori. Come descritto più indietro in questo argomento, tali fattori includono il carico di lavoro esistente e le caratteristiche di SQL Server utilizzate. È consigliabile analizzare il carico di lavoro esistente eseguendo le attività seguenti in un ambiente di test di SQL Server:
Attivare l'aumento automatico delle dimensioni di tempdb.
Eseguire singole query o file di traccia del carico di lavoro e monitorare l'utilizzo dello spazio di tempdb.
Eseguire operazioni di manutenzione degli indici, come la ricompilazione degli indici, e monitorare lo spazio occupato da tempdb.
Impiegare i valori di utilizzo dello spazio ottenuti ai passaggi precedenti per prevedere i requisiti del carico di lavoro totale. Correggere poi tali valori in funzione delle attività simultanee previste e quindi impostare conseguentemente la dimensione di tempdb.
Per ulteriori informazioni sul monitoraggio dello spazio di tempdb, vedere Risoluzione dei problemi relativi allo spazio su disco insufficiente in tempdb. Per ulteriori informazioni sulla stima dell'utilizzo di tempdb durante le operazioni sugli indici, vedere Esempio di spazio su disco per gli indici.
Configurazione di tempdb per gli ambienti di produzione
Per ottenere prestazioni di tempdb ottimali, attenersi alle linee guida e alle indicazioni offerte in Ottimizzazione delle prestazioni di tempdb.
Come monitorare l'utilizzo di tempdb
L'esaurimento dello spazio disponibile in tempdb può provocare interruzioni significative nell'ambiente di produzione di SQL Server e può impedire alle applicazioni di completare le operazioni in esecuzione. È possibile utilizzare la vista a gestione dinamica sys.dm_db_file_space_usage per monitorare lo spazio su disco utilizzato da queste caratteristiche nei file di tempdb. Per monitorare l'allocazione delle pagine o l'attività di deallocazione in tempdb a livello di sessione o di attività, è inoltre possibile utilizzare le viste a gestione dinamica sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Queste viste consentono di identificare le query, le tabelle temporanee o le variabili di tabella che utilizzano una grande quantità di spazio su disco in tempdb. Sono anche disponibili diversi contatori che consentono di monitorare lo spazio libero disponibile in tempdb e le risorse che stanno utilizzando tempdb. Per ulteriori informazioni, vedere Risoluzione dei problemi relativi allo spazio su disco insufficiente in tempdb.
Vedere anche