Estimation de la taille d'un index non-cluster

  • Article

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

  1. Calculez l'espace utilisé pour stocker les informations d'index dans les niveaux non-feuille de l'index non-cluster.
  2. Calculez l'espace utilisé pour stocker les informations d'index dans le niveau feuille de l'index non-cluster.
  3. Faites la somme des valeurs calculées.

Étape 1. Calcul de l'espace utilisé pour le stockage des informations d'index dans les niveaux non-feuille

ms190620.note(fr-fr,SQL.90).gifImportant :
Conservez les valeurs utilisées dans cette étape pour vous en servir dans l'étape 2.

Vous pouvez utiliser la procédure suivante 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 de 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 contenir des colonnes de longueur fixe et de longueur 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. Pour plus d'informations, consultez Types de données (Moteur de base de données).
    Num_Key_Cols = nombre total de colonnes clés (longueur fixe et longueur 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_Cols = Num_Key_Cols + 1
    Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1
    Max_Var_Key_Size = Max_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 cluster. Les colonnes qui doivent être associées à la clé d'index non-cluster sont les colonnes de la clé de cluster qui ne sont pas déjà présentes dans l'ensemble des colonnes clés de l'index non-cluster.
    Num_Key_Cols = Num_Key_Cols + nombre de colonnes de clé de cluster non présentes dans l'ensemble de colonnes de clé de l'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é de cluster de longueur fixe non présentes dans l'ensemble de colonnes de clé de l'index non-cluster.
    Num_Variable_Key_Cols = Num_Variable_Key_Cols + nombre de colonnes de clé de cluster de longueur variable non présentes dans l'ensemble de colonnes de clé de l'index non-cluster (+ 1 si l'index cluster est non unique).
    Max_Var_Key_Size = Max_Var_Key_Size + taille maximale en octets des colonnes de clé de cluster de longueur variable non présentes dans l'ensemble de colonnes de clé de l'index non-cluster (+ 4 si l'index cluster est non unique).
  4. Une partie de la ligne, nommée null bitmap, peut être réservée à la gestion de l'acceptation de valeurs NULL dans les colonnes. Calculez sa taille :
    En présence de colonnes pouvant être NULL dans la clé d'index, notamment toutes les colonnes clés de cluster nécessaires comme décrit dans l'étape 3, une partie de la ligne d'index est réservée à la gestion des valeurs de colonne NULL.
    Index_Null_Bitmap = 2 + ((nombre de colonnes de clé acceptant des valeurs NULL + 7) / 8)
    Seule la partie entière de l'expression précédente doit être utilisée. Omettez le reste.
    En absence de colonnes clés pouvant être NULL, attribuez à Index_Null_Bitmap la valeur 0.
  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 Cette formule suppose que toutes les colonnes de longueur variable sont entièrement remplies. Si vous estimez qu'un pourcentage inférieur de l'espace de stockage des colonnes de longueur variable sera utilisé, vous pouvez multiplier la valeur Max_Var_Key_Size par ce pourcentage pour obtenir une estimation plus précise de la taille globale de la table.
    En cas d'absence de colonnes de longueur variable, définissez Variable_Key_Size sur 0.
  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 libres par page) :
    Index_Rows_Per_Page = 8096 / (Index_Row_Size + 2)
    Comme les lignes d'index ne peuvent pas être fractionnées sur plusieurs pages de données, arrondissez le nombre de lignes d'index par page à la ligne entière inférieure. Le chiffre 2 de la formule concerne l'entrée de la ligne du tableau d'emplacement de la page.
  8. Calculez le nombre de niveaux de l'index :
    Levels = 1 + log Index_Rows_Per_Page (Num_Rows / Index_Rows_Per_Page)
    cette valeur n'inclut pas le niveau feuille de l'index non-cluster.
  9. Calculez le nombre de pages de l'index :
    Num_Index_Pages = ?Level (Index_Rows_Per_Page)Level – 1
    où 1 <= Level <= Levels
    À titre d'exemple simple, supposez un index dont le nombre total de lignes d'index nécessaires au niveau supérieur à la feuille est 1 000 et que 10 lignes d'index peuvent rentrer dans une page. Cent pages sont donc nécessaires pour stocker ces 1 000 lignes. Le niveau suivant de l'index doit stocker 100 lignes, soit 10 pages. Le niveau final de l'index doit stocker 10 lignes, soit 1 page. L'utilisation de ces nombres dans les formules précédentes produit les résultats suivants :
    Height = 1 + log10 (1000 / 10) = 3
    Num_Index_Pages = (10)3-1 + (10)2-1 + (10)1-1 = 111, est le nombre de pages décrites dans l'exemple.
  10. Calculez la taille de l'index (8 192 octets par page) :
    Index_Space_Used* = 8192 x *Num_Index_Pages

Étape 2. Calcul de l'espace utilisé pour le stockage des 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 aurez besoin des valeurs conservées dans l'étape 1 pour effectuer cette étape-ci.

  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 :
    ms190620.note(fr-fr,SQL.90).gifRemarque :
    SQL Server 2005 introduit la possibilité d'étendre un index non-cluster en incluant les colonnes non clés 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éation d'index avec colonnes incluses.
    ms190620.note(fr-fr,SQL.90).gifRemarque :
    SQL Server 2005 introduit la possibilité d'associer des colonnes varchar, nvarchar, varbinary ou 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, varbinary ou sql_variant et 4 000 octets pour les colonnes nvarchar. Toutefois, l'association de leurs largeurs peut 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. Pour plus d'informations, consultez Données de dépassement de ligne de plus de 8 Ko.
    Si l'index non-cluster ne possède aucune colonne incluse, utilisez les valeurs de l'étape 1, y compris les éventuelles modifications déterminées dans l'étape 1.3 :

Num_Leaf_Cols* = *Num_Key_Cols Fixed_Leaf_Size* = *Fixed_Key_Size Num_Variable_Leaf_Cols* = *Num_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 (Moteur de base 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 ligne de données a déjà été prise en compte dans l'étape 1.3 et aucune modification supplémentaire n'est nécessaire. 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_Cols + 1
Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + 1
Max_Var_Leaf_Size = Max_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 cluster. Les colonnes qui doivent être associées à la clé d'index non-cluster sont les colonnes de la clé de cluster qui ne sont pas déjà présentes dans l'ensemble des colonnes clés de l'index non-cluster.
Num_Leaf_Cols = Num_Leaf_Cols + nombre de colonnes de clé de cluster non présentes dans l'ensemble de colonnes de clé de l'index non-cluster (+ 1 si l'index cluster est non unique)
Fixed_Leaf_Size = Fixed_Leaf_Size + nombre des colonnes de clé de cluster de longueur fixe non présentes dans l'ensemble de colonnes de clé de l'index non-cluster
Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + nombre de colonnes de clé de cluster de longueur variable non présentes dans l'ensemble de colonnes de clé de l'index non-cluster (+ 1 si l'index cluster est non unique).
Max_Var_Leaf_Size = Max_Var_Leaf_Size + taille en octets des colonnes de clé de cluster de longueur variable non présentes dans l'ensemble de colonnes de clé de l'index non-cluster (+ 4 si l'index cluster est non 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 dans la clé d'index, notamment les colonnes clés de cluster 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 Cette formule suppose que toutes les colonnes de longueur variable sont entièrement remplies. Si vous estimez qu'un pourcentage inférieur 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 cas d'absence de colonnes de longueur variable, 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 (représentant le pointeur de l'ID de la page enfant)
6. Calculez le nombre de lignes d'index par page (8 096 octets libres par page) :
Leaf_Rows_Per_Page = 8096 / (Leaf_Row_Size + 2)
Comme les lignes d'index ne peuvent pas être fractionnées sur plusieurs pages de données, arrondissez le nombre de lignes d'index par page à la ligne entière inférieure. 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. Comme les lignes ne peuvent pas être fractionnées sur plusieurs pages de données, arrondissez le nombre de lignes par page à la ligne entière inférieure. Au fur et à mesure que le facteur de remplissage s’accroît, davantage de données seront stockées sur chaque page et il y aura 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 libres par page) :
Leaf_Space_Used* = 8192 x *Num_Leaf_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 non-cluster (octets) = Leaf_Space_Used + Index_Space_used

Ce calcul ne tient pas compte des éléments suivants :

  • Partitionnement
    L'espace supplémentaire issu du partitionnement est minimal, mais son calcul est compliqué. Sa prise en compte n'est pas essentielle.
  • Pages d'allocation
    Une page IAM au moins est utilisée pour assurer le suivi des pages allouées à un segment, mais le surplus d'espace est minime et il n'existe pas d'algorithme pour calculer de manière déterministe le nombre exact de pages IAM qui seront utilisées.
  • Valeurs des objets LOB (Large OBject)
    L'algorithme permettant de déterminer exactement la quantité d'espace qui sera utilisé pour stocker les valeurs varchar(max), varbinary(max), nvarchar(max), text, ntext, xml et image des types de données LOB est complexe. Il suffit d'ajouter simplement la taille moyenne des valeurs LOB attendues, de la multiplier par Num_Rows et d'ajouter ce produit à la taille totale de l'index non-cluster.

Voir aussi

Concepts

Indications pour la conception d'index cluster
Création d'index (Moteur de base de données)
Indications pour la conception d'index non-cluster
Estimation de la taille d'une table
Estimation de la taille d'un index cluster
Estimation de la taille d'un segment de mémoire

Autres ressources

Estimation de la taille d'une base de données

Aide et Informations

Assistance sur SQL Server 2005