Split TempDB in Sql Server 2019 è un operazione che spesso viene fatta dai vari sistemisti per migliorare le performance di SQL Server.
Prendo di riferimento sempre l’ultima versione rilasciata da Microsoft.
Prima di vedere come fare lo Split TempDB in Sql Server è necessario sapere cos’è la TempDB.
Sommario
TempDB in Sql Server: cos’è?
La o il TempDB è un database presente in ogni versione di SQL SERVER. Al suo interno vengono memorizzate principalmente gli oggetti interni che crea il motore del database.
Questi oggetti interni sono ad esempio le tabelle di lavoro dove SQL “parcheggia” le elaborazioni intermedie durante l’esecuzione delle Query (Hash JOIN o Hash Aggregate).
Nella TempDB vengono salvati anche i risultati intermedi per le query fanno GROUP BY, ORDER BY o nelle UNION.
Ecco perché il/la TempDB è così importante.
Quindi:
1) Se viene posizionato su uno Storage “lento” …lente saranno anche le Query!
2) Se non lo configuriamo correttamente …lente saranno le Query!
Il/la TempDB è inoltre una risorsa condivisa nel senso che è comune a tutta l’istanza per cui un collo di bottiglia può influenzare le prestazioni di tutti i database sull’istanza stessa.
Split TempDB in Sql Server 2019: posizione TempDB
Lo scopo di questo post è semplicemente mostrare come spostare tempdb in un’altra posizione e come dividere tempdb su più file di dati mantenendo dimensioni e crescite di file uniformi.
Innanzitutto dobbiamo sapere dove risiedono i file tempdb e come vengono chiamati?
Lo standard è che si chiamino tempdev e templog, ma puoi dare un’occhiata alle proprietà di tempdb in SSMS (apri database di sistema, fai clic destro su tempdb e seleziona proprietà) e fai clic sulla sezione dei file per mostrarti i nomi e posizioni:
oppure puoi eseguire quanto segue che ti mostrerà lo stesso:
USE tempdb;
GO
EXEC sp_helpfile;
GOIl risultato della query:
Ora conosciamo le dimensioni e le posizioni, possiamo impostare gli script per spostare i file esistenti. In questo modo, possiamo anche correggere la crescita a un importo specifico anziché a una percentuale.
La quantità di crescita dovrebbe essere di 10 MB per un massimo di 100 MB di file, 20 MB per i file da 100 a 200 MB e il 10% per oltre 250 MB, ma è necessario limitare questa dimensione per garantire che la crescita possa avvenire rapidamente.
Spostare TempDB in Sql Server su dischi performanti
Per spostare TempDb su altri dischi con maggiori performance basta eseguire la query indicata.
Naturalmente, la scelta viene fatta quando si esegue l’installazione di Sql Server 2019.
USE master;
GO
--move data file (as named above: tempdev)
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev,
FILENAME = 'T:\TempDB\tempdb.mdf',
SIZE = 250MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50MB
);
--move log file (log file as named above: templog)
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = templog,
FILENAME = 'T:\TempDB\tempdb.ldf',
SIZE = 1024MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 64MB
);
GOQuando il servizio SQL viene riavviato, tempdb si troverà nella sua nuova posizione, questo può essere verificato usando lo script sp_helpfile sopra indicato.
Split TempDB in Sql Server 2019
Ecco invece la query per fare lo Split TempDB in Sql Server:
/*Restart the SQL service to move tempdb then delete old files */
--add the extra files here
USE MASTER
GO
ALTER DATABASE tempdb
ADD FILE
(
NAME = tempdev2,
FILENAME = 'T:\TempDB\tempdb2.ndf',
SIZE = 250MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50MB
),
(
NAME = tempdev3,
FILENAME = 'T:\TempDB\tempdb3.ndf',
SIZE = 250MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50MB
),
(
NAME = tempdev4,
FILENAME = 'T:\TempDB\tempdb4.ndf',
SIZE = 250MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 50MB
);
GOUna volta eseguita la query per lo split TempDb in SqlServer esegui il riavvio dei servizi .
Perchè fare lo split TempDB in Sql Server
Naturalmente, come sappiamo dai DBA, è obbligatorio modificare i dati e le proprietà del registro del database tempdb. A meno che non ne facciamo una configurazione personalizzata, SQL Server creerà un solo file di dati e file di registro per impostazione predefinita.
La realtà è che avremo principalmente bisogno di creare più file negli ambienti di produzione. Esistono molti consigli non solo su come crearli, ma anche sulla quantità di file da creare. Ad essere sincero, non credo che il numero di file di dati dipenda solo dal numero di core della macchina, ma dipende anche dalla concorrenza, dai problemi di contesa tempdb, dai carichi di lavoro sul server e, chiaramente, dalle prestazioni delle query.
Un altro motivo per cui potresti voler utilizzare più file di dati è aumentare il throughput I/O su tempdb, specialmente se è in esecuzione su una memoria molto veloce.
Quando si creano più file di dati, questi saranno tutti nel filegroup primario e SQL Server utilizza un algoritmo di riempimento proporzionale per determinare quale file utilizzare per ciascuna richiesta per creare un oggetto.
Se tutti i file hanno esattamente le stesse dimensioni, SQL Server utilizza i file in modo “Round Robin“, ripartendo il carico equamente tra i file. Questo è, ovviamente, esattamente quello che vuoi.
Vorrei solo suggerire di dividere il database tempdb in quattro file di dati (se si dispone di quattro o otto core) o otto file di dati (per 16, 32, 64 o più core) e SOLO un file di registro. Idealmente, dobbiamo anche localizzarli in diverse unità RAID1, RAID5 o RAID10. Ora avendo la situazione molto chiara, ti mostrerò un piccolo script per dividere il database tempdb predefinito in 8 file di dati e rimanere l’unico file di registro.
Conclusione
Abbiamo visto come eseguire lo split TempDb in SqlServer e vari consigli sul perchè fare questa configurazione. Utilizzando gli script sopra, personalizzati in base alle esigenze, è possibile scoprire dove si trova tempdb e come vengono chiamati i file, scrivere lo spostamento dei file esistenti e aggiungerne di nuovi per facilitare o rimuovere la contesa. Durante il processo puoi anche configurare dimensioni e crescite appropriate.
