Avez-vous déjà demandé comment fonctionne réellement le paramètre de facteur de remplissage dans SQL Server ? Selon la documentation officielle de Microsoft, le paramètre de facteur de remplissage s’applique uniquement lorsqu’un index est créé ou reconstruit, et le moteur de base de données ne maintient pas dynamiquement le pourcentage spécifié d’espace vide dans les pages. Cependant, il existe un phénomène peu connu qui permet une “construction d’index hors ligne” lors du premier INSERT dans une table avec un index clusterisé, ce qui entraîne un facteur de remplissage effectif identique à celui de l’index clusterisé. Dans cet article, nous explorerons cette fonctionnalité non documentée et discuterons de ses utilisations possibles.

Mise en place des tests

Avant de plonger dans les tests, mettons en place l’environnement. Nous créerons une base de données de test avec le modèle de récupération COMPLET et effectuerons une sauvegarde COMPLÈTE pour nous assurer que la base de données est dans l’état souhaité. Nous créerons également une table de test avec un index clusterisé unique, “en augmentation constante”, et un facteur de remplissage attribué de 70 %. Cela servira de base pour nos expériences.

Le test “normal”

Dans le test “normal”, nous effectuerons deux INSERT multi-lignes dans la table de test et mesurerons la densité des pages (facteur de remplissage effectif) après chaque INSERT. Le code de chaque INSERT est identique, et nous observerons que le facteur de remplissage est ignoré et que les pages sont remplies à près de 100 %.

Le test “spécial”

Dans le test “spécial”, nous répéterons les mêmes étapes que dans le test “normal”, mais avec une légère modification. Nous ajouterons l’indice WITH(TABLOCK) à chaque instruction INSERT. Étonnamment, le premier INSERT observera et suivra réellement le facteur de remplissage de l’index clusterisé, ce qui entraînera une densité de page inférieure. Cependant, le deuxième INSERT ne suivra pas le facteur de remplissage, car la table contient déjà des lignes qui n’ont été remplies qu’à 70 %. Ce comportement est dû à la “construction d’index hors ligne” qui se produit lorsque l’indice WITH(TABLOCK) est utilisé.

Comprendre le phénomène

Alors, comment se produit ce phénomène ? La réponse réside dans l’utilisation de l’indice WITH(TABLOCK) et des modifications apportées à SQL Server 2008 pour prendre en charge le “journalisation minimale” des opérations INSERT/SELECT. Le premier INSERT, avec l’indice WITH(TABLOCK), déclenche en réalité une “construction d’index hors ligne” dans le cadre de la transaction, ce qui explique le comportement observé. Ce comportement n’est pas limité aux modèles de récupération SIMPLE ou BULK LOGGED et peut être observé dans tous les modèles de récupération.

Conditions requises

Plusieurs conditions doivent être remplies pour que ce phénomène se produise :

• La table doit avoir un index clusterisé.
• La table ne doit avoir qu’un index clusterisé et aucun index non clusterisé.
• La table doit être vide.
• Le premier INSERT doit être effectué dans un lot séparé ou après avoir supprimé et recréé la table.

Utilisations possibles

Bien que cette fonctionnalité non documentée ne doive pas être utilisée dans des environnements de production en raison de son caractère non pris en charge, elle peut avoir quelques utilisations possibles :

• Construire rapidement des données de test et l’index clusterisé en même temps lors des tests.
• Faciliter les processus ETL et les importations en préparant les données avec un facteur de remplissage souhaité.
• Gagner des paris en démontrant la capacité à faire suivre un INSERT d’un facteur de remplissage attribué sur un index clusterisé vide.

Il est important de noter que cette fonctionnalité est non documentée et non prise en charge, et qu’elle peut changer dans les futures versions de SQL Server. Cependant, elle peut être une “Oolie” SQL fascinante à explorer et à expérimenter dans des environnements contrôlés.

Merci de votre lecture !