database tempdb

Articolo
03/22/2025

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Database SQL in Microsoft Fabric

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

Panoramica

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

gli oggetti User creati in modo esplicito. Essi includono:
- Tabelle e indici temporanei globali o locali in queste tabelle
- Procedure memorizzate temporanee
- Variabili di tabella
- Tabelle restituite nelle funzioni con valori di tabella
- Cursori
Gli oggetti utente che possono essere creati in un database utente possono essere creati anche in tempdb, ma vengono creati senza una garanzia di durabilità e vengono eliminati quando l'istanza del motore di database viene riavviata.
Oggetti interni creati dal motore di database. Essi includono:
- Tabelle di lavoro per archiviare i risultati intermedi di spooling, cursori, ordinamenti e l'archiviazione temporanea di oggetti di grandi dimensioni (LOB).
- File di lavoro per operazioni hash join o hash aggregate.
- Risultati intermedi dell'ordinamento per operazioni come la creazione o la ricompilazione di indici (se SORT_IN_TEMPDB è specificato), o per alcune query quali GROUP BY, ORDER BY o UNION.
Ogni oggetto interno usa un minimo di nove pagine: una pagina IAM e un'estensione di otto pagine. Per ulteriori informazioni sulle pagine e sulle estensioni, vedere Pagine ed estensioni.
Version archivia, ovvero raccolte di pagine di dati che contengono le righe di dati che supportano il controllo delle versioni delle righe . Esistono due tipi: un archivio delle versioni comuni e un archivio delle versioni di compilazione degli indici online. Gli archivi delle versioni contengono:
- Versioni di riga generate dalle transazioni di modifica dei dati in un database che utilizza transazioni di isolamento basate sul controllo delle versioni delle righe READ COMMITTED o SNAPSHOT.
- Versioni di riga generate dalle transazioni di modifica dei dati per funzionalità come le operazioni sugli indici online, i set di risultati multipli attivi (MARS), e i trigger AFTER.

Le operazioni all'interno di tempdb vengono registrate in modo minimo. tempdb viene ricreato ogni volta che viene avviato il motore di database in modo che il sistema inizi sempre con un database tempdb vuoto. Le stored procedure temporanee e le tabelle temporanee locali vengono eliminate automaticamente quando la sessione che li ha creati si disconnette.

tempdb non è mai necessario salvare nulla da un periodo di tempo di attività del motore di database a un altro. 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
Registro	`templog`	`templog.ldf`	8 megabyte	Aumento automatico di 64 megabyte fino a un massimo di 2 terabyte

Tutti i file di dati tempdb devono avere sempre le stesse dimensioni iniziali e gli stessi parametri di crescita.

Numero di file di dati tempdb

A seconda della versione del motore di database, della relativa configurazione e del carico di lavoro, tempdb potrebbe richiedere più file di dati per ridurre la contesa di allocazione.

Il numero totale consigliato di file di dati dipende dal numero di processori logici nel computer. Come indicazioni generali:

Se il numero di processori logici è minore o uguale a otto, usare lo stesso numero di file di dati.
Se il numero di processori logici è maggiore di otto, usare otto file di dati.
Se la contesa nell'allocazione di tempdb viene ancora osservata, aumentare il numero di file di dati a multipli di quattro fino a quando la contesa non diminuisce a livelli accettabili o apportare modifiche al carico di lavoro.

Per altre informazioni, vedere Raccomandazioni per ridurre la contesa di allocazione nel database tempdb di SQL Server.

Per controllare le dimensioni correnti e i parametri di crescita per tempdb, usare la vista del catalogo sys.database_files in tempdb.

