Database tempdb

Articolo
06/25/2013

Il database di sistema tempdb è una risorsa globale disponibile a tutti gli utenti connessi all'istanza di SQL Server e viene utilizzata per contenere gli elementi seguenti:

Oggetti utente temporanei creati in modo esplicito, ad esempio tabelle temporanee globali o locali, stored procedure temporanee, variabili di tabella o cursori.
Oggetti interni creati dal Motore di database di SQL Server, ad esempio tabelle di lavoro in cui archiviare i risultati intermedi delle operazioni di spooling o di ordinamento.
Versioni di riga generate dalle transazioni di modifica dei dati in un database in cui viene utilizzato il 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.

In tempdb viene registrato un numero minimo di operazioni Consente 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, nessuna parte del database tempdb viene salvata per le sessioni successive di SQL Server. Le operazioni di backup e di ripristino non sono consentite nel database tempdb.

Proprietà fisiche di tempdb

Nella tabella seguente sono elencati i valori iniziali di configurazione dei dati e dei file di log di tempdb. Le dimensioni di questi file possono variare leggermente a seconda dell'edizione di SQL Server.

File	Nome logico	Nome fisico	Aumento di dimensioni del file
Dati primari	tempdev	tempdb.mdf	Crescita automatica del 10% fino a quando il disco risulta pieno
Log	templog	templog.ldf	Aumento automatico delle dimensioni del 10% fino a un massimo di 2 TB

Le dimensioni del database tempdb possono influire sulle prestazioni di un sistema. Se ad esempio le dimensioni di tempdb sono eccessivamente ridotte, è possibile che l'elaborazione del sistema sia troppo occupata con l'aumento automatico delle dimensioni del database per supportare i requisiti del carico di lavoro a ogni avvio di SQL Server. Per ovviare a questo overhead, aumentare le dimensioni di tempdb.

Miglioramenti delle prestazioni in tempdb

In SQL Server le prestazioni di tempdb sono state migliorate come segue:

È possibile memorizzare nella cache tabelle temporanee e variabili di tabella. La memorizzazione nella cache consente di eseguire molto rapidamente le operazioni di eliminazione e creazione degli oggetti temporanei e di ridurre i problemi di contesa nell'allocazione delle pagine.
Il protocollo di latch delle pagine di allocazione è stato migliorato. In questo modo è possibile ridurre il numero di latch di aggiornamento (UP) utilizzati.
L'overhead di registrazione per il database tempdb è stato ridotto. In questo modo si riduce l'utilizzo di banda per operazioni di I/O su disco nel file di log di tempdb.
L'algoritmo per l'allocazione delle pagine miste in tempdb è stato migliorato.

Spostamento dei dati e dei file di log di tempdb

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

Opzioni di database

Nella tabella seguente sono elencati i valori predefiniti delle singole opzioni di database di tempdb e viene indicato se l'opzione è modificabile. Per visualizzare le impostazioni correnti di queste opzioni, utilizzare 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	ON	Sì
AUTO_SHRINK	OFF	No
AUTO_UPDATE_STATISTICS	ON	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	ON	No
ENCRYPTION	OFF	No
NUMERIC_ROUNDABORT	OFF	Sì
PAGE_VERIFY	CHECKSUM per nuove installazioni di SQL Server. NONE per aggiornamenti di SQL Server.	Sì
PARAMETERIZATION	SIMPLE	Sì
QUOTED_IDENTIFIER	OFF	Sì
READ_COMMITTED_SNAPSHOT	OFF	No
RECOVERY	SIMPLE	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).

Restrizioni

Di seguito sono riportate le operazioni che non è possibile eseguire sul database tempdb:

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 p di proprietà del ruolo dbo.
Creazione di uno snapshot del database.
Eliminazione del database.
Eliminazione dell'utente guest dal database.
Attivazione 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 database o del filegroup primario 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 per la connessione a tempdb per impedire a un utente di utilizzarlo, tuttavia questa operazione non è consigliabile poiché in alcune operazioni di routine è richiesto l'utilizzo di tempdb.