database tempdb

Si applica a:SQL ServerDatabase SQL diAzure Istanza gestita di SQL di Azure

Questo articolo descrive il tempdb database di sistema, una risorsa globale disponibile per tutti gli utenti connessi a un'istanza di SQL Server, database SQL di Azure o Istanza gestita di SQL di Azure.

Panoramica

Il tempdb database di sistema è una risorsa globale che contiene:

  • Oggetti utente temporanei creati in modo esplicito. Tra questi vi sono tabelle e indici temporanei globali o locali, stored procedure temporanee, variabili di tabella, tabelle restituite in funzioni con valori di tabella e cursori.

  • Oggetti interni creati dal motore di database. che includono:

    • Tabelle di lavoro in cui archiviare i risultati intermedi di operazioni di spooling e di ordinamento e cursori, nonché in cui archiviare LOB (Large Object) temporanei.
    • 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 è specificato SORT_IN_TEMPDB) o per alcune query GROUP BY, ORDER BY o UNION.

    Ogni oggetto interno usa un minimo di nove pagine: una pagina IAM e un extent di otto pagine. Per altre informazioni sulle pagine e sugli extent, vedere Pagine ed extent.

  • Archivi delle versioni, raccolte di pagine di dati che contengono le righe di dati che supportano le caratteristiche per il controllo delle versioni delle righe. Esistono due tipi di archivi delle versioni: uno comune e uno per la compilazione di indici online. Gli archivi delle versioni contengono:

    • versioni di riga generate dalle transazioni di modifica dei dati in un database in cui viene usato READ COMMITTED tramite isolamento del controllo delle versioni delle righe o transazioni di isolamento dello snapshot.
    • Versioni di riga generate dalle transazioni di modifica dei dati per le caratteristiche, ad esempio le operazioni sugli indici online, la caratteristica MARS (Multiple Active Result Set) e i trigger AFTER.

Le operazioni all'interno di tempdb sono a registrazione minima in modo che sia possibile eseguire il rollback delle transazioni. tempdb viene ricreato ogni volta che SQL Server viene avviato in modo che il sistema inizi sempre con una copia pulita del database. Poiché le tabelle e le stored procedure temporanee vengono eliminate automaticamente al momento della disconnessione e poiché al momento della chiusura del sistema non vi sono connessioni attive,

tempdb non deve mai essere salvato da una sessione di SQL Server a un'altra. Le operazioni di backup e ripristino non sono consentite in tempdb.

Proprietà fisiche di tempdb in SQL Server

Nella tabella seguente sono elencati i valori iniziali di configurazione dei dati tempdb e dei file di log in SQL Server. I valori sono basati sulle impostazioni predefinite per il database model. Le dimensioni di questi file possono variare leggermente per le diverse edizioni di SQL Server.

file Nome logico Nome fisico Dimensioni iniziali Aumento di dimensioni del file
Dati primari tempdev tempdb.mdf 8 megabyte Aumento automatico di 64 MB fino a quando il disco risulta pieno
File di dati secondari temp# tempdb_mssql_#.ndf 8 megabyte Aumento automatico di 64 MB fino a quando il disco risulta pieno
Log templog templog.ldf 8 megabyte Aumento automatico di 64 megabyte fino a un massimo di 2 terabyte

Il numero di file di dati secondari dipende dal numero di processori (logici) del computer. In generale, se il numero di processori logici è minore o uguale a otto, usare un numero di file di dati pari al numero dei processori logici. Se il numero di processori logici è maggiore di otto, usare otto file di dati. Se la contesa persiste, aumentare il numero di file di dati per multipli di quattro fino a quando la contesa si riduce a livelli accettabili o modificare il carico di lavoro o il codice.

Il valore predefinito per il numero di file di dati si basa sulle linee guida generali in KB 2154845.

Per controllare le dimensioni correnti e i parametri di crescita per tempdb, vista tempdb.sys.database_filesquery .

Spostare i file di dati e di log tempdb in SQL Server

Per spostare i file di dati e di log di tempdb, vedere Spostare i database di sistema.

Opzioni di database per tempdb in SQL Server

