Condividi tramite

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

  • Articolo

Questo articolo consente di risolvere il problema in cui si nota un grave blocco quando il server riscontra un carico elevato.

Versione originale del prodotto: SQL Server
Numero KB originale: 2154845

Sintomi

In un server che esegue Microsoft SQL Server si noterà un blocco grave quando il server riscontra un carico elevato. Le viste a gestione dinamica [sys.dm_exec_request o sys.dm_os_waiting_tasks] indicano che queste richieste o attività sono in attesa di risorse tempdb . Inoltre, il tipo di attesa è PAGELATCH_UPe la risorsa di attesa punta 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).

Note

Se una pagina è divisibile in modo uniforme per 8088, si tratta di una pagina PFS. Ad esempio, la pagina 2:3:905856 è un file PFS in file_id=3 in tempdb.

Le operazioni seguenti usano ampiamente tempdb :

  • Operazione ripetitiva di creazione e rilascio di tabelle temporanee (locale o globale).
  • Variabili di tabella che usano tempdb per l'archiviazione.
  • Tabelle di lavoro associate a CURSORS.
  • Tabelle di lavoro associate a una clausola ORDER BY.
  • Tabelle di lavoro associate a una clausola GROUP BY.
  • File di lavoro associati a HASH PLANS.

Queste attività possono causare problemi di contesa.

Causa

Quando il database tempdb viene usato molto, SQL Server potrebbe riscontrare conflitti quando tenta di allocare le pagine. A seconda del grado di contesa, ciò può causare la breve mancata risposta di query e richieste che coinvolgono tempdb .

Durante la creazione dell'oggetto, due pagine (2) devono essere allocate da un extent misto e assegnate al nuovo oggetto. Una pagina è relativa alla mappa di allocazione degli indici (IAM) e la seconda è per la prima pagina dell'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 gigabyte di dati.

Per allocare una pagina dall'extent misto, SQL Server deve analizzare la pagina Spazio disponibile pagina (PFS) per determinare quale pagina mista è gratuita da allocare. La pagina PFS tiene traccia dello spazio disponibile in ogni pagina e ogni pagina PFS tiene traccia di circa 8000 pagine. La sincronizzazione appropriata viene mantenuta per apportare modifiche alle pagine PFS e SGAM; e che possono 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. Ciò causa un'intensa contesa nella pagina SGAM quando sono in corso diverse allocazioni di pagine miste. Ciò può causare i problemi documentati nella sezione Sintomi .

Note

Le attività di de-allocazione devono anche modificare le pagine. Ciò può contribuire all'aumento della contesa.

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

Risoluzione

  • SQL Server 2016 e versioni successive:

    Revisione

    Per altre informazioni su queste raccomandazioni e altre modifiche introdotte nella revisione di SQL 2016

  • 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 nelle strutture di allocazione. Come regola, se il numero di processori logici è minore o uguale a otto (8), usare lo stesso numero di file di dati dei processori logici. Se il numero di processori logici è maggiore di otto (8), usare otto file di dati. Se la contesa continua, aumentare il numero di file di dati di quattro (4) fino al numero di processori logici fino a quando la contesa non viene ridotta a livelli accettabili. In alternativa, apportare modifiche al carico di lavoro o al codice.

    • Valutare la possibilità di implementare le procedure consigliate in Uso di tempdb in SQL Server 2005.

    • Se i passaggi precedenti non riducono significativamente la contesa di allocazione e la contesa si trova nelle pagine SGAM, implementare il flag di traccia -T1118. In questo flag di traccia, SQL Server alloca extent completi a ogni oggetto di database, eliminando così la contesa nelle pagine SGAM.

      Note

Aumentare il numero di file di dati tempdb con dimensioni uguali

Ad esempio, se le dimensioni dei singoli file di dati di tempdb sono pari a 8 GB e le dimensioni del file di log sono pari a 2 GB, è consigliabile aumentare il numero di file di dati a otto (8) (ognuno di 1 GB per mantenere le dimensioni uguali) e lasciare invariato il file di log. La presenza di file di dati diversi su dischi separati offre un vantaggio aggiuntivo per le prestazioni. Tuttavia, questo non è obbligatorio. I file possono coesistere nello stesso volume del disco.

