Consigli per ridurre la contesa di allocazione nel database tempdb SQL Server

  • Articolo

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

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

Sintomi

In un server che esegue Microsoft SQL Server si nota un grave blocco quando il server sta riscontrando un carico elevato. Viste a gestione dinamica [sys.dm_exec_request o sys.dm_os_waiting_tasks] indica 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 avere il formato 2:1:1, 2:1:3 e così via (pagine PFS e SGAM in tempdb).

Nota

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

Le operazioni seguenti usano in modo esteso tempdb :

  • Operazioni ripetitive di creazione e rilascio di tabelle temporanee (locali o globali).
  • 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 PIANI HASH.

Queste attività possono causare problemi di contesa.

Causa

Quando il database tempdb viene usato in modo frequente, SQL Server potrebbero riscontrare contese quando tenta di allocare pagine. A seconda del grado di contesa, ciò può causare un breve blocco delle query e delle richieste che coinvolgono tempdb .

Durante la creazione dell'oggetto, due (2) pagine 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 PFS (Page Free Space) per determinare quale pagina mista può essere allocata. La pagina PFS tiene traccia dello spazio disponibile in ogni pagina e ogni pagina PFS tiene traccia di circa 8000 pagine. Viene mantenuta la sincronizzazione appropriata per apportare modifiche alle pagine PFS e SGAM; e che 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. 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 .

Nota

Le attività di deallocazione devono anche modificare le pagine. Questo 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 in SQL 2016, vedere

  • 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. Di 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 multipli 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 raccomandazioni sulle 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. Con questo flag di traccia, SQL Server alloca extent completi a ogni oggetto di database, eliminando così la contesa nelle pagine SGAM.

      Nota

Aumentare il numero di file di dati tempdb con dimensioni uguali

Ad esempio, se la dimensione del file di dati singolo di tempdb è di 8 GB e la dimensione del file di log è di 2 GB, è consigliabile aumentare il numero di file di dati a otto (8) (ognuno di 1 GB per mantenere il ridimensionamento uguale) e lasciare il file di log così com'è. La presenza di diversi file di dati in dischi separati offrirebbe un vantaggio aggiuntivo per le prestazioni. Tuttavia, questa operazione non è necessaria. I file possono coesistere nello stesso volume del disco.

Il numero ottimale di file di dati tempdb dipende dal grado di contesa riscontrato 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 il ridimensionamento uguale 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 pagine miste consecutive (se esiste più di un file). Il nuovo algoritmo di allocazione per SGAM è 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.

Come l'aumento del numero di file di dati tempdb riduce la contesa

L'elenco seguente illustra come l'aumento del numero di file di dati tempdb con dimensioni uguali riduce la contesa:

  • Se si dispone di un file di dati per tempdb, sono disponibili solo una 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 rispettando il riempimento proporzionale. Pertanto, se sono presenti 10 file di dimensioni uguali, la prima allocazione è 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.

Come l'implementazione del flag di traccia -T1118 riduce la contesa

Nota

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 a pagina singola nel server.
  • Disabilitando la maggior parte delle allocazioni a pagina singola, 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 combinata con l'hotfix 8.00.0702 e l'aumento dei file di dati tempdb , l'effetto netto è una riduzione della contesa nella pagina SGAM. Per informazioni sullo spazio, vedere la sezione successiva.

Svantaggi

Lo svantaggio dell'uso di -T1118 è che è possibile che le dimensioni del database aumentino se si verificano le condizioni seguenti:

  • 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, sprecando così 56 KB di spazio di archiviazione. Tuttavia, se il nuovo oggetto usa più di 64 KB (otto pagine) nella sua durata, non vi è 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 superano mai una (1) pagina.

Riferimenti