Bonjour à tous,

C'est la rentrée et qui dit rentrée dit nouveaux projets !!! Dans le cadre de la refonte de notre SI, nous devons évaluer et tester le traitement de plusieurs millions de données (ligne) au sein d'une base de données. Pour les besoins de nos tests, nous avons loué un serveur chez OVH avec la configuration suivante :
  • Windows Server 2008R2
  • Intel Xeon E3 1245v2
  • 32Go de ram
  • 2 x 2 To SATA
  • SQL Server 2012 Web Edition


L'un de nos tests consiste à alimenter une table hiérarchique (utilisation du type hierarchyid) avec près de 500 millions de ligne.

Lors de l'ajout de ces données, les performances sont au rendez-vous :
  • L'ajout de 2,5 millions de ligne prend en moyenne 50 secondes
  • L'ajout de 25 millions de ligne prend en moyenne 12 minutes
  • L'ajout de 50 millions de ligne prend en moyenne 30 minutes


Tout ce déroule bien jusqu'au premier palier des 100 millions tout comme celui des 200 millions de ligne. Le souci commence au palier des 300 millions de ligne où les performances commencent à diminuer mais cela reste correct jusqu'au palier des 350 millions de ligne. Au-delà, les ajouts de lignes sont catastrophiques :
  • L'ajout de 2,5 millions de ligne prend en moyenne 25 minutes
  • L'ajout de 7,5 millions de ligne prend en moyenne 1 heure 15 minutes


Je précise qu'après chaque ajout d'un lot de 2,5 millions de ligne, je fais un UPDATE STATISTICS de la table et je vérifie l'état de fragmentation des indexes pour oui ou non les réorganiser.
Lors des derniers traitements d'ajout, je m'aperçois dans le moniteur d'activité qu'un grand nombre de processus ont un type d'attente "CXPACKET" avec des temps d'attente allant de 10ms à 90 000ms.

La requête qui permet de faire les ajouts est la suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
 
INSERT INTO Data(
	hid, id, ref_objet, is_list, value)
SELECT
	CAST(hid.ToString() + CAST('1' AS varchar(30)) + '/' AS hierarchyid),	
	id,
	@ref_objet,
	@is_list,
	NULL
FROM Data
WHERE ref_objet = @ref_objet_parent
Elle consiste à récupérer un niveau de la hiérarchie puis d'ajouter un nœud à chaque niveau.

Qu'elles pourraient être les raisons d'une telle chute de performance ? Il y a-t-il une configuration particulière à mettre en place pour un tel volume de données, que se soit sur la base de données utilisateur ou sur les bases système ?

Merci d'avance à ceux qui répondront