Configurare le impostazioni tempdb per l’istanza gestita di SQL di Azure

Si applica a:Istanza gestita di SQL di Azure SQL

Questo articolo illustra come configurare le impostazioni tempdb per Istanza gestita di SQL di Azure.

Istanza gestita di SQL di Azure consente di configurare quanto segue:

• Numero di file tempdb
• Incremento per l'aumento delle dimensioni del file tempdb
• Dimensione massima di tempdb

Le impostazioni tempdb vengono mantenute dopo il riavvio, l'aggiornamento o il failover dell'istanza.

Panoramica

tempdb è uno dei database di sistema predefiniti fornito con Istanza gestita di SQL di Azure. La struttura di tempdb è uguale a qualsiasi altra struttura di database utente. La differenza consiste nel fatto che, poiché tempdb viene usato per l'archiviazione non durevole, le transazioni vengono registrate in modo minimo.

tempdb non può essere eliminato, scollegato, portato offline, rinominato o ripristinato. Tentare una qualsiasi di queste operazioni restituisce un errore. tempdb viene rigenerato a ogni avvio dell'istanza del server e a tutti gli oggetti che potrebbero essere stati creati in tempdb durante una sessione precedente non vengono mantenuti al riavvio del servizio dopo un'operazione di gestione degli aggiornamenti dell'istanza o un failover.

Il carico di lavoro in tempdb differisce dai carichi di lavoro in altri database utente. Gli oggetti e i dati vengono creati e eliminati di frequente e la co-occorrenza è estremamente elevata. Per ogni istanza gestita è presente un solo tempdb. Anche se si dispone di più database e applicazioni che si connettono all'istanza, tutti usano lo stesso database tempdb. I servizi possono riscontrare conflitti quando tentano di allocare pagine in un tempdb usato di frequente. A seconda del grado di contesa, le query e le richieste che coinvolgono tempdb potrebbero non rispondere. Questo è il motivo per cui tempdb è fondamentale per le prestazioni del servizio.

Numero di file tempdb

Aumentando il numero di file di dati tempdb, vengono create una o più pagine GAM e SGAM per ogni file di dati, che consente di migliorare la concorrenza di tempdb e ridurre la contesa di pagine PFC. Tuttavia, l'aumento del numero di file di dati tempdb potrebbe avere altre implicazioni sulle prestazioni, quindi testarli attentamente prima di implementare nell'ambiente di produzione.

Per impostazione predefinita, Istanza gestita di SQL di Azure crea 12 file di dati tempdb e 1 file di log tempdb, ma è possibile modificare questa configurazione.

La modifica del numero di file tempdb presenta le limitazioni seguenti:

• Il nome logico del nuovo file non fa distinzione tra maiuscole e minuscole, con un massimo di 16 caratteri e senza spazi.
• Il numero massimo di file tempdb è 128.

Nota

Non è necessario riavviare il server dopo l'aggiunta di nuovi file; tuttavia, i file più vuoti verranno riempiti con priorità più alta e l'algoritmo round robin per l'allocazione delle pagine verrà perso fino a quando il sistema non verrà riequilibrato.

È possibile usare sia SQL Server Management Studio (SSMS) che Transact-SQL (T-SQL) per modificare il numero di file per tempdb in Istanza gestita di SQL di Azure.

SQL Server Management Studio (SSMS)

È possibile usare SQL Server Management Studio (SSMS) per modificare il numero di file tempdb. A tale scopo, effettuare i passaggi seguenti:

1. Connettersi all’Istanza gestita usando SSMS.

2. In Esplora oggetti espandere Database, quindi espandere Database di sistema.

3. Fare clic con il pulsante destro del mouse su tempdb e scegliere Proprietà.

4. Selezionare File in Seleziona una pagina per visualizzare il numero di file tempdb esistenti.

5. Per aggiungere un file, scegliere Aggiungi, quindi fornire informazioni sul nuovo file di dati nella riga.

   

6. Per rimuovere un file tempdb, scegliere il file da rimuovere dall'elenco dei file di database, quindi selezionare Rimuovi.

Transact-SQL (T-SQL)

È possibile usare Transact-SQL (T-SQL) per controllare il numero di file tempdb esistenti e aggiungere o rimuovere file.

Per contare il numero di tutti i file tempdb esistenti usare il comando seguente:

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files

Per contare solo il numero di file di dati tempdb usare il comando seguente:

USE tempdb
SELECT COUNT(*) TempDBFiles FROM sys.database_files where type = 0

Per rinominare un nuovo file tempdb usare il comando seguente:

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name')

Per rimuovere un file tempdb esistente usare il comando seguente:

ALTER DATABASE tempdb REMOVE FILE [file_name]

Incremento della crescita

L'aumento delle prestazioni dei file tempdb può avere un impatto sulle prestazioni delle query con tempdb. Di conseguenza, gli incrementi di crescita dei file di dati tempdb troppo piccoli possono causare frammentazione dell'estensione, mentre gli incrementi troppo grandi possono causare un rallentamento della crescita o un errore di crescita se non vi è spazio sufficiente. Il valore ottimale per gli incrementi di crescita dei file tempdb dipende dal carico di lavoro.

Gli incrementi di crescita predefiniti per Istanza gestita di SQL sono 254 MB per i file di dati tempdb e 64 MB per i file di log tempdb, ma è possibile configurare incrementi di crescita per adattarsi al carico di lavoro e ottimizzare le prestazioni.

Considerare quanto segue:

• Il parametro di crescita dei file supporta le unità seguenti per int_growth_increment: KB, MB, GB, TB e %.
• Gli incrementi di crescita devono corrispondere a tutti i file di dati tempdb poiché, in caso contrario, l'algoritmo round robin che alloca le pagine potrebbe essere interessato.

È possibile usare sia SQL Server Management Studio (SSMS) che Transact-SQL (T-SQL) per modificare l'incremento di crescita per i file tempdb.

SQL Server Management Studio (SSMS)

È possibile usare SQL Server Management Studio (SSMS) per modificare l'incremento di crescita dei file tempdb. A tale scopo, effettuare i passaggi seguenti:

1. Connettersi all’Istanza gestita usando SSMS.

2. In Esplora oggetti espandere Database, quindi espandere Database di sistema.

3. Fare clic con il pulsante destro del mouse su tempdb e scegliere Proprietà.

4. Selezionare File in Seleziona una pagina per visualizzare il numero di file tempdb esistenti.

5. Scegliere i puntini di sospensione (...) accanto a un file di dati per aprire la finestra di dialogo Cambia proprietà aumento automatico.

6. Selezionare la casella accanto a Abilita aumento automatico, quindi modificare le impostazioni di aumento automatico specificando i valori di crescita dei file, in percentuale o megabyte.

   

7. Per salvare le impostazioni, fare clic su OK.

Transact-SQL (T-SQL)

Usare il comando T-SQL seguente per modificare gli incrementi di crescita per i file di dati tempdb:

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', FILEGROWTH = int_growth_increment [KB|MB|GB|TB|%])

Dimensioni massime

tempdbsize è la somma di tutti i file tempdb. La dimensione del file tempdb è uno spazio allocato (azzerato) per il file tempdb. Le dimensioni iniziali del file per tutti i file tempdb sono di 16 MB, ovvero le dimensioni di tutti i file tempdb quando l'istanza viene riavviata o viene eseguito il failover. Quando lo spazio usato di un file di dati tempdb raggiunge le dimensioni del file, tutti i file di dati tempdb aumentano automaticamente con gli incrementi di crescita configurati.

tempdbused space è la somma dello spazio usato di tutti i file tempdb. Lo spazio usato dal file tempdb è uguale alla parte di tale dimensione del file tempdb occupata con valori diversi da zero. La somma di tempdbused space e tempdbfree space è uguale alla dimensione del file tempdb.

È possibile usare T-SQL per determinare lo spazio corrente usato e libero per i file tempdb.

Per conoscere spazio usato, spazio libero e dimensioni dei file di dati tempdb, eseguire questo comando:

USE tempdb
SELECT SUM((allocated_extent_page_count)*1.0/128) AS TempDB_used_data_space_inMB, 
	SUM((unallocated_extent_page_count)*1.0/128) AS TempDB_free_data_space_inMB, 
	SUM(total_page_count*1.0/128) AS TempDB_data_size_inMB 
FROM sys.dm_db_file_space_usage

La schermata seguente mostra un output di esempio:

Per conoscere spazio usato, spazio libero e dimensioni dei file di log tempdb, eseguire questo comando:

USE tempdb
SELECT used_log_space_in_bytes*1.0/1024/1024 AS TempDB_used_log_space_inMB,
     (total_log_size_in_bytes- used_log_space_in_bytes)*1.0/1024/1024 AS TempDB_free_log_space_inMB,
     total_log_size_in_bytes*1.0/1024/1024 AS TempDB_log_size_inMB
FROM sys.dm_db_log_space_usage

La schermata seguente mostra un output di esempio:

tempdb max size è il limite dopo il quale non è possibile aumentare ulteriormente tempdb.

tempdb max size in Istanza gestita di SQL presenta le limitazioni seguenti:

• Nel livello di servizio General Purpose le dimensioni massime per tempdb sono limitate a 24 GB/vCore (96-1920 GB) e il file di log è di 120 GB.
• Nel livello di servizio Business Critical tempdb compete con altri database per le risorse, quindi l'archiviazione riservata viene condivisa tra tempdb e altri database. La dimensione massima del file di log tempdb è di 2 TB.

Il file tempdb aumentano fino a raggiungere il limite massimo consentito dal livello di servizio o dalle dimensioni massime del file tempdb configurate manualmente.

È possibile usare sia SQL Server Management Studio (SSMS) che Transact-SQL (T-SQL) per modificare la dimensione massima per i file tempdb.

SQL Server Management Studio (SSMS)

Per determinare tempdb max size corrente in SSMS, seguire questa procedura:

1. Connettersi all’Istanza gestita usando SSMS.
2. In Esplora oggetti espandere Database, quindi espandere Database di sistema.
3. Fare clic con il pulsante destro del mouse su tempdb e scegliere Proprietà.
4. Nella pagina Generale controllare il valore Dimensioni in Database per determinare le dimensioni massime di tempdb. Il valore -1 indica dimensioni massime di tempdb illimitate.

Per cambiare tempdb max size corrente in SSMS, seguire questa procedura:

1. Connettersi all’Istanza gestita usando SSMS.
2. In Esplora oggetti espandere Database, quindi espandere Database di sistema.
3. Fare clic con il pulsante destro del mouse su tempdb e scegliere Proprietà.
4. Selezionare File in Seleziona una pagina per visualizzare il numero di file tempdb esistenti.
5. Scegliere i puntini di sospensione (...) accanto a un file di dati per aprire la finestra di dialogo Cambia proprietà aumento automatico.
6. Modificare le impostazioni tempdb max size modificando i valori in Dimensioni massime del file.
7. Per salvare le impostazioni, fare clic su OK.

Transact-SQL (T-SQL)

Per determinare tempdb max size, eseguire questo comando:

USE tempdb
SELECT name, max_size FROM sys.database_files

Per ottenere le dimensioni totali di tempdb in MB, eseguire questo comando:

USE tempdb
SELECT (SUM(size)*1.0/128) AS TempDB_size_InMB FROM sys.database_files

Lo screenshot seguente mostra un report di esempio:

Per impostare le dimensioni massime per un nuovo file di dati tempdb, eseguire il comando seguente:

ALTER DATABASE tempdb ADD FILE (NAME = 'file_name', MAXSIZE = int_maxsize[KB|MB|GB|TB])

Per modificare le dimensioni massime di un file tempdb esistente eseguire il comando seguente:

ALTER DATABASE tempdb MODIFY FILE (NAME = file_name, MAXSIZE = int_maxsize[KB|MB|GB|TB])

Limiti di tempdb

La tabella seguente definisce i limiti per varie impostazioni di configurazione tempdb:

Impostazione di configurazione	Valori
Nomi logici di file tempdb	16 caratteri al massimo
Numero di file tempdb	128 file al massimo
Numero predefinito di file tempdb	13 (1 file di log + 12 file di dati)
Dimensioni iniziali dei file di dati tempdb	16 MB
Incremento per l'aumento delle dimensioni predefinito dei file di dati tempdb	256 MB
Dimensioni iniziali dei file di log tempdb	16 MB
Incremento per l'aumento delle dimensioni predefinito dei file di log tempdb	64 MB
max tempdbsize iniziali	-1 (illimitato)
Dimensioni massime di tempdb	Fino alle dimensioni di archiviazione

Passaggi successivi

• Per informazioni su come creare la prima istanza gestita, vedere Guida di avvio rapido.
• Per un elenco di confronto delle funzionalità, vedere Confronto tra le funzionalità: database SQL di Azure e SQL Server.
• Per altre informazioni sulla configurazione della rete virtuale, vedere Configurazione di una rete virtuale per Istanza gestita di SQL.
• Per una guida di avvio rapido sulla creazione di un'istanza gestita e il ripristino di un database da un file di backup, vedere Creare un'istanza gestita.
• Per un'esercitazione sull'uso del Servizio Migrazione del database di Azure, vedere Migrazione a Istanza gestita di SQL con il Servizio Migrazione del database.
• Per informazioni sul monitoraggio avanzato delle prestazioni del database dell'istanza gestita di SQL con intelligence predefinita per la risoluzione dei problemi, vedere Monitorare l'istanza gestita di SQL di Azure usando Analisi SQL di Azure.
• Per informazioni sui prezzi, vedere Database SQL - Prezzi.