Estimation de la taille d'un index cluster

Mis à jour : 15 septembre 2007

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 le total des valeurs calculées.

Étape 1. Calcul de l'espace utilisé pour le stockage des données au niveau feuille

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 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. Pour plus d'informations, consultez Types de données (Moteur de base de données).
   Num_Cols = nombre total des colonnes (longueur fixe et 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. S'il s'agit d'un index cluster non unique, définissez la colonne uniqueifier :
   Cette colonne est une colonne de longueur variable qui accepte les valeurs NULL. Elle présentera une valeur non NULL et une taille de 4 octets dans les lignes qui contiennent des valeurs de clés 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_Cols = Num_Cols + 1
   Num_Variable_Cols = Num_Variable_Cols + 1
   Max_Var_Size = Max_Var_Size + 4
   Ces modifications supposent que toutes ces valeurs ne seront pas uniques.
4. Une partie de la ligne, appelée bitmap NULL, est réservée à la gestion de l'acceptation de valeurs NULL dans les colonnes. 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 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 Max_Var_Size par ce pourcentage afin d'obtenir une estimation plus précise de la taille globale de la table.

   Remarque :

   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 de 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. Pour plus d'informations, consultez Données de dépassement de ligne de plus de 8 Ko.

   S'il n'y a pas de colonnes de longueur variable, attribuez à ***Variable\_Data\_Size*** la valeur 0.
6. Calculez la taille totale de la ligne :
   Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_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 disponibles par page) :
   Rows_Per_Page = 8096 / (Row_Size + 2)
   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. La valeur 2 contenue dans la formule concerne l'entrée de la ligne dans le tableau d'emplacements de la page.
8. Calculez le nombre de lignes disponibles réservées par page, en fonction du taux de remplissage spécifié :
   Free_Rows_Per_Page = 8096 x ((100 - / 100) / (de******2)
   Le taux de remplissage utilisé dans le calcul est un nombre entier 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 taux de remplissage s'accroît, davantage de données seront stockées sur chaque page et il y aura moins de pages. La valeur 2 contenue dans la formule concerne 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_Pages = Num_Rows / (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.
10. Calculez la quantité d'espace nécessaire pour le stockage des données au niveau feuille (au total, 8192 octets par page) :
    Leaf_space_used* = 8192 x *Num_Pages

Étape 2. Calcul de l'espace utilisé pour le stockage des 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 contenues dans la clé de l'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 au niveau intérieur, calculez l'espace occupé par chacun de ces groupes de colonnes au sein de 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
2. Définissez les colonnes uniqueifier nécessaires s'il s'agit d'un index non unique :
   Cette colonne est une colonne de longueur variable qui accepte les valeurs NULL. Elle présentera une valeur non NULL et une taille de 4 octets dans les lignes qui contiennent des valeurs de clés d'index 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_Key_Cols = Num_Key_Cols + 1
   Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1
   Max_Var_Key_Size = Max_Var_Key_Size + 4
   Ces modifications supposent que toutes ces valeurs ne seront pas uniques.
3. Calculez la taille du bitmap NULL :
   En présence de colonnes acceptant les valeurs NULL dans la clé d'index, une partie de la ligne d'index est réservée à la gestion de l'acceptation de valeurs NULL. Calculez sa taille :
   Index_Null_Bitmap = 2 + ((nombre de colonnes clés acceptant les valeurs NULL + 7) / 8)
   Seule la partie entière de l'expression précédente doit être utilisée. Ignorez le reste.
   En l'absence de colonnes clés acceptant les valeurs NULL, attribuez à Index_Null_Bitmap la valeur 0.
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 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, vous devez attribuer 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 (8096 octets 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. La valeur 2 contenue dans la formule concerne l'entrée de la ligne dans le tableau d'emplacements de la page.
7. Calculez le nombre de niveaux contenus dans l'index :
   Niveaux = 1 + log Index_Rows_Per_Page (Num_Rows / Index_Rows_Per_Page)
   Arrondissez cette valeur au nombre entier supérieur le plus proche. Cette valeur n'inclut pas le niveau feuille de l'index cluster.
8. Calculez le nombre de pages contenues dans l'index :
   Num_Index_Pages =∑Niveau (Num_Rows / (Index_Rows_Per_PageNiveau)) où 1 <= Niveau <= Niveaux
   En guise d'exemple simple, imaginons un index dont le nombre total de lignes d'index nécessaires au-dessus du niveau feuille serait de 1000. Sachant que 25 lignes d'index occupent une page, il faudrait 40 pages pour stocker ces 1000 lignes. Le niveau suivant de l'index doit contenir 40 lignes. Cela représente donc 2 pages. Sachant que le dernier niveau de l'index doit contenir 2 lignes, une seule page est nécessaire. En appliquant ces valeurs à la formule précédente, voici le résultat que l'on obtient :
   Height = 1 + log25 (1000 / 25) = 3
   Num_Index_Pages = (1000/(25)3) + (1000/(25)2) + (1000/(25)1) = 43, qui est le nombre de pages décrites dans l'exemple.
9. Calculez la taille de l'index (8192 octets par page) :
   Index_Space_Used* = 8192 x *Num_Index_Pages

Étape 3. Total des valeurs calculées

Faites le total des valeurs obtenues aux étapes précédentes :

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

Ce calcul ne tient pas compte des facteurs suivants :

• Partitionnement
  L'espace réservé pour le partitionnement est minime, mais complexe à calculer. Il n'est pas nécessaire de l'inclure.
• Pages d'allocation
  Au moins une page IAM est utilisée pour assurer le suivi des pages allouées à un segment de mémoire, mais l'espace réservé est minime. De plus, il n'existe aucun algorithme capable de calculer de manière déterministe et exacte le nombre de pages IAM qui seront utilisées.
• Valeurs LOB
  L'algorithme permettant de déterminer avec exactitude la quantité d'espace qui sera utilisée pour stocker les valeurs des types de données LOB varchar(max), varbinary(max), nvarchar(max), text, ntext, xml et image est complexe. Il suffit simplement de faire la somme de la taille moyenne des valeurs LOB attendues, de la multiplier par Num_Rows, puis d'ajouter le résultat à la taille totale de l'index 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 non-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

Historique des modifications