Condividi tramite

database tempdb

  • Articolo

Si applica a: SQL Server, Database SQL di Azure e Istanza gestita di SQL di Azure

Questo articolo illustra che l database di sistema tempdb è una risorsa globale a disposizione degli utenti connessi a un istanza di SQL Server, al database SQL di Azure o a un'istanza gestita di SQL di Azure.

Panoramica

Il database di sistema tempdb è 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 ha mai nulla da salvare 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 potrebbero variare leggermente a seconda dell'edizione 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 i parametri di dimensione e crescita correnti di tempdb, la query visualizza tempdb.sys.database_files.

Spostare i file di dati e di resoconto 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 Sì
ANSI_NULL_DEFAULT OFF Sì
ANSI_NULLS OFF Sì
ANSI_PADDING OFF Sì
ANSI_WARNINGS OFF Sì
ARITHABORT OFF Sì
AUTO_CLOSE OFF No
AUTO_CREATE_STATISTICS In... Sì
AUTO_SHRINK OFF No
AUTO_UPDATE_STATISTICS In... Sì
AUTO_UPDATE_STATISTICS_ASYNC OFF Sì
CHANGE_TRACKING OFF No
CONCAT_NULL_YIELDS_NULL OFF Sì
CURSOR_CLOSE_ON_COMMIT OFF Sì
CURSOR_DEFAULT GLOBAL Sì
Opzioni relative alla disponibilità del database ONLINE

MULTI_USER

READ_WRITE		 No

No

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

NONE per gli aggiornamenti di SQL Server		 Sì
PARAMETERIZATION SEMPLICE Sì
QUOTED_IDENTIFIER OFF Sì
READ_COMMITTED_SNAPSHOT OFF No
RECOVERY SEMPLICE No
RECURSIVE_TRIGGERS OFF Sì
Opzioni relative a Service Broker ENABLE_BROKER Sì
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 tempdb in database SQL di Azure differisce dal comportamento di SQL Server, Istanza gestita di SQL di Azure e SQL Server nelle VM di Azure.

tempdb in database SQL

I database singoli e in pool nel database SQL di Azure supportano le tabelle temporanee globali e le stored procedure temporanee globali nell'ambito del livello di database e archiviate in tempdb. Le tabelle temporanee globali e le stored procedure temporanee globali vengono condivise per le sessioni di tutti gli utenti all'interno dello stesso database. Le sessioni utente da 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, ogni database singolo in un server logico ha il proprio tempdb. In un pool elastico, tempdb è una risorsa condivisa per tutti i database dello stesso pool. Tuttavia, gli oggetti temporanei creati in un database non sono visibili agli altri database del pool.

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

Per altre informazioni sulle dimensioni tempdb in database SQL di Azure, rivedere:

tempdb in istanza gestita di SQL

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

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

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

Restrizioni

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. Se, ad esempio, le dimensioni definite per tempdb sono eccessivamente ridotte, il carico di elaborazione del sistema potrebbe essere in parte dovuto alla necessità di aumentare automaticamente le dimensioni di tempdb fino a raggiungere quelle necessarie per supportare il carico di lavoro a ogni riavvio dell'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.

All'interno di ogni filegroup i file di dati devono avere le stesse dimensioni, perché SQL Server usa un algoritmo di riempimento proporzionale che favorisce le allocazioni nei file con maggiore spazio disponibile. 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 dei file su un valore ragionevole e impostare lo stesso valore in tutti i file di dati, per impedire ai file di database tempdb di crescere di un valore troppo piccolo. Se l'aumento delle dimensioni dei file è troppo ridotto rispetto alla quantità di dati scritti in tempdb, tempdb potrebbe doversi espandere tramite eventi di crescita automatica. Gli eventi di aumento automatico delle dimensioni 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 di database DELAYED_DURABILITY è impostata su DISABLED per tempdb, SQL Server usa commit lazy per scaricare le modifiche del log tempdb 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, leggere -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 configurazione di SQL migliora il materiale sussidiario per l'allocazione iniziale del file tempdb. Il programma di configurazione 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.
  • È stata introdotta una nuova DMV sys.dm_tran_version_store_space_usage in SQL Server 2017 per tenere traccia dell'utilizzo dell'archivio versioni per ogni database. Questa nuova DMV sarà utile per monitorare tempdb nell'utilizzo dell'archivio delle versioni per gli amministratori di database che possono pianificare in modo proattivo il dimensionamento tempdb 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, diminuendo così l'utilizzo tempdb non necessario.

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, in modo da consentire la produttività massima del disco. In quanto 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 TempDB ottimizzati per la memoria rimuovono un collo di bottiglia nelle attese PAGELATCH in tempdb e sbloccano un nuovo livello di scalabilità. Per altre informazioni, guardare questa demo video su Come (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 della pagina PFS (Concurrent Page Free Space) riducono la contesa di latch di patch in tutti i database; un problema che è 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 Dettagli: pagine GAM, SGAM e PFS.
  • Per impostazione predefinita, una nuova installazione di SQL Server in Linux crea più file di dati tempdb basati sul 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 file tempdb è di 8 MB, con un aumento automatico di 64 MB. Questo comportamento è simile all'installazione predefinita di SQL Server in Windows.

Introdotto in SQL Server 2022 (16.x)

Metadati tempdb ottimizzati per la memoria

La contesa tra metadati in tempdb è tipicamente 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à database in memoria: metadati tempdb ottimizzati per la memoria.

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

Nota

Al momento la funzionalità di metadati TempDB ottimizzata per la memoria non è disponibile in database SQL di Azure o Istanza gestita di SQL di Azure.

Guardare questo video di sette minuti per le informazioni generali su come e quando usare i metadati tempdb ottimizzati per la memoria:

Configurare e utilizzare 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 inoltre un riavvio per avere effetto, anche se i metadati TempDB ottimizzati 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 alle viste di sistema tempdb nella stessa transazione della 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 nelle altre viste del catalogo di sistema in SQL Server, tutte le transazioni eseguite nelle viste di sistema sono nell'isolamento READ COMMITTED (o in questo caso READ COMMITTED SNAPSHOT).

  • 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 manualmente 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 specifici errori di memoria insufficiente 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 SQL Server dipende da molti fattori. Come precedentemente descritto, 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:

  • Attivare l'aumento automatico delle dimensioni di 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.

Monitoraggio dell'uso di tempdb

L'esaurimento dello spazio su disco in tempdb può causare gravi rotture nell'ambiente di produzione SQL Server. Può anche impedire alle applicazioni in esecuzione di completare le operazioni. Per eseguire il monitoraggio dello spazio su disco usato dai file di tempdb, è possibile usare la vista a gestione dinamica (DMV, Dynamic Management View) sys.dm_db_file_space_usage.

Ad esempio, i quattro script di esempio seguenti trovano la quantità di spazio disponibile in tempdb, la quantità di spazio utilizzata dall'archivio versioni, la quantità di spazio utilizzata dagli oggetti interni e la quantità di spazio utilizzata 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 spazio tempdb 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;

Ad esempio, usare lo script seguente per ottenere lo spazio tempdb utilizzato dagli oggetti interni nella sessione corrente, per le attività completate e in esecuzione:

-- 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;

Contenuto correlato