Discussion :

[Philippe Robert]

La valeur par défaut du FILLFACTOR est 0, ce qui signifie que lors de la création d'un index, les pages du niveau feuille sont remplies à 100% de leur capacité.

Lors de l'insertion d'une donnée, une page doit être scindée en deux (séparation de page). Si on effectue souvent des insertion/suppression/modification sur une table, la fragmentation des index peut s'accentuer.

J'ai dès lors utilisé des facteurs de rempllissage de 90% sur des index apliqués sur une colonne. Si l'index contient plus de deux colonnes, j'utilise des facteurs de remplissage allant de 85 à 75% selon les cas, car j'ai remarqué que les index sur plusieurs colonnes se fragmentent plus rapidement.

Cependant, le texte suivant que l'on trouve dans la documentation de SQL me fait douter "Le paramètre du taux de remplissage s'applique uniquement lorsque l'index est créé ou regénéré. Le SQL Server 2005 Database Engine (Moteur de base de données de SQL Server 2005) ne conserve pas dynamiquement le pourcentage spécifié d'espace libre dans les
pages. Tenter de préserver l'espace supplémentaire sur les pages de données serait en opposition avec l'objectif initial du recours au facteur de remplissage car le Database Engine (Moteur de base de données) devrait procéder à des fractionnements de pages afin de conserver le pourcentage d'espace disponible (déterminé par ce même facteur de remplissage)
sur chaque page à mesure que des données sont entrées."

Est-ce que cela signifie que quelques soit le taux de remplissage (fillfactor) utilisé, SQL effectuera une séparation de page afin de conserver ce même taux de remplissage ?

Si tel est le cas, quelle est la bonne façon de procéder ?

Autre question, exise-t'il des dynamic management view qui m'indiquerait, par exemple, que sont les index les plus utilisés ? Les tables les plus souvent mises à jours ?

Merci à l'avance.

[Ptit_Dje]

Le fill factor est utilisé lorsque l'index est créé ou regénéré.
Après SQL serveur rempli les index sans se soucier de cet indice.
Afin de garder de la place (libre dans vos pages d'index), prévoyer de lancer des jobs pour reconstruire vos index et maintenir cet indice.

[Philippe Robert]

Je dispose déjà de ce genre de processus. Ma question essentielle concerne le bon choix du taux de remplissage. J'ai déjà rencontré des situations ou la fragmentation d'un index a eu lieu en journée, et le job de réindexation est exécuté la nuit.

[elsuket]

Bonjour,

En spécifiant la valeur du FILLFACTOR, vous précisez combien d'espace libre vous souhaitez conserver dans les pages de vos indexes, afin d'éviter le split de page, contre-performant.
Durant le "fonctionnement" de votre base de données, SQL Server continue d'insérer des données dans les pages, en "ignorant" la valeur du fillfactor que vous avez spécifiée, sinon il devrait réaliser au moins autant de splits de page pour le conserver : l'intérêt du FILLFACTOR n'existerait donc plus.

Pour des tables fortement mises à jour, il m'arrive d'utiliser des FILLFACTOR de 50% pour obtenir une fragmentation quasi-nulle.

J'ai déjà rencontré des situations ou la fragmentation d'un index a eu lieu en journée, et le job de réindexation est exécuté la nuit.

Dans ce cas, prévoyez d'exécuter votre job de reconstruction des indexes plus régulièrement, en déterminant les plages horaires de faible activité.

exise-t'il des dynamic management view qui m'indiquerait, par exemple, que sont les index les plus utilisés ? Les tables les plus souvent mises à jours ?

- sys.dm_db_index_usage_stats
- sys.dm_db_index_physical_stats

Voici une petite requête, qui relie l'utilisation des indexes (colonnes user_seeks, user_scans, user_lookups) à leur taux de fragmentation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT DB_NAME(IUS.database_id) DB, 
		OBJECT_NAME(IUS.object_id) Tables,
		IDX.NAME IndexName,
		CAST(IPS.avg_fragmentation_in_percent AS NUMERIC(5,2)) Frag,
		CAST(IPS.avg_page_space_used_in_percent AS NUMERIC(5,2)) PgUsage,
		IPS.page_count Pg,
		IDX.fill_factor,
		IUS.user_seeks,
		IUS.user_scans,
		IUS.last_user_scan,
		IUS.last_user_seek,
		IUS.user_lookups,
		IUS.last_user_lookup,
		IDX.is_hypothetical,
		IDX.is_padded
FROM sys.dm_db_index_usage_stats IUS
JOIN sys.indexes IDX ON IUS.object_id = IDX.object_id
					AND IUS.index_id = IDX.index_id
JOIN sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(NULL), NULL, NULL, 'SAMPLED')IPS
	ON IUS.object_id = IPS.object_id
	AND IUS.index_id = IPS.index_id
WHERE OBJECT_NAME(IUS.object_id) NOT LIKE 'sys%'
AND IPS.index_id > 0

[Philippe Robert]

Merci pour ces précisions.
A+