Condividi tramite

Stimare le dimensioni di un indice cluster

Si applica a:SQL ServerAzure SQL DatabaseAzure SQL Istanza gestitaDatabase SQL in Microsoft Fabric

Per stimare la quantità di spazio necessaria per l'archiviazione dati in un indice cluster, è possibile utilizzare la procedura seguente:

  1. Calcolare lo spazio utilizzato per archiviare dati nel livello foglia dell'indice cluster.
  2. Calcolare lo spazio utilizzato per archiviare le informazioni sugli indici per l'indice cluster.
  3. Sommare i valori calcolati.

Passaggio 1: Calcolare lo spazio usato per archiviare i dati nel livello foglia

  1. Specificare il numero di righe presenti nella tabella:

    • Num_Rows = numero di righe della tabella

  2. Specificare il numero di colonne di lunghezza fissa e e variabile e calcolare lo spazio necessario per la loro archiviazione:

    Calcolare lo spazio occupato da ognuno di questi gruppi di colonne all'interno della riga di dati. Le dimensioni di una colonna dipendono dal tipo di dati e dalla lunghezza specificata.

    • Num_Cols = numero totale di colonne (a lunghezza fissa e a lunghezza variabile)
    • Fixed_Data_Size = dimensioni totali in byte di tutte le colonne a lunghezza fissa
    • Num_Variable_Cols = numero di colonne a lunghezza variabile
    • Max_Var_Size = dimensioni massime in byte di tutte le colonne a lunghezza variabile

  3. Se l'indice cluster non è univoco, tenere conto della colonna uniqueifier :

    La colonna "uniqueifier" è una colonna a lunghezza variabile che può contenere valori Null. È diverso da Null ed è di 4 byte nelle righe con valori di chiave non univoci. Questo valore fa parte della chiave di indice ed è necessario per garantire che ogni riga includa un valore di chiave univoco.

    • Num_Cols = Num_ColsNum_Cols + 1
    • Num_Variable_Cols = Num_Variable_ColsNum_Variable_Cols + 1
    • = Max_Var_SizeMax_Var_Size + 4

    Queste modifiche presuppongono che tutti i valori siano non univoci.

  4. Parte della riga, nota come bitmap Null, è riservata per gestire la nullabilità delle colonne. Calcolarne le dimensioni:

    • Null_Bitmap = 2 + ((Num_Cols + 7) / 8)

    Utilizzare solo la parte intera del risultato dell'espressione indicata in precedenza, eliminando eventuali residui.

  5. Calcolare le dimensioni dei dati di lunghezza variabile:

    Se la tabella include colonne di lunghezza variabile, determinare la quantità di spazio utilizzata per l'archiviazione delle colonne nella riga:

    • Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

    I byte aggiunti a Max_Var_Size servono a tenere traccia di ogni colonna variabile. Questa formula si basa sul presupposto che tutte le colonne a lunghezza variabile siano piene al 100%. Se si prevede che verrà usata una percentuale inferiore dello spazio di archiviazione delle colonne a lunghezza variabile, è possibile modificare il valore di Max_Var_Size in base a tale percentuale per ottenere una stima più accurata delle dimensioni complessive della tabella.

    È possibile combinare colonne varchar, nvarchar, varbinaryo sql_variant che fanno eccedere gli 8.060 byte per la larghezza totale definita della tabella. La lunghezza di ogni colonna deve essere compresa nel limite di 8.000 byte per una colonna varchar, varbinaryo sql_variant e di 4.000 byte per le colonne nvarchar . Tuttavia, le larghezze combinate potrebbero superare il limite di 8.060 byte in una tabella.

    Se non sono presenti colonne di lunghezza variabile, impostare Variable_Data_Size su 0.

  6. Calcolare le dimensioni totali della riga:

    • = Row_Size + Fixed_Data_Size + Variable_Data_SizeNull_Bitmap + 4

    Il valore 4 rappresenta l'overhead dell'intestazione di riga di una riga di dati.

  7. Calcolare il numero di righe per pagina (8.096 byte gratuiti per pagina):

    • Rows_Per_Page = 8096 / (Row_Size + 2)

    Poiché le righe non si distribuiscono su più pagine, il numero di righe per pagina deve essere arrotondato per difetto all'intera riga più vicina. Il valore 2 nella formula è per la voce della riga nell'array di slot della pagina.

  8. Calcolare il numero di righe libere riservate per pagina, in base al fattore di riempimento specificato:

    • Free_Rows_Per_Page = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)

    Il fattore di riempimento utilizzato nel calcolo è un valore intero, non una percentuale. Poiché le righe non si distribuiscono su più pagine, il numero di righe per pagina deve essere arrotondato per difetto all'intera riga più vicina. Man mano che aumenta il fattore di riempimento, più dati vengono archiviati in ogni pagina e sono presenti meno pagine. Il valore 2 nella formula è per la voce della riga nell'array di slot della pagina.

  9. Calcolare il numero di pagine necessario per archiviare tutte le righe:

    • Num_Leaf_PagesNum_Rows / (Rows_Per_PageFree_Rows_Per_Page)

    Il numero di pagine stimato deve essere arrotondato alla pagina intera più vicina.

  10. Calcolare la quantità di spazio necessaria per archiviare i dati nel livello foglia (8.192 byte totali per pagina):

    • Leaf_space_used = 8192 x Num_Leaf_Pages

