Perte de performances

[Nicof28]

Bonjour,

J'interviens sur une base de données pour laquelle une SP me pose des problèmes. J'ai optimisé les index pour celle ci et donc réduit son temps d'exécution, l'utilisation CPU et les lectures. J'obtiens un temps d'exécution d'environ 300ms avec 60 000 lectures.
Lorsque cette sp est exécuté lors de tests nominaux, certains appels dépassent les 4s avec 500 000 lectures.
Il semble que certains appels n'utilisent pas les index. Lorsque je reprends la requête à partir du profiler et que je l'exécute à nouveau, je redescends à 300ms et 60 000 lectures.
Database Engine Tuning Advisor ne me donne pas d'améliorations, lorsque je l'exécute à partir des traces des tests nominaux.
J'ai ajouté la commande SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED dans la SP, mais cela n'a rien changé.

Quelqu'un aurait'il une piste qui expliquerait pourquoi certaines exécutions ne sont pas optimisées ?

[Nicof28]

Precision supplémentaire, le temps d'exécution et le nombre de lectures augmente lorsqu'il y a plusieurs appels simultanés.

[Sergejack]

C'est peut-être du au lock escalation.

[elsuket]

Bonjour,

Il s'agit peut-être d'un problème de "reniflage" de la valeur des paramètres à la compilation : le plan d’exécution est compilé avec des valeurs qui vont très bien pour la majorité des appels, mais peut devenir catastrophique pour certaines valeurs particulières.

Difficile de vous aider sans au moins le plan d'exécution réel de la requête lorsqu'elle s'exécute correctement, et quand elle devient contre-performante.

Vous pouvez traquer cela avec SQL Profiler, côté serveur.

Est-ce que les statistiques sont maintenues sur la table ?
Quel est le nombre de lignes de la table ?
Cette table supporte-t-elle de nombreuses modifications ?

@++

[SQLpro]

Donnez nous le code de cette procédure, mais je parie qu'il y a un curseur dedans....

A +

[mikedavem]

Je pencherai plus pour un problème de parameter-sniffing comme Elsuket. Le plan d'exécution en cache n'est pas optimale pour le domaine de valeurs utilisé par la procédure stockée. A voir aussi du côté des statistiques, du taux d'échantillonnage etc ...

Il faudrait avoir plus d'éléments en effet comme le code de la procédure et les plans d'exécutions pour comparer ...

++

[Nicof28]

Merci pour vos réponses.

Je ne peux pas vous donner le code car la personne qui l'a développée a eu la bonne idée de faire une SP de 1500 lignes !
Par rapport à son contenu, elle a un paramètre xml en entrée qui va définir son comportement, elle crée au moins une vingtaine de tables temporaires (tables temporaires et tables dans des variables), et le tout bien sûr en dynamique.
En fonction des données en entrée, elle va exécuter des requêtes différentes. Et oui il y a aussi un curseur à l'intérieur.
Il y a bien des lock escalation lorsqu'il n'y a pas le paramètre READ UNCOMMITTED.
Cette SP est un concentré des bad practices :-)


Si j'ai bien compris ce que vous m'avez répondu, c'est que le plan d'exécution créé par une des exécutions perturbe les autres exécutions qui ont besoin d'un plan d'exec différent.

[Sergejack]

Envoyé par Nicof28

Il y a bien des lock escalation lorsqu'il n'y a pas le paramètre READ UNCOMMITTED.

Je lis entre les lignes qu'il n'y en aurait pas lorsque la transaction est READ UNCOMMITTED (j'avais bien un doute sur le sujet).

Si tu décèles que certains des query plus que d'autres au sein de la procédure peuvent avoir des performances trop inconsistantes, tu peux tenter l'option "OPTION (RECOMPILE)" avec ceux-ci.
Mal(heureusement) SQL Server prépare les plans d’exécution de tous les query d'un même batch avant de les exécuter plutôt que un par un avec leurs exécutions respectives qui peuvent fortement changer les statistiques qui dirigent la préparation des plans.

[Nicof28]

Merci pour l'info,

Je viens de faire un test avec l'option Recompile et je n'ai pas de gain lorsque j'en lance 4 en parallèle.

Je pense qu'il n'y a pas grand chose à faire étant donné qu'il y a beaucoup trop de traitements dynamiques qui ammènent à une multitude de possibilités de requêtes qui se font principalement sur des tables temporaires.

Je pense que je vais devoir la reprendre complètement.
Si quelqu'un a une idée miracle, je suis toujours preneur

[SQLpro]

1) supprimer le XML et passer tous les paramètres dans une table en dur
2) supprimer les tables temporaires autant que faire se peut
3) éradiquer le curseur, qui oui, fait une escalade de verrous
4) le SQL dynamique n'est pas un problème
5) ne jamais oublier de préfixer TOUS LES OBJETS par leur schéma SQL, même s'il n'y en a qu'un et que c'est dbo.

A +