Nella tabella seguente vengono elencati i valori predefiniti per ogni opzione di database del database tempdb ed è indicato se è possibile modificare le varie opzioni. Per visualizzare le impostazioni correnti di queste opzioni, usare la vista del catalogo sys.databases .

Opzione di database Valore predefinito Modificabile
ALLOW_SNAPSHOT_ISOLATION OFF
ANSI_NULL_DEFAULT OFF
ANSI_NULLS OFF
ANSI_PADDING OFF
ANSI_WARNINGS OFF
ARITHABORT OFF
AUTO_CLOSE OFF No
AUTO_CREATE_STATISTICS In...
AUTO_SHRINK OFF No
AUTO_UPDATE_STATISTICS In...
AUTO_UPDATE_STATISTICS_ASYNC OFF
CHANGE_TRACKING OFF No
CONCAT_NULL_YIELDS_NULL OFF
CURSOR_CLOSE_ON_COMMIT OFF
CURSOR_DEFAULT GLOBAL
Opzioni relative alla disponibilità del database ONLINE

MULTI_USER

READ_WRITE
No

No

No
DATE_CORRELATION_OPTIMIZATION OFF
DB_CHAINING In... No
ENCRYPTION OFF No
MIXED_PAGE_ALLOCATION OFF No
NUMERIC_ROUNDABORT OFF
PAGE_VERIFY CHECKSUM per le nuove installazioni di SQL Server

NONE per gli aggiornamenti di SQL Server
PARAMETERIZATION SEMPLICE
QUOTED_IDENTIFIER OFF
READ_COMMITTED_SNAPSHOT OFF No
RECOVERY SEMPLICE No
RECURSIVE_TRIGGERS OFF
Opzioni relative a Service Broker ENABLE_BROKER
TRUSTWORTHY OFF No

Per una descrizione di queste opzioni di database, vedere Opzioni ALTER DATABASE SET (Transact-SQL).

tempdb in Azure SQL

Il comportamento di nel database SQL di tempdb Azure differisce dal comportamento di SQL Server, Istanza gestita di SQL di Azure e SQL Server nelle macchine virtuali di Azure.

tempdb nel database SQL

I database singoli e in pool nel database SQL di Azure supportano tabelle temporanee globali e stored procedure temporanee globali con ambito a livello di database e archiviate in tempdb. Le tabelle temporanee globali e le stored procedure temporanee globali vengono condivise per tutte le sessioni degli utenti all'interno dello stesso database. Le sessioni utente di altri database non possono accedere alle tabelle temporanee globali. Per altre informazioni, vedere Tabelle temporanee globali con ambito database (database SQL di Azure).

Per i database singoli e in pool nel database SQL di Azure, in tutti i database di sistema sono accessibili solo il database e tempdb il master database. Per altre informazioni, vedere Che cos'è un server logico in Azure?

Per altre informazioni sulle tempdb dimensioni nel database SQL di Azure, vedere:

tempdb in Istanza gestita di SQL

Istanza gestita di SQL di Azure supporta oggetti temporanei nello stesso modo di SQL Server, in cui tutte le tabelle temporanee globali e le stored procedure temporanee globali sono accessibili da tutte le sessioni utente all'interno della stessa istanza gestita. Analogamente, tutti i database di sistema sono accessibili.

È possibile configurare il numero di tempdb file, i relativi incrementi di crescita e le relative dimensioni massime. Per altre informazioni sulla configurazione delle tempdb impostazioni in Istanza gestita di SQL di Azure, vedere Configurare le impostazioni di tempdb per Istanza gestita di SQL di Azure.

Per altre informazioni sulle tempdb dimensioni in Istanza gestita di SQL di Azure, vedere Limiti delle risorse.

Limitazioni

