Partager via

Estimer la taille d’un index non cluster

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

Utilisez la procédure suivante pour estimer la quantité d'espace nécessaire au stockage d'un index non cluster :

  1. Calculez les variables à utiliser dans les étapes 2 et 3.

  2. Calculez l'espace utilisé pour stocker les informations d'index au niveau feuille de l'index non cluster.

  3. Calculez l'espace utilisé pour stocker les informations d'index aux niveaux non-feuille de l'index non cluster.

  4. Faites la somme des valeurs calculées.

Étape 1. Calculer des variables à utiliser dans les étapes 2 et 3

Vous pouvez utiliser la procédure suivante afin de calculer les variables utilisées pour estimer la quantité d'espace nécessaire au stockage des niveaux supérieurs de l'index.

  1. Déterminez le nombre de lignes que contiendra la table :

    • Num_Rows = nombre de lignes dans la table

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

  3. Tenez compte du localisateur de ligne de données nécessaire si l'index n'est pas unique :

    Si l'index non-cluster n'est pas unique, le localisateur de ligne de données est associé à la clé d'index non-cluster pour produire une valeur de clé unique pour chaque ligne.

    Si l'index non-cluster est au-dessus d'un segment, le localisateur de ligne de données est le RID de ce segment. Sa taille est de 8 octets.

    • = Num_Key_ColsNum_Key_Cols + 1
    • = Num_Variable_Key_ColsNum_Variable_Key_Cols + 1
    • = Max_Var_Key_SizeMax_Var_Key_Size + 8

    Si l'index non cluster est au-dessus d'un index cluster, le localisateur de lignes de données est la clé de clustering. Les colonnes qui doivent être combinées avec la clé de l'index non clusterisé sont celles de la clé de clustering qui ne sont pas déjà présentes dans l’ensemble des colonnes de l'index non clusterisé.

    • Num_Key_Cols = Num_Key_Cols + nombre de colonnes de clés de clustering non présentes dans l’ensemble de colonnes de clés d’index non cluster (+ 1 si l’index cluster est non unique)

    • Fixed_Key_Size = Fixed_Key_Size + taille totale, en octets, des colonnes de clés de clustering de longueur fixe non présentes dans l’ensemble de colonnes de clés d’index non cluster

    • Num_Variable_Key_Cols = Num_Variable_Key_Cols + nombre de colonnes de clés de clustering de longueur variable non présentes dans l’ensemble de colonnes de clés d’index non cluster (+ 1 si l’index cluster n’est pas unique)

    • Max_Var_Key_Size = Max_Var_Key_Size + taille maximale, en octets, des colonnes de clés de clustering de longueur variable non présentes dans l’ensemble de colonnes de clés d’index non cluster (+ 4 si l’index cluster n’est pas unique)

  4. Une partie de la ligne, appelée bitmap de valeurs nulles, peut être réservée pour gérer la nullabilité des colonnes. Calculez sa taille :

    En présence de colonnes autorisant des valeurs Null dans la clé d'index, notamment toutes les colonnes clés de clustering nécessaires comme décrit dans l'étape 1.3, une partie de la ligne d'index est réservée à la bitmap Null.

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

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

    En présence de colonnes de longueur variable dans la clé d'index, notamment les colonnes clés d'index cluster nécessaires, déterminez l'espace utilisé pour le stockage des colonnes dans la ligne d'index :

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

    Les octets ajoutés à Max_Var_Key_Size sont destinés au suivi de chaque colonne variable. Cette formule suppose que toutes les colonnes de longueur variable sont pleines de 100 %. 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.

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

  7. 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. Le chiffre 2 de la formule concerne l'entrée de la ligne du tableau d'emplacement de la page.

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

Vous pouvez utiliser la procédure suivante pour estimer la quantité d'espace nécessaire au stockage du niveau feuille de l'index. Vous avez besoin des valeurs conservées à l’étape 1 pour effectuer cette étape.

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

    Vous pouvez étendre un index non cluster en incluant les colonnes sans clé en plus des colonnes de clés d'index. Ces colonnes supplémentaires sont stockées uniquement au niveau feuille de l'index non-cluster. Pour plus d’informations, consultez Créer des index avec des colonnes incluses.

    Note

    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. Ceci s'applique également aux lignes de feuille d'index non-cluster possédant des colonnes incluses.

    Si l’index non cluster n’a pas de colonnes incluses, utilisez les valeurs de l’étape 1, y compris les modifications déterminées à l’étape 1.3 :

    • = Num_Leaf_ColsNum_Key_Cols
    • Taille_Fixe_Feuille = Taille_Fixe_Clé
    • = Num_Variable_Leaf_ColsNum_Variable_Key_Cols
    • Max_Var_Leaf_Size = Max_Var_Key_Size

    Si l'index non-cluster ne possède aucune colonne incluse, ajoutez les valeurs appropriées aux valeurs de l'étape 1, y compris les éventuelles modifications de l'étape 1.3. La taille d'une colonne dépend du type des données et de la longueur spécifiée. Pour plus d’informations, consultez Types de données.

    • Num_Leaf_Cols = Num_Key_Cols + nombre de colonnes incluses
    • Fixed_Leaf_Size = Fixed_Key_Size + taille totale, en octets, des colonnes incluses de longueur fixe
    • Num_Variable_Leaf_Cols = Num_Variable_Key_Cols + nombre de colonnes incluses de longueur variable
    • Max_Var_Leaf_Size = Max_Var_Key_Size + taille maximale, en octets, des colonnes incluses de longueur variable

  2. Tenez compte du localisateur de ligne de données :

    Si l’index non cluster n’est pas unique, la surcharge du localisateur de lignes de données a déjà été prise en compte à l’étape 1.3 et aucune autre modification n’est requise. Passez à l'étape suivante.

    Si l'index non-cluster est unique, le localisateur de ligne des données doit être pris en compte dans toutes les lignes au niveau feuille.

    Si l'index non-cluster est au-dessus d'un segment, le localisateur de ligne de données est le RID du segment (taille 8 octets).

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

    Si l'index non cluster est au-dessus d'un index cluster, le localisateur de lignes de données est la clé de clustering. Les colonnes qui doivent être combinées avec la clé de l'index non clusterisé sont celles de la clé de clustering qui ne sont pas déjà présentes dans l’ensemble des colonnes de l'index non clusterisé.

    • Num_Leaf_Cols = Num_Leaf_Cols + nombre de colonnes de clés de clustering non présentes dans l’ensemble de colonnes de clés d’index non cluster (+ 1 si l’index cluster n’est pas unique)

    • Fixed_Leaf_Size = Fixed_Leaf_Size + nombre de colonnes de clés de clustering de longueur fixe non présentes dans l’ensemble de colonnes de clés d’index non cluster

    • Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + nombre de colonnes de clés de clustering de longueur variable non présentes dans l’ensemble de colonnes de clés d’index non cluster (+ 1 si l’index cluster n’est pas unique)

    • Max_Var_Leaf_Size = Max_Var_Leaf_Size + taille, en octets, des colonnes de clés de clustering de longueur variable non présentes dans l’ensemble de colonnes de clés d’index non cluster (+ 4 si l’index cluster n’est pas unique)

  3. Calculez la taille de null bitmap :

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

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

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

    En présence de colonnes de longueur variable (colonnes clés ou incluses), notamment les colonnes clés de clustering nécessaires comme décrit précédemment dans l’étape 2.2, déterminez l’espace utilisé pour le stockage des colonnes dans la ligne d’index :

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

    Les octets ajoutés à Max_Var_Key_Size sont destinés au suivi de chaque colonne variable. Cette formule suppose que toutes les colonnes de longueur variable sont pleines de 100 %. Si vous estimez qu’un pourcentage moins élevé de l’espace de stockage des colonnes de longueur variable sera utilisé, vous pouvez multiplier la valeur Max_Var_Leaf_Size par ce pourcentage pour obtenir une estimation plus précise de la taille globale de la table.

    En l’absence de colonnes de longueur variable (colonnes clés ou incluses), attribuez à Variable_Leaf_Size la valeur 0.

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

    • Leaf_Row_Size = Fixed_Leaf_Size + Variable_Leaf_Size + Leaf_Null_Bitmap + 1 (représentant la surcharge de l’en-tête de la ligne d’index)

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

    • Leaf_Rows_Per_Page = 8096 / (Leaf_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. Le chiffre 2 de la formule concerne l'entrée de la ligne du tableau d'emplacement de la page.

  7. 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) / (Leaf_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. Le chiffre 2 de la formule concerne l'entrée de la ligne du tableau d'emplacement de la page.

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

    • Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

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

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

    • Leaf_Space_Used = 8192 x Num_Leaf_Pages

Étape 3. Calculer l’espace utilisé pour stocker les informations d’index dans les niveaux non feuille

Utilisez la procédure suivante pour estimer la quantité d'espace nécessaire au stockage des niveaux intermédiaires et racine de l'index. Vous avez besoin des valeurs conservées des étapes 2 et 3 pour effectuer cette étape.

  1. Calculez le nombre de niveaux non-feuille contenus dans l'index :

    • Niveaux non-lieu = 1 + log(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 feuille de l’index non-cluster.

  2. Calculez le nombre de pages non-feuille contenues dans l'index :

    • Num_Index_Pages = ∑ Niveau (Num_Leaf_Pages/Index_Rows_Per_Page^Niveau) où 1 <= Niveau <= Niveaux

    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 requiert donc 2 pages. Le niveau final de l'index doit stocker 2 lignes. Cela requiert donc 1 page. Il en résulte 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.

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

    • Index_Space_Used = 8192 x Num_Index_Pages

Étape 4. Total des valeurs calculées

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

Taille de l’index non 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 cela.

  • Pages d’allocation : il existe au moins une page IAM utilisée pour suivre les pages allouées à un tas, mais la surcharge d’espace est minimale et il n’existe pas d’algorithme pour calculer de manière déterministe exactement le nombre de pages IAM à utiliser.

  • 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 BLOB attendues, de multiplier par Num_Rows et d'ajouter le résultat à la taille totale de l'index non 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