Discussion :

[StitchP]

Bonjour à toutes et à tous,

je rencontre un phénomène quelque peu étrange que je ne m'explique pas...
Voilà mon problème :

Je dispose de 2 bases de données dont le contenu est identique, dans le même environnement (seul leur nom change car elles ne vont pas être utilisées de la même façon, mais ce n'est pas le sujet).
Je lance une procédure stockée (récupération de données selon différents critères, sur plusieurs vues), la même, sur chacune des 2 bases.

Le temps d'exécution pour chaque BDD est complètement incohérent : quelques secondes pour la première, plusieurs minutes pour la seconde

L'ennui c'est que cette procédure est utilisée en production, et que selon le volume de données demandées (filtre sur une plage temporelle), son utilisation habituelle est plutôt de l'ordre de la 1/2h...
Je pensais que cela venait de l'environnement de production, mais vu mes résultats en développement je commence à avoir de sérieux doutes

Quelqu'un aurait-il une idée ? Des propriétés à regarder du côté des bdd ?

Merci par avance !

[pulsdrum]

Bonjour,

Plusieurs pistes :
- Regarde dans les propriétés de tes bases de données dans quels fichiers / disques dures elles sont stockées.
- Regarde les index utilisés par la procédure via l’affichage du plan d'exécution graphique dans SQL Server Management Studio.
- Regarde si t’es tables/vues utilisées par la procédure son partitionnées.

D’autre part, sache que tu peux optimiser ta procédure pour des paramètres spécifique (OPTION OPTIMIZE FOR).

Tu peux aussi utiliser l’option « WITH RECOMPILE » pour dire à SQL Server de générer un nouveau plan d’exécution à chaque appel de la procédure.

Enfin tu peux utiliser "SQL Server Database Engine Tuning Advisor" pour optimiser ta procédure. Il te proposera de créer des index, de partitionner t’es tables… S’il pense que c’est utile

++

[elsuket]

Bonjour,

Tu peux aussi utiliser l’option « WITH RECOMPILE » pour dire à SQL Server de générer un nouveau plan d’exécution à chaque appel de la procédure.

Si la procédure stockée est utilisée en production et appelée très fréquemment, cela va consommer beaucoup de CPU.

En piste 1 on peut tout simplement vérifier si les statistiques sont maintenues sur les deux serveurs ...

@++

[StitchP]

Bonjour et merci pour vos réponses

@pulsdrum :
les fichiers de bdd et les logs pour les 2 bases sont stockées au même endroit.

@elsuket :
de quelles statistiques parles-tu ?

La procédure stockée n'est a priori pas utilisée souvent, quelques fois par jour.
Je vais regarder du côté de "SQL Server Database Engine Tuning Advisor" aussi...
mais j'ai des doutes que ce ne soit qu'un histoire d'optimisation si ?
Dans le sens où avec les mêmes données qu'en production je passe de 5 minutes à 1/2h...

[Rei Ichido]

Les statistiques, ça change vraiment tout.
J'aime bien l'exemple de l'annuaire, ça parle aux gens. Alors imaginez la requête suivante : trouvez toutes les personnes s'appelant "Ichido", pour toutes les villes.

Mon annuaire comporte réellement 10 villes, et 10000 noms (la plupart différents, peut-être 9000 valeurs différentes) par ville.

Statistiques à jour : il y a 10 villes dans l'annuaire, la dispersion sur les noms est très forte, je vais aller regarder chaque ville et pour chacune, je vais essayer de trouver le nom. Je vais faire 10 lectures à petit scan d'index, et concaténer le tout.
Statistiques pas du à jour : j'ai 10000 villes, et seulement 10 noms par ville. Il semble nettement plus raisonnable de tout lire d'un coup, et filtrer directement sur le nom. Je fais un "full table scan" et je me lis les 100 000 enregistrements.

On imagine la différence de temps de traitement

[StitchP]

En fait j'avais rapatrié une sauvegarde la bdd de production pour la tester (histoire d'avoir les mêmes volumes). Je dois donc avoir les mêmes statistiques dans les 2 environnements non ? Tout comme le même taux de fragmentation des index a priori ?

A noter que j'utilise la base en source pour Reporting Services. Je sais que je ne suis pas sur le forum Microsoft BI, mais pensez-vous que ça puisse jouer d'une façon ou d'une autre sur le temps de réponse ?
Je ne vois que ça comme différence entre mes 2 bases de test en fait...

[elsuket]

Je dois donc avoir les mêmes statistiques dans les 2 environnements non ? Tout comme le même taux de fragmentation des index a priori ?

C'est effectivement exact.

Est-ce que les bases de données sont sur le même serveur ?

@++

[JLCantara]

Je dois donc avoir les mêmes statistiques dans les 2 environnements non ? Tout comme le même taux de fragmentation des index a priori ?

Bonjour StichP,

Pourquoi assumer et ne pas vérifier? Est-ce vraiment la même PS? Car il faut bien admettre, qu'à priori, ce problème ne tient pas: la même procédure appliquée à 2 BD identiques a un comportement erratique...