Nel database tempdb non è possibile eseguire le operazioni seguenti:

  • Aggiunta di filegroup.
  • Backup o ripristino del database.
  • Modifica delle regole di confronto. Le regole di confronto predefinite corrispondono a quelle del server.
  • Modifica del proprietario del database. tempdb è di proprietà di sa.
  • Creazione di uno snapshot del database.
  • Eliminazione del database.
  • Eliminazione dell'utente guest dal database.
  • Abilitazione dell'acquisizione dei dati delle modifiche.
  • Partecipazione al mirroring del database.
  • Rimozione del filegroup primario, del file di dati primario o del file di log.
  • Ridenominazione del filegroup primario o del database.
  • Esecuzione di DBCC CHECKALLOC.
  • Esecuzione di DBCC CHECKCATALOG.
  • Impostazione del database su OFFLINE.
  • Impostazione del filegroup primario o del database su READ_ONLY.

Autorizzazioni

Qualsiasi utente può creare oggetti temporanei in tempdb. Gli utenti possono accedere solo ai propri oggetti, a meno che non ottengano ulteriori autorizzazioni. È possibile revocare l'autorizzazione di connessione a tempdb per impedire a un utente di usare tempdb. Non è consigliabile perché per alcune operazioni di routine è necessario usare tempdb.

Ottimizzare le prestazioni di tempdb in SQL Server

Le dimensioni e la posizione fisica del database tempdb possono influire sulle prestazioni di un sistema. Ad esempio, se le dimensioni definite per tempdb sono troppo piccole, parte del carico di elaborazione del sistema potrebbe essere impiegato con la scalabilità tempdb automatica delle dimensioni necessarie per supportare il carico di lavoro ogni volta che si riavvia l'istanza di SQL Server.

Se possibile, usare l'inizializzazione immediata dei file per migliorare le prestazioni delle operazioni di aumento delle dimensioni dei file di dati.

Preallocare lo spazio per tutti i file di tempdb impostando le relative dimensioni su un valore adeguato per il carico di lavoro tipico nell'ambiente. La preallocazione evita che tempdb si espanda con una frequenza eccessiva, con effetti negativi sulle prestazioni. È opportuno impostare il database tempdb per l'aumento automatico delle dimensioni, per aumentare lo spazio su disco per le eccezioni non pianificate.

I file di dati devono avere dimensioni uguali all'interno di ogni filegroup, perché SQL Server usa un algoritmo di riempimento proporzionale che favorisca le allocazioni nei file con più spazio libero. La suddivisione di tempdb in più file di dati di dimensioni uguali garantisce un livello elevato di efficienza parallela nelle operazioni che usano tempdb.

Impostare l'incremento di crescita del file su una dimensione ragionevole e impostarlo sullo stesso incremento in tutti i file di dati, per impedire che i file di tempdb database aumentino di un valore troppo piccolo. Se la crescita dei file è troppo piccola rispetto alla quantità di dati scritti in tempdb, tempdb potrebbe essere necessario espandere frequentemente tramite eventi di aumento automatico. Gli eventi di aumento automatico influiscono negativamente sulle prestazioni.

Per controllare i parametri di dimensione e crescita correnti di tempdb, usare la query seguente:

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

Posizionare il database tempdb in un sottosistema di I/O veloce. In presenza di molti dischi collegati direttamente, utilizzare lo striping del disco. File singoli o gruppi di file di dati di tempdb non devono necessariamente trovarsi in dischi o spindle diversi, a meno che non si verifichino anche colli di bottiglia di I/O.

Posizionare il database tempdb in dischi diversi da quelli usati dai database utente.

Nota

Anche se l'opzione DELAYED_DURABILITY di database è impostata su DISABLED per tempdb, SQL Server usa commit lazy per scaricare tempdb le modifiche del log sul disco, poiché tempdb viene creato all'avvio e non è necessario eseguire il processo di ripristino.

Miglioramenti delle prestazioni in tempdb per SQL Server

Introdotto in SQL Server 2016 (13.x)

  • Le tabelle temporanee e le variabili di tabella vengono memorizzate nella cache. La memorizzazione nella cache consente di eseguire molto rapidamente le operazioni di eliminazione e creazione degli oggetti temporanei. La memorizzazione nella cache riduce anche l'allocazione delle pagine e la contesa dei metadati.
  • Il protocollo di latch delle pagine di allocazione è stato migliorato per ridurre il numero di latch di UP (aggiornamento) usati.
  • L'overhead di registrazione per tempdb è stato ridotto per diminuire l'uso della larghezza di banda per le operazioni di I/O del disco nel file di log di tempdb.
  • Durante l'installazione di una nuova istanza vengono aggiunti più file di dati di tempdb. Questa attività può essere eseguita usando il nuovo controllo input dell'interfaccia utente nella sezione Configurazione del motore di database e il parametro della riga di comando /SQLTEMPDBFILECOUNT. Per impostazione predefinita, il programma di installazione aggiunge un numero di file di dati tempdb pari al numero di processori logici oppure a otto, a seconda di quale sia il valore inferiore.
  • Se ci sono più file di dati tempdb, le dimensioni di tutti i file aumentano contemporaneamente e della stessa quantità in base alle impostazioni di espansione specificate. Il flag di traccia 1117 non è più necessario. Per altre informazioni, vedere -T1117 e -T1118 modifiche per TEMPDB e database utente.
  • Tutte le allocazioni in tempdb usano extent uniformi. Il flag di traccia 1118 non è più necessario. Per ulteriori informazioni sui miglioramenti apportati alle prestazioni in tempdb, vedere l'articolo del blog TEMPDB - Files and Trace Flags and Updates, Oh My!.
  • Per il filegroup primario, la proprietà AUTOGROW_ALL_FILES è attivata e non può essere modificata.

Introdotto in SQL Server 2017 (14.x)

  • L'esperienza di installazione di SQL migliora le indicazioni per l'allocazione iniziale tempdb dei file. Il programma di installazione di SQL avvisa i clienti se le dimensioni iniziali del file sono impostate su un valore maggiore di 1 GB e se l'inizializzazione immediata dei file non è abilitata, impedendo ritardi di avvio dell'istanza.
  • In SQL Server 2017 è stata introdotta una nuova sys.dm_tran_version_store_space_usage DMV per tenere traccia dell'utilizzo dell'archivio delle versioni per ogni database. Questa nuova DMV sarà utile nel monitoraggio tempdb dell'utilizzo dell'archivio delle versioni per gli amministratori di database che possono pianificare tempdb in modo proattivo il dimensionamento in base al requisito di utilizzo dell'archivio delle versioni per ogni database.
  • Nuove funzionalità di elaborazione di query intelligenti, ad esempio join adattivi e feedback delle concessioni di memoria, riducono le perdite di memoria nelle esecuzioni consecutive di una query, riducendo l'utilizzo non necessario tempdb .

Introdotto in SQL Server 2019 (15.x)

  • A partire da SQL Server 2019 (15.x), SQL Server non usa l'opzione FILE_FLAG_WRITE_THROUGH quando si aprono i file per tempdb consentire la velocità effettiva massima del disco. Poiché tempdb viene ricreato all'avvio di SQL Server, queste opzioni non sono necessarie perché sono per altri database di sistema e database utente per la coerenza dei dati. Per altre informazioni su FILE_FLAG_WRITE_THROUGH, vedere Algoritmi di registrazione e archiviazione dei dati che estendono l'affidabilità dei dati in SQL Server.
  • I metadati ottimizzati tempdb per la memoria rimuove un collo di bottiglia nelle attese PAGELATCH in tempdbe sblocca un nuovo livello di scalabilità. Per altre informazioni, guardare questa demo video su Procedura (e Quando): Metadati tempDB ottimizzati per la memoria. Per altre informazioni, vedere Monitoraggio e risoluzione dei problemi relativi ai metadati tempdb ottimizzati per la memoria.
  • Gli aggiornamenti delle pagine PFS (Concurrent Page Free Space) riducono la contesa di latch di patch in tutti i database, un problema più comunemente riscontrato in tempdb. Questo miglioramento cambia la modalità di gestione della concorrenza per gli aggiornamenti delle pagine PFS, in modo che l'aggiornamento possa essere eseguito in un latch condiviso anziché in un latch esclusivo. Questo comportamento è abilitato per impostazione predefinita in tutti i database (incluso TempDB) a partire da SQL Server 2019 (15.x). Per altre informazioni sulle pagine PFS, vedere Sotto le quinte: pagine GAM, SGAM e PFS.
  • Per impostazione predefinita, una nuova installazione di SQL Server in Linux crea più tempdb file di dati, in base al numero di core logici (con un massimo di otto file di dati). Questo non vale per gli aggiornamenti sul posto di una versione principale o secondaria. Ogni tempdb file è di 8 MB, con una crescita automatica di 64 MB. Questo comportamento è simile all'installazione predefinita di SQL Server in Windows.

Introdotto in SQL Server 2022 (16.x)

  • SQL Server 2022 (16.x) ha introdotto una scalabilità migliorata con miglioramenti della concorrenza della latch di pagina di sistema. Gli aggiornamenti simultanei delle pagine della mappa di allocazione globale (GAM, Global Allocation Map) e delle pagine della mappa di allocazione globale condivisa (SGAM, Shared Global Allocation Map) riducono la contesa dei latch di pagina durante l'allocazione/deallocazione di pagine ed extent di dati. Questi miglioramenti si applicano a tutti i database utente e in particolare a vantaggi tempdb elevati per i carichi di lavoro. Per altre informazioni sulle pagine GAM e SGAM, vedere Sotto le quinte: pagine GAM, SGAM e PFS. Per altre informazioni, guardare System Page Latch Concurrency Enhancements (Ep. 6) | Dati esposti.

Metadati tempdb ottimizzati per la memoria

La contesa dei metadati in tempdb è storicamente stata un collo di bottiglia per la scalabilità per molti carichi di lavoro in esecuzione in SQL Server. SQL Server 2019 (15.x) introduce una nuova funzionalità che fa parte della famiglia di funzionalità di database in memoria: metadati ottimizzati tempdb per la memoria.

Questa funzionalità rimuove in modo efficace questo collo di bottiglia e sblocca un nuovo livello di scalabilità per tempdbcarichi di lavoro pesanti. In SQL Server 2019 (15.x), le tabelle di sistema coinvolte nella gestione dei metadati delle tabelle temporanee possono essere spostate in tabelle senza latch, non durevoli e ottimizzate per la memoria.

Nota

Attualmente la funzionalità dei metadati ottimizzati tempdb per la memoria non è disponibile nel database SQL di Azure o in Istanza gestita di SQL di Azure.

Guardare questo video di sette minuti per una panoramica di come e quando usare i metadati ottimizzati tempdb per la memoria:

Configurare e usare i metadati tempdb ottimizzati per la memoria

Per accettare questa nuova funzionalità, usare lo script seguente:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Per rendere effettiva questa modifica della configurazione è necessario riavviare il servizio.

È possibile verificare se tempdb è ottimizzato per la memoria usando il comando T-SQL seguente:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Se il server non viene avviato per qualsiasi motivo dopo aver abilitato i metadati tempdb ottimizzati per la memoria, è possibile ignorare la funzionalità avviando l'istanza di SQL Server con la configurazione minima usando l'opzione di avvio -f. In questo modo sarà possibile disabilitare la funzionalità e riavviare SQL Server in modalità normale.

Per proteggere il server da potenziali condizioni di memoria insufficiente, è possibile associare tempdb a un pool di risorse. Questa operazione viene eseguita con il comando ALTER SERVER anziché i passaggi che si seguono normalmente per associare un pool di risorse a un database.

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');

Questa modifica richiede anche un riavvio per rendere effettivo anche se i metadati ottimizzati tempdb per la memoria sono già abilitati.