Passaggio 2. Calcolare lo spazio usato per archiviare le informazioni sull'indice

Per stimare la quantità di spazio necessario per archiviare i livelli superiori dell'indice, è possibile utilizzare la seguente procedura:

  1. Specificare il numero di colonne a lunghezza fissa e a lunghezza variabile incluse nella chiave dell'indice e calcolare lo spazio necessario per archiviarle:

    Le colonne chiave di un indice possono includere colonne a lunghezza fissa e a lunghezza variabile. Per stimare le dimensioni delle righe di indice di livello interno, calcolare lo spazio occupato da ognuno di questi gruppi di colonne all'interno della riga di indice. Le dimensioni di una colonna dipendono dal tipo di dati e dalla lunghezza specificata.

    • Num_Key_Cols = numero totale di colonne chiave (a lunghezza fissa e a lunghezza variabile)
    • Fixed_Key_Size = dimensioni totali in byte di tutte le colonne chiave a lunghezza fissa
    • Num_Variable_Key_Cols = numero di colonne chiave a lunghezza variabile
    • Max_Var_Key_Size = dimensioni massime in byte di tutte le colonne chiave a lunghezza variabile

  2. Tenere conto di qualsiasi univoco necessario se l'indice non è univoco:

    La colonna "uniqueifier" è una colonna a lunghezza variabile che può contenere valori Null. È non nullo e di dimensioni pari a 4 byte nelle righe con valori di chiave di indice non univoci. Questo valore fa parte della chiave di indice ed è necessario per garantire che ogni riga includa un valore di chiave univoco.

    • Num_Key_Cols = Num_Key_ColsNum_Key_Cols + 1
    • Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1
    • = Max_Var_Key_SizeMax_Var_Key_Size + 4

    Queste modifiche presuppongono che tutti i valori siano non univoci.

  3. Calcolare le dimensioni del bitmap null:

    Se nella chiave di indice sono incluse colonne che ammettono valori null, una parte della riga di indice viene riservata per la mappa di bit null. Calcolarne le dimensioni:

    • Index_Null_Bitmap = 2 + ((numero di colonne nella riga di indice + 7) / 8)

    Deve essere utilizzata solo la parte Integer dell'espressione precedente. Scartare eventuali resti.

    Se invece non sono disponibili colonne chiave che ammettono i valori Null, impostare Index_Null_Bitmap su 0.

  4. Calcolare le dimensioni dei dati di lunghezza variabile:

    Se l'indice include colonne a lunghezza variabile, determinare la quantità di spazio utilizzata per l'archiviazione delle colonne nella riga di indice:

    • Variable_Key_Size = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size

    I byte aggiunti a Max_Var_Key_Size servono a tenere traccia di ogni colonna a lunghezza variabile. Questa formula si basa sul presupposto che tutte le colonne a lunghezza variabile siano piene al 100%. Se si prevede una percentuale inferiore di uso dello spazio di archiviazione delle colonne a lunghezza variabile, è possibile modificare il valore di Max_Var_Key_Size in base a tale percentuale per ottenere una stima più accurata delle dimensioni complessive della tabella.

    Se non sono disponibili colonne a lunghezza variabile, impostare Variable_Key_Size su 0.

  5. Calcolare le dimensioni della riga di indice:

    • Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (per l'overhead dell'intestazione di una riga di indice) + 6 (per il puntatore ID della pagina figlio)

  6. Calcolare il numero di righe di indice per pagina (8.096 byte gratuiti per pagina):

    • Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

    Poiché le righe di indice non si estendono su singole pagine, il numero di righe di indice per pagina deve essere arrotondato all'intero numero di righe più vicino. Il 2 nella formula rappresenta la voce della riga nell'array di slot della pagina.

  7. Calcolare il numero di livelli nell'indice:

    • Non-leaf_Levels = 1 + log (Index_Rows_Per_Page) (Num_Leaf_Pages) / Index_Rows_Per_Page

    Arrotondare questo valore per eccesso al numero intero più vicino. Questo valore non include il livello foglia dell'indice clusterizzato.

  8. Calcolare il numero di pagine non foglia nell'indice:

    • Num_Index_Pages = ∑Level (Num_Leaf_Pages/ (Index_Rows_Per_Page^Level))

      dove 1 <= Level <= Non-leaf_Levels

    Arrotondare ogni addendo al numero intero più vicino. Per un esempio semplice, considerare un indice in cui Num_Leaf_Pages = 1000 e Index_Rows_Per_Page = 25. Il primo livello di indice al di sopra del livello foglia archivia 1.000 righe di indice, ovvero una riga di indice per pagina foglia e 25 righe di indice possono adattarsi per pagina. Ciò significa che sono necessarie 40 pagine per archiviare le 1.000 righe di indice. Nel livello successivo dell'indice devono invece essere archiviate 40 righe, Ciò significa che richiede due pagine. Il livello finale dell'indice deve archiviare due righe. Ciò significa che richiede una pagina. In questo modo vengono fornite 43 pagine di indice non foglia. Se nella formula precedente si utilizzano questi numeri, il risultato sarà il seguente:

    • Non-leaf_Levels = 1 + log(25) (1000 / 25) = 3

    • Num_Index_Pages = 1000/(25^3)+ 1000/(25^2) + 1000/(25^1) = 1 + 2 + 40 = 43, ovvero il numero di pagine descritto nell'esempio.

  9. Calcolare le dimensioni dell'indice (8.192 byte totali per pagina):

    • Index_Space_Used = 8192 x Num_Index_Pages

Passaggio 3. Totale dei valori calcolati

Sommare i valori ottenuti nei due passaggi precedenti:

  • Dimensioni indice cluster (byte) = Leaf_Space_Used + Index_Space_used

Questo calcolo non considera le condizioni seguenti:

  • Partizionamento: il sovraccarico dello spazio dal partizionamento è minimo, ma complesso da calcolare. Non è importante includere.

  • Pagine di allocazione: è presente almeno una pagina IAM usata per tenere traccia delle pagine allocate a un heap. L'overhead dello spazio è minimo e non esiste alcun algoritmo per calcolare in modo deterministico il numero di pagine IAM che verranno usate.

  • Valori LOB (Large Object): algoritmo per determinare esattamente la quantità di spazio che verrà usata per archiviare i tipi di dati LOB varchar(max), varbinary(max), nvarchar(max), text, ntext, xml e image è complesso. È sufficiente aggiungere solo le dimensioni medie dei valori LOB previsti, moltiplicare per Num_Rows e aggiungerla alla dimensione totale dell'indice cluster.

  • Compressione: non è possibile precalcolatare le dimensioni di un indice compresso.

  • Colonne di tipo sparse: per informazioni sui requisiti di spazio delle colonne di tipo sparse, vedere Usare colonne di tipo sparse.

Contenuti correlati

  • Last updated on