• Articolo

Stima delle dimensioni di un indice cluster

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. Effettuare il totale dei valori calcolati.

Passaggio 1. Calcolare lo spazio utilizzato per l'archiviazione di dati nel livello foglia

  1. Specificare il numero di righe che verranno incluse nella tabella:

    Num_Rows = numero di righe nella 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. Per ulteriori informazioni, vedere Tipi di dati (Motore di database).

    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, considerare la colonna uniqueifier:

    La colonna uniqueifier è una colonna a lunghezza variabile che ammette valori Null. Sarà una colonna non Null da 4 byte nelle righe che includono valori chiave non univoci. Questo valore fa parte della chiave di indice ed è necessario per garantire che ogni riga includa un valore di chiave univoca.

    Num_Cols = Num_Cols + 1

    Num_Variable_Cols = Num_Variable_Cols + 1

    Max_Var_Size = Max_Var_Size + 4

    In queste modifiche si presuppone che tutti i valori siano non univoci.

  4. Parte della riga, detta mappa di bit Null, è riservata alla gestione del supporto di valori NULL per la colonna. 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 a 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 una percentuale inferiore di utilizzo 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.

<div class="alert">


> [!NOTA]
> <P>È possibile combinare colonne varchar, nvarchar, varbinary o sql_variant che fanno eccedere di 8.060 byte la larghezza totale definita della tabella. La lunghezza di ogni colonna deve essere compresa nel limite di 8.000 byte per una colonna varchar, varbinary o sql_variant e di 4.000 byte per le colonne nvarchar. Le larghezze combinate di tali colonne possono tuttavia superare il limite di 8.060 byte in una tabella. Per ulteriori informazioni, vedere <A href="ms186981(v=sql.100).md">Dati di overflow della riga che superano 8 KB</A>.</P>


</div>

Se non sono disponibili colonne di lunghezza variabile, impostare ***Variable\_Data\_Size*** su 0.

  1. Calcolare le dimensioni totali della riga:

    Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

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

  2. Calcolare il numero di righe per pagina (8096 byte liberi per pagina):

    Rows_Per_Page = 8096 / (Row_Size + 2)

    Poiché le righe non si estendono su più pagine, il numero di righe per pagina deve essere arrotondato alla riga completa più vicina. Il valore 2 nella formula rappresenta la voce della riga nella matrice di slot della pagina.

  3. Calcolare il numero di righe libere riservate per pagina, in base al fattore di riempimentoms177459(v=sql.100).md 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 estendono su più pagine, il numero di righe per pagina deve essere arrotondato alla riga completa più vicina. Con l'aumentare del fattore di riempimento, una maggiore quantità di dati verrà archiviata in ogni pagina ed il numero di queste diminuirà. Il valore 2 nella formula rappresenta la voce della riga nella matrice di slot della pagina.

  4. Calcolare il numero di pagine necessarie per l'archiviazione di tutte le righe:

    Num_Leaf_Pages = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)

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

  5. Calcolare la quantità di spazio necessaria per l'archiviazione dei dati nel livello foglia (8192 byte totali per pagina):

Leaf_space_used* = 8192 x *Num_Leaf_Pages

Passaggio 2. Calcolare lo spazio utilizzato per l'archiviazione di 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 nella chiave di indice e calcolare lo spazio necessario per la relativa archiviazione:

    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. Per ulteriori informazioni, vedere Tipi di dati (Motore di database).

    Num_Key_Cols = numero totale di colonne chiave (a lunghezza fissa e a lunghezza variabile)

    Fixed_Key_Size = dimensione totale 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. Considerare le eventuali colonne uniqueifier necessarie se l'indice è non univoco:

    La colonna uniqueifier è una colonna a lunghezza variabile che ammette valori Null. Sarà una colonna non Null da 4 byte nelle righe che includono valori 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_Cols + 1

    Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1

    Max_Var_Key_Size = Max_Var_Key_Size + 4

    In queste modifiche si presuppone che tutti i valori siano non univoci.

  3. Calcolare le dimensioni della mappa di bit 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. Eliminare gli eventuali residui.

    Se non vi sono colonne chiave che ammettono valori Null, impostare Index_Null_Bitmap su 0.

  4. Calcolare le dimensioni dei dati a 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 utilizzo 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.

  1. 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)

  2. Calcolare il numero di righe di indice per pagina (8096 byte liberi per pagina):

    Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

    Poiché le righe di indice non si estendono su più pagine, il numero di righe di indice per pagina deve essere arrotondato alla riga completa più vicina. Il valore 2 nella formula rappresenta la voce della riga nella matrice di slot della pagina.

  3. 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. Nel valore non è incluso il livello foglia dell'indice cluster.

  4. Calcolare il numero di pagine non foglia dell'indice:

Num_Index_Pages =∑Level (Num_Rows / (Index_Rows_Per_PageLevel))

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. Nel primo livello dell'indice sopra il livello foglia vengono archiviate 1000 righe di indice, ovvero una riga di indice per pagina foglia, ed è possibile inserire 25 righe di indice per pagina. Per archiviare le 1000 righe di indice, sono quindi necessarie 40 pagine. Nel livello successivo dell'indice devono invece essere archiviate 40 righe, pertanto sono necessarie 2 pagine. Nel livello finale dell'indice devono essere archiviate 2 righe, pertanto è necessaria una sola pagina. Si ottengono quindi 43 pagine di indice non foglia. Se nella formula precedente si utilizzano questi numeri, il risultato sarà il seguente:

***Non-leaf\_Levels*** = 1 + log25 (1000 / 25) = 3

***Num\_Index\_Pages*** = 1000/(253)+ 1000/(252) + 1000/(251) = 1 + 2 + 40 = 43, ovvero il numero di pagine descritto nell'esempio.
  1. Calcolare le dimensioni dell'indice (8192 byte totali per pagina):

Index_Space_Used* = 8192 x *Num_Index_Pages

Passaggio 3. Sommare i valori calcolati

Calcolare il totale dei valori ottenuti dai due passaggi precedenti:

Dimensioni indice cluster (byte) = Leaf_Space_Used + Index_Space_used

Nel calcolo non vengono considerati i fattori seguenti:

  • Partizionamento

    L'overhead dello spazio derivante dal partizionamento è minimo, ma difficile da calcolare. Non è fondamentale includerlo.

  • Pagine di allocazione

    Esiste almeno una pagina IAM utilizzata per tenere traccia delle pagine allocate su un heap, ma l'overhead dello spazio è minimo e non è presente alcun algoritmo per calcolare in modo deterministico l'esatto numero di pagine IAM che verranno utilizzate.

  • Valori LOB

    L'algoritmo per determinare con esattezza la quantità di spazio utilizzata per archiviare i tipi di dati LOB varchar(max), varbinary(max), nvarchar(max), text, ntext, xml e image è complesso. È sufficiente aggiungere le dimensioni medie dei valori LOB previste, moltiplicare per Num_Rows e quindi aggiungere il valore ottenuto alle dimensioni totali dell'indice cluster.

  • Compressione

    Non è possibile pre-calcolare la dimensione di un indice compresso.