Condividi tramite


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 ricostruzione 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 funzionalità che supportano il controllo delle versioni delle righe. Vengono utilizzati due archivi delle versioni: uno comune e uno per la creazione di indici in linea. 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 funzionalità quali operazioni sugli indici in linea, MARS (Multiple Active Result Sets) e trigger AFTER.

Nella tabella seguente vengono elencate le funzionalità 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.

Funzionalità

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.

Ottimizzazione delle prestazioni dell'importazione bulk

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.

Utilizzo delle espressioni di tabella comuni

WITH common_table_expression (Transact-SQL)

Cursori

I cursori gestiti da keyset e i cursori statici utilizzano tabelle di lavoro che vengono create 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.

Informazioni sulla scelta di un tipo di cursore

Posta elettronica database

Vedere "Service Broker" di seguito in questa tabella.

Posta elettronica database

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.

DBCC CHECKDB (Transact-SQL)

Ottimizzazione delle prestazioni di DBCC CHECKDB

Notifiche di eventi

Vedere "Service Broker" di seguito in questa tabella.

Informazioni sulle notifiche degli eventi

Indici

Quando si crea o si ricostruisce un indice (in linea o non in linea) 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 creazione 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 in linea 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 in linea richiedono spazio per i record di versione in tempdb. Quando le operazioni sugli indici in linea 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. Per le operazioni sugli indici in linea che consistono nella creazione, nell'eliminazione o nella ricostruzione di un indice cluster è inoltre necessario spazio su disco aggiuntivo per la creazione e il mantenimento di un indice di mapping temporaneo. 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

Esempio di spazio su disco per gli indici

Funzionamento delle operazioni sugli indici in linea

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.

Utilizzo di tipi di dati per valori di grandi dimensioni

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.

Utilizzo di MARS (Multiple Active Result Set)

Notifiche delle query

Vedere "Service Broker" di seguito in questa tabella.

Utilizzo della notifica delle query

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.

Caching e riutilizzo del piano di esecuzione

Controllo delle versioni delle righe

Il controllo delle versioni delle righe è un framework generale utilizzato a supporto delle funzionalità seguenti.

  • Trigger

  • MARS (Multiple Active Result Set)

  • Operazioni sugli indici in cui viene utilizzata l'opzione ONLINE

  • Livelli di isolamento delle transazioni basati sul controllo delle versioni delle righe

    • Una nuova implementazione del livello di isolamento Read committed che utilizza il controllo delle versioni delle righe per garantire la consistenza in lettura a livello di istruzione.

    • Un livello di isolamento dello snapshot che garantisce la consistenza in lettura a livello di transazione.

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:

[Dimensione dell'archivio delle versioni] = 2 *

[Dati generati per l'archivio delle versioni ogni minuto] *

[Durata di esecuzione massima (in minuti) della transazione]

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 la creazione di applicazioni asincrone a regime di controllo libero 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.

Panoramica (Service Broker)

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.

Creazione di stored procedure (Motore di database)

Tabelle temporanee e variabili table

  • Tabelle e indici definiti dall'utente

  • Tabelle e indici di sistema

  • Tabelle e indici temporanei globali

  • Tabelle e indici temporanei locali

  • Variabili table

  • Tabelle restituite da funzioni con valori di tabella

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:

  • Non vengono creati vincoli denominati.

  • Dopo la creazione della tabella temporanea non vengono eseguite istruzioni DDL (Data Definition Language) come CREATE INDEX o CREATE STATISTICS che interessano la tabella.

  • L'oggetto temporaneo non viene creato utilizzando istruzioni SQL dinamiche, come ad esempio sp_executesql N'create table #t(a int)'.

  • L'oggetto temporaneo viene creato all'interno di un altro oggetto, come una stored procedure, un trigger, una funzione definita dall'utente, o è la tabella restituita da una funzione definita dall'utente con valori di tabella.

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 pagine per ogni tabella temporanea

* numero medio di tabelle temporanee per procedura

* numero massimo di esecuzioni contemporanee della procedura

CREATE TABLE (Transact-SQL)

Utilizzo di variabili e parametri (Motore di database)

DECLARE @local_variable (Transact-SQL)

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.

CREATE TRIGGER (Transact-SQL)

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.

CREATE FUNCTION (Transact-SQL)

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

sp_xml_preparedocument (Transact-SQL)

Esecuzione di query su codice XML tramite OPENXML

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 funzionalità di SQL Server utilizzate. È consigliabile analizzare il carico di lavoro esistente eseguendo le attività seguenti in un ambiente di test di SQL Server:

  1. Attivare l'aumento automatico delle dimensioni di tempdb.

  2. Eseguire singole query o file di traccia del carico di lavoro e monitorare l'utilizzo dello spazio di tempdb.

  3. Eseguire operazioni di manutenzione degli indici, come la ricostruzione degli indici, e monitorare lo spazio occupato da tempdb.

  4. 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 funzionalità 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.