Discussion :

[Takuan75]

Bonjour à tous,

Mon application web utilise une base de données SQL Server 2008 R2 hébergée sur un serveur Azure (je sais ce n'est pas top). La base fait environ 12 Go et comporte environ 200 tables dont une dizaine un peu importante (entre 500 000 et 25 millions de lignes et pour certaines 4 ou 5 index). J'ai un grand nombre de traitements par batch qui sont lancé sur cette base en permanence. Voilà pour le décor.

J'ai depuis quelques semaines une dégradation des temps de réponses de mon application web avec des temps de réponses totalement aléatoire (la même page peut répondre en 200ms et 1 minute après en 30s). La dégradation des performance est due exclusivement aux requêtes (et non au code du site web). J'ai isolé une requête (un update d'une ligne sur 2 champs sur une table de 500 000 lignes) qui prend à elle seule la quasi totalité du temps et dont les temps d'exécution varie d'une minute à l'autre de 20ms à 30s. La dégradation des temps réponse a démarré brutalement à 6h du matin sans qu'un volume de données important n'ai été ajouté dans les tables. J'ajoute que la table sur laquelle porte la requête qui a un temps de réponse aléatoire n'est pas du tout impacté par les traitements par batch.

Je rame un peu pour diagnostiquer et régler le problème.

Est ce que vous auriez des recommandations sur des points à vérifier ou des méthodes pour débusquer le problème ?

Merci d'avance

Takuan

[SQLpro]

Les statistiques sont-elles à jour et les index défragmentés ?
Quel est le plan de requête de cette fameuse requête (et son code) ?

A +

[bvesan]

Bonjour,
Une dégradation aléatoire de temps de réponse est communément à deux raisons:

Concurrence d'accès
Calcul des statistiques embarqués dans l'éxécution d'une fonction.


Pour identifier la problème, vous pouvez faire appel au suivi des attentes en base:

Réinitialisez les compteurs d'attente.

Code :

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

dbcc sqlperf("sys.dm_os_wait_stats" , CLEAR)

Exécutez plusieurs fois la requête incriminée (ou attendez simplement quelques minutes)
Regardez quelles sont les attentes les plus importantes.

Code :

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

1
2
3
4
select wait_type,wait_time_ms
from sys.dm_os_wait_stats 
where wait_type NOT LIKE '%CLR%' and wait_type not like '%SLEEP%' and wait_type not like 'XE%' and wait_type NOT IN ('WAITFOR','REQUEST_FOR_DEADLOCK_SEARCH','HADR_FILESTREAM_IOMGR_IOCOMPLETION','CHECKPOINT_QUEUE','DIRTY_PAGE_POLL','LOGMGR_QUEUE','BROKER_TO_FLUSH','BROKER_TASK_STOP','SOS_SCHEDULER_YIELD')
and wait_time_ms >0

La ou les principales attentes vous donneront la raison de votre problème de performance. Vous pourrez ensuite rechercher sur le Net (ou ici, bien entendu) les informations correspondant aux attentes remontées.

Cordialement.

Benjamin

[Takuan75]

Frédéric, le code de la requête est le suivant :

Code :

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

1
2
3
4
UPDATE	dbo.Session
SET		Session_EndDate=GetDate(),
		Session_NbPage = Session_NbPage + 1
WHERE   Session_Id = 720419

Son plan d'exécution est :

(D'ailleurs je ne comprends pas pourquoi la seconde étape est le recalcul de l'index cluster qui était avant sur Session_EndDate mais que j'ai passé il y a 2 jours sur la PK (Session_Id))

Même si je ne suis pas un débutant complet en SQL Server, je suis loin d'être un expert donc je vais sans doute poser des questions stupides ...
Comment je peux savoir si les statistiques sont à jour et les index défragmentés ?
Quelles sont les bonnes pratiques en matière de plan de maintenance ?

Benjamin, je viens de faire tourner requête quelques minutes après avoir réinitialisé les compteurs et voici les temps d'attentes les plus importants :
WRITELOG 244375
FT_IFTS_SCHEDULER_IDLE_WAIT 240016
CXPACKET 87285
LATCH_EX 16432
IO_COMPLETION 10176

Je vais creuser la question ...

Un point que j'ai oublié de préciser et qui est sans doute important : j'ai de nombreuses erreurs avec le messages "La transaction (ID de processus xx) a été bloquée sur les ressources verrou | tampon de communication par un autre processus et a été choisie comme victime. Réexécutez la transaction".

Merci à vous deux.

[SQLpro]

CXPACKET élevé => parallélisme => passer le a 1 (max degree of...)

Les verrous mortels sont le digne d'un mauvais design de bd et/ou mauvaise pratique de dev.

Avez-vous des tables de plus de 20 colonnes et si oui combien (relativement) ?
Quel outil de développement utilisez vous ? Un ORM genre Hibernate par exemple ????

A +

[dbaffaleuf]

Comment je peux savoir si les statistiques sont à jour et les index défragmentés ?
Quelles sont les bonnes pratiques en matière de plan de maintenance ?

- Utiliser sys.dm_db_index_physical_stats en mode limited dans un premier temps, regarder les indexes avec +5000pages seulement, notamment la colonne avg_fragmentation_in_percent. (http://msdn.microsoft.com/fr-fr/library/ms188917.aspx)
- Ne pas utiliser l'assistant de plan de maintenance pour le rebuild des indexes, il les fait tous sans sélection. Il vaut mieux utiliser une procédure stockée qui filtre sur les indexes les plus gros et les plus fragmentés au départ (http://ola.hallengren.com/sql-server...intenance.html)

[Takuan75]

Je viens de passer le parallélisme de 0 à 1 (sur une VM 8 coeurs, ce n'est pas un peu radical ?).

Il y a effectivement des problèmes de conception et de code. Ou plus précisément des points qui n'étaient pas des problèmes en sont devenus avec les évolutions fonctionnelles et la montée en charge.
Nous nous y attaquons au fur et à mesure

Sur les 300 tables de la base, il y en a 6 qui sont entre 20 et 24 champs, deux à 40-50 et une à 90 (mais qui comporte 5 millions de ligne et qui va monter à 12) : nous avons commencé à découper cette table en plusieurs.

Nous n'utilisons pas d'ORM.

[Takuan75]

David, merci pour les infos et les liens, ils vont bien m'aider !

[dbaffaleuf]

Je viens de passer le parallélisme de 0 à 1 (sur une VM 8 coeurs, ce n'est pas un peu radical ?).

Je ne suis pas pour non plus. Il faut évaluer la proportion de CXPACKET par rapport aux autres attentes légitimes (hors IDLE wait). Sur une machine multiprocesseur il est normal d'avoir du CXPACKET. Lorsque ça dépasse 80% des attentes et que ça peut être corrélé avec une charge CPU importante, alors oui il faut regarder les requêtes qui parallélisent.

Il faut bien se souvenir que la raison pour laquelle l'optimiseur décide de compiler un plan parallèle, c'est d'abord le coût. Donc il faut en premier s'attaquer à cette question, pourquoi les requêtes coûtent si cher ?

Je suis plus favorable à une augmentation du cost threshold for parallelism qu'au MAXDOP par exemple. Pour moi 5 est une valeur beaucoup trop faible par rapport aux volumes qui sont brassés aujourd'hui. C'est un paramètre historique qui date potentiellement de la 7.0, basé sur un ordre de grandeur vieux de plus de 15 ans. On recommande en environnement DWH de le monter aux alentours de 30-40.

Et 8 vCPUs, ce n'est peut être pas une très bonne idée non plus. S'il faut réellement ce besoin en multi processeurs, le passage en environnement virtualisé n'était peut être pas une bonne idée. On recommande rarement plus de 4 vCPUs en raison des problèmes de synchronisation des processeurs physiques d'un hyperviseur, qui ne peut garantir une affinité matérielle d'un processeur (ou core ou thread) à un vCPU. Donc lorsque le guest a besoin de 8 vCPUs en même temps, l'hyperviseur doit lui trouver la ressource, et plus il y a de vCPUs libres à trouver plus c'est difficile. Des mécanismes d'attente vont se mettre en place. Même si le modèle de planification sur ESX a bien évolué depuis la 2.5, le co-relaxed scheduling a encore des lacunes. Il faut regarder les performances au niveau de l'hyperviseur, notamment la partie % Ready, qui correspond à ces attentes de synchronisation.

Ressources :
- http://www.vmware.com/files/pdf/tech...Sched-Perf.pdf
- Ainsi que cet article de Duncan Epping qui date de 2008 mais le même algorithme est utilisé encore aujourd'hui en 5.1: http://www.yellow-bricks.com/2008/07...rtual-cpu-vms/)

HTH,

[SQLpro]

Je viens de passer le parallélisme de 0 à 1 (sur une VM 8 coeurs, ce n'est pas un peu radical ?).

Il y a effectivement des problèmes de conception et de code. Ou plus précisément des points qui n'étaient pas des problèmes en sont devenus avec les évolutions fonctionnelles et la montée en charge.
Nous nous y attaquons au fur et à mesure

Sur les 300 tables de la base, il y en a 6 qui sont entre 20 et 24 champs, deux à 40-50 et une à 90 (mais qui comporte 5 millions de ligne et qui va monter à 12) : nous avons commencé à découper cette table en plusieurs.

Nous n'utilisons pas d'ORM.

Corrélées ces tables avec leur usage et vous verrez quels sont les points noirs...
Par exemple en utilisant cette requête :

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
WITH T AS
(
SELECT AVG(user_seeks + user_scans + user_lookups) AS MOYENNE_OP_LECTURE,
       AVG(user_updates) AS MOYENNE_OP_ECRITURE
FROM   sys.dm_db_index_usage_stats AS s
       INNER JOIN sys.objects AS o
             ON s.object_id = o.object_id
       INNER JOIN sys.schemas AS sc
             ON o.schema_id = sc.schema_id
WHERE  o."type" IN ('U', 'V')
)
SELECT DISTINCT sc.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
       user_seeks + user_scans + user_lookups AS NOMBRE_OP_LECTURES, 
       user_updates AS NOMBRE_OP_ECRITURES,
       MOYENNE_OP_LECTURE,
       MOYENNE_OP_ECRITURE
FROM   sys.dm_db_index_usage_stats AS s
       INNER JOIN sys.objects AS o
             ON s.object_id = o.object_id
       INNER JOIN sys.schemas AS sc
             ON o.schema_id = sc.schema_id
       CROSS JOIN T
WHERE  EXISTS(SELECT *
              FROM   INFORMATION_SCHEMA.COLUMNS AS c
              WHERE  sc.name = TABLE_SCHEMA 
                AND  o.name = TABLE_NAME
                AND  ORDINAL_POSITION > 20)

A +

[Takuan75]

David, si je comprends bien, il vaut mieux que je passe ma VM en 4 coeurs et que j'en profite pour monter la mémoire de 14 à 28 Go. Le point noir restera les disques mais là je ne peux rien faire ... Après je tune les paramètres que vous mentionnez.

Frédéric, merci pour la requête ! Sans surprise, ma plus grosse table cumule un grand nombre de lecture et d'écriture et donc ça coince ...
Sur cette table j'ai assez peu de possibilité de re-normalisation, j'ai en effet pas mal de bit, int et date, je vais donc éclater les champs dans plusieurs tables. J'imaginais faire ça en fonction de l'usage et des traitement qui utilisent cette table et en particulier isoler les champs qui sont fréquemment mis à jours des champs qui ne bougent jamais, c'est la bonne voie ?

[SQLpro]

Frédéric, merci pour la requête ! Sans surprise, ma plus grosse table cumule un grand nombre de lecture et d'écriture et donc ça coince ...
Sur cette table j'ai assez peu de possibilité de re-normalisation, j'ai en effet pas mal de bit, int et date, je vais donc éclater les champs dans plusieurs tables. J'imaginais faire ça en fonction de l'usage et des traitement qui utilisent cette table et en particulier isoler les champs qui sont fréquemment mis à jours des champs qui ne bougent jamais, c'est la bonne voie ?

Oui. Découper en fonction de l'usage.
Vous pouvez aussi tenter d'utiliser la compression de ligne ou de pages....

A +