Perte de performance après ajout de millions de lignes

**Ornitho76** · 05/09/2014, 11h04

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

**iberserk** · 05/09/2014, 15h11

Bonjour,
Précos de base à vérifier déjà:
Avez vous taillé votre base avec suffisamment d'espace afin d’éviter les autogrowth?
data et log séparés
testez avec MAXDOP à 1

etc.

**aieeeuuuuu** · 05/09/2014, 15h26

dans la série des vérifications de base :
avez vous bien un index sur ref_objet, incluant hid et id ?

**Ornitho76** · 05/09/2014, 17h21

@iberserk :
La taille de la base est resté en autogrowth, s'agissant d'un premier test, je n'ai effectivement pas définit une taille fixe. De plus je ne savais pas ce que représenterait 500 millions de ligne en volume. Après un premier test, je vais pouvoir fixer une taille de 80Go voir 100Go si je pousse les tests.

Les fichiers data et log ne sont pas séparé car le serveur a été configuré avec un RAID 1 et il n'est actuellement pas possible de le modifier. J'avais déjà pris en compte ce paramètre.

J'effectuerai des tests avec MAXDOP à 1, mais je reste perplexe sur le gain que ça ferait gagné au vu de la configuration serveur actuelle.

@aieeeuuuuu :
J'ai un index sur ref_objet mais sans inclure hid et id, je vais donc le créer.

Merci pour vos réponses, je vais refaire une phase de test avec les différentes solutions que vous m'avez indiqué et voir s'il y a toujours ce problème de chute de performance.

**iberserk** · 06/09/2014, 10h01

Au delà de ce test, quid des performances en exploitation de cette table avec 1/2 milliard d'enregistrement en sollicitant le hid?

**iberserk** · 06/09/2014, 10h05

que donnes le résultat de la requète suivante avant et après les insertion:

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
24
;WITH Waits AS  
(SELECT wait_type, wait_time_ms,  
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,  
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn  
FROM sys.dm_os_wait_stats  
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',  
'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',  
'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',  
'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',  
'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',  
'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH','SP_SERVER_DIAGNOSTICS_SLEEP',  
'HADR_FILESTREAM_IOMGR_IOCOMPLETION','BACKUPIO','BACKUPBUFFER','BACKUPTHREAD','DIRTY_PAGE_POLL','SQLTRACE','OLEDB','TRACEWRITE','MSQL_XP','FT_IFTSHC_MUTEX','SQLTRACE_FILE_WRITE_IO_COMPLETION'))  
 
SELECT W1.wait_type,   
CAST(W1.wait_time_ms AS DECIMAL(12, 2)) AS wait_time_s,  
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,  
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 
FROM Waits AS W1  
INNER JOIN Waits AS W2  
ON W2.rn < = W1.rn  
where W1.pct>1
GROUP BY W1.rn, W1.wait_type, W1.wait_time_ms, W1.pct  
HAVING SUM(W2.pct) - W1.pct < 99.999 OPTION (RECOMPILE);
GO

**Ornitho76** · 06/09/2014, 14h07

C'est justement ce que nous voulons tester, les performances du type hierarchyid sur un gros volume de données. Un des avantages de ce type est qu'il propose des fonctions CLR permettant la recherche de tous les nœuds enfants. Pour le moment, les premiers testes montre qu'il faut 100ms en moyenne pour retourner l'ensemble des nœuds depuis le niveau 1.

Que fait cette requête au juste ?

**iberserk** · 06/09/2014, 15h56

Quelle prudence!

Elle donnes le détails des principaux types d'attentes (like CXPACKET).

**Ornitho76** · 06/09/2014, 16h39

Bien, j'effectuerai le test.
Faut-il réinitialiser des compteurs étant donné que des insert ont déjà été effectué ?

**iberserk** · 06/09/2014, 19h46

Code :

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

DBCC SQLPERF ('sys.dm_os_wait_stats',clear)

**SQLpro** · 10/09/2014, 22h03

Envoyé par Ornitho76

C'est justement ce que nous voulons tester, les performances du type hierarchyid sur un gros volume de données. Un des avantages de ce type est qu'il propose des fonctions CLR permettant la recherche de tous les nœuds enfants. Pour le moment, les premiers testes montre qu'il faut 100ms en moyenne pour retourner l'ensemble des nœuds depuis le niveau 1.

Pour information ce type est particulièrement peu performant sur de gros volume de données. Un de mes clients à payer cher pour le savoir hélas. Il avait farci sa base de hierarchyid dans au moins 30 tables. Le problème de ce type de données est qu'il est manipulé sous SQL CLR (ocuhe .net) et que cela induit deux problématique :
1) plus de parallélisme (SQL CLR ne peut pas paralléliser les processus). Dans le pire des cas on peut avoir un goulet d'étrangelemnt de parallélisation
2) marshalling. La sérialisation et la désérialisation des informations binaires du hierarchyid dans certains des fonctions de manipulation de ce type entraine de nombreux aller-retour entre la machine SQL CLR et le moteur SQL

Pour toutes ces raisons, j'avais invité mon client à revenir sur une méthode plus traditionnelle et qui a fait ses preuves, la structuration des arborescences en mode intervallaire.
Voire les papiers que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/arborescence/
http://blog.developpez.com/sqlpro/p8...vallaire_proce
http://blog.developpez.com/sqlpro/p7...edure_de_depla
http://blog.developpez.com/sqlpro/p7...edure_de_derec

A +

**Ornitho76** · 10/09/2014, 22h41

Merci pour les liens, effectivement c'est très intéressant, j'effectuerai des tests pour comparer avec nos tests en cours.

Mais me viens une question après la lecture du premier lien : lors d'un update, cela implique la mise à jour de l'ensemble des données et cela 2 fois (Borne droite et borne gauche pour reprendre l'exemple).
Sur un gros volume de données, est-ce que cette structure est performante ?

**SQLpro** · 11/09/2014, 19h01

Envoyé par Ornitho76

Merci pour les liens, effectivement c'est très intéressant, j'effectuerai des tests pour comparer avec nos tests en cours.

Mais me viens une question après la lecture du premier lien : lors d'un update, cela implique la mise à jour de l'ensemble des données et cela 2 fois (Borne droite et borne gauche pour reprendre l'exemple).
Sur un gros volume de données, est-ce que cette structure est performante ?

4 choses peuvent améliorer le problème :
1) décorréler les données de la table des méta données de l'arbre en utilisant une table annexe en lien 1:1
2) ne faire qu'un seul UPDATE des deux bornes à l'aide d'un CASE
3) prévoir un facteur de remplissage des pages d'index un peu mou (genre 80%)
4) dans le pire des cas (très grosse volumétrie et très fort nombre de transactions à la minute), passer des INT aux DECIMAL et insérer les nœuds entre deux. Exemple entre 5 et 6 on créé un nouveau nœud avec 5.33333333 et 5.6666667. mais les requêtes deviennent plus complexes.

A +

Perte de performance après ajout de millions de lignes [2012]

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager