Partager via

Estimer la taille d’un index cluster

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Vous pouvez estimer la quantité d'espace nécessaire au stockage des données d'un index cluster en procédant comme suit :

  1. Calculez l'espace utilisé pour le stockage des données au niveau feuille de l'index cluster.
  2. Calculez l'espace utilisé pour le stockage des informations relatives à l'index cluster.
  3. Faites la somme des valeurs calculées.

Étape 1. Calculer l’espace utilisé pour stocker les données au niveau feuille

  1. Spécifiez le nombre de lignes présentes dans le tableau :

    • Num_Rows = nombre de lignes dans la table

  2. Spécifiez le nombre de colonnes de longueur fixe et variable et calculez l'espace nécessaire à leur stockage :

    Calculez l'espace que chacun de ces groupes de colonnes occupe dans la ligne de données. La taille d'une colonne dépend du type des données et de la longueur spécifiée.

    • Num_Cols = nombre total de colonnes (de longueur fixe et de longueur variable)
    • Fixed_Data_Size = taille totale, en octets, de toutes les colonnes de longueur fixe
    • Num_Variable_Cols = nombre de colonnes de longueur variable
    • Max_Var_Size = taille maximale, en octets, de toutes les colonnes de longueur variable

  3. Si l'index de cluster n'est pas unique, prenez en compte la colonne uniquificateur :

    Cette colonne est une colonne de longueur variable qui accepte les valeurs NULL. Il s'agit de valeurs non nulles et de taille 4 octets dans les lignes qui ont des valeurs de clé non uniques. Cette valeur fait partie de la clé d'index et est nécessaire pour garantir que chaque ligne contient une valeur de clé unique.

    • = Num_ColsNum_Cols + 1
    • = Num_Variable_ColsNum_Variable_Cols + 1
    • = Max_Var_Size + 4

    Ces modifications supposent que toutes les valeurs ne sont pas uniques.

  4. Une partie de la ligne, connue sous le nom de bitmap NULL, est réservée pour gérer la possibilité de valeur NULL de la colonne. Calculez sa taille :

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

    Seule la partie entière de l'expression précédente doit être utilisée ; omettez le reste.

  5. Calculez la taille des données de longueur variable :

    En présence de colonnes de longueur variable dans la table, déterminez l'espace utilisé pour stocker les colonnes dans la ligne au moyen de la formule suivante :

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

    Les octets ajoutés à Max_Var_Size servent à assurer le suivi de chaque colonne variable. Il est supposé, lorsque vous utilisez cette formule, que toutes les colonnes de longueur variable sont entièrement remplies. Si vous pensez qu’un pourcentage inférieur de l’espace de stockage des colonnes de longueur variable sera utilisé, vous pouvez ajuster la valeur de Max_Var_Size en fonction de ce pourcentage pour obtenir une estimation plus précise de la taille globale de la table.

    Vous pouvez combiner des colonnes varchar, nvarchar, varbinaryou sql_variant qui provoquent le dépassement de la largeur totale de la table définie au-delà de 8 060 octets. La longueur de chacune de ces colonnes doit toujours être inférieure à la limite de 8 000 octets pour une colonne varchar, varbinaryou sql_variant et de 4 000 octets pour les colonnes nvarchar . Toutefois, leurs largeurs combinées peuvent dépasser la limite de 8 060 octets dans une table.

    En l’absence de toute colonne de longueur variable, attribuez la valeur 0 à Variable_Data_Size.

  6. Calculez la taille totale de la ligne :

    • = Row_Size + Fixed_Data_Size + Variable_Data_SizeNull_Bitmap + 4

    La valeur 4 correspond à l'espace réservé à l'en-tête d'une ligne de données.

  7. Calculez le nombre de lignes par page (8 096 octets gratuits par page) :

    • Rows_Per_Page = 8096 / (Row_Size + 2)

    Étant donné que les lignes ne s’étendent pas sur les pages, le nombre de lignes par page doit être arrondi à la ligne entière la plus proche. La valeur 2 dans la formule correspond à l'entrée de la ligne dans le tableau d'emplacements de la page.

  8. Calculez le nombre de lignes libres réservées par page en fonction du taux de remplissage Fill_Factor spécifié :

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

    Le facteur de remplissage utilisé dans le calcul est un nombre et non un pourcentage. Étant donné que les lignes ne s’étendent pas sur les pages, le nombre de lignes par page doit être arrondi à la ligne entière la plus proche. À mesure que le facteur de remplissage augmente, plus de données sont stockées sur chaque page et il y a moins de pages. La valeur 2 dans la formule correspond à l'entrée de la ligne dans le tableau d'emplacements de la page.

  9. Calculez ensuite le nombre de pages de données requises pour le stockage de toutes les lignes :

    • Num_Leaf_Pages = Num_Rows / (Rows_Per_Page - - )

    Le nombre de pages de données estimé doit être arrondi à la page entière la plus proche.

  10. Calculez la quantité d’espace nécessaire pour stocker les données au niveau feuille (8 192 octets totaux par page) :

    • Leaf_space_used = 8192 x Num_Leaf_Pages

Étape 2. Calculer l’espace utilisé pour stocker les informations d’index