Il numero ottimale di file di dati tempdb dipende dal grado di contesa visualizzato in tempdb. Come punto di partenza, è possibile configurare tempdb in modo che sia almeno uguale al numero di processori logici assegnati per SQL Server. Per i sistemi di fascia superiore, il numero iniziale potrebbe essere otto (8). Se la contesa non viene ridotta, potrebbe essere necessario aumentare il numero di file di dati.

È consigliabile usare la stessa dimensionamento dei 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. A causa di questo miglioramento, il file iniziale è diverso per ogni allocazione di pagina mista consecutiva (se sono presenti più file). Il nuovo algoritmo di allocazione per SGAM è un round robin puro e non rispetta il riempimento proporzionale per mantenere la velocità. È consigliabile creare tutti i file di dati tempdb con le stesse dimensioni.

In che modo l'aumento del numero di file di dati tempdb riduce la contesa

L'elenco seguente illustra come aumentare il numero di file di dati tempdb con dimensioni uguali riduce la contesa:

  • Se si dispone di un file di dati per tempdb, è disponibile una sola pagina GAM e una pagina SGAM per ogni 4 GB di spazio.

  • L'aumento del numero di file di dati con le stesse dimensioni per tempdb crea in modo efficace una o più pagine GAM e SGAM per ogni file di dati.

  • L'algoritmo di allocazione per GAM alloca un extent alla volta (otto pagine contigue) dal numero di file in modo round robin mentre rispetta il riempimento proporzionale. Pertanto, se sono presenti 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 risorse della pagina PFS viene ridotta perché otto pagine alla volta sono contrassegnate come FULL perché GAM sta allocando le pagine.

In che modo l'implementazione del flag di traccia -T1118 riduce la contesa

Note

Questa sezione si applica solo a SQL Server 2014 e versioni precedenti.

L'elenco seguente illustra come l'uso del flag di traccia -T1118 riduce la contesa:

  • -T1118 è un'impostazione a livello di server.
  • Includere il flag di traccia -T1118 nei parametri di avvio per SQL Server in modo che il flag di traccia rimanga attivo anche dopo il riciclo di SQL Server.
  • -T1118 rimuove quasi tutte le allocazioni di pagine singole nel server.
  • Disabilitando la maggior parte delle allocazioni di pagine singole, si riduce la contesa nella pagina SGAM.
  • Se -T1118 è attivato, quasi tutte le nuove allocazioni vengono effettuate da una pagina GAM (ad esempio, 2:1:2) che alloca otto (8) pagine (un extent) alla volta a un oggetto anziché a una singola pagina da un extent per le prime otto (8) pagine di un oggetto, senza il flag di traccia.
  • Le pagine IAM usano ancora le allocazioni a pagina singola dalla pagina SGAM, anche se -T1118 è attivato. Tuttavia, quando viene combinato con l'hotfix 8.00.0702 e i file di dati tempdb aumentati, l'effetto netto è una riduzione della contesa nella pagina SGAM. Per problemi di spazio, vedere la sezione successiva.

Svantaggi

Lo svantaggio dell'uso di -T1118 è che potrebbero verificarsi aumenti delle dimensioni del database se le condizioni seguenti sono vere:

  • I nuovi oggetti vengono creati in un database utente.
  • Ognuno dei nuovi oggetti occupa meno di 64 KB di spazio di archiviazione.

Se queste condizioni sono vere, è possibile allocare 64 KB (otto pagine * 8 KB = 64 KB) per un oggetto che richiede solo 8 KB di spazio, quindi sprecare 56 KB di spazio di archiviazione. Tuttavia, se il nuovo oggetto utilizza più di 64 KB (otto pagine) nella sua durata, non esiste alcun svantaggio per il flag di traccia. Pertanto, in uno scenario peggiore, SQL Server può allocare sette (7) pagine aggiuntive durante la prima allocazione solo per i nuovi oggetti che non aumentano mai oltre una pagina (1).

Riferimenti