Suggerimenti per ridurre la contesa dell'allocazione nel database tempdb di SQL Server

Original product version: SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022
Numero KB originale: 2154845

Sommario

Questo articolo illustra come diagnosticare e ridurre i conflitti di allocazione nel database SQL Server tempdb. Vengono illustrate PAGELATCH_UP le attese nelle pagine PFS (Page Free Space), Global Allocation Map (GAM) e Shared Global Allocation Map (SGAM). Descrive come usare più file di dati di dimensioni uguali tempdb e, nelle versioni precedenti, i flag di traccia 1118 e 1117 per migliorare le prestazioni SQL Server in carichi di lavoro simultanei pesanti.

Sintomi

In un server che esegue Microsoft SQL Server si noterà un blocco grave quando il server è sottoposto a un carico elevato. Le viste di gestione dinamica sys.dm_exec_requests e sys.dm_os_waiting_tasks mostrano che le richieste o le attività sono in attesa delle risorse tempdb. Il tipo di attesa è PAGELATCH_UP, e la risorsa di attesa fa riferimento alle pagine in tempdb. Queste pagine potrebbero essere nel formato 2:1:1, 2:1:3 e così via (pagine PFS e SGAM in tempdb).

Le operazioni seguenti usano tempdb ampiamente:

• Operazioni ripetitive di creazione e rilascio su tabelle temporanee (locale o globale).
• Variabili di tabella che usano tempdb per l'archiviazione.
• Tabelle di lavoro associate ai cursori.
• Tabelle di lavoro associate a una ORDER BY clausola .
• Tabelle di lavoro associate a una GROUP BY clausola .
• File di lavoro associati ai piani hash.

Queste attività possono causare conflitti.

Causa

Quando tempdb viene usato molto, SQL Server può riscontrare conflitti durante l'allocazione delle pagine. A seconda del grado di contesa, le query e le richieste che usano tempdb potrebbero diventare brevemente non rispondenti.

Durante la creazione di oggetti nelle versioni precedenti di SQL Server, il sistema alloca due pagine da un extent misto e le assegna al nuovo oggetto. Una pagina è la pagina Mappa allocazione indici (IAM) e la seconda è la prima pagina per l'oggetto. SQL Server tiene traccia degli extent misti usando la pagina Mappa di allocazione globale condivisa (SGAM). Ogni pagina SGAM tiene traccia di circa 4 GB di dati.

Per allocare una pagina in un extent misto, SQL Server esegue una scansione della pagina Page Free Space (PFS) per trovare una pagina mista libera. La pagina PFS tiene traccia dello spazio disponibile in ogni pagina e ogni pagina PFS tiene traccia di circa 8.000 pagine. La sincronizzazione è necessaria quando SQL Server modifica le pagine PFS e SGAM e tale sincronizzazione può bloccare altri modificatori per brevi periodi.

Quando SQL Server cerca una pagina mista da allocare, avvia sempre l'analisi nello stesso file e nella stessa pagina SGAM. Questo comportamento causa una contesa intensa nella pagina SGAM quando molte allocazioni di pagine miste vengono eseguite contemporaneamente, che produce i sintomi descritti in precedenza.

Per altre informazioni sui meccanismi di allocazione usati da SQL Server (SGAM, GAM, PFS e IAM), vedere la sezione Contenuto correlato.

Soluzione

SQL Server 2014 ha raggiunto la fine del supporto esteso il 9 luglio 2024, quindi la maggior parte dei lettori dovrebbe seguire la guida SQL Server 2016 e versioni successive.

SQL Server 2016 e versioni successive

1. Esaminare il database tempdb per informazioni sulle prestazioni correnti.
2. Assicurarsi che tempdb abbia più file di dati di uguali dimensioni. Come punto di partenza, usare un file di dati per processore logico fino a otto file. Se la contesa continua, aggiungere file in gruppi di quattro fino al numero di processori logici.
3. Mantenere aggiornata l'istanza SQL Server con l'aggiornamento cumulativo più recente per ottenere ulteriori miglioramenti all'allocazione. Ad esempio, vedere KB 4099472: miglioramento dell'algoritmo round robin della pagina PFS in SQL Server 2014, 2016 e 2017.
4. Se la contenzione persiste su SQL Server 2019 o una versione successiva, consultare le indicazioni sul database tempdb relative agli aggiornamenti PFS simultanei e ai metadati ottimizzati per la memoria tempdb.

SQL Server 2014 e versioni precedenti

Per migliorare la concorrenza di tempdb, provare i metodi seguenti:

• Aumentare il numero di file di dati in tempdb per ottimizzare la larghezza di banda del disco e ridurre la contesa sulle strutture di allocazione. Come regola, se il numero di processori logici è di otto o meno, usare lo stesso numero di file di dati dei processori logici. Se il numero di processori logici è maggiore di otto, iniziare con otto file di dati. Se la contesa continua, aggiungere file di dati in gruppi di quattro fino al numero di processori logici o modificare il carico di lavoro o il codice.

• Esaminare le linee guida sulle procedure consigliate in Lavorare con tempdb in SQL Server 2005.

• Se i passaggi precedenti non riducono significativamente la contesa di allocazione e la contesa riguarda le pagine SGAM, attivare il flag di traccia -T1118. Con questo flag di traccia, SQL Server alloca extent interi a ogni oggetto del database, riducendo notevolmente la contesa sulle pagine SGAM.

  Nota

  • Questo flag di traccia influisce su tutti i database nell'istanza di SQL Server. Per verificare se la contesa di allocazione si verifica nelle pagine SGAM, vedere Monitoraggio della contesa causata dalle operazioni DML.
  • Per SQL Server 2014, applicare Service Pack 3 per ottenere la correzione descritta in KB 4099472, che riduce ulteriormente la contesa eseguendo allocazioni cicliche delle pagine PFS tra diverse pagine PFS nello stesso file di dati.

Altre informazioni sull'allocazione di tempdb

Comportamento predefinito di tempdb in SQL Server 2016 e versioni successive

A partire da SQL Server 2016, sono stati introdotti diversi miglioramenti che riducono la contesa di allocazione tempdb per impostazione predefinita, quindi in genere non è necessario abilitare i flag di traccia 1118 o 1117:

• Le allocazioni in extent misti sono disabilitate per tempdb per impostazione predefinita (comportamento precedentemente abilitato dal trace flag 1118). Tutte le nuove allocazioni derivano da extent uniformi.
• Tutti i tempdb file di dati aumentano automaticamente di dimensioni insieme (comportamento precedentemente abilitato dal flag di traccia 1117).
• SQL Server Il programma di installazione consiglia più file di dati di dimensioni uguali tempdb in base al numero di processori logici, fino a otto.
• Aggiornamenti PFS simultanei riducono ulteriormente la contesa sui latch delle pagine PFS in SQL Server 2019 e versioni successive.

Se utilizzi SQL Server 2016 o una versione successiva, concentrati sul dimensionamento e sul numero di file tempdb. Usare i flag di traccia solo in SQL Server 2014 o versioni precedenti.

Esempio di file di dati di tempdb con dimensioni uguali

Ad esempio, se il singolo tempdb file di dati è di 8 GB e il file di log è di 2 GB, aumentare il numero di file di dati a otto (ogni 1 GB per mantenere la dimensione uguale) e lasciare invariato il file di log. L'inserimento dei file di dati in dischi separati può offrire un vantaggio aggiuntivo sulle prestazioni, ma non è necessario. I file possono coesistere nello stesso volume del disco.

Il numero ottimale di file di dati tempdb dipende dal grado di contesa riscontrato. Come punto di partenza, impostare il numero di file uguale al numero di processori logici assegnati a SQL Server. Per i sistemi di fascia superiore, iniziare con otto file. Se la contesa non viene ridotta, aggiungere altri file di dati.

Usare il ridimensionamento uguale per tutti i file di dati. SQL Server 2000 Service Pack 4 (SP4) ha introdotto una correzione che usa un algoritmo round robin per le allocazioni di pagine miste. Con questo miglioramento, il file iniziale cambia per ogni allocazione di pagine miste consecutive quando esistono più file. L'algoritmo di allocazione SGAM è puro round robin e non rispetta il riempimento proporzionale, quindi creare tutti i tempdb file di dati con le stesse dimensioni.

In che modo più file di dati tempdb riducono i conflitti

L'aggiunta di file di dati di dimensioni tempdb uguali riduce la contesa nei modi seguenti:

• Se si dispone di un file di dati per tempdb, è disponibile una pagina GAM e una pagina SGAM per ogni 4 GB di spazio.
• Più file di dati delle stesse dimensioni creano in modo efficace una o più pagine GAM e SGAM per ogni file di dati.
• L'algoritmo di allocazione GAM alloca un extent alla volta (otto pagine contigue) dai file in modo round robin, rispettando il riempimento proporzionale. Ad esempio, se si dispone di 10 file di dimensioni uguali, la prima allocazione proviene da File1, la seconda da File2, la terza da File3 e così via.
• La contesa delle pagine PFS viene ridotta perché otto pagine alla volta vengono contrassegnate come FULL mentre GAM alloca le pagine.

In che modo il flag di traccia -T1118 riduce la contesa

Il trace flag -T1118 riduce la contesa come segue:

• -T1118 è un'impostazione a livello di server.
• Aggiungere -T1118 ai parametri di avvio SQL Server in modo che il flag di traccia rimanga attivo dopo un SQL Server riavvio.
• -T1118 rimuove quasi tutte le allocazioni a pagina singola nel server.
• La disattivazione della maggior parte delle allocazioni a pagina singola riduce la contesa delle pagine SGAM.
• Quando -T1118 è attivo, quasi tutte le nuove allocazioni provengono da una pagina GAM (ad esempio, 2:1:2) che alloca otto pagine (un extent) alla volta a un oggetto, invece di singole pagine di un extent per le prime otto pagine di un oggetto.
• Le pagine IAM usano ancora allocazioni di singole pagine dalla pagina SGAM anche quando -T1118 è attivato. Tuttavia, in combinazione con l'hotfix 8.00.0702 e un maggior numero di file di dati tempdb, l'effetto complessivo è una riduzione della contesa delle pagine SGAM. Per problemi di spazio, vedere la sezione successiva.

Flag di traccia 1118 a confronto con il comportamento predefinito nelle versioni moderne di SQL Server

Svantaggi del trace flag -T1118

Lo svantaggio dell'uso -T1118 è che le dimensioni del database possono aumentare se entrambe le condizioni seguenti sono vere:

• Creare nuovi oggetti in un database utente.
• Ogni nuovo oggetto occupa meno di 64 KB di spazio di archiviazione.

In queste condizioni, SQL Server può allocare 64 KB (otto pagine * 8 KB) per un oggetto che richiede solo 8 KB, che spreca 56 KB di spazio di archiviazione. Se il nuovo oggetto utilizza più di 64 KB (otto pagine) nel corso del suo ciclo di vita, l'uso del flag di traccia non presenta svantaggi. Nel peggiore dei casi, SQL Server alloca sette pagine aggiuntive durante la prima allocazione, ma solo per nuovi oggetti che non crescono mai oltre una pagina.

Contenuti correlati

• Database tempdb
• DBCC TRACEON: i flag di traccia (Transact-SQL)