Heap tables in SQL Server

Nell’ultimo post di questa serie di blog, ho discusso di cosa fosse un indice B-Tree e ho brevemente spiegato la sua storia.

Prima di immergerci negli indici utilizzati da SQL Server, è importante impostare le fondamenta delle strutture dati. La tabella più semplice che puoi creare è un heap. Un heap è una struttura non ordinata di pagine che non sono collegate tra loro.

Quando si dovrebbe usare cumuli?

La raccomandazione di best practice è di evitare gli heap di SQL Server, nella maggior parte dei casi. Kimberly Tripp spiega alcune delle ragioni e il dibattito qui, ma mi occuperò di alto livello. Gli heap, essendo la struttura di archiviazione di base, mancano di funzionalità che migliorano le prestazioni per i carichi di lavoro normali (OLTP). Alcuni sostengono che gli heap sono migliori per le operazioni di estrazione, trasformazione e caricamento (ETL) perché ci sono meno pagine da mantenere durante le operazioni di scrittura (AGGIORNAMENTO/INSERIMENTO/ELIMINAZIONE). In particolare, gli heap possono essere più veloci di un indice B-tree se si verificano suddivisioni di pagina e molti livelli dell’indice devono essere aggiornati. Inoltre, l’identificatore di riga (RID), discusso più avanti, è di soli 8 byte. Questo può funzionare meglio di un indice cluster con molte colonne chiave perché gli indici non cluster devono memorizzare la chiave cluster o RID per ogni riga indicizzata. In genere consiglio di utilizzare un indice cluster (più sugli indici cluster mentre continuiamo questa serie) piuttosto che un heap ma, se li usi, usali solo quando hai dimostrato che saranno più efficienti di un indice cluster. Dopo aver discusso la struttura di un heap, illustrerò i problemi di prestazioni dell’utilizzo degli heap.

Struttura heap

Un heap è un raggruppamento di pagine non ordinate che non sono collegate. Page anatomy è fuori portata per questa serie poiché tutti i tipi di tabelle indicizzate e non indicizzate utilizzano la stessa struttura della pagina, ma ti incoraggio a controllare qui e qui per saperne di più.

Un heap è composto da una o più pagine Index Allocation map (index) che puntano alle pagine di dati che compongono l’heap. L’unica eccezione a questo è quando si ha una riga che è stata aggiornata e non potrebbe più adattarsi alla sua pagina. In tal caso, si ottiene un puntatore di inoltro alla riga che è stata spostata in una pagina esistente con spazio o una nuova pagina. È possibile produrre una catena di record di inoltro se la riga continua a dover essere ricollocata da ulteriori operazioni.

heap-structure-1

Riferimento immagine: MSDN

Un heap ha una riga in sys.le partizioni per partizione e il suo index_id saranno uguali a zero. In questo record, first_iam_page punta alla prima delle pagine Index Allocation map (index). Una pagina maps mappa le pagine per ogni unità di allocazione e gestisce 4 blocchi GB della tabella. Le unità di allocazione includono:

  1. IN_ROW_DATA allocation unit
  2. LOB_DATA allocation unit
  3. ROW_OVERFLOW_DATA allocation unit

La pagina I contiene l’intestazione standard di 96 byte seguita dall’intestazione I. L’intestazione I inizia con otto slot per allocazioni di estensione miste e quindi una bit-map da 8000 byte per individuare le estensioni uniformi allocate alla tabella.

Un’estensione mista è quella che contiene pagine da più di una tabella. Questo è anche indicato come una misura condivisa. Lo scopo di un’estensione mista è quello di risparmiare spazio con tabelle che sono più piccole di 64 KB. Un’estensione uniforme è quella in cui tutte le pagine nell’estensione appartengono a quella singola tabella.

mixed-extent

Riferimento immagine: The Extent

Per indagare sulla nostra pagina I, prima dobbiamo individuarla.

DBCC IND (‘demo’, ‘ dbo.demo’,1)

dbcc-ind

Con DBCC IND, abbiamo 17 pagine di dati e 1 pagina I. Le pagineAM non sono autoreferenziali, quindi Ifid (Id file Id) e Ipid (ID pagina I) sono NULL. Ciò significa che la nostra pagina I è in Id file (PageFID) 1 ed è Id pagina (PagePID) 297. DBCC PAGINA ci permetterà di visualizzare la nostra pagina I.

TRACCIA DBCC(3604);

PAGINA DBCC (‘demo’,1,297,3);

dbcc-page-iam

In GIALLO ci sono otto allocazioni a pagina singola nella nostra misura mista. In ARANCIONE le estensioni uniformi sono visualizzate in intervalli.

Implicazioni sulle prestazioni

