Partager via

Spécifier le facteur de remplissage d’un index

Cette rubrique décrit le facteur de remplissage et comment spécifier une valeur de facteur de remplissage sur un index dans SQL Server 2014 à l’aide de SQL Server Management Studio ou de Transact-SQL.

L’option de facteur de remplissage est fournie pour optimiser le stockage et les performances des données d’index. Lorsqu’un index est créé ou reconstruit, la valeur du facteur de remplissage détermine le pourcentage d’espace sur chaque page de niveau feuille à remplir avec des données, en réservant le reste sur chaque page comme espace libre pour une croissance future. Par exemple, la spécification d’une valeur de facteur de remplissage de 80 signifie que 20 % de chaque page de niveau feuille seront laissés vides, ce qui permet l’expansion de l’index lorsque les données sont ajoutées à la table sous-jacente. L’espace vide est réservé entre les lignes d’index plutôt qu’à la fin de l’index.

La valeur du facteur de remplissage est un pourcentage de 1 à 100, et la valeur par défaut à l’échelle du serveur est 0, ce qui signifie que les pages de niveau feuille sont remplies en capacité.

Remarque

Les valeurs de facteur de remplissage 0 et 100 sont identiques à tous les égards.

Dans cette rubrique

Avant de commencer

Considérations relatives aux performances

Fractionnements de page

Une valeur de facteur de remplissage choisie correctement peut réduire les fractionnements de pages potentiels en fournissant suffisamment d’espace pour l’expansion de l’index, car les données sont ajoutées à la table sous-jacente. Lorsqu’une nouvelle ligne est ajoutée à une page d’index complète, le moteur de base de données déplace environ la moitié des lignes vers une nouvelle page pour faire place à la nouvelle ligne. Cette réorganisation est connue sous le nom de fractionnement de page. Un fractionnement de page permet d’obtenir de nouveaux enregistrements, mais peut prendre du temps et est une opération gourmande en ressources. En outre, elle peut entraîner une fragmentation qui provoque des opérations d’E/S accrues. Lorsque des fractionnements de page fréquents se produisent, l’index peut être reconstruit à l’aide d’une valeur de facteur de remplissage nouvelle ou existante pour redistribuer les données. Pour plus d’informations, consultez Réorganiser et reconstruire des index.

Bien qu'une valeur faible mais non nulle du facteur de remplissage puisse réduire la nécessité de fractionner des pages à mesure que l'index augmente, cela entraine une augmentation des besoins en espace de stockage et peut diminuer les performances de lecture. Même pour une application conçue pour de nombreuses opérations d’insertion et de mise à jour, le nombre de lectures de base de données dépasse généralement le nombre d'écritures d’un facteur de 5 à 10. Par conséquent, la spécification d’un facteur de remplissage autre que la valeur par défaut peut réduire les performances de lecture de la base de données d’une manière inverse proportionnelle au paramètre de facteur de remplissage. Par exemple, une valeur de facteur de remplissage de 50 peut entraîner une diminution des performances de lecture de la base de données de deux fois. Les performances de lecture sont réduites, car l’index contient plus de pages, ce qui augmente les opérations d’E/S de disque requises pour récupérer les données.

Ajout de données à la fin de la table

Un facteur de remplissage différent de 0 ou 100 peut être adapté aux performances si les nouvelles données sont réparties uniformément dans la table. Toutefois, si toutes les données sont ajoutées à la fin de la table, l’espace vide dans les pages d’index ne sera pas rempli. Par exemple, si la colonne de clé d’index est une colonne IDENTITY, la clé pour les nouvelles lignes augmente toujours et les lignes d’index sont ajoutées logiquement à la fin de l’index. Si des lignes existantes sont mises à jour avec des données qui allongent la taille des lignes, utilisez un facteur de remplissage inférieur à 100. Les octets supplémentaires sur chaque page permettent de réduire les fractionnements de page causés par une longueur supplémentaire dans les lignes.

Sécurité

Autorisations

Nécessite l’autorisation ALTER sur la table ou la vue. L’utilisateur doit être membre du rôle serveur fixe sysadmin ou des rôles de base de données fixes db_ddladmin et db_owner .

Utilisation de SQL Server Management Studio

Pour spécifier un facteur de remplissage à l’aide du Concepteur de tables

  1. Dans l’Explorateur d’objets, cliquez sur le signe plus pour développer la base de données contenant la table sur laquelle vous souhaitez spécifier le facteur de remplissage d’un index.

  2. Cliquez sur le signe plus pour développer le dossier Tables .

  3. Cliquez avec le bouton droit sur la table sur laquelle vous souhaitez spécifier le facteur de remplissage d’un index, puis sélectionnez Création.

  4. Dans le menu Concepteur de tables , cliquez sur Index/Clés.

  5. Sélectionnez l’index avec le facteur de remplissage que vous souhaitez spécifier.

  6. Développez La spécification de remplissage, sélectionnez la ligne Facteur de remplissage et entrez le facteur de remplissage souhaité dans la ligne.

  7. Cliquez sur Fermer.

  8. Dans le menu Fichier , sélectionnez Enregistrernom_table.

Pour spécifier un facteur de remplissage dans un index à l’aide de l’Explorateur d’objets

  1. Dans l’Explorateur d’objets, cliquez sur le signe plus pour développer la base de données contenant la table sur laquelle vous souhaitez spécifier le facteur de remplissage d’un index.

  2. Cliquez sur le signe plus pour développer le dossier Tables .

  3. Cliquez sur le signe plus pour développer la table sur laquelle vous souhaitez spécifier le facteur de remplissage d’un index.

  4. Cliquez sur le signe plus pour développer le dossier Indexes.

  5. Cliquez avec le bouton droit sur l’index avec le facteur de remplissage que vous souhaitez spécifier et sélectionnez Propriétés.

  6. Sous Sélectionner une page, sélectionnez Options.

  7. Dans la ligne du facteur de remplissage , entrez le facteur de remplissage souhaité.

  8. Cliquez sur OK.

Utilisation de Transact-SQL

Pour spécifier un facteur de remplissage dans un index existant

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter. L’exemple reconstruit un index existant et applique le facteur de remplissage spécifié pendant l’opération de reconstruction.

    USE AdventureWorks2012;  
GO  
-- Rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index   
-- with a fill factor of 80 on the HumanResources.Employee table.  

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
REBUILD WITH (FILLFACTOR = 80);   
GO

Une autre façon de spécifier un facteur de remplissage dans un index

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.

    USE AdventureWorks2012;  
GO  
/* Drops and re-creates the IX_Employee_OrganizationLevel_OrganizationNode index on the HumanResources.Employee table with a fill factor of 80.  
*/  

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
   (OrganizationLevel, OrganizationNode)   
WITH (DROP_EXISTING = ON, FILLFACTOR = 80);   
GO

Pour plus d’informations, consultez ALTER INDEX (Transact-SQL).

  • Last updated on