SQL-Server et performance

Version imprimable

17/01/2012, 11h55
Imageek

SQL-Server et performance

Bonjour,

Mon serveur virtuel (Windows Server 2003) héberge un serveur de base de données SQL Server.

Tous les matins, un job tourne pendant en moyenne 2h.

J'ai voulu raccourcir ce temps en ajoutant un CPU (manipulation faite à distance par quelqu'un d'autre).

Résultat, le job prend 3h maintenant à s'éxécuter.

Je ne sais pas où chercher le problème, avez-vous une piste pour commencer?

Merci par avance !
17/01/2012, 13h28
mikedavem
Que fais votre job exactement ? Est-ce un script TSQL ? Un lot SSIS ? etc ...

Combien de processeurs sont utilisés par SQL Server depuis l'ajout de votre VCPU ?
Code:

1 2 3 SELECT cpu_count, hyperthread_ratio FROM sys.dm_os_sys_info;
++
17/01/2012, 14h03
Imageek
Bonjour mikedavem et merci de vous intéresser à mon souci !

Alors, il s'agit d'un lot SSIS qui fait de l'import de données d'une base d'un serveur vers la base de mon serveur.

Voici le résultat obtenu avec votre requête:
Code:

1 2 3 cpu_count hyperthread_ratio ----------- ----------------- 4 1
Cela signifie que les 4 processeurs sont utilisés?
Que signifie le champ "hyperthread_ratio"?
17/01/2012, 19h46
mikedavem
Cela signifie simplement que SQL Server voit 4 CPU ici. Si vous n'avez rien modifié de particulier il les utilisera tous (a moins d'avoir paramétré l'affinity mask mais je doute que c'est le cas pour vous).

Vérifiez éventuellement votre parallélisme :

Eventuellement tester avec le parallèlisme désactivé
Code:

1 2 3 4 5 6 EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'max degree of parallelism' GO
.. et faites un essai avec le parallélisme désactivé.
Code:

1 2 EXEC sp_configure 'max degree of parallelism', 1; GO
++

Bonjour,

si c'est un problème de parallélisation, vous devriez voir énormément de CXPACKET :

Code:

