SQLShack

In questo articolo, discuteremo le migliori pratiche che dovrebbero essere seguite per mantenere il database TempDB in uno stato di salute e prevenire qualsiasi crescita inaspettata del database, oltre alla procedura che può essere seguita per rilevare questa crescita inaspettata una volta avvenuta.

Panoramica TempDB

Il database TempDB è uno dei più importanti database di sistema SQL Server, che viene utilizzato per memorizzare oggetti utente temporanei, come le tabelle temporanee definite dall’utente o restituite dall’esecuzione di funzioni con valori di tabella, stored procedure temporanee, variabili di tabella o indici.

Oltre agli oggetti utente, TempDB verrà utilizzato per memorizzare gli oggetti interni creati dal motore di database di SQL Server durante le diverse operazioni interne, come l’ordinamento intermedio, lo spooling, l’aggregazione o le operazioni di cursore.

Il database di sistema TempDB viene utilizzato anche per memorizzare le versioni delle righe al fine di supportare le funzionalità che richiedono il monitoraggio delle modifiche eseguite sulle righe della tabella, come il livello di isolamento delle istantanee delle transazioni, le ricostruzioni degli indici online o la funzionalità Più set di risultati attivi.

Il database TempDB verrà eliminato e ricreato di nuovo ogni volta che il servizio SQL Server viene riavviato, iniziando con una nuova copia pulita del database. Sulla base di questo fatto, tutti gli utenti e database interno obiettato che sono memorizzati su questo database verrà eliminato automaticamente quando il servizio SQL Server viene riavviato o quando la sessione in cui questi oggetti creati viene disconnesso. Pertanto le operazioni di backup e ripristino non sono disponibili per TempDB.

Perché TempDB

Il database di sistema TempDB svolge un ruolo importante nel processo di ottimizzazione delle prestazioni di SQL Server. Poiché viene utilizzato come memorizzazione nella cache per memorizzare diversi tipi di oggetti del database utente e per memorizzare gli oggetti interni del sistema al fine di accelerare i processi relativi al motore di database SQL Server.

A causa di questo ruolo fondamentale che il database di sistema TempDB svolge nel migliorare le prestazioni complessive dell’istanza di SQL Server, è molto importante applicare le best practice iniziali di dimensioni, crescita automatica e posizione sui dati del database TempDB e sui file di registro. L’applicazione di queste best practice in un modo che si adatta al carico di lavoro dell’istanza di SQL Server impedirà l’espansione dei dati del database TempDB e dei file di registro molto frequentemente, tenendo conto che il processo di espansione del file è un processo costoso in cui SQL Server Engine richiederà spazio aggiuntivo dal sistema operativo, che eseguirà l’azzeramento su Si può immaginare il tempo e le risorse necessarie per questo processo di allocazione.

Se si applicano già tutte le best practice e si è verificata un’operazione di crescita imprevista, è possibile accelerare il processo di allocazione utilizzando l’opzione di inizializzazione del file istantaneo del database, in cui il sistema operativo si fiderà del servizio SQL Server e allocherà lo spazio richiesto senza eseguire l’operazione di spreco di tempo e risorse sullo spazio allocato.

Best practice

Esistono numerose best practice di configurazione del database di sistema TempDB raccomandate per ottenere le migliori prestazioni da quella risorsa condivisa e proteggere l’istanza di SQL Server da qualsiasi crescita imprevista nei dati del database TempDB o nei file di registro che potrebbero riempire lo spazio su disco sottostante.

Queste configurazioni includono:

• Il numero dei file di dati TempDB deve corrispondere al numero di processori logici, fino a otto file, sulla macchina in cui è installata l’istanza di SQL Server. Se il numero di processori logici che macchina è superiore a otto, impostare il numero di file di dati per otto anni, con la possibilità di estendere da più di 4 nel caso in cui si dispone di un database TempDB contesa problema
• assicurarsi che il database TempDB file hanno dimensioni uguali all’interno dello stesso filegroup per fornire il più alto grado possibile di operazioni parallele efficienza, a causa del Riempimento Proporzionale scrittura meccanismo che viene utilizzato per scrivere i dati all’interno del file di dati. Per ulteriori informazioni, verificare la Comprensione di SQL Server algoritmo Proporzionale
• Configurare il database TempDB di dati e file di registro con un ragionevole dimensione iniziale e di auto-crescita importi che si adatta la quantità prevista di dati che verranno scritti su questi file, e i diversi tipi di carichi di lavoro effettuata nell’istanza di SQL Server, tra cui le attività dell’utente e indice di operazioni di manutenzione, al fine di evitare le frequenti piccoli incrementi che riguardano il complessivo istanza prestazioni
• Individuare il file TempDB veloce da un sottosistema di I/O in modo da evitare qualsiasi I / O colli di bottiglia
• Individuare il TempDB su un’unità disco separata, lontano dai database utente e altri database di sistema. In questo modo, le pesanti operazioni di lettura e scrittura eseguite su TempDB non influiranno o saranno influenzate dal carico di lavoro dei database degli utenti, migliorando le prestazioni complessive dell’istanza di SQL Server

Per ulteriori informazioni sui nuovi miglioramenti nelle prestazioni di TempDB, controllare il documento del database TempDB.

Dove applicare

Prima della versione di SQL Server 2016, l’allocazione delle dimensioni TempDB può essere eseguita dopo l’installazione dell’istanza di SQL Server, dalla pagina delle proprietà del database. Dove è possibile controllare il numero di file di dati del database che si adattano all’istanza SQL Server corrente, la dimensione iniziale per ciascun file di dati e log del database, il valore corretto per l’opzione Autogrowth e la posizione dei dati del database e dei file di log.

Per eseguire queste modifiche, espandere il nodo di Database in SQL Server Management Studio, espandere il Sistema di nodo Database e quindi fare clic destro sul database tempdb e scegliere l’opzione Proprietà, come di seguito:

Dal Database aprire la finestra delle Proprietà, sfoglia la pagina File e controllare o modificare i vari dati del database e file di registro e le impostazioni mostrate di seguito:

A partire dalla versione di SQL Server 2016, tutte queste modifiche, con le best practice che si adattano alle risorse server correnti già suggerite dalla procedura guidata di installazione, possono essere controllate ed eseguite durante il processo di installazione dell’istanza di SQL Server, utilizzando la procedura guidata di installazione di SQL Server, in Configurazioni:

Traccia TempDB crescita

Monitoraggio, la crescita del TemDB file di database e le query che stanno consumando il TempDB risorse possono essere eseguite utilizzando i contatori di Performance Monitor associato con i file di TempDB e lo strumento di SQL Profiler, che è in attesa di un database specifico tipo di carico di lavoro.

Un modo più amichevole dell’amministratore del database per monitorare la crescita dei file di database TempDB sta interrogando le seguenti viste di gestione dinamica:

• sys.dm_db_file_space_usage che restituisce le informazioni sull’utilizzo dello spazio per ogni file nel database, senza mostrare di sessione o attività consumato quello spazio
• sys.dm_db_session_space_usage che restituisce il numero di allocata e deallocata pagine per ogni sessione
• sys.dm_db_task_space_usage che restituisce il numero di allocata e deallocata pagine per ogni attività

si noti che il risultato restituito dal sys.dm_db_session_space_usage DMV riassunto il risultato restituito dalla sys.dm_db_task_space_usage DMV quando tutte le attività vengono completate correttamente, a causa del fatto che, più attività relative alla stessa query verranno eseguite nella stessa sessione. Un altro vantaggio di questi DMV è che mostrerà il tipo di oggetto che consuma le risorse TempDB e se questo oggetto è un oggetto utente, come una tabella temporanea o variabile o un oggetto interno, come la manutenzione dell’indice o l’operazione di ordinamento. Quindi, in base al numero di pagine consumate da ciascun oggetto, è possibile specificare se TempDB viene consumato da un oggetto interno o utente e ottimizzare quel consumatore.

sys.dm_db_file_space_usage

Il DMV sys.dm_db_file_space_usage può essere utilizzato per monitorare lo spazio su disco utilizzato nei file di database TempDB. Le query seguenti mostrano lo spazio non allocato, lo spazio utilizzato per memorizzare le versioni delle righe, lo spazio consumato dagli oggetti interni e infine lo spazio consumato dagli oggetti utente, come segue:

il risultato, che è tornato dal mio istanza locale di SQL Server, è possibile vedere che c’è di 60 MB inutilizzati nel file del database TempDB, con solo lo 0,5 MB utilizzati per gli oggetti interni e a 1,5 MB per gli oggetti utente, come mostrato di seguito:

sys.dm_db_task_space_usage e sys.dm_db_session_space_usage

sys.dm_db_task_space_usage e sys.dm_db_session_space Dmv sono utilizzati per controllare il numero di pagine allocate e deallocato da ogni attività o di sessione nel database TempDB. In questo modo, sarete in grado di vedere quale utente o un oggetto interno che sta consumando lo spazio TempDB.

Ad esempio, il risultato seguente viene restituito dall’interrogazione del sys.dm_db_task_space_usage DMV nel database TempDB, dove restituirà il numero di pagine allocate e deallocate per ogni utente o attività interna, come segue:

Ma il DBA non richiederà molti dettagli per iniziare a tracciare la query che sta consumando le risorse TempDB. Quindi, abbiamo bisogno di riassumere il risultato precedente per ogni sessione, utilizzando la query di seguito:

E i riassunti pagine consumo di risultato per ogni sessione sarà come di seguito:

Lo stesso risultato può essere restituito dalla query sys.dm_db_session_space_usage DMW, che riassume le pagine di allocazione e deallocazione per tutte le attività in esecuzione nella stessa sessione, come mostrato di seguito:

Cerchiamo di eseguire alcune operazioni su database TempDB risorse interne e gli oggetti utente, come di seguito:

Quindi eseguire il consolidato query seguente, che riporta il numero di allocata e deallocata pagine interne e oggetti utente e di visualizzare sia le operazioni completate (usare sys.dm_db_session_space_usage DMV) e l’incompleto operazioni (usare sys.dm_db_task_space_usage DMV):

E le operazioni precedenti risultato riflette l’output della query, che mostra il numero di pagine allocate e deallocato creando l’utente tabella temporanea e l’ordinamento dei dati durante l’indice del processo di ricostruzione, come mostrato di seguito:

Per ottenere il testo della query che utilizza il database TempDB risorse, è possibile utilizzare SQL competenze di sviluppo unendo il risultato di ys.dm_db_task_space_usage DMV con altri Dmv come sys.dm_exec_requests e sys.dm_exec_sessions con il sys.dm_exec_sql_text Dinamico Funzione di Gestione, in cui verrà visualizzata la query che stanno consumando spazio di TempDB ora, come in questa query.

Il risultato sarà simile al seguente:

• Autore
• Post Recenti

Ahmad Yaseen è un Microsoft Big Data ingegnere con una profonda conoscenza ed esperienza in BI SQL, Database SQL Server, Amministrazione e Sviluppo dei campi.
È un esperto di soluzioni certificate Microsoft in gestione e analisi dei dati, Microsoft Certified Solution Associate in amministrazione e sviluppo di database SQL, Azure Developer Associate e Microsoft Certified Trainer.
Inoltre, sta contribuendo con i suoi suggerimenti SQL in molti blog.
Visualizza tutti i messaggi di Ahmad Yaseen

Ultimi messaggi di Ahmad Yaseen (vedi tutti)

• Azure Dati di Fabbrica Intervista Domande e Risposte – febbraio 11, 2021
• Come monitor Azure Dati di Fabbrica – gennaio 15, 2021
• Uso del Controllo del codice Sorgente in Azzurro i Dati di Fabbrica – gennaio 12, 2021