Répartition des fichiers de données dans les FileGroups avec SQL Server (suite)
Comme nous l’avons vu dans le sujet précédent, il n’est pas compliqué de mettre en place la répartition à partir du moment ou l’on sait comment fonctionne le Proportional Fill.
La partie la plus ardue consiste à redistribuer les donnes du MDF originel dans tous les fichiers du Filegroup.
Il existe différentes méthodes qui pour certaines peuvent poser de gros problèmes en production.
Nous allons donc voir comment choisir la bonne méthode en fonction du contexte et en mesurer l’efficacité et les impacts éventuels.
Nous avons plusieurs possibilités :
1- Vider le MDF via un Shrink vers les fichiers additionnels du même Filegroup.
2- Faire un Rebuild des index avec le tri dans Tempdb pour répartir les données du MDF dans tous les fichiers du Filegroup.
Le mieux est de les tester et de voir le résultat, et c’est exactement ce que nous allons faire.
Il suffit de lancer la commande suivante :
USE [Audit_sagex3db]
GO
DBCC SHRINKFILE ('DataFile1', EMPTYFILE)
GO
Puis d’attendre la fin du process pour analyser le résultat…
Premier constat, c’est long, terriblement long, sans parler des blocages éventuels engendrés sur une base de données de production.
Je vous rappelle au passage qu’il est question là d’une base de données de 10Go avec seulement 8.4Go de données.
Ma machine de test est équipée de 8 cœurs I7 (2.69Ghz), 32Go de RAM et deux disques SSD de 2To.
Le résultat est sans appel…
22 minutes pour 8.6Go de données !
Si nous extrapolons simplement, cela ferait 220 minutes pour une base de 100Go et 2200 minutes pour une base de 1To (24 heures = 1440 minutes).
Encore une fois, je ne parle même pas des blocages en production pas plus que de la fragmentation engendrée.
Mais quel est le résultat ?
Regardons de nouveau notre Spin Target.
Le résultat est clairement catastrophique !
Le MDF originel est désormais pratiquement vide.
2 des 3 fichiers NDF sont pleins et le dernier a un Spin Target de 625.
Au prochain cycle d’écriture, les deux NDF pleins auront un FileGrowth de 128Mo et donc un Spin Target très élevé.
Au final, les données ne sont désormais réparties que sur 3 fichiers au lieu de 4, toutes les nouvelles données entrantes iront dans la majorité des cas dans le MDF originel.
En clair, retour à la case départ !
Faire un Rebuild des index avec le tri dans Tempdb pour répartir les données
Nous allons dans un premier temps générer le code de REBUILD de toutes les tables via INFORMATION_SCHEMA.TABLES.
Il y a deux solutions:
1-Faire un rebuild de tous les index de toutes les tables.
2-Faire un rebuild des tables qui ont un index Heap puis traiter les autres index séparément.
Dans notre cas, je vais faire les deux.
Use [Audit_sagex3db];
Go
Select table_name
,('Alter table '+QUOTENAME(table_name)+' rebuild with(online = on);') as HeapCmd
,('Alter index all on '+QUOTENAME(table_name)+' rebuild with(sort_in_tempdb = on, online = on);') as AllIdxCmd
from INFORMATION_SCHEMA.tables
where table_type = 'base table'
Le script ci-dessus va générer la liste des commandes de REBUILD à exécuter.
Veuillez noter qu’en SQL Server Standard le REBUILD est forcément OFFLINE et donc bloquant.
Le résultat parle de lui même.
La répartition s’est tout de même faite sur les 4 fichiers mais comme le Spin Target a évolué entre chaque REBUILD, au final il y a toujours plus de données dans le MDF originel que dans les NDF additionnels.
De plus, ceux-ci ont désormais un Spin Target de 6.
Ce résultat est tout à fait normal car durant la répartition, l’espace libre du MDF a changé après chaque REBUILD et il est au final plus important qu’au début, puisque quelques données sont tout de même allées dans les NDF ce qui impacte le Spin Target de ceux-ci.
La boucle est bouclée, retour à la case départ ou presque.
Je vois déjà la question arriver…
Alors quelle est la meilleure solution?
Il n’y en a pas en vrai, c’est toujours du cas par cas, le Shrink Empty File n’est vraiment pas la solution, le REBUILD oui mais avec ses contraintes aussi.
Il faudra dans la mesure du possible faire du REBUILD ONLINE, dans une fenêtre de tir où cela n’impacte pas d’autres traitements et probablement en HNO.
Il faudra entre chaque REBUILD vérifier l’espace libre dans le MDF et faire du Shrink par petits blocs pour éviter les blocages, à moins de pouvoir faire des Extents pour équilibrer le Spin Target.
Le Shrink devra consolider l’espace libre (truncate_only) du MDF avant de réduire celui-ci, et moins il y aura d’espace libre dans le MDF, plus les Shrink seront longs et toujours par petits blocs.
De plus, il faudra arrêter les Shrink quand tous les fichiers auront un Spin Target de 1.
Le Shrink engendrera forcément de la fragmentation, c’est inévitable, mais la maintenance courante des index devrait corriger cela.
Il faut garder à l’esprit que ce sera une tâche longue mais qui au final portera ses fruits.
La dernière fois que j’ai dû faire ce genre de manipulation sur une base (AX) de 4.5To, avec un seul MDF, cela a pris 3 mois car la fenêtre de tir était réduite et l’espace disponible pour ajouter des NDF encore plus (il y avait 600Go de libre dans le MDF, il aurait donc fallu ajouter 1.8To pour les 3 NDF ce qui n’était absolument pas possible).
Il a donc fallu jongler entre les Shrink et les Extents pour équilibrer au mieux sans blocage.
Comme je le disais, chaque cas est particulier…