Vous pouvez estimer la quantité d'espace nécessaire au stockage des niveaux supérieurs de l'index en procédant comme suit :

  1. Spécifiez le nombre de colonnes de longueur fixe et de longueur variable de la clé d'index et calculez l'espace nécessaire à leur stockage :

    Les colonnes clés d'un index peuvent inclure des colonnes de longueur fixe et variable. Pour estimer la taille de la ligne d'index du niveau intérieur, calculez l'espace occupé par chacun de ces groupes de colonnes dans la ligne d'index. La taille d'une colonne dépend du type des données et de la longueur spécifiée.

    • Num_Key_Cols = nombre total de colonnes clés (de longueur fixe et variable)
    • Fixed_Key_Size = taille totale, en octets, de toutes les colonnes clés de longueur fixe
    • Num_Variable_Key_Cols = nombre de colonnes clés de longueur variable
    • Max_Var_Key_Size = taille maximale, en octets, de toutes les colonnes clés de longueur variable

  2. Tenez compte de tout identificateur unique nécessaire si l'index n'est pas unique :

    Cette colonne est une colonne de longueur variable qui accepte les valeurs NULL. Il s’agit de valeurs non nulles et de taille de 4 octets dans des lignes qui ont une clé d’index non unique. Cette valeur fait partie de la clé d'index et est nécessaire pour garantir que chaque ligne contient une valeur de clé unique.

    • = Num_Key_ColsNum_Key_Cols + 1
    • = Num_Variable_Key_ColsNum_Variable_Key_Cols + 1
    • = Max_Var_Key_SizeMax_Var_Key_Size + 4

    Ces modifications supposent que toutes les valeurs ne sont pas uniques.

  3. Calculez la taille de null bitmap :

    En présence de colonnes autorisant des valeurs Null dans la clé d'index, une partie de la ligne d'index est réservée à la bitmap Null. Calculez sa taille :

    • Index_Null_Bitmap = 2 + ((nombre de colonnes dans la ligne d’index + 7) / 8)

    Seule la partie entière de l'expression précédente doit être utilisée. Omettez le reste.

    En l’absence de toute colonne clé autorisant les valeurs Null, attribuez la valeur 0 à Index_Null_Bitmap.

  4. Calculez la taille des données de longueur variable :

    En présence de colonnes de longueur variable, déterminez l'espace utilisé pour le stockage des colonnes dans la ligne d'index au moyen de la formule suivante :

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

    Les octets ajoutés à Max_Var_Key_Size servent à assurer le suivi de chaque colonne de longueur variable. Il est supposé, lorsque vous utilisez cette formule, que toutes les colonnes de longueur variable sont entièrement remplies. Si vous pensez qu’un pourcentage inférieur de l’espace de stockage des colonnes de longueur variable sera utilisé, vous pouvez ajuster la valeur de Max_Var_Key_Size en fonction de ce pourcentage pour obtenir une estimation plus précise de la taille globale de la table.

    En l’absence de toute colonne de longueur variable, attribuez la valeur 0 à Variable_Key_Size.

  5. Calculez la taille de la ligne d'index :

    • Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (représentant la surcharge de l’en-tête de la ligne d’index) + 6 (représentant le pointeur de l’ID de la page enfant)

  6. Calculez le nombre de lignes d’index par page (8 096 octets gratuits par page) :

    • Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)

    Étant donné que les lignes d’index ne s’étendent pas sur les pages, le nombre de lignes d’index par page doit être arrondi à la ligne entière la plus proche. La 2 formule correspond à l’entrée de la ligne dans le tableau d’emplacements de la page.

  7. Calculez le nombre de niveaux contenus dans l'index :

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

    Arrondissez cette valeur au nombre entier supérieur le plus proche. Cette valeur n’inclut pas le niveau de la feuille de l’index clusterisé.

  8. Calculez le nombre de pages non-en-feuilles dans l’index :

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

      où 1 <= Level <= Non-leaf_Levels

    Arrondissez chaque élément de la somme au nombre entier supérieur le plus proche. À titre d’exemple simple, imaginez un index où Num_Leaf_Pages = 1000 and Index_Rows_Per_Page = 25. Le premier niveau d’index au-dessus du niveau feuille stocke 1 000 lignes d’index, qui est une ligne d’index par page feuille, et 25 lignes d’index peuvent correspondre par page. Cela signifie que 40 pages sont requises pour stocker ces 1 000 lignes d’index. Le niveau suivant de l'index doit stocker 40 lignes. Cela signifie qu’il nécessite deux pages. Le niveau final de l’index doit stocker deux lignes. Cela signifie qu’il nécessite une page. Cela donne 43 pages d’index non-feuille. Lorsque ces nombres sont utilisés dans les formules précédentes, le résultat est le suivant :

    • 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, ce qui correspond au nombre de pages décrit dans l’exemple.

  9. Calculez la taille de l’index (8 192 octets totaux par page) :

    • Index_Space_Used = 8192 x Num_Index_Pages

Étape 3. Total des valeurs calculées

Faites la somme des valeurs obtenues à partir des deux étapes précédentes :

  • Taille de l’index cluster (octets) = Leaf_Space_Used + Index_Space_used

Ce calcul ne prend pas en compte les conditions suivantes :

  • Partitionnement : la surcharge d’espace du partitionnement est minimale, mais complexe à calculer. Il n’est pas important d’inclure.

  • Pages d’allocation : il existe au moins une page IAM utilisée pour suivre les pages allouées à un tas. La surcharge d’espace est minimale et il n’existe aucun algorithme pour calculer de façon déterministe exactement le nombre de pages IAM qui seront utilisées.

  • Valeurs d’objet volumineux (LOB) : algorithme permettant de déterminer exactement la quantité d’espace utilisée pour stocker les types de données métier varchar(max), varbinary(max), nvarchar(max), text, ntext, xml et les valeurs d’image sont complexes. Il suffit d’ajouter la taille moyenne des valeurs LOB attendues, de la multiplier par Num_Rows et d’ajouter cela à la taille totale de l’index clusterisé.

  • Compression : vous ne pouvez pas précalculer la taille d’un index compressé.

  • Colonnes éparses : pour plus d’informations sur les besoins en espace des colonnes éparses, consultez Utiliser des colonnes éparses.

Contenu connexe

  • Last updated on