L’esecuzione di varie operazioni DML (Data Manipulation Language) su un heap ha questi effetti.

  • INSERISCI – Le nuove righe possono essere posizionate nella prima pagina disponibile con spazio sufficiente. Quindi, ogni volta che viene inserita una nuova riga, verrà probabilmente aggiunta all’ultima pagina.
  • AGGIORNAMENTO-Le righe possono rimanere sulla stessa pagina se si inserisce nella pagina dopo l’aggiornamento, in caso contrario verrà rimosso dalla pagina corrente e posizionato sulla prima pagina disponibile con spazio sufficiente e un puntatore di inoltro è scritto nella sua pagina originale.
  • ELIMINA – I dati non vengono sovrascritti, lo spazio viene semplicemente contrassegnato come disponibile per il riutilizzo. Ciò può far sì che la tabella consumi molto più spazio su disco di quanto sia necessario.
  • SELECT-Una scansione della tabella sull’intera tabella dovrà essere letta per la maggior parte delle query. L’eccezione a questo è quando è disponibile un indice non cluster adatto. Discuteremo gli indici non cluster più avanti in questa serie.

Legge

Senza alcun indice di supporto, un heap richiede una scansione completa della tabella per qualsiasi query. Questo perché la struttura fisica non è ordinata e non vi è alcun collegamento alla pagina per supportare le scansioni dell’intervallo.

IMPOSTA STATISTICHE IO SU

DBCC IND (‘demo’,’dbo.demo’,1)

SELEZIONARE COUNT(*)
DA dbo.demo

Ripetere il nostro comando DBCC IND dall’alto ci ricorda che abbiamo 18 pagine per questa tabella. 17 pagine di dati e 1 pagina I.

–Risultati DBCC IND rimossi perché è già visualizzato sopra.
-18 risultato riga, indica 18 pagine.

(18 righe interessate)
Esecuzione DBCC completata. Se DBCC stampato messaggi di errore, contattare l’amministratore di sistema.

Numero di riga
—-
5031
(1 righe interessate)

Tabella ‘Demo’. Numero di scansione 1, lettura logica 17, lettura fisica 0, lettura avanti 0, lettura logica lob 0, lettura fisica lob 0, lettura avanti lob 0.
(1 riga interessata)

Nota le 17 letture logiche dalla nostra query. Questo perché la pagina I non viene conteggiata nella metrica di lettura logica e abbiamo eseguito una scansione completa della tabella con 18 pagine.

Scrive e indica in avanti

Come accennato in precedenza, gli inserimenti e le eliminazioni sono abbastanza semplici. Un INSERIMENTO si verifica sulla prima pagina con spazio disponibile anche se ciò significa la creazione di una nuova pagina. Le eliminazioni vengono eseguite disallocando lo spazio occupato dalla riga.

Gli aggiornamenti sono dove le cose vanno in discesa per gli heap. Quando viene aggiornato un tipo di dati a lunghezza fissa, l’aggiornamento verrà eseguito sul posto. Se un tipo di dati di lunghezza variabile viene aggiornato e la lunghezza è aumentata, è possibile che la riga non si adatti più alla stessa pagina. Quando la riga non si adatta più, un puntatore di inoltro viene inserito nello spazio in cui si trovava la riga originale e la riga viene trasferita in una nuova pagina. Questo processo ha un impatto negativo sulle prestazioni di lettura. Dimostriamolo.

Originariamente tutti i nostri record nel dbo.tabella demo conteneva VARCHARs con 9 stringhe di caratteri.

SELEZIONA TOP 5 *
DA dbo.demo

dati della tabella demo

Per creare alcuni puntatori in avanti aggiorneremo tutte le nostre righe e aumenteremo la lunghezza della colonna varLen.

aggiorna dbo.demo
imposta varLen = replica (‘a’,100)

Ora abbiamo 95 pagine nel nostro heap perché tutte le nostre righe sono state trasferite in nuove pagine con i puntatori in avanti lasciati indietro.

DBCC IND (‘demo’, ‘ dbo.demo’,1)

dbcc-output-1

Come puoi immaginare, con gli heap che richiedono scansioni complete della tabella, dovremo leggere molte più pagine per la stessa quantità di record ora.

IMPOSTA STATISTICS IO SU

SELEZIONA COUNT (*)
DA dbo.demo
(1 riga interessata)

Tabella ‘Demo’. Numero di scansione 1, lettura logica 4405, lettura fisica 0, lettura avanti 0, lettura logica lob 0, lettura fisica lob 0, lettura avanti lob 0.

Le nostre letture sono aumentate drasticamente perché le pagine originali dovevano essere lette e quindi i puntatori in avanti dovevano essere seguiti. L’impatto sulle prestazioni e lo spreco di spazio in memoria e su disco è il motivo per cui abbiamo bisogno di indici in cluster.

La prossima volta

La struttura dati heap non è un indice ed è spesso la struttura dati con le peggiori prestazioni per le tabelle in SQL Server. Lo abbiamo trattato oggi come una base di comprensione che sarà utile mentre discutiamo gli indici. Nella prossima parte di questa serie, verranno trattati gli indici in cluster, una struttura ad albero B+per i dati delle tabelle.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato.