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, vengono create una o più pagine GAM e SGAM per ogni file di tempdb dati, che consente di migliorare la tempdb concorrenza e ridurre la contesa di pagine PFS. 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.

È 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.

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

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

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

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.

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

Dimensioni massime

tempdb dimensioni è la somma delle dimensioni 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.

tempdb spazio utilizzato è la somma dello spazio utilizzato 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 tempdb spazio utilizzato e tempdb spazio disponibile è uguale alle dimensioni 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.

USE tempdb
SELECT name, max_size FROM sys.database_files

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

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

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:

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 il monitoraggio avanzato dell’istanza gestita di SQL, si veda Database Watcher.
• Per informazioni sui prezzi, vedere Database SQL - Prezzi.