Spostare i file di dati e di log di 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
`ACCELERATED_DATABASE_RECOVERY`	`OFF`	No
`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`	`ON`	Sì
`AUTO_SHRINK`	`OFF`	No
`AUTO_UPDATE_STATISTICS`	`ON`	Sì
`AUTO_UPDATE_STATISTICS_ASYNC`	`OFF`	Sì
`AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)`	`OFF`	No
`CHANGE_TRACKING`	`OFF`	No
`COMPATIBILITY_LEVEL`	Dipende dalla versione del motore di database. Per ulteriori informazioni, vedere ALTER DATABASE (Transact-SQL) livello di compatibilità.	Sì
`CONCAT_NULL_YIELDS_NULL`	`OFF`	Sì
`CONTAINMENT`	`NONE`	No
`CURSOR_CLOSE_ON_COMMIT`	`OFF`	Sì
`CURSOR_DEFAULT`	`GLOBAL`	Sì
Stato del database	`ONLINE`	No
Aggiornamento del database	`READ_WRITE`	No
L'accesso utente del database	`MULTI_USER`	No
`DATE_CORRELATION_OPTIMIZATION`	`OFF`	Sì
`DB_CHAINING`	`ON`	No
`DELAYED_DURABILITY`	`DISABLED` Indipendentemente da questa opzione, la durabilità ritardata è sempre abilitata su `tempdb`.	Sì
`ENCRYPTION`	`OFF`	No
`MIXED_PAGE_ALLOCATION`	`OFF`	No
`NUMERIC_ROUNDABORT`	`OFF`	Sì
`PAGE_VERIFY`	`CHECKSUM` per le nuove installazioni di SQL Server È possibile che il valore esistente `PAGE_VERIFY` venga conservato quando viene aggiornata un'istanza di SQL Server.	Sì
`PARAMETERIZATION`	`SIMPLE`	Sì
`QUOTED_IDENTIFIER`	`OFF`	Sì
`READ_COMMITTED_SNAPSHOT`	`OFF`	No
`RECOVERY`	`SIMPLE`	No
`RECURSIVE_TRIGGERS`	`OFF`	Sì
Intermediario di Servizio	`ENABLE_BROKER`	Sì
`TARGET_RECOVERY_TIME`	60	Sì
`TEMPORAL_HISTORY_RETENTION`	`ON`	Sì
`TRUSTWORTHY`	`OFF`	No

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

Tempdb nel Database SQL di Azure

Nel database SQL di Azure alcuni aspetti del comportamento e della configurazione di tempdb sono diversi da SQL Server.

Per i database singoli, ogni database in un server logico ha un proprio tempdb. In un pool elastico, tempdb è una risorsa condivisa per tutti i database nello stesso pool, ma gli oggetti temporanei creati da un database non sono visibili ad altri database nello stesso pool elastico.

Gli oggetti in tempdb, incluse le viste del catalogo e le viste a gestione dinamica (DMV), sono accessibili tramite un riferimento tra database al database tempdb. Ad esempio, è possibile eseguire una query sulla vista sys.database_files:

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Le tabelle temporanee globali in database SQL di Azure sono con ambito database. Per altre informazioni, vedere tabelle temporanee globali con ambito database nel database SQL di Azure.

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

Modello di acquisto vCore: database singoli e database in pool
Modello di acquisto DTU: database singoli e database in pool

tempdb nell'istanza gestita di SQL

In Istanza gestita di SQL di Azure alcuni aspetti del comportamento di tempdb e della configurazione predefinita sono diversi da SQL Server.

È 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.

Istanza gestita di SQL di Azure supporta gli 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 di SQL.

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

tempdb nel database SQL all'interno di Fabric

Per altre informazioni sulle dimensioni di tempdb nel database SQL in Microsoft Fabric, vedere la sezione Limiti delle risorse in confronto delle funzionalità di : Database SQL di Azure e database SQL in Microsoft Fabric.

Analogamente a Azure SQL Database, le tabelle temporanee globali in database SQL in Microsoft Fabric hanno un ambito di database. Per ulteriori informazioni, vedere tabelle temporanee globali con ambito specificato nel database SQL di Azure.

Restrizioni

Nel database tempdb non è possibile eseguire le operazioni seguenti:

Aggiunta di filegroup.
Backup o ripristino del database.
Modifica della collazione. La collatione predefinita è quella 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 della funzionalità di Change Data Capture.
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.
In esecuzione 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 non temporanei in tempdb, a meno che non ricevano autorizzazioni aggiuntive.

È possibile revocare l'autorizzazione CONNECT di tempdb impedire a un utente o a un ruolo del database di usare tempdb. Questa operazione non è consigliata perché molte operazioni richiedono l'uso di tempdb.

Ottimizzare le prestazioni di tempdb in SQL Server

