Optimiser le stockage des bases de données
Pour optimiser le stockage des bases de données, vous devez prendre en compte le remplissage proportionnel et la configuration de tempdb.
Qu’est-ce que le remplissage proportionnel ?
Si vous insérez un gigaoctet de données dans une base de données SQL Server qui compte deux fichiers de données, vous pensez logiquement que la taille de chacun de vos fichiers de données augmentera d’environ 512 mégaoctets. Or, cette augmentation n’est pas nécessairement répartie de manière égale, car SQL Server insère des données dans les fichiers de données avec différents volumes en fonction de la taille du fichier de données. Dans l’exemple ci-dessus, si la taille de vos fichiers de données est de deux gigaoctets chacun, vous vous attendez à une distribution égale des données. Toutefois, si l’un de vos fichiers de données était de 10 gigaoctets et que l’autre était de 1 Go, environ 900 Mo iraient dans le fichier de 10 gigaoctets et 100 Mo dans le fichier d’un gigaoctet. Bien que ce comportement se produise dans toute base de données, avec la nature intensive en écriture de tempdb, un modèle d’écriture inégale peut provoquer un goulot d’étranglement sur le fichier le plus volumineux, car davantage d’écritures y sont effectuées.
Configuration de tempdb dans SQL Server
SQL Server 2016 a changé ce comportement en détectant le nombre de processeurs disponibles à l’installation, en configurant le nombre approprié de fichiers, jusqu’à 8, et en redimensionnant uniformément les fichiers de données. De plus, les comportements des indicateurs de trace 1117 et 1118 sont intégrés au moteur de base de données, mais uniquement pour tempdb. Pour les charges de travail lourdes de tempdb, il peut y avoir des avantages à augmenter le nombre de fichiers tempdb au-delà de huit, jusqu’au nombre de processeurs sur votre ordinateur.
SQL Server utilise tempdb pour bien d’autres choses que le stockage de tables temporaires définies par l’utilisateur. Les tables de travail utilisées pour stocker les résultats de requête intermédiaires, les opérations de tri et le magasin des versions pour le versioning de lignes ne représentent que quelques utilisations de tempdb. Pour cette raison, il est important de placer tempdb sur le stockage à latence la plus faible possible et de configurer correctement ses fichiers de données.
Avant SQL Server 2016, tempdb n’avait qu’un seul fichier de données par défaut. Avoir un seul fichier signifiait qu’il pouvait y avoir une contention pour plusieurs processus tentant d’accéder aux pages système de la base de données tempdb. Une solution courante à ce problème de contention consistait à activer l’indicateur de trace 1118, qui changeait le mode d’allocation des extensions. Une autre bonne pratique couramment recommandée consistait à créer plusieurs fichiers de données tempdb. Parce que SQL Server utilise un algorithme de remplissage proportionnel pour les bases de données avec plusieurs fichiers de données, il était également important de s’assurer que ces fichiers aient la même taille et augmentent à la même vitesse. Pour ce faire, de nombreux administrateurs de base de données utilisaient l’indicateur de trace 1117, qui forçait toutes les bases de données avec plusieurs fichiers de données à les faire grossir au même rythme.