Partager via

Estimer la taille d’un index non cluster

Procédez comme suit pour estimer la quantité d’espace nécessaire pour stocker 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 des feuilles de l’index non clusterisé.

  3. Calculez l’espace utilisé pour stocker les informations d’index dans les niveaux non-feuilles de l’index non clusterisé.

  4. Total des valeurs calculées.

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

Vous pouvez utiliser les étapes suivantes pour calculer les variables utilisées pour estimer la quantité d’espace nécessaire pour stocker les niveaux supérieurs de l’index.

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

    Num_Rows = nombre de lignes dans la table

  2. Spécifiez le nombre de colonnes de longueur fixe et de longueur variable dans la clé d’index et calculez l’espace nécessaire pour leur stockage :

    Les colonnes clés d’un index peuvent inclure des colonnes de longueur fixe et de longueur variable. Pour estimer la taille de ligne d’index de 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 de données et de la spécification de longueur.

    Num_Key_Cols = nombre total de colonnes clés (longueur fixe et longueur variable)

    Fixed_Key_Size = taille totale d’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 d’octets de toutes les colonnes clés de longueur variable

  3. Tenez compte du localisateur de lignes de données qui est requis si l’index n’est pas unique.

    Si l’index non cluster n’est pas unique, le localisateur de lignes de données est combiné avec la clé d’index non cluster pour produire une valeur de clé unique pour chaque ligne.

    Si l’index non clusterisé se trouve sur un tas, le localisateur de lignes de données est le RID du tas. Il s’agit d’une taille de 8 octets.

    = Num_Key_ColsNum_Key_Cols + 1

    Num_Variable_Key_Cols = Num_Variable_Key_Cols + 1

    = Max_Var_Key_SizeMax_Var_Key_Size + 8

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

    Num_Key_Cols = Num_Key_ColsNum_Key_Cols + nombre de colonnes clés de clustering non dans l’ensemble de colonnes clés d’index non clusterisé (+ 1 si l’index clusterisé n’est pas unique)

    Fixed_Key_Size = Fixed_Key_Size + taille totale en octets des colonnes de clé de clustering de longueur fixe qui ne sont pas incluses dans l’ensemble des colonnes de clés d’index non cluster

    = Num_Variable_Key_ColsNum_Variable_Key_Cols + nombre de colonnes clés de regroupement de longueur variable ne faisant pas partie de l'ensemble des colonnes clés d'index non regroupées (+ 1 si l'index regroupé n'est pas unique)

    = (Max_Var_Key_Size)Max_Var_Key_Size + taille maximale d’octets des colonnes clés de clustering de longueur variable qui ne sont pas incluses dans l’ensemble de colonnes clés d’index non clusterisé (+ 4 si l’index clusterisé n’est pas unique)

  4. Une partie de la ligne, connue sous le nom de bitmap null, peut être réservée pour gérer la nullabilité des colonnes. Calculez sa taille :

    S’il existe des colonnes nullables dans la clé d’index, y compris les colonnes clés de clustering nécessaires, comme décrit à 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. Jetez tout reste.

    S’il n’existe aucune colonne clé nullable, définissez Index_Null_Bitmap sur 0.

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

    S’il existe des colonnes de longueur variable dans la clé d’index, y compris les colonnes de clé d’index cluster nécessaires, déterminez la quantité d’espace utilisée pour stocker les 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 prévoyez qu’un pourcentage plus petit de l’espace de stockage de colonne de longueur variable sera utilisé, vous pouvez ajuster la valeur Max_Var_Key_Size par ce pourcentage pour obtenir une estimation plus précise de la taille globale de la table.

    S’il n’existe aucune colonne de longueur variable, définissez Variable_Key_Size sur 0.

  6. Calculez la taille de ligne d’index :

    Index_Row_Size = Fixed_Key_Size + Variable_Key_Size + Index_Null_Bitmap + 1 (pour la surcharge de l'en-tête de ligne d'une ligne d'index) + 6 (pour le pointeur de l'ID de la page enfant)

  7. Calculez le nombre de lignes d’index par page (8096 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 2 dans la formule correspond à l’entrée de la ligne dans le tableau d’emplacements de la page.

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

Vous pouvez utiliser les étapes suivantes pour estimer la quantité d’espace nécessaire pour stocker le niveau feuille de l’index. Vous aurez besoin des valeurs conservées de 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 pour leur stockage :

    Remarque

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

    Remarque

    Vous pouvez combiner varchar, nvarcharou varbinarysql_variant des colonnes qui entraînent une largeur de table définie totale supérieure à 8 060 octets. La longueur de chacune de ces colonnes doit toujours être comprise dans la limite de 8 000 octets pour une varcharcolonne, varbinaryou sql_variant une colonne, et 4 000 octets pour nvarchar les colonnes. Toutefois, leurs largeurs combinées peuvent dépasser la limite de 8 060 octets dans une table. Cela s’applique également aux lignes de feuilles d’index non-clusterisé qui incluent des colonnes.

    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_Cols = Num_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 comporte des colonnes incluses, ajoutez les valeurs appropriées aux valeurs de l’étape 1, y compris les modifications apportées à l’étape 1.3. La taille d’une colonne dépend du type de données et de la spécification de longueur. Pour plus d’informations, consultez Types de données (Transact-SQL).

    Num_Leaf_Cols = Num_Key_Cols + nombre de colonnes incluses

    Fixed_Leaf_Size = Fixed_Key_Size + taille totale en octets des colonnes à longueur fixe incluses

    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 d’octets des colonnes incluses de longueur variable

  2. Prenez en compte l'identifiant des lignes 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 modification supplémentaire n’est requise. Passez à l’étape suivante.

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

    Si l’index non cluster se trouve sur un tas, le localisateur de lignes de données est le tas RID (taille de 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-clusterisé se trouve sur un index clusterisé, le localisateur de lignes de données est la clé de clusterisation. Les colonnes qui doivent être combinées avec la clé d’index non cluster sont les colonnes de la clé de clustering qui ne sont pas déjà présentes dans l’ensemble de colonnes de clé d’index non cluster.

    Num_Leaf_Cols + nombre de colonnes de clé de clustering qui ne figurent pas dans l’ensemble des colonnes de clé de l’index non clusterisé (+ 1 si l’index clusterisé n’est pas unique)

    = Fixed_Leaf_SizeFixed_Leaf_Size + nombre de colonnes clés de clustering de longueur fixe qui ne sont pas dans l'ensemble des colonnes de clé d'index non clusterisé

    Num_Variable_Leaf_Cols = Num_Variable_Leaf_Cols + nombre de colonnes de clé de clustering à longueur variable qui ne sont pas dans l’ensemble des colonnes clés d’index non clusterisé (+ 1 si l’index clusterisé n’est pas unique)

    Max_Var_Leaf_Size = Max_Var_Leaf_Size + taille en octets des colonnes clés de clustering de longueur variable qui ne sont pas dans l'ensemble des colonnes clés de l'index non cluster (+ 4 si l'index cluster n'est pas unique)

  3. Calculez la taille bitmap Null :

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

    Seule la partie entière de l’expression précédente doit être utilisée. Jetez tout reste.

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

    S’il existe des colonnes de longueur variable dans la clé d’index, y compris les colonnes clés de clustering nécessaires, comme décrit précédemment à l’étape 2.2, déterminez la quantité d’espace utilisée pour stocker les 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 prévoyez qu’un pourcentage plus petit de l’espace de stockage de colonne de longueur variable sera utilisé, vous pouvez ajuster la valeur Max_Var_Leaf_Size par ce pourcentage pour obtenir une estimation plus précise de la taille globale de la table.

    S’il n’existe aucune colonne de longueur variable, définissez Variable_Leaf_Size sur 0.

  5. Calculez la taille de ligne d’index :

    Leaf_Row_Size Fixed_Leaf_Size Variable_Leaf_Size Leaf_Null_Bitmap + 1 (pour la surcharge de l'en-tête de ligne d'une ligne d'index) + 6 (pour le pointeur d'ID de la page enfant)

  6. Calculez le nombre de lignes d’index par page (8096 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 2 dans la formule correspond à l’entrée de la ligne dans le tableau d’emplacements de la page.

  7. Calculez le nombre de lignes libres réservées par page, en fonction du facteur de remplissage 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 une valeur entière au lieu d’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 seront stockées sur chaque page et il y aura moins de pages. Le 2 dans la formule correspond à l’entrée de la ligne dans le tableau d’emplacements de la page.

  8. Calculez le nombre de pages requises pour stocker toutes les lignes :

    Num_Leaf_Pages = Num_Rows / (Leaf_Rows_Per_Page - Free_Rows_Per_Page)

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

  9. Calculez la taille de l’index (8192 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 feuilles

Suivez ces étapes pour estimer la quantité d’espace nécessaire pour stocker les niveaux intermédiaires et racines de l’index. Vous aurez besoin des valeurs conservées des étapes 2 et 3 pour effectuer cette étape.

  1. Calculez le nombre de niveaux qui ne sont pas des feuilles dans l’index :

    Niveaux non-feuilles = 1 + log Index_Rows_Per_Page (Num_Leaf_Pages / Index_Rows_Per_Page)

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

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

    Num_Index_Pages = ∑Level (Num_Leaf_Pages/Index_Rows_Per_PageLevel) où 1 <= Level <= Levels

    Arrondissez chaque addition jusqu’au nombre entier le plus proche. En guise d’exemple simple, considérez un index où Num_Leaf_Pages = 1000 et Index_Rows_Per_Page = 25. Le premier niveau d’index au-dessus du niveau feuille stocke 1 000 lignes d’index, qui sont une ligne d’index par page feuille, et 25 lignes d’index peuvent tenir 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 2 pages. Le niveau final de l’index doit stocker 2 lignes. Cela signifie qu’il nécessite 1 page. Cela génère 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 + log25 (1000 / 25) = 3

    Num_Index_Pages = 1000/(253)+ 1000/(25 2) + 1000/(251) = 1 + 2 + 40 = 43, qui est le nombre de pages décrites dans l’exemple.

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

    Index_Space_Used = 8192 x Num_Index_Pages

Étape 4. Total des valeurs calculées

Total 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

    La surcharge d’espace du partitionnement est minimale, mais complexe à calculer. Il n'est pas nécessaire d'inclure.

  • 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 aucun algorithme pour calculer exactement le nombre de pages IAM utilisées.

  • Valeurs de l’objet large (LOB)

    L'algorithme pour déterminer exactement la quantité d'espace qui sera utilisée pour stocker les types de données varchar(max), varbinary(max), nvarchar(max), text, ntext, xml, et les valeurs image est complexe. Il suffit d’ajouter la taille moyenne des valeurs de grands objets attendues, de multiplier par Num_Rows et d’ajouter cela à 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.

Voir aussi

Description des index cluster et non cluster
Créer des index non cluster
Créer des index cluster
Estimer la taille d’une table
Estimer la taille d'un index cluster
Estimer la taille d'un tas
Estimer la taille d'une base de données

  • Last updated on