Le dimensioni e il posizionamento fisico dei file tempdb possono influire sulle prestazioni. Ad esempio, se le dimensioni iniziali di tempdb sono troppo piccole, potrebbero essere richiesti tempo e risorse per aumentare automaticamente tempdb fino alle dimensioni necessarie per supportare il carico di lavoro ogni volta che l'istanza del motore di database viene riavviata.

Se possibile, usare l'inizializzazione immediata dei file per migliorare le prestazioni delle operazioni di crescita dei file di dati.
- A partire da SQL Server 2022 (16.x), anche gli eventi di crescita dei file di log delle transazioni fino a 64 MB possono trarre vantaggio dall'inizializzazione immediata dei file. Per altre informazioni, vedere inizializzazione immediata dei file e il log delle transazioni.
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 impedisce alla tempdb di ridimensionarsi automaticamente troppo spesso, il che può influire negativamente sulle prestazioni.
I file nel database tempdb devono essere impostati su aumento automatico per fornire spazio durante gli eventi di crescita non pianificati.
La divisione di tempdb in più file di dati di dimensioni uguali può migliorare l'efficienza delle operazioni che usano tempdb.
- Per evitare uno squilibrio di allocazione dei dati, i file di dati devono avere gli stessi parametri di dimensioni iniziali e di crescita perché il motore di database usa un algoritmo di riempimento proporzionale che favorisca le allocazioni nei file con più spazio disponibile.
- Impostare l'incremento della crescita dei file su una dimensione ragionevole, ad esempio 64 MB, e aumentare lo stesso incremento di crescita per tutti i file di dati per evitare lo squilibrio della crescita.

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

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Posizionare il database tempdb in un sottosistema di I/O veloce. I singoli file di dati o i gruppi di file di dati tempdb non devono necessariamente trovarsi su dischi diversi, a meno che non si riscontrino colli di bottiglia di I/O a livello di disco.

Se è presente una contesa di I/O tra tempdb e database utente, inserire tempdb file su dischi diversi dai dischi usati dai database utente.

Nota

Per migliorare le prestazioni, la durabilità ritardata di è sempre abilitata su tempdb anche se l'opzione del database DELAYED_DURABILITY è impostata su DISABLED. Poiché tempdb viene ricreato all'avvio, non esegue un processo di ripristino e non fornisce una garanzia di durabilità.

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 UP (aggiornamento) usati.
L'overhead di registrazione per tempdb è stato ridotto per diminuire il consumo della larghezza di banda I/O del disco sul file di log tempdb.
Il programma di installazione di SQL aggiunge più file di dati tempdb durante una nuova installazione dell'istanza. Esaminare i consigli e configurare il tempdb nella pagina di configurazione del motore di database di SQL Setup, oppure usare il parametro da riga di comando /SQLTEMPDBFILECOUNT. Per impostazione predefinita, il programma di installazione di SQL aggiunge tanti file di dati tempdb quanti sono i processori logici, o fino a un massimo di 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 utilizzano estensioni 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!.
La proprietà AUTOGROW_ALL_FILES è sempre attivata per il filegroup PRIMARY.

Introdotto in SQL Server 2017 (14.x)

L'esperienza della configurazione di SQL migliora le indicazioni 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.
La vista a gestione dinamica sys.dm_tran_version_store_space_usage tiene traccia dell'utilizzo dell'archivio delle versioni per ogni database. Questa DMV è utile per gli amministratori di database che vogliono pianificare in modo proattivo il ridimensionamento di tempdb in base alle esigenze di utilizzo dell'archivio delle versioni per ogni database.
l'elaborazione intelligente delle query funzionalità, ad esempio join adattivi e feedback delle concessioni di memoria, riducono le perdite di memoria in esecuzioni consecutive di una query, riducendo tempdb'utilizzo.

Introdotto in SQL Server 2019 (15.x)

Il motore di database non usa l'opzione FILE_FLAG_WRITE_THROUGH all'apertura di file tempdb per consentire la velocità effettiva massima del disco. Poiché tempdb viene ricreato all'avvio, questa opzione non è necessaria per garantire la durabilità 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.
metadati TempDB ottimizzati per la memoria rimuovono la contesa sui metadati temporanei degli oggetti in tempdb.
Gli aggiornamenti simultanei delle pagine Page Free Space (PFS) riducono la contesa di latch di pagina in tutti i database, un problema riscontrato più comunemente in tempdb. Questo miglioramento modifica la gestione della concorrenza degli aggiornamenti delle pagine PFS in modo che possano essere aggiornati con un latch condiviso, anziché un latch esclusivo. Questo comportamento è attivato per impostazione predefinita in tutti i database (incluso tempdb) a partire da SQL Server 2019 (15.x). Per ulteriori informazioni sulle pagine PFS, leggere Dietro le quinte: 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 si applica agli aggiornamenti in loco di una versione principale o secondaria. Ogni tempdb file di dati è 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)

Introdotto una maggiore scalabilità con i miglioramenti della concorrenza nei latch delle pagine 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 sui latch di pagina durante l'allocazione/deallocazione di pagine ed estensioni di dati. Questi miglioramenti si applicano a tutti i database degli utenti e offrono vantaggi significativi soprattutto per i carichi di lavoro pesanti in tempdb. Per altre informazioni sulle pagine GAM e SGAM, leggere Sotto il cofano: 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 contenzione dei metadati degli oggetti temporanei è stata storicamente un ostacolo alla scalabilità per molti carichi di lavoro in SQL Server. A tale scopo, SQL Server 2019 (15.x) ha introdotto una funzionalità che fa parte del database in memoria famiglia di funzionalità: metadati TempDB ottimizzati per la memoria.

L'abilitazione della funzionalità di metadati TempDB ottimizzati per la memoria elimina questo collo di bottiglia per i carichi di lavoro che erano precedentemente limitati dalla contesa sui metadati degli oggetti temporanei all'interno di tempdb. A partire da SQL Server 2019 (15.x), le tabelle di sistema coinvolte nella gestione dei metadati di oggetti temporanei possono diventare tabelle senza latch, non durevoli e ottimizzate per la memoria.

Mancia

A causa delle limitazioni correnti , è consigliabile abilitare i metadati TempDB ottimizzati per la memoria solo quando si verifica una contesa di metadati oggetto e influisce in modo significativo sui carichi di lavoro.

La query di diagnostica seguente restituisce una o più righe se si verifica una contesa temporanea dei metadati dell'oggetto. Ogni riga rappresenta una tabella di sistema e restituisce il numero di sessioni che si contendono l'accesso a tale tabella al momento dell'esecuzione di questa query di diagnostica.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Guardare questo video di sette minuti per una panoramica di come e quando usare la funzionalità di metadati TempDB ottimizzata per la memoria:

Nota

Attualmente, la funzionalità metadati TempDB ottimizzata per la memoria non è disponibile nel database SQL di Azure, nel database SQL di Microsoft Fabric e in Istanza gestita di SQL di Azure.

Configurare e usare i metadati TempDB ottimizzati per la memoria

Le sezioni seguenti includono i passaggi per abilitare, configurare, verificare e disabilitare la funzionalità metadati TempDB ottimizzata per la memoria.

Abilitare

Per abilitare questa funzionalità, usare lo script seguente:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Per altre informazioni, vedere ALTER SERVER. 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 valore restituito è 1 e si è verificato un riavvio dopo l'abilitazione della funzionalità, la funzionalità è abilitata.

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 del motore di database con configurazione minima usando l'opzione di avvio -f. È quindi possibile disabilitare la funzionalità e rimuovere l'opzione -f per riavviare il motore di database in modalità normale.

Collegarsi al pool di risorse per limitare l'utilizzo della memoria

Per proteggere il server da potenziali condizioni di memoria insufficiente, è consigliabile associare tempdb a un resource governor pool di risorse che limita la memoria utilizzata dai metadati TempDB ottimizzati per la memoria. Lo script di esempio seguente crea un pool di risorse e imposta la memoria massima su 20%, abilita Resource Governore associa tempdb al pool di risorse.

Questo esempio usa 20% come limite di memoria a scopo dimostrativo. Il valore ottimale nell'ambiente potrebbe essere maggiore o minore a seconda del carico di lavoro e può cambiare nel tempo se il carico di lavoro cambia.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

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

Questa modifica richiede anche un riavvio del servizio, anche se i metadati TempDB ottimizzati per la memoria sono già abilitati.

Verificare l'associazione del pool di risorse e monitorare l'utilizzo della memoria

