SQL Server TempDB Tutorial

Da: Eric Blinn | Aggiornato: 2020-07-17 | Commenti (2) | Correlati: Più > Database di sistema

Problema

SQL Server viene installato con quattro database di sistema per impostazione predefinita. Theyare maestro, modello, msdb, e TempDB. Ho bisogno di sapere qual è il database di sistema “TempDB”e a cosa serve. Questi tutorial precedenti covermaster, modello emsdb in grande dettaglio.

Soluzione

Questo suggerimento riguarderà il database TempDB. TempDB è un database che hamolte funzioni all’interno di SQL Server, ma raramente viene chiamato esplicitamente. Hascos molte funzioni che è spesso uno dei più trafficati, se non il database più trafficato sulla maggior parte delle istanze di SQL Server. Continuate a leggere per imparare whatmany di queste funzioni sono!

Panoramica di SQL Server TempDB

Qual è lo scopo di SQL Server TempDB?

Una delle funzioni di TempDB è di agire come una pagina o un file di swap a livello di sistema operativo. Se un’operazione di SQL Server è troppo grande per essere completata in memoria o se la concessione di memoria iniziale per una query è troppo piccola, l’operazione può essere spostata su disco in TempDB.

Un’altra funzione di TempDB è quella di memorizzare tabelle temporanee. Chiunque abbia creato una tabella temporanea in T-SQL utilizzando un prefisso pound o hash ( # ) o il prefisso doublepound/hash ( # # ) ha creato un oggetto in TempDB poiché è qui che vengono memorizzati.

CREATE TABLE #MSSQLTips (RowID int);

Quando atrigger sta eseguendo le tabelle virtuali inserite ed eliminate vengono memorizzate in TempDB.

Tutti i database che useREAD COMMITTED SNAPSHOT ISOLATION (RCSI) avranno le loro informazioni sul controllo delle versioni in TempDB.

Ciascuna delle funzioni di cui sopra di TempDB sono completate senza mai menzionareTempDB per nome e quindi possono essere usi sorprendenti per TempDB.

TempDB può anche essere chiamato esplicitamente in alcuni modi. Le tabelle possono essere generatein TempDB facendo riferimento al database in un’istruzione create. Il codice sembra esattamente come una normale operazione DDL, ma quando viene eseguito in TempDB la tabella è,per definizione, una tabella temporanea.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB viene rigenerato ad ogni avvio dell’istanza di SQL Server. Anyobjects che potrebbero essere stati creati in TempDB durante una sessione precedente notpersist al riavvio del servizio. TempDB ottiene il suo elenco di oggetti iniziale dal database del modello che generalmente sarà vuoto o quasi vuoto.

La manutenzione dell’indice può facoltativamente essere richiesta per eseguire ordinamenti in TempDB anziché tentare di farlo utilizzando lo spazio libero nel database utente.

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

Posso eseguire SQL Server senza un database TempDB?

SQL Server non può essere eseguito (tranne in scenari di ripristino molto estremi e per periodi molto brevi) senza un TempDB.

La versione del database TempDB è unica per la versione, l’edizione e il livello di patch di SQL Server?

TempDB è un database per lo più vuoto e non c’è nulla di particolarmente unico su qualsiasi edizione o livello di patch di SQL Server.

Ci sono state solo 2 modifiche significative a TempDB negli ultimi anni al livello di versione. Il primo è che a partire da SQL Server 2016 il comportamento di TEMPDB è stato modificato in modo tale che l’abilitazione dei flag di traccia T1117 e T1118 non sono più consideratiuna best practice. Il secondo è che a partire da SQL Server 2019 alcuni oggetti di sistema pesantemente utilizzati in TempDB sono stati aggiornati alle tabelle in memoria per ridurre il contenutoe migliorare le prestazioni complessive del server.

Puoi eliminare o rinominare il database TempDB e dovresti farlo per qualsiasi motivo?

TempDB non può essere eliminato, staccato, offline o rinominato. Tentativoqualsiasi di queste operazioni restituirà un errore. Non vi è alcun motivo che questo shouldever essere tentato in quanto questo è un database di sistema critico.

Posizione del database TEMPDB di SQL Server

Quali file e nomi fisici supportano il database TempDB?

I nomi di file logici predefiniti sono tempdev per data e templog per log.Possono essere trovati su disco come tempdb.mdf e templog.ldf rispettivamente. TempDBcommonly ha molti file di dati.

Quanti file di dati TempDB dovrebbero esserci?

Dovrebbe esserci un file di dati TempDB per ogni thread/core/vCPU sull’istanza con un massimo di 8.

Dove trovo il database TempDB su disco e in SSMS?

I file possono essere trovati interrogando sys.sysfiles dmv o il riquadro file nella finestra databaseproperties.

SELECT * FROM TempDB.sys.sysfiles;

Fa la differenza dove risiedono i file di database TempDB (hanno bisogno di essere su C:\, SAN, ecc.)?

Le best practice impongono che i file di dati TempDB si trovino nello storageavailable più veloce a causa dei loro livelli di attività. Per alcuni casi questo può significare storingthem con i file di dati del database utente, ma un volume separato su ispreferred disco separato, se disponibile.

Ci sono opinioni diverse sul file di log TempDB, ma questo autore preferisceputtare questo file con gli altri file di log delle transazioni.

È possibile spostare il database TempDB?

Il database TempDB può essere spostato. Il metodo per spostare TempDB è facileper implementare. Basta modificare la proprietà FILENAME dei file desiderati. Nessun file deve essere spostato come quando vengono spostati altri database di sistema. SqlServer creerà i file con il nuovo nome e / o nella nuova posizione all’avvio del servizio successivo. Basta non dimenticare di tornare indietro ed eliminare i file TEMPDB ormai inutilizzati che verranno lasciati alle spalle.

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

Dimensione e crescita del database TEMPDB di SQL Server

Come devono essere configurate le impostazioni di crescita del database e del log delle transazioni per il database TempDB?

Le impostazioni di Autogrowth devono essere impostate su numeri specifici di MB, non su una percentuale.Ogni file di dati dovrebbe avere lo stesso numero di MB impostati per la crescita.

Il dimensionamento corretto dei file per un database è generalmente una best practice, ma mai più che per TempDB. Monitora l’istanza, specialmente se ènuovo, e determina se i file crescono durante il normale funzionamento.In tal caso, modificare la dimensione iniziale in modo tale che SQL Server non sarà costretto a crescere i file in modo coerente dopo ogni avvio del servizio.

Qual è la dimensione tipica del database TempDB e quanto è grande?

Non c’è una buona risposta a questa domanda. Ciò che è tipico per un’istanza maynot essere tipico per un altro. Questo autore ha visto casi in cui il TempDB è basicallyunused media solo pochi MB di spazio utilizzato in un dato momento. Ancora, othersare costantemente essere interrogato e utilizzando centinaia di GB di spazio – anche intospace misurata in TB – soprattutto durante una finestra di manutenzione indice.

Quando si decide quanto spazio dedicare a TempDB su una nuova istanza ci sono poche cose che possono essere fatte. La nuova istanza sostituisce una precedente, esistente instance? In tal caso, utilizzare una traccia perfmon o un’acquisizione DMV programmata per determinare lo spazio utilizzato in TempDB nel corso di diversi giorni. Assicurati di includereuna finestra di manutenzione in quella cattura. Prova ad allocare spazio sufficiente sulla nuova istanza per tenere conto dello spazio utilizzato più grande catturato durante la traccia con spazio di manovra aggiuntivo. Quindi ridimensiona correttamente i file per adattarli all’allocazione dello spazio.

Le 2 query di seguito possono aiutare. Il primo prenderà un’istantanea della dimensione dei dati e dei file di registro insieme allo spazio utilizzato all’interno dei file. Questo canbe eseguito su base regolare per monitorare l’utilizzo nel tempo. La seconda query restituirà il numero di volte in cui il file di registro è cresciuto dall’ultima volta che l’istanza è stata riavviata.

Se non esiste un carico di lavoro esistente che può essere utilizzato come guida e non vi sono altre indicazioni, forse da un fornitore di software, l’unica cosa che si può fare è assicurarsi che ci sia spazio in TempDB per tenere conto dei singoli oggetti più grandi che possono essere ordinati in TempDB durante un indice maintenancewindows. Da lì, monitorare l’utilizzo e regolare di conseguenza.

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

Server TempDB Database Maintenance

È possibile ridurre il database e il log delle transazioni per il database TempDB?

Tecnicamente, sì, ma questa non è un’operazione che dovrebbe mai essere completata.L’obiettivo dovrebbe essere sempre quello di TempDB di dimensioni corrette. Se un qualche tipo di operazione una tantum fa sì che questi file crescano inspiegabilmente, torneranno alla normalità al riavvio di nextservice.

Dovresti ricostruire gli indici?

TempDB non farà parte di alcun piano di manutenzione relativo all’indice. Questo non significa ricostruire indici, riorganizzare o aggiornare le statistiche.

Dovresti eseguire controlli di integrità qual è il codice?

Ci sono opinioni diverse su whetherTempDB dovrebbe essere controllato per l’integrità.Questo autore esporrà l’argomentoper e contro e permetterà al lettore di decidere.

Contro: un’operazione di controllo dell’integrità non può controllare TempDB con la stessa totalità di tutti gli altri database.I dati che esistono in TempDB sono altamente transitori, quindi l’operazione di controllo dell’integrità non troverà molto per controllare in un dato momento.Per lo stesso motivo, qualsiasi thatdoes di dati diventano corrotti è probabile che sia dati attivi che provocheranno un avviso quando beingread da disco molto prima che un’operazione di controllo di integrità probabilmente sarà chiamata.

Per: Mentre un controllo di integrità operationcannot controlla tutto in TempDB che controlla in altri database che non significa che non ha valore.TempDB è un database integrale ese c’è corruzione sapere su di esso è imperativo in modo che l’azione correttiva può essere presa.

Il codice per eseguire un controllo di integrità è al di sotto.

DBCC CHECKDB ('TempDB'); 

SQL Server TEMPDB Database Objects

Quali sono le tabelle e le procedure chiave nel database TEMPDB che sono importanti per i professionisti di SQL Server?

Non ce ne sono.Questo database viene utilizzato per temporarystorage di objects.It non viene installato con anytables o procedure.

Posso memorizzare i miei oggetti nel database TEMPDB?

Gli oggetti possono certamente essere creati in TempDB, ma non persisteranno oltre l’avvio successivo del servizio SQL Server.

SQL Server TEMPDB Database Permissions

Chi ha accesso al database TempDB e ci sono diversi livelli di autorizzazioni?

Tutti hanno accesso e lo stesso accesso al TempDB.

Backup del database TEMPDB di SQL Server

È necessario eseguire il backup del database TempDB?

non è possibile eseguire il backup di TempDB. Memorizza solo dati transitori e viene ricreato ogni volta che il servizio si avvia, quindi non c’è nulla da recuperare in caso di disastro.

SQL Server TempDB Database Recovery Model

Quale tempdb di recupero dovrebbe avere il database TempDB e il modello di recupero può essere modificato?

TempDB è in ripristino SEMPLICE e questo non può essere modificato.

SQL Server TempDB Database Restore

C’è mai bisogno di ripristinare il database TempDB?

TempDB non può essere ripristinato. Non ci dovrebbero essere dati e nessun codice in TEMPDB da recuperare. Se TempDB diventa danneggiato, è sufficiente eliminare i dati e file di log in modo che quelli nuovi possono essere generati al prossimo avvio del servizio.

Passi successivi

• Questo suggerimento mostra come creare avvisi per problemi riscontrati all’interno delle dimensioni TempDB e dello spazio su disco
• Questo suggerimento mostra come abilitare l’inizializzazione istantanea dei file, una caratteristica importante per tutti i DB, ma in particolare TempDB se non è dimensionato correttamente.

Ultimo Aggiornamento: 2020-07-17

Circa l’autore

Eric Blinn è il Sr. Data Architect per Squire Patton Boggs. Egli è anche un autore SQL e passare leader del gruppo locale.
Visualizza tutti i miei suggerimenti

Risorse correlate

• Altri suggerimenti per SQL Server DBA…