Il me semble évident qu'il manque un élément crucial:
1 - Bin, les 2 PS sont presque identiques sauf que...
2 - Ah bin, j'ai oublié de dire que les BD sont sur 2 serveurs différents...
3 - Misère, j'ai oublié de dire que les comptes-utilisateurs sont différents...

Est-ce que LA PS tourne dans le même temps dans le studio? Il n'est absolument pas nécessaire de 'recompiler': SQL fait un très bon travail...

Note que 'reporting services' est un engin très capricieux: les modifications du SQL doit être fait dans le studio sinon il semble (je me suis salement battu avec la chose) que l'optimisation n'ait pas lieu.

Bonne chance,
JLCantara.

[StitchP]

Bonjour JLCantara et merci de me prendre pour une truffe

Car il faut bien admettre, qu'à priori, ce problème ne tient pas: la même procédure appliquée à 2 BD identiques a un comportement erratique...

Je suis bien d'accord, ça n'a aucun sens. C'est bien pour avoir des pistes sur d'éventuelles différences auxquelles je n'aurai pas pensé que je pose la question sur un forum...

Pour répondre à tes questions :
- oui mes 2 bdd de TEST sont bien sur le même serveur,
- j'utilise le même compte utilisateur pour tester ma procédure stockée
- les 2 PS sont bien identiques
- j'ai regardé les statistiques et je suis dans le même état en production qu'en développement.
- j'ai reconstruit 1 index là où ma procédure prend le plus de temps mais ça ne change quasi rien à la durée d'exécution
(=> j'ai trouvé qu'un filtre type "date between xxx and xxx" était la cause de la lenteur de ma PS)

Comme je l'ai dit dans mon précédent message, la seule différence que je vois entre mes 2 bdd est la connexion pour l'une à Reporting Services.

Note que 'reporting services' est un engin très capricieux: les modifications du SQL doit être fait dans le studio sinon il semble (je me suis salement battu avec la chose) que l'optimisation n'ait pas lieu.

Je n'ai pas compris... tu parles de la modification de la PS ?

@elsuket : non, ma base de test et celle de production ne sont pas sur le même serveur. J'ai donc pensé à des sollicitations de la base plus importante en production qu'en développement.

Ce qui me paraît étrange c'est que ce problème de performance est apparu du jour en lendemain. La taille de la bdd n'a pas doublé en une nuit, est-ce que quelques données "de trop" peuvent suffire à tout foutre en l'air ?
(Je précise que ce n'est pas moi qui administre le serveur et la bdd)

[JLCantara]

Bonjour StichP,

Bin non, je ne te prends pas pour une truffe...Mais j'ai fais 2,5 années comme Academic Support pour l'université Concordia et là, vraiment, j'en ai vu de toutes les couleurs.

Si les 2 PS sont identiques (tien, pourquoi 2 PS?) alors il est possible que les 2 BD, elles, ne le sont pas. Je te suggère le shareware que tu vas trouver au site suivant:

http://www.sqldbtools.com/

Je ne l'ai jamais utilisé mais ça semble OK; se méfier cependant car j'ai attrapé SearchNu en procédant selon les instructions d'un participant...

Oui, je parlais du PS; car si mes souvenirs sont justes (et ils ne le sont pas toujours), Report Services garde une version locale de la requête utilisée.

As-tu, sans préjudice, déconnecté la BD du Report Services et tourné ta ou tes 2 PS?

Quel mystère,
JLCantara.

[StitchP]

Bonjour

alors, 2 procédures stockées : celle de production, celle de développement.

As-tu, sans préjudice, déconnecté la BD du Report Services et tourné ta ou tes 2 PS?

Ma bdd est bien connectée à Reporting Services (elle est en DataSource).

J'ai finalement résolu mon problème de performance en déportant le filtre sur les dates dans mon rapport (et non plus dans la procédure stockée).
Ce n'est certainement pas la solution la plus propre, mais je réduis de 80% mon temps d'exécution.

Merci encore à tout pour vos réponses, même si je n'ai pas vraiment trouvé la cause de mon problème...

Bonne journée !

[elsuket]

J'ai finalement résolu mon problème de performance en déportant le filtre sur les dates dans mon rapport (et non plus dans la procédure stockée).

Cela signifie à priori qu'il manque un index sur la colonne date ...

Le mieux est que vous donniez le plan d'exécution réel (CTRL+M avant d'exécuter la procédure stockée) sur les deux bases de données, et que vous précédiez les deux EXEC par un SET STATISTICS IO ON.
Une fois généré, faites un clic-droit dans une zone vierge du plan, enregistrez les deux plans, et mettez-les en pièce attachée à votre prochain post



@++

[StitchP]

Cela signifie à priori qu'il manque un index sur la colonne date ...

Oui... En fait la requête porte sur une vue. Dans la table sur laquelle s'appuie la vue, il y un index sur la date + un autre champ.

Je relancerai les procédures stockées selon la procédure indiquée dans votre post et je mettrai le résultat.

Merci encore