Limitazioni degli elementi tempdb ottimizzati per la memoria

  • L'attivazione o la disattivazione della funzionalità non avviene in modo dinamico. A causa delle modifiche intrinseche che devono essere apportate alla struttura del database tempdb, è necessario un riavvio per abilitare o disabilitare la funzionalità.

  • È possibile che una singola transazione non possa accedere alle tabelle ottimizzate per la memoria in più di un database. Tutte le transazioni che interessano una tabella ottimizzata per la memoria in un database utente non saranno in grado di accedere alle viste di sistema tempdb nella stessa transazione. Se si tenta di accedere tempdb alle viste di sistema nella stessa transazione di una tabella ottimizzata per la memoria in un database utente, viene visualizzato l'errore seguente:

    A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
    

    Esempio

    BEGIN TRAN;
    
    SELECT *
    FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb
    
    INSERT INTO <user database>.<schema>.<mem-optimized table>
    VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail
    
    COMMIT TRAN;
    
  • Poiché le query eseguite nelle tabelle ottimizzate per la memoria non supportano gli hint di blocco e isolamento, le query eseguite nelle viste di catalogo tempdb ottimizzate per la memoria non rispetteranno gli hint di blocco e isolamento. Come per altre viste del catalogo di sistema in SQL Server, tutte le transazioni sulle viste di sistema si trovano in READ COMMITTED (o in questo caso) READ COMMITTED SNAPSHOTisolamento.

  • Non è possibile creare indici columnstore nelle tabelle temporanee quando sono abilitati i metadati tempdb ottimizzati per la memoria.

  • A causa della limitazione per gli indici columnstore, l'uso della stored procedure di sistema sp_estimate_data_compression_savings con il parametro di compressione dei dati COLUMNSTORE o COLUMNSTORE_ARCHIVE non è supportato quando sono abilitati i metadati tempdb ottimizzati per la memoria.

  • Una stored procedure di sistema è disponibile per fare in modo che il motore in memoria rilasci la memoria correlata alle righe eliminate di dati in memoria idonei per l'operazione di Garbage Collection. Ciò può essere utile per la risoluzione di errori di memoria specifici dei metadati tempdb ottimizzati per la memoria (HkTempDB). Per altre informazioni, vedere sys.sp_xtp_force_gc (Transact-SQL).

Nota

Queste limitazioni si applicano solo quando si fa riferimento alle visualizzazioni di sistema tempdb. È possibile creare una tabella temporanea nella stessa transazione quando si accede a una tabella ottimizzata per la memoria in un database utente, se necessario.

Pianificazione delle capacità per tempdb in SQL Server

La determinazione delle dimensioni appropriate per tempdb in un ambiente di produzione di SQL Server dipende da molti fattori. Come descritto in precedenza, questi fattori includono il carico di lavoro esistente e le funzionalità di SQL Server usate.

È consigliabile analizzare il carico di lavoro esistente eseguendo le attività seguenti in un ambiente di test di SQL Server:

  • Impostare l'aumento automatico su per tempdb.
  • Eseguire singole query o file di traccia del carico di lavoro e monitorare l'uso dello spazio da parte di tempdb.
  • Eseguire operazioni di manutenzione degli indici, ad esempio la ricompilazione degli indici stessi e monitorare lo spazio occupato da tempdb.
  • Usare i valori di utilizzo dello spazio dei passaggi precedenti per stimare l'utilizzo totale del carico di lavoro. Modificare questo valore per l'attività simultanea proiettata, quindi impostare le dimensioni di tempdb di conseguenza.

Monitorare l'uso di tempdb

L'esaurimento dello spazio su disco in tempdb può causare interruzioni significative nell'ambiente di produzione di SQL Server. Può anche impedire alle applicazioni in esecuzione di completare le operazioni. È possibile usare la sys.dm_db_file_space_usage visualizzazione a gestione dinamica per monitorare lo spazio su disco usato nei tempdb file.

Ad esempio, i quattro script di esempio seguenti trovano la quantità di spazio disponibile in tempdb, la quantità di spazio usata dall'archivio versioni, la quantità di spazio usata dagli oggetti interni e la quantità di spazio usata dagli oggetti utente:

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Per eseguire il monitoraggio dell'allocazione delle pagine o dell'attività di deallocazione in tempdb a livello di sessione o di attività, è possibile usare le DMV 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 usano una grande quantità di spazio su disco in tempdb. È possibile usare inoltre diversi contatori che consentono di monitorare lo spazio libero disponibile in tempdb e le risorse che stanno usando tempdb.

Ad esempio, usare lo script seguente per ottenere lo tempdb spazio utilizzato dagli oggetti interni in tutte le attività attualmente in esecuzione in ogni sessione:

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Usare lo script seguente per trovare lo tempdb spazio utilizzato dagli oggetti interni nella sessione corrente, sia per le attività in esecuzione che per le attività completate:

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

Passaggi successivi