SQL Server 2008 R2 + problème de perf

**davy.g** · 24/02/2011, 13h48

Bonjour,

Suite à une migration de notre décisionnel vers SQL Server 2008R2, nous rencontrons de gros problème de perf sur un traitement de référence.
Une 1ère migration sur un environnement de test a été effectué sur sql 2008R2 : acun problème de perf rencontré, alors que nous étions sur une petite machine virtuelle et sur des disques (SAN) pas rapide.

Nous avons ensuite installé notre décisionnel sur les futures machines de Prod (DL 580 G7 avec 4 procs x 6 cores (Intel xeon 7542)) et placé nos bases sur des disques performants sur le SAN (découpage bases systèmes, bases utilisateurs, journaux, tempdb sur des LUN dufférents). Ces CPUs ne font pas d'hyperthreading.

Ce traitement de référence est passé de 01H30 à plus de 16H00.

Nous avons ensuite testé ces jobs sur un autre serveur moins puissant (DL 380 G7 : 1 proc x 4 cores) et avons utilisé les mêmes LUN et nous retrouvons des temps très corrects.

Avez-vous une explication sur ce phénomène?

Merci de votre aide.

**Jinroh77** · 24/02/2011, 13h58

Les MVP ou autres membres plus qu'avertis devraient passer pour plus de détails et question sûrement, mais en quoi consistent ces traitements ?
Vous avez pu identifier 1 point de contention particulier ?
Un petit coup de monitoring pour savoir où ça bloque ?

**davy.g** · 24/02/2011, 14h02

Oui, j'ai beaucoup de wait de type PAGEIOLATCH_SH et à un moment donné j'ai un lock sur la TEMPDB.
Mais pourquoi ces différences en fonction des serveurs (CPU)?

Merci.

**Jinroh77** · 24/02/2011, 14h09

Par hasard (et parce que j'ai posé le même genre de question il y a peu) vous avez beaucoup de traitements sur des tables temporaires ? beaucoup de tris effectués par le moteur etc. ?

Combien avez-vous défini de fichiers pour votre base TempDB ?

**davy.g** · 24/02/2011, 14h11

J'ai 12 fichiers de 16Go dans ma base TEMPDB.
Pour l'instant, il n'y a pas de problème d'accroissement.

**mikedavem** · 24/02/2011, 14h16

Vous ne parlez pas de la quantité de mémoire allouée sur vos différents serveurs ? Qu'en est il ?

Est ce que ce sont des serveurs de production ? Des serveurs virtuels ?

++

**davy.g** · 24/02/2011, 14h17

Sur les serveurs de tests, il y a 12Go de RAM et 32Go de RAM sur le serveur de PROD (dont 20 pour SQL Server).
Le serveur de Prod (DL 580) et le dernier serveur de test (DL 380) ne sont pas virtualisés.

**Jinroh77** · 24/02/2011, 14h19

~~Le futur serveur de prod n'est pas virtualisé ?~~
Aviez-vous autant de fichier pour votre tempDB sur votre dev ?
Les fichiers de votre tempdb sont-ils sur des axes distinct ?

**davy.g** · 24/02/2011, 14h26

Non, sur le serveur de test (DL 580), j'avais un seul fichier pour la TEMPDB.
Mes 12 fichiers de TEMPDB de Prod sont sur un seul LUN.

**davy.g** · 24/02/2011, 14h29

Les problèmes se posent principalement sur une seule base qui fait environ 450Go et qui est découpé en 3 fichiers (1 fichier DATA, 1 fichier INDEX, et 1 fichier LOG) qui sont sur des LUNs différents.

**Jinroh77** · 24/02/2011, 15h09

Le problème est au chargement de cette base ou c'est sur la base en elle-même que vous avez identifié des contentions ?

Vous n'avez pas répondu, en savez-vous un peu plus sur les traitements et sur l'utilisation de tables temporaires ou autre lors de cette alimentation ?

Votre DWH se charge-t-il plutôt via du SSIS en mode composant etc. ou plutôt à base de procédures stockées ?

**davy.g** · 24/02/2011, 15h12

Le problème se pose sur une procédure stockée qui génère beaucoup d'I/O sur la même base.
Oui il y a des tris durant ce traitement.

**Jinroh77** · 24/02/2011, 15h21

Je ne souhaite pas trop m'engager sur les conclusions et préfère attendre qu'un expert apporte plus d'éléments, mais l'ensemble de ces opérations s'effectuent dans votre tempDB, pour cela, il est très pratique de l'avoir découpé en plusieurs fichiers, par contre, à placer tous ces fichiers sur le même LUN, le moteur, pensant pouvoir paralléliser les traitements risque de perdre plus de temps à se retrouver à finalement voir les traitements traités en série.

Avez-vous la possibilité de testé en utilisant moins de fichiers, mais en répartissant ceux-là sur 2 ou 3 LUN distincts ?

**davy.g** · 24/02/2011, 15h39

Etes-vous sûr qu'il faille placer les datafiles de la Tempdb sur de multiples LUN ?
Est-il nécessaire que je découpe ma base utilisateur en plusieurs fichiers sur des disques séparés?

**SQLpro** · 24/02/2011, 17h17

En mettant 12 fichiers de la tempdb sur un seul et même LUN (axe physique) vous obligez SQL Server à faire des plan avec 12 accès parallèle, mais au final dans les IO, chaque thread va attendre la libération du disque.
Pour vous convaincre que c'est dans cette voie qu'il faut aller auditer les problèmes, reconfigurez le serveur (sp_configure) en utilisant un MAX DOP = 1....
mais la solution finale résidera dans un équilibrage des fichiers sur différents axes réellement mécaniquement indépendant.
Donc pas de LUN taillé dans la masse !

A +

**davy.g** · 24/02/2011, 17h31

Merci pour votre réponse.
Je vais donc ajouter 3 Lun supplémentaires pour ma TEMPDB (4 au total) et y placer 3 datafiles par LUN.
Ma base utilisateur principale est découpé en 1 fichier de données et 1 fichier d'index sur 2 Lun différents.
Pensez-vous que je doive également découper ma base en y ajoutant des fichiers supplémentaires sur des Lun différents?

Merci.

**dbaffaleuf** · 24/02/2011, 17h58

Envoyé par SQLpro

En mettant 12 fichiers de la tempdb sur un seul et même LUN (axe physique) vous obligez SQL Server à faire des plan avec 12 accès parallèle, mais au final dans les IO, chaque thread va attendre la libération du disque.
Pour vous convaincre que c'est dans cette voie qu'il faut aller auditer les problèmes, reconfigurez le serveur (sp_configure) en utilisant un MAX DOP = 1....
mais la solution finale résidera dans un équilibrage des fichiers sur différents axes réellement mécaniquement indépendant.
Donc pas de LUN taillé dans la masse !

A +

J'aurais bien creusé dans cette direction là aussi. 24 cores et tout de suite le coût de paralléliser devient très tentant pour l'optimiseur. Regarder le top 5 des waits sur la machine (dans quelle position se trouve CXPACKET):

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
25
26
27
28
29
30
31
32
 
WITH Waits AS  (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    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', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT
     W1.wait_type AS WaitType,
     W1.WaitS,
     W1.ResourceS,
     W1.SignalS,
     W1.WaitCount,
     W1.Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
     ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95 -- percentage threshold
ORDER BY W1.Percentage desc
GO

Par contre Fred je ne vois pas pourquoi tu dis que chaque thread attend la libération du disque si les 12 fichiers sont sur le même disque. Les IO seront de toutes façons asynchrones que ce soit avec un seul device ou avec plusieurs. La completion des IO va juste remonter plus vite avec plusieurs axes mais il y a de l'attente de toutes façons ?

**SQLpro** · 24/02/2011, 19h18

Envoyé par dbaffaleuf

Par contre Fred je ne vois pas pourquoi tu dis que chaque thread attend la libération du disque si les 12 fichiers sont sur le même disque. Les IO seront de toutes façons asynchrones que ce soit avec un seul device ou avec plusieurs. La completion des IO va juste remonter plus vite avec plusieurs axes mais il y a de l'attente de toutes façons ?

Si la LUN est sur un même agrégat taillé dans la masse, il y a des chances que tout le monde attend. Par exemple c'est quasi systématique en virtualisation !!!
De toute façon il y a toujours des attentes de synchro dès qu'il y a parallélisme, certains thread finissant statistiquement avant d'autres....

A +

**davy.g** · 24/02/2011, 19h22

Bonjour,

J'ai placé ma tempdb sur 4 Lun différents (3 datafiles par Lun) et le traitement semble plus long qu'avant. On dirait qu'il fait beaucoup plus d'accès à la Tempdb.

**mikedavem** · 24/02/2011, 19h42

Personnellement je ne mettrais pas autant de fichiers pour tempdb .. cela peut engendrer un overhead de traitement avec la multiplication des pages IAM à gérer pour accéder aux pages d'un même objet (on a qd même 12 CPU ici)

En avez vous vraiment besoin ? Avez vous vraiment une contention ces fichiers tempdb ? Avez vous pu le mesurer ?

Vous êtes sur un SAN ... avez vous vérifier la configuration de vos cartes HBA ? Drivers ? Valeur de file d'attentes de vos cartes ? Cache actif ou non du votre SAN pour les opérations d'écritures ? Configuration RAID ?

Avez vous également regardé au niveau des valeurs de compteurs au niveau du sous sytéme disque pour voir si celui-ci ne souffre pas de problème de performance ?

% disk read
% disk write
Avg Queue Length
Avg. disk sec / read
Avg. disk sec / writes
Avg. disk sec / transfer

...

++

SQL Server 2008 R2 + problème de perf

Administration SQL Server

Discussions similaires

Partager

Partager