Per verificare che tempdb sia associato a un pool di risorse e per monitorare le statistiche di utilizzo della memoria per il pool, usare la query seguente:

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Rimuovere l'associazione del pool di risorse

Per rimuovere l'associazione del pool di risorse mantenendo abilitati i metadati TempDB ottimizzati per la memoria, eseguire il comando seguente e riavviare il servizio:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Disabilitare

Per disabilitare i metadati TempDB ottimizzati per la memoria, eseguire il comando seguente e riavviare il servizio:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Limitazioni dei metadati TempDB ottimizzati per la memoria

L'abilitazione o la disabilitazione della funzionalità di metadati TempDB ottimizzata per la memoria richiede un riavvio.
In alcuni casi, potresti osservare un utilizzo elevato della memoria da parte del clerk della memoria MEMORYCLERK_XTP, causando errori di esaurimento della memoria nel carico di lavoro.

Per visualizzare l'utilizzo della memoria da parte del MEMORYCLERK_XTP clerk rispetto a tutti gli altri clerk di memoria e rispetto alla memoria del server di destinazione, eseguire la query seguente:
```
SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
       SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
       SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
FROM sys.dm_os_memory_clerks
CROSS JOIN sys.dm_os_sys_info;
```
Se la memoria MEMORYCLERK_XTP è elevata, è possibile attenuare il problema nel modo seguente:
- Associare il database tempdb a un pool di risorse che limita il consumo di memoria in base ai metadati TempDB ottimizzati per la memoria. Per ulteriori informazioni, vedere Configurare e utilizzare i metadati tempdb ottimizzati per la memoria.
- È possibile eseguire periodicamente una stored procedure di sistema per liberare la memoria MEMORYCLERK_XTP non più necessaria. Per altre informazioni, vedere sys.sp_xtp_force_gc (Transact-SQL).
Per maggiori informazioni, vedere errori di memoria insufficiente nei metadati tempdb ottimizzati per la memoria (HkTempDB).
Quando si usa In-Memory OLTP, una singola transazione non può accedere alle tabelle ottimizzate per la memoria in più di un database. Per questo motivo, qualsiasi transazione di lettura o scrittura che coinvolga una tabella ottimizzata per la memoria in un database utente non può nemmeno accedere alle viste di sistema tempdb nello stesso periodo di transazione. In questo caso, viene visualizzato l'errore 41317:
```
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.
```
Questa limitazione si applica anche ad altri scenari in cui una singola transazione tenta di accedere alle tabelle ottimizzate per la memoria in più di un database.

Ad esempio, è possibile che venga visualizzato l'errore 41317 se si esegue una query sulla vista del catalogo sys.stats in un database utente contenente tabelle ottimizzate per la memoria. Ciò si verifica perché la query tenta di accedere alle statistiche e ai dati su una tabella ottimizzata per la memoria nel database utente e ai metadati ottimizzati per la memoria in tempdb.

Lo script di esempio seguente genera questo errore quando i metadati tempDB ottimizzati per la memoria sono abilitati:
```
BEGIN TRAN;

-- Create an In-memory OLTP transaction that accesses a system view in tempdb
SELECT name
FROM tempdb.sys.tables;

-- An attempt to create an In-memory OLTP transaction in the user database fails
INSERT INTO <user database>.<schema>.<memory-optimized table>
VALUES (1);

COMMIT TRAN;
```
Nota

Questa limitazione non si applica alle tabelle temporanee. È possibile creare una tabella temporanea nella stessa transazione che accede a una tabella ottimizzata per la memoria in un database utente.
Le query sulle viste del catalogo di sistema usano sempre il livello di isolamento READ COMMITTED. Quando i metadati TempDB ottimizzati per la memoria sono abilitati, le query sulle viste del catalogo di sistema in tempdb usano il livello di isolamento SNAPSHOT. In entrambi i casi, i suggerimenti di blocco non vengono rispettati.
Non è possibile creare indici columnstore nelle tabelle temporanee quando sono abilitati i metadati TempDB ottimizzati per la memoria.
- Di conseguenza, 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 i metadati di TempDB ottimizzati per la memoria sono abilitati.

Pianificazione delle capacità per tempdb in SQL Server

La determinazione delle dimensioni appropriate per tempdb dipende da molti fattori. Questi fattori includono il carico di lavoro e le funzionalità del motore di database usate.