1
2
3
4
5
6
7
8
SELECT TOP 10
	[Wait type] = wait_type,
	[Wait time (s)] = wait_time_ms / 1000,
	[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' 
--AND wait_type NOT LIKE 'CLR_%'
ORDER BY wait_time_ms DESC;

Cdt,

18/01/2012, 17h23
mikedavem

Pas forcément. Si le package SSIS est lancé une fois par jour et que d'autres types d'attentes se produisent plus souvent on ne le verra pas de cette façon. Ils peuvent être "noyer" dans la masse.

Il vaut mieux utiliser la vue sys.dm_os_waiting_tasks pour vérifier les tâches qui auraient un lien avec un éventuel type d'attente CX_PACKET.

++

Glouferu, voici le résultat de la requête que vous m'avez proposé:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Wait type                                                    Wait time (s)        % waiting
------------------------------------------------------------ -------------------- ---------------------------------------
CLR_AUTO_EVENT                                               35690                39.75
SQLTRACE_BUFFER_FLUSH                                        29952                33.36
ASYNC_NETWORK_IO                                             17754                19.77
PAGEIOLATCH_SH                                               2195                 2.44
IO_COMPLETION                                                1185                 1.32
SOS_SCHEDULER_YIELD                                          1162                 1.29
PAGEIOLATCH_EX                                               615                  0.69
CLR_MANUAL_EVENT                                             389                  0.43
LOGBUFFER                                                    246                  0.27
WRITELOG                                                     195                  0.22

mikedavem, en exécutant cette requête:

Code:

SELECT * FROM sys.dm_os_waiting_tasks

je n'ai pas de type d'attente CX_PACKET (qu'est-ce que cela signifie par ailleurs?)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
waiting_task_address session_id exec_context_id wait_duration_ms     wait_type                                                    resource_address   blocking_task_address blocking_session_id blocking_exec_context_id resource_description
-------------------- ---------- --------------- -------------------- ------------------------------------------------------------ ------------------ --------------------- ------------------- ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x00FF42F8           2          0               203                  LAZYWRITER_SLEEP                                             NULL               NULL                  NULL                NULL                     NULL
0x00FF43E8           3          0               253750               LOGMGR_QUEUE                                                 0x027E4BC0         NULL                  NULL                NULL                     NULL
0x00FF44D8           4          0               2781                 REQUEST_FOR_DEADLOCK_SEARCH                                  0x070900AC         NULL                  NULL                NULL                     NULL
0x00FF45C8           5          0               30065687             KSOURCE_WAKEUP                                               NULL               NULL                  NULL                NULL                     NULL
0x00FF46B8           11         0               510547               CHECKPOINT_QUEUE                                             0x027FB718         NULL                  NULL                NULL                     NULL
0x00FF47A8           10         0               30093640             ONDEMAND_TASK_QUEUE                                          0x027FD760         NULL                  NULL                NULL                     NULL
0x00FF4A78           7          0               953                  SQLTRACE_BUFFER_FLUSH                                        NULL               NULL                  NULL                NULL                     NULL
0x00FF4D48           12         0               30065703             BROKER_EVENTHANDLER                                          NULL               NULL                  NULL                NULL                     NULL
0x00FF4B68           9          0               30086906             BROKER_TRANSMITTER                                           NULL               NULL                  NULL                NULL                     NULL
0x00FF4C58           13         0               30086906             BROKER_TRANSMITTER                                           NULL               NULL                  NULL                NULL                     NULL
0x00FF5D38           51         0               0                    OLEDB                                                        NULL               NULL                  NULL                NULL                     NULL
0xB5F4A118           NULL       NULL            1024625              CLR_AUTO_EVENT                                               NULL               NULL                  NULL                NULL                     NULL
0xB5F5A118           NULL       NULL            1024625              CLR_AUTO_EVENT

Sinon, j'ai lancé une requête avec le Max Degree of Parallelism = 0. La requête prend 14 min à s'exécuter. En changeant cette valeur à 2, elle ne gagne que 2 minutes. Et encore, je doute que c'est parce que je l'avais déjà lancée une fois avant...

Je me demandais qu'avec 4 coeurs physiques, n'est-il pas mieux de configurer cette valeur à 2 normalement?

Le processeur (un Intel Xeon X7460) ne fait pas d'hyperthreading normalement).

22/01/2012, 10h54
elsuket

Bonjour,

Citation:

Sinon, j'ai lancé une requête avec le Max Degree of Parallelism = 0. La requête prend 14 min à s'exécuter. En changeant cette valeur à 2, elle ne gagne que 2 minutes.

Si vous avez zéro, SQL Server a le choix d'utiliser autant de processeurs qu'il le juge nécessaire.
Si vous le mettez à un, vous forcez SQL Server à n'utiliser qu'un seul CPU, ce qui signifie que vous désactivez la parallélisme.
Si vous le mettez à 2, vous limitez SQL Server, s'il décide de paralléliser une requête, à l'utilisation de 2 CPU maximum, ce qui vous en laisse deux de libre dans votre cas, mais qui peut aussi prendre plus de temps à s'exécuter.

Au passage, l'utilisation de sp_configure pour changer de niveau de parallélisation affecte toute l'instance SQL Server, c'est à dire toutes les requêtes s'exécutant sur toutes les bases de données hébergées par cette instance. C'est donc un peu brutal (oui Mikedavem, tu es une brute :))

Cela dit, je ne sais pas s'il est possible de gérer le degré de parallélisme dans un package SSIS. En revanche si le package SSIS exécute une procédure stockée, il suffit de faire suivre la requête par OPTION (MAXDOP 1), et de tester.

Vous pouvez aussi regarder ce qui se passe sur le serveur lorsque le package est en cours d'exécution avec la requête suivante (seulement celle Mise à jour du 13/05/2011) : la colonne Program vous donnera le nom du job, et sur la même ligne que celle qui a le nom du job qui exécute le package SSIS, les colonnes wait_type et wait_time vous indiquent respectivement :

- quel est le type d'attente
- depuis combien de temps cette attente à lieu, en millisecondes

Dites-nous ce que vous obtenez. Vous pouvez exécuter la requête du billet autant de fois que nécessaire, elle est assez rapide.

Vous pouvez également extraire le plan d'exécution en passant en paramètre à la fonction sys.dm_exec_query_plan() la valeur de la colonne plan_handle pour la même ligne : une fois exécutée sys.dm_exec_query_plan(), cliquez sur le lien dans la dernière colonne ;)

@++ ;)
22/01/2012, 11h18
mikedavem

Citation:

Au passage, l'utilisation de sp_configure pour changer de niveau de parallélisation affecte toute l'instance SQL Server, c'est à dire toutes les requêtes s'exécutant sur toutes les bases de données hébergées par cette instance. C'est donc un peu brutal (oui Mikedavem, tu es une brute )

Juste un moyen simple de voir si le package est affecté par l'ajout d'une CPU sans toucher forcément au package !! :) Bien entendu il faudrait jouer avec cette option juste pour le test en veillant à ce qu'aucun plan en cache pour le lot SSIS ne soit présent. Ceci étant dit la mise en place de cette option n'affectera pas les plans d'exécution déjà en cache. Mais une question légitime serait de savoir si seul ce package est concerné ou d'autres requêtes SQL ont vu leur performance diminué ?

Après on ne sait toujours pas ce que fait ce package dans la source. Import d'une table, une requête SQL, une procédure stockée ???

On peut essayer de voir aussi directement dans BIDS ce qui prend le plus de temps avec l'onglet Progress.

++
23/01/2012, 11h07
Imageek
Mon job SSIS importe des données d'une base d'un serveur vers la base de mon serveur.

La première étape est l'import de données brute ; on fait un simple copié collé des tables.

La deuxième étape est le remplissage de tables "complexes" ; on fait un SELECT sur plusieurs tables qu'on croise, donc pour la plupart, ce sont des gros calculs.

C'est sur cette deuxième étape que c'est long.

En effet, sur un import, j'ai le résultat suivant:
Code:

1 2 wait_type wait_time last_wait_type Threads % DB Command Status CXPACKET 74187 CXPACKET 1 0.00 DRIVE SELECT SUSPENDED
La valeur de wait_time me fait peur. En effet, cette requête met 30 minutes à s'exécuter alors qu'une dizaine de minutes devrait suffir. Est-ce que la valeur de wait_time signifie que les pré-calculs pour cette requête dure le temps indiqué?!

Pour info, je pense remettre les propriétés sur le parallélisme par défaut, étant donné que ça a l'air d'être la meilleure solution et qu'il faut en fait regarder ailleurs...
23/01/2012, 11h14
mikedavem

Quelle valeur pour l'option max degree of parallelism dans le cas du test ?

++
23/01/2012, 11h30
mustargus

Salut,

Pour augmenter la vitesse de la machine virtuelle il faut que tu réduises les ressources de cette machine virtuelle

Exemple si tu à 2 Go de RAM physique tu affecteras que 800 ou 600 Mo pour la machine virtuelle ou si tu à un Dual-Core tu affecteras qu'un seul CPU à la machine virtuelle

Une fois j'ai installé Windows 8 sur une machine virtuelle sur mon PC i7 4 Go
et elle a mis 15 min pour démarré donc j'ai réduit les ressources utilisées par cette machine virtuelle de 1 Go de RAM et qu'un seul CPU ça démarrer en 5 min.

Cordialement.
23/01/2012, 15h43
elsuket

Citation:

La valeur de wait_time me fait peur. En effet, cette requête met 30 minutes à s'exécuter alors qu'une dizaine de minutes devrait suffir. Est-ce que la valeur de wait_time signifie que les pré-calculs pour cette requête dure le temps indiqué?!

Attention : les valeurs de colonne wait_time sont en millisecondes.
Donc dans votre cas, vous avez fait une capture où la requête attend 74 secondes que le calcul parallélisé soit préparé.
C'est-à-dire que SQL Server est en train de calculer comment répartir la collection de données à partir de plusieurs threads de façon à ce que le calcul s'exécute le plus rapidement possible.
Et cela prend parfois du temps. Cela se produit typiquement quand les statistiques de la table ne sont pas à jour, et /ou quand il manque un index qui servirait bien la requête.
D'autre part rien ne permet de prédire le temps d'exécution d'une requête.
Vous dites qu'elle devrait prendre 10 minutes, mais sur quoi vous basez-vous ?

Cependant on ne peut pas vous aider beaucoup plus car vous n'avez pas répondu aux questions que moi et Mikedavem vous avons posé :

- Quel est la valeur de l'option max degree of parallelism ?
- Après avoir exécuté la requête de mon billet plusieurs fois pendant l'exécution du package SSIS, est-ce que vous voyez de façon consistante l'attente CXPACKET ?
- Il est dommage que vous n'ayiez pas capturé le plan d'exécution comme je vous l'ai indiqué, car cela nous aurait définitivement permis de comprendre ce qui se passe

@++ ;)
23/01/2012, 17h13
Glouferu

Bonjour,

Je vais peut-être dire des bêtises mais 33% des temps d'attentes proviennent des SQLTRACE_BUFFER_FLUSH -> du coup j'essaierais à tout hasard de chercher une trace, un audit de configurer sur le serveur.
Si avec 3 cpu, le traitement dur 1 heure de plus ... je repasserais votre vm à 2 CPU.

Elsuket et mikedavem sont de meilleurs conseils que moi. Mais parfois, on cherche compliqué alors que c'est tout simple. Après, effectivement je pense que nos experts ont besoins de plus d'info pour vous aider au mieux :mouarf:

Cdt,
24/01/2012, 09h53
Imageek

L'option max degree of parallelism valait 4 au moment des tests. J'ai gardé cette valeur par la suite.

Je ne vois pas l'attente CXPACKET spécialement après avoir exécuté plusieurs fois la requête pendant l'exécution du job...

Je n'arrive pas à mettre le plan d'exécution, étant donné qu'il est trop grand (385213 caractères), est-ce que je peux regarder à un endroit en particulier?

Glouferu, je pense également à repasser à 2 CPU, ce qui est dommage, mais ça sera envisagé si je ne trouve pas de solution :(