Condividi tramite

Stimare le dimensioni di un indice non cluster

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

Per stimare la quantità di spazio necessaria per archiviare un indice non cluster, effettuare le operazioni seguenti:

  1. Calcolare le variabili da utilizzare nei passaggi 2 e 3.

  2. Calcolare lo spazio utilizzato per l'archiviazione di informazioni sull'indice nel livello foglia dell'indice non cluster.

  3. Calcolare lo spazio utilizzato per l'archiviazione di informazioni sull'indice nei livelli non foglia dell'indice non cluster.

  4. Sommare i valori calcolati.

Passaggio 1: Calcolare le variabili da usare nei passaggi 2 e 3

Per calcolare le variabili utilizzate per stimare la quantità di spazio necessario per archiviare i livelli superiori dell'indice, è possibile effettuare le operazioni seguenti:

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

    • Num_Rows = numero di righe della tabella

  2. 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

  3. Considerare anche l'indicatore di posizione delle righe di dati, necessario se l'indice non è univoco:

    Se l'indice non cluster non è univoco, l'indicatore di posizione delle righe di dati viene combinato con la chiave dell'indice non cluster in modo da ottenere un valore di chiave univoco per ciascuna riga.

    Se l'indice non-cluster è su un heap, l'indicatore di posizione delle righe di dati corrisponde al RID dell'heap. le cui dimensioni sono pari a 8 byte.

    • 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 + 8

    Se l'indice non clusterizzato è su un indice clusterizzato, l'indicatore di posizione delle righe di dati corrisponde alla chiave di clustering. Le colonne che devono essere combinate con la chiave dell'indice non clusterizzato sono quelle della chiave di clusterizzazione che non sono già presenti nel set di colonne chiave dell'indice non clusterizzato.

    • Num_Key_Cols = Num_Key_Cols + numero delle colonne chiave di clustering che non sono nel set di colonne chiave dell'indice non clusterizzato (+ 1 se l'indice clusterizzato non è univoco)

    • Fixed_Key_Size = Fixed_Key_Size + dimensioni totali in byte delle colonne di chiave di clustering a lunghezza fissa che non sono nel set delle colonne chiave dell'indice non clusterizzato

    • Num_Variable_Key_Cols = Num_Variable_Key_Cols + numero di colonne chiave di clustering a lunghezza variabile non nel set di colonne chiave di indice non cluster (+ 1 se l'indice cluster è non univoco)

    • Max_Var_Key_Size = Max_Var_Key_Size + dimensione massima in byte delle colonne chiave di raggruppamento a lunghezza variabile non presenti nel set delle colonne chiave di indice non raggruppato (+ 4 se l'indice raggruppato non è unico)

  4. Una parte della riga, nota come bitmap null, potrebbe essere riservata per gestire la possibilità di nullità delle colonne. Calcolarne le dimensioni:

    Se sono presenti colonne che ammettono valori null nella chiave dell'indice, incluse le eventuali colonne chiave di clustering necessarie descritte nel passaggio 1.3, parte della riga di indice viene riservata alla mappa di bit null.

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

    Deve essere utilizzata solo la parte Integer dell'espressione precedente. Scartare i residui.

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

  5. Calcolare le dimensioni dei dati a lunghezza variabile:

    Se sono presenti colonne a lunghezza variabile nella chiave dell'indice, incluse eventuali colonne chiave dell'indice cluster necessarie, determinare lo spazio utilizzato per archiviare le colonne all'interno della 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 sono per tenere traccia di ogni colonna variabile. Questa formula presuppone che tutte le colonne a lunghezza variabile siano complete 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.

  6. 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 figlia)

  7. 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 pagine, il numero di righe di indice per pagina deve essere arrotondato per difetto al numero intero più vicino. Il numero 2 nella formula si riferisce all'elemento della riga nell'array di slot della pagina.

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

Per stimare la quantità di spazio necessaria per archiviare il livello foglia dell'indice, effettuare le operazioni seguenti. Per completare questo passaggio, sono necessari i valori mantenuti nel passaggio 1.

  1. Specificare il numero di colonne a lunghezza fissa e a lunghezza variabile incluse a livello foglia e calcolare lo spazio necessario per archiviarle:

    È possibile estendere un indice non cluster includendo colonne non chiave oltre alle colonne chiave. Queste colonne aggiuntive vengono archiviate solo al livello delle foglie dell'indice non-cluster. Per altre informazioni, vedere Creare indici con colonne incluse.

    Note

    È 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. Lo stesso vale inoltre per le righe foglia dell'indice non clusterizzato con colonne incluse.

    Se l'indice non cluster non include colonne, usare i valori del passaggio 1, incluse eventuali modifiche determinate nel passaggio 1.3:

    • = Num_Leaf_ColsNum_Key_Cols
    • Fixed_Leaf_Size = Fixed_Key_Size
    • Num_Variable_Leaf_Cols = Num_Variable_Key_Cols
    • Max_Var_Leaf_Size = Max_Var_Key_Size

    Se l'indice non cluster dispone di colonne incluse, aggiungere i valori appropriati a quelli del passaggio 1, incluse le eventuali modifiche del passaggio 1.3. Le dimensioni di una colonna dipendono dal tipo di dati e dalla lunghezza specificata. Per ulteriori informazioni, vedi Tipi di dati.

    • Num_Leaf_Cols = Num_Key_Cols + numero di colonne incluse
    • Fixed_Leaf_Size = Fixed_Key_Size + dimensioni massime in byte di tutte le colonne incluse a lunghezza fissa
    • Num_Variable_Leaf_Cols = Num_Variable_Key_Cols + numero di colonne incluse a lunghezza variabile
    • Max_Var_Leaf_Size = Max_Var_Key_Size + dimensioni massime in byte di tutte le colonne incluse a lunghezza variabile

  2. Considerare l'indicatore di posizione delle righe di dati:

    Se l'indice non cluster non è univoco, l'overhead per il localizzatore di righe di dati è già stato considerato nel passaggio 1.3 e non sono necessarie ulteriori modifiche. Andare al passaggio successivo.

    Se l'indice non cluster è univoco, è necessario considerare l'indicatore di posizione delle righe di dati per tutte le righe al livello foglia.

    Se l'indice non cluster è su un heap, l'indicatore di posizione delle righe di dati è il RID dell'heap (dimensione di 8 byte).

    • Num_Leaf_Cols = Num_Leaf_ColsNum_Leaf_Cols + 1
    • = Num_Variable_Leaf_ColsNum_Variable_Leaf_Cols + 1
    • Max_Var_Leaf_Size = Max_Var_Leaf_SizeMax_Var_Leaf_Size + 8

    Se l'indice non clusterizzato è su un indice clusterizzato, l'indicatore di posizione delle righe di dati corrisponde alla chiave di clustering. Le colonne che devono essere combinate con la chiave dell'indice non clusterizzato sono quelle della chiave di clusterizzazione che non sono già presenti nel set di colonne chiave dell'indice non clusterizzato.

    • Num_Leaf_Cols = Num_Leaf_Cols + numero di colonne chiave di clustering non incluse nell'insieme delle colonne chiave di indice non clusterizzato (+ 1 se l'indice clusterizzato è non univoco)

    • Fixed_Leaf_Size = Fixed_Leaf_Size + numero di colonne chiave di clustering a lunghezza fissa che non sono incluse nel set di colonne chiave dell'indice non clusterizzate

    • Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + numero di colonne chiave del clustering a lunghezza variabile che non sono nel set di colonne chiave dell'indice non clusterizzato (+ 1 se l'indice cluster è non univoco)

    • Max_Var_Leaf_Size = Max_Var_Leaf_Size + dimensione in byte delle colonne di clustering di lunghezza variabile non nel set di colonne chiave dell'indice non clusterizzato (+ 4 se l'indice clusterizzato non è univoco)

  3. Calcolare la dimensione del bitmap nullo.

    • Leaf_Null_Bitmap = 2 + ((Num_Leaf_Cols + 7) / 8)

    Deve essere utilizzata solo la parte Integer dell'espressione precedente. Scartare i residui.

  4. Calcolare le dimensioni dei dati a lunghezza variabile:

    Se sono presenti colonne a lunghezza variabile (colonne chiave o incluse), incluse eventuali colonne chiave di clustering necessarie descritte al passaggio 2.2, determinare lo spazio usato per archiviare le colonne all'interno della riga di indice:

    • Variable_Leaf_Size = 2 + (Num_Variable_Leaf_Cols x 2) + Max_Var_Leaf_Size

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

    Se non sono presenti colonne a lunghezza variabile (colonne chiave o incluse), impostare Variable_Leaf_Size su 0.

  5. Calcolare le dimensioni della riga di indice:

    • Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_Bitmap + 1 (per l'overhead dell'intestazione di una riga di indice)

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

    • Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)

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

  7. 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) / (Leaf_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 per difetto al numero intero di righe più vicino. Man mano che aumenta il fattore di riempimento, più dati vengono archiviati in ogni pagina e sono presenti meno pagine. Il numero 2 nella formula si riferisce all'elemento della riga nell'array di slot della pagina.

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

    • Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

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

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

    • Spazio_Foglia_Usato = 8192 x Numero_Pagine_Foglia

Passaggio 3. Calcolare lo spazio utilizzato per archiviare le informazioni sull'indice nei livelli non fogliare

Per stimare la quantità di spazio necessaria per archiviare i livelli intermedio e radice dell'indice, effettuare le operazioni seguenti: Per completare questo passaggio, sono necessari i valori mantenuti dai passaggi 2 e 3.

  1. Calcolare il numero di livelli non fogliari dell'indice:

    • Livelli non foglia = 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 non clusterizzato.

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

    • Num_Index_Pages = ∑Livello (Num_Leaf_Pages/Index_Rows_Per_Page^Livello) dove 1 <= Livello <= Livelli

    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, cioè una riga di indice per ogni pagina foglia, con 25 righe di indice che possono essere inserite 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, 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 + 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.

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

    • Index_Space_Used = 8192 x Num_Index_Pages

Passaggio 4. Totale dei valori calcolati

Sommare i valori ottenuti nei due passaggi precedenti:

Dimensione indice non clusterizzato (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, ma il sovraccarico dello spazio è minimo e non esiste alcun algoritmo per calcolare in modo deterministico esattamente il numero di pagine IAM da usare.

  • 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 la dimensione media dei valori LOB previsti, moltiplicare per Num_Rows e aggiungerla alla dimensione totale dell'indice non 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