È consigliabile analizzare tempdb consumo di spazio eseguendo le attività seguenti in un ambiente di test in cui è possibile riprodurre il carico di lavoro tipico:

Abilitare aumento automatico per i file di tempdb. Tutti i file di dati tempdb devono avere le stesse dimensioni iniziali e la stessa configurazione di aumento automatico.
Riprodurre il carico di lavoro e monitorare l'uso dello spazio tempdb.
Se usi la manutenzione periodica dell'indice , esegui le operazioni di manutenzione e monitora lo spazio tempdb.
Usare i valori massimi usati dai 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 interruzioni significative e tempi di inattività dell'applicazione. È possibile usare la sys.dm_db_file_space_usage visualizzazione a gestione dinamica per monitorare lo spazio usato nei file di tempdb.

Ad esempio, lo script di esempio seguente trova:

Spazio libero in tempdb (non considerando lo spazio libero su disco che potrebbe essere disponibile per la crescita di tempdb)
Spazio usato dall'archivio versioni
Spazio usato dagli oggetti interni
Spazio usato dagli oggetti utente

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Per monitorare a livello di sessione o attività l'attività di allocazione o deallocazione delle pagine in tempdb, è possibile utilizzare le visualizzazioni di gestione dinamica sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Queste viste consentono di identificare query, tabelle temporanee o variabili di tabella che usano grandi quantità di spazio tempdb.

Ad esempio, usare lo script di esempio seguente per ottenere lo spazio tempdb allocato e deallocato da oggetti interni in tutte le attività attualmente in esecuzione in ogni sessione:

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 di esempio seguente per trovare lo spazio allocato tempdb e attualmente consumato da oggetti interni e utente per ogni sessione e richiesta, sia in esecuzione che completata.

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Condividi tramite

database tempdb

Panoramica

Proprietà fisiche di tempdb in SQL Server

Numero di file di dati tempdb

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

Opzioni di database per tempdb in SQL Server

Tempdb nel Database SQL di Azure

tempdb nell'istanza gestita di SQL

tempdb nel database SQL all'interno di Fabric

Restrizioni

Autorizzazioni

Ottimizzare le prestazioni di tempdb in SQL Server

Miglioramenti delle prestazioni in tempdb per SQL Server

Introdotto in SQL Server 2016 (13.x)

Introdotto in SQL Server 2017 (14.x)

Introdotto in SQL Server 2019 (15.x)

Introdotto in SQL Server 2022 (16.x)

Metadati TempDB ottimizzati per la memoria

Configurare e usare i metadati TempDB ottimizzati per la memoria

Abilitare

Collegarsi al pool di risorse per limitare l'utilizzo della memoria

Verificare l'associazione del pool di risorse e monitorare l'utilizzo della memoria

Rimuovere l'associazione del pool di risorse

Disabilitare

Limitazioni dei metadati TempDB ottimizzati per la memoria

Pianificazione delle capacità per tempdb in SQL Server

Monitoraggio dell'uso di tempdb

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

database tempdb

Panoramica

Proprietà fisiche di tempdb in SQL Server

Numero di file di dati tempdb

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

Opzioni di database per tempdb in SQL Server

Tempdb nel Database SQL di Azure

tempdb nell'istanza gestita di SQL

tempdb nel database SQL all'interno di Fabric

Restrizioni

Autorizzazioni

Ottimizzare le prestazioni di tempdb in SQL Server

Miglioramenti delle prestazioni in tempdb per SQL Server

Introdotto in SQL Server 2016 (13.x)

Introdotto in SQL Server 2017 (14.x)

Introdotto in SQL Server 2019 (15.x)

Introdotto in SQL Server 2022 (16.x)

Metadati TempDB ottimizzati per la memoria

Configurare e usare i metadati TempDB ottimizzati per la memoria

Abilitare

Collegarsi al pool di risorse per limitare l'utilizzo della memoria

Verificare l'associazione del pool di risorse e monitorare l'utilizzo della memoria

Rimuovere l'associazione del pool di risorse

Disabilitare

Limitazioni dei metadati TempDB ottimizzati per la memoria

Pianificazione delle capacità per tempdb in SQL Server

Monitoraggio dell'uso di tempdb

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive