Governance delle risorse di spazio in tempdb

Si applica a: SQL Server 2025 (17.x) Preview e versioni successive

Quando si abilita tempdb la governance delle risorse spaziali, si migliora l'affidabilità ed è possibile evitare interruzioni impedendo alle query o ai carichi di lavoro in esecuzione di consumare una grande quantità di spazio in tempdb.

A partire da SQL Server 2025 (17.x) Preview, è possibile usare Resource Governor per applicare un limite alla quantità totale di spazio utilizzata da un gruppo di carico di tempdb lavoro. Un gruppo di carico di lavoro può essere associato a un'applicazione, a un utente, a un gruppo di utenti e così via. Quando una richiesta (una query) tenta di superare il limite, Resource Governor lo interrompe con un errore distinto che indica che è stato applicato il limite del gruppo di carico di lavoro.

In effetti, è possibile partizionare lo spazio condiviso tempdb tra carichi di lavoro diversi. Ad esempio, è possibile impostare un limite superiore per un gruppo di carico di lavoro usato da un'applicazione mission-critical e impostare un limite inferiore per il default gruppo di carico di lavoro usato da tutti gli altri carichi di lavoro.

Per esempi di configurazione dettagliata, vedere Esercitazione: Esempi per configurare la governance delle risorse dello spazio tempdb.

Introduzione a Resource Governor

Resource Governor offre un framework flessibile per impostare limiti di spazio diversi tempdb per applicazioni, utenti, gruppi di utenti e così via. È anche possibile impostare limiti in base alla logica personalizzata.

Se non si ha familiarità con Resource Governor in SQL Server, vedere Resource Governor per informazioni sui concetti e sulle funzionalità.

Per una guida alla configurazione e alle migliori pratiche del gestore delle risorse, vedere Esercitazione: Esempi di configurazione del gestore delle risorse e migliori pratiche.

Impostare i limiti relativi al consumo di spazio tempdb

È possibile limitare il tempdb consumo di spazio da parte di un gruppo di carico di lavoro in uno dei due modi seguenti:

• Impostare un limite fisso usando l'argomento GROUP_MAX_TEMPDB_DATA_MB .

  Il limite fisso è utile quando i requisiti di utilizzo del carico di lavoro tempdb sono noti in anticipo o quando tempdb le dimensioni non cambiano.

• Impostare un limite di percentuale usando l'argomento GROUP_MAX_TEMPDB_DATA_PERCENT .

  Il limite di percentuale è utile quando è possibile modificare le dimensioni massime di tempdb nel tempo e si vuole che lo tempdb spazio disponibile per ogni gruppo di carico di lavoro cambi proporzionalmente senza riconfigurare Resource Governor. Ad esempio, se si aumentano le prestazioni di una macchina virtuale di Azure che esegue SQL Server e si aumentano le dimensioni massime tempdb , tempdb lo spazio disponibile per ogni gruppo di carico di lavoro con un limite di percentuale aumenta.

Per ulteriori informazioni sugli argomenti GROUP_MAX_TEMPDB_DATA_MB e GROUP_MAX_TEMPDB_DATA_PERCENT, vedere CREATE WORKLOAD GROUP o ALTER WORKLOAD GROUP.

Se per lo stesso gruppo di carico di lavoro vengono specificati limiti fissi e percentuali, il limite fisso ha la precedenza rispetto al limite di percentuale.

In una determinata istanza di SQL Server è possibile avere una combinazione di gruppi di carico di lavoro con limiti fissi, limiti di percentuale o nessun limite per tempdb l'utilizzo dello spazio.

Configurazione limite percentuale

I limiti di percentuale sono effettivi solo quando la configurazione del tempdb file di dati soddisfa i requisiti riepilogati nella tabella seguente:

La query seguente consente di visualizzare la configurazione corrente tempdb del file di dati:

SELECT file_id,
       name,
       size * 8. / 1024 AS size_mb,
       IIF(max_size = -1, NULL, max_size * 8. / 1024) AS maxsize_mb,
       IIF(is_percent_growth = 0, growth * 8. / 1024, NULL) AS filegrowth_mb,
       IIF(is_percent_growth = 1, growth, NULL) AS filegrowth_percent
FROM sys.master_files
WHERE database_id = 2
      AND
      type_desc = 'ROWS';

Per un determinato file nel set di risultati:

• Se la maxsize_mb colonna è NULL, MAXSIZE è UNLIMITED.
• Quando o filegrowth_mbfilegrowth_percent è zero, allora FILEGROWTH è zero.

Se si imposta GROUP_MAX_TEMPDB_DATA_PERCENT ed esegue l'istruzione ALTER RESOURCE GOVERNOR RECONFIGURE , ma la configurazione del file di dati non soddisfa i requisiti, l'istruzione viene completata correttamente e i limiti di percentuale vengono archiviati, ma non vengono applicati. In questo caso, viene visualizzato il messaggio di avviso 10989, gravità 10, GROUP_MAX_TEMPDB_DATA_PERCENT non è attivo perché i requisiti di configurazione di tempdb non sono soddisfatti. Il messaggio viene registrato anche nel log degli errori.

Per rendere effettivo il limite di percentuale, riconfigurare tempdb i file di dati per soddisfare i requisiti ed eseguire ALTER RESOURCE GOVERNOR RECONFIGURE di nuovo. Per altre informazioni sulla configurazione di SIZE, FILEGROWTHe MAXSIZE, vedere Opzioni file e filegroup ALTER DATABASE.

Se è attivo un limite di percentuale e si aggiungono, rimuovono o ridimensionano tempdb i file di dati, è necessario eseguire ALTER RESOURCE GOVERNOR RECONFIGURE per aggiornare Resource Governor con le nuove dimensioni massime ( tempdb 100%).

Come funziona

Questa sezione descrive in dettaglio la governance delle tempdb risorse spaziali.

• Man mano che le pagine di dati in tempdb vengono allocate e deallocate, Resource Governor tiene conto dello tempdb spazio utilizzato da ogni gruppo di carico di lavoro.

  Se Resource Governor è abilitato e viene impostato un tempdb limite di consumo di spazio per un gruppo di carico di lavoro e una richiesta (una query) in esecuzione nel gruppo di carico di lavoro tenta di portare il consumo totale tempdb di spazio del gruppo al di sopra del limite, la richiesta viene interrotta con l'errore 1138, gravità 17, Non è stato possibile allocare una nuova pagina per il database 'tempdb' perché questo supera il limite impostato per il gruppo di carico di lavoro 'workload-group-name'.

  Quando una richiesta viene interrotta con l'errore 1138, il valore nella total_tempdb_data_limit_violation_count colonna della vista a gestione dinamica (DMV) sys.dm_resource_governor_workload_groups viene incrementato di uno e l'evento tempdb_data_workload_group_limit_reached esteso viene generato.

• Resource Governor tiene traccia di tutti gli tempdb utilizzi che possono essere attribuiti a un gruppo di lavoro, incluse tabelle temporanee, variabili (incluse variabili di tabella), parametri con valori di tabella, tabelle non temporanee, cursori e l'utilizzo di tempdb durante l'elaborazione delle query, ad esempio spool, spill, tabelle di lavoro e file di lavoro.

  Il consumo di spazio per le tabelle temporanee globali e le tabelle non temporanee in tempdb viene attribuito al gruppo di carico di lavoro che inserisce la prima riga nella tabella, anche se le sessioni in altri gruppi di carico di lavoro aggiungono, modificano o rimuovono righe nella stessa tabella.

• I limiti di consumo configurati tempdb per ogni gruppo di carico di lavoro sono esposti nella vista del catalogo sys.resource_governor_workload_groups nelle colonne group_max_tempdb_data_mb e group_max_tempdb_data_percent.

  Il consumo corrente e il picco di consumo dello spazio da parte di un gruppo di lavoro tempdb sono esposti nella DMV sys.dm_resource_governor_workload_groups, rispettivamente nelle colonne tempdb_data_space_kb e peak_tempdb_data_space_kb.

  Suggerimento

  tempdb_data_space_kb e peak_tempdb_data_space_kb le colonne in sys.dm_resource_governor_workload_groups vengono mantenute anche se non vengono impostati limiti al tempdb consumo di spazio.

  È possibile creare la funzione di classificazione e i gruppi di carico di lavoro senza impostare inizialmente limiti. Monitorare tempdb l'utilizzo da parte di ogni gruppo nel tempo per stabilire modelli di utilizzo rappresentativi e quindi impostare i limiti in base alle esigenze.

• Tempdb l'utilizzo da parte degli archivi delle versioni, incluso l'archivio versioni permanente (PVS) quando il ripristino accelerato del database (ADR) è abilitato in tempdb, non è regolato perché le versioni di riga potrebbero essere usate dalle richieste in più gruppi di carico di lavoro.

• Il consumo di spazio in tempdb viene tenuto conto del numero di pagine di dati da 8 KB usate. Anche se una pagina non è piena di dati, aggiunge 8 KB all'utilizzo tempdb da parte di un gruppo di carico di lavoro.

• Tempdb la contabilità dello spazio viene mantenuta per tutta la durata di un gruppo di lavoro. Se un gruppo di carico di lavoro viene eliminato mentre le tabelle temporanee globali o le tabelle non temporanee i cui dati sono attribuiti a questo gruppo di carico di lavoro rimangono in tempdb, lo spazio usato da queste tabelle non viene attribuito a nessun altro gruppo di carico di lavoro.

• Tempdb la governance delle risorse spaziali gestisce lo spazio nei file di dati tempdb, ma non lo spazio del disco nei volumi sottostanti. A meno che non si preparino anticipatamente i file di dati tempdb alle dimensioni previste, lo spazio sui volumi dove si trova tempdb potrebbe venire occupato da altri file. Se non c'è più spazio disponibile per l'espansione dei file di dati tempdb, tempdb potrebbe esaurire lo spazio prima che venga raggiunto un limite di spazio per i gruppi di carico di lavoro tempdb.

• La governance delle risorse spaziali in tempdb si applica ai file di dati, ma non al file di log delle transazioni. Per assicurarsi che il log delle transazioni in tempdb non consumi una grande quantità di spazio, abilitare ADR in tempdb.

Differenze con il rilevamento dello spazio a livello di sessione

La DMV sys.dm_db_session_space_usage fornisce le tempdb statistiche di allocazione e deallocazione dello spazio per ogni sessione. Anche se è presente una sola sessione in un gruppo di carico di lavoro, le statistiche di utilizzo dello spazio fornite da questa DMV potrebbero non corrispondere esattamente alle statistiche fornite nella visualizzazione sys.dm_resource_governor_workload_groups , per i motivi seguenti:

• A differenza di sys.dm_resource_governor_workload_groups, sys.dm_db_session_space_usage:
  • Non riflette l'utilizzo dello spazio dalle attività attualmente in esecuzione. Le statistiche in sys.dm_db_session_space_usage vengono aggiornate al completamento di un'attività. Le statistiche in sys.dm_resource_governor_workload_groups vengono aggiornate continuamente.
  • Non tiene traccia delle pagine della mappa di allocazione degli indici (IAM). Per altre informazioni, vedere Guida all'architettura di pagine ed estensioni.
• Dopo l'eliminazione delle righe o quando una tabella, un indice o una partizione viene eliminata o troncata, le pagine di dati potrebbero essere deallocate da un processo in background asincrono. La deallocazione della pagina potrebbe essere ritardata. sys.dm_resource_governor_workload_groups riflette queste deallocazioni di pagina man mano che si verificano, anche se la sessione che ha causato queste deallocazioni è stata chiusa e non è più presente in sys.dm_db_session_space_usage.

Procedure consigliate per la governance delle risorse dello spazio tempdb

Prima di configurare la governance delle tempdb risorse spaziali, prendere in considerazione le procedure consigliate seguenti:

• Esaminare le procedure consigliate generali per Resource Governor.

• Per la maggior parte degli scenari, evitare di impostare il tempdb limite di utilizzo dello spazio su un valore ridotto o zero, in particolare per il gruppo di default carico di lavoro. Se lo fai, molte attività comuni potrebbero iniziare a fallire se devono allocare spazio in tempdb. Ad esempio, se si imposta il limite fisso o percentuale su 0 per il default gruppo di carico di lavoro, potrebbe non essere possibile aprire Esplora oggetti in SQL Server Management Studio (SSMS).

• A meno che non siano stati creati gruppi di lavoro personalizzati e una funzione di classificazione che assegna i carichi di lavoro ai gruppi dedicati, evitare di limitare l'utilizzo del tempdb da parte del gruppo di lavoro default. Ciò può interrompere le query con errore 1138 quando tempdb ha ancora spazio inutilizzato che nessun carico di lavoro dell'utente può utilizzare.

• È consentito che la somma dei valori per tutti i gruppi di carico di GROUP_MAX_TEMPDB_DATA_MB lavoro superi le dimensioni massime tempdb . Ad esempio, se la dimensione massima tempdb è di 100 GB, i limiti per il gruppo di carico di lavoro GROUP_MAX_TEMPDB_DATA_MB e il gruppo di carico di lavoro B possono essere 80 GB ciascuno.

  Questo approccio impedisce comunque a ogni gruppo di carico di lavoro di consumare tutto lo spazio in tempdb lasciando 20 GB per altri gruppi di carico di lavoro. Allo stesso tempo, si evitano interruzioni di query non necessarie quando lo spazio disponibile tempdb è ancora disponibile perché i gruppi di carico di lavoro A e B probabilmente non consumano una grande quantità di tempdb spazio contemporaneamente.

  Analogamente, la somma dei valori per tutti i gruppi di carico di GROUP_MAX_TEMPDB_DATA_PERCENT lavoro può superare il 100%. È possibile allocare più tempdb spazio a ogni gruppo se si sa che è improbabile che più gruppi causino un utilizzo elevato tempdb contemporaneamente.

Contenuti correlati

• Esercitazione: Esempi per configurare la governance delle risorse dello spazio tempdb
• Gestore delle risorse
• Esercitazione: Esempi di configurazione di Resource Governor e procedure consigliate
• ALTERA IL GOVERNATORE DELLE RISORSE
• CREA GRUPPO DI CARICO DI LAVORO
• ALTERA GRUPPO DI LAVORO
• RIMUOVI GRUPPO DI CARICO DI LAVORO
• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups