Discussion :

[mb10]

Bonjour,

Je rencontre actuellement un problème sur une procédure stockée.
Quand j'exécute seulement la requête qu'elle contient. La requête elle-même mais moins de 30 s. Mais quand j'execute la procédure stockée (contenant cette même requête), le temps dexécution est très long plusieurs minutes (10 mins ).

Je ne comprends pas pourquoi.

Merci d'avance de votre aide.

Cordialement.

[elsuket]

Bonjour,

Lorsqu'on soumet une requête à SQL Server, il devise un plan d'exécution, c'est à dire la suite d'opérations la moins coûteuse pour résoudre la requête. Ce plan est placé dans un cache.

Si vous exécutez la requête seule en remplaçant les paramètres par leur valeur en dur, l'optimiseur crée un plan qui est taillé sur mesure pour ces valeurs (1).
Si vous déclarez et affectez les variables avant d'exécuter la requête, alors l'optimiseur ne tient pas compte de la valeur des variables (aussi bizarre que cela puisse paraître), et utilise des règles d'optimisation par défaut (2).

Lorsque vous exécutez la procédure stockée, le moteur de base de données exécute celle-ci avec le plan qu'il a compilé auparavant, pour les valeurs que vous passez en paramètre.
Il se peut donc que le plan qui a été créé avant ne soit pas optimal pour les valeurs de variables que vous passez : c'est ce que l'on appelle un problème de "reniflage" des valeurs de paramètre.

Pour voir avec quelles valeurs le plan a été compilé, vous pouvez :

• demander le plan d'exécution réel en pressant CTRL + M sous SQL Server Management Studio avant d'exécuter la procédure stockée
• ou précéder l'appel de celle-ci par un SET STATISTICS XML ON

Dans la console, après exécution de la procédure stockée, vous obtiendrez un troisième onglet ("Execution plan") qui montre le plan d'exécution graphique.
Celui-ci est l'interprétation du document XML sous-jacent, que l'on obtient par un clic droit dans une zone vierge du plan graphique, en choisissant l'option Show Execution Plan XML.
Une fois le document XML obtenu, allez en toute fin de celui-ci : vous allez trouver un groupe d'éléments dont la structure est la suivante :

<ColumnReference Column="@unParametre" ParameterCompiledValue="uneValeur" ParameterRuntimeValue="uneAutreValeur" />

Les noms des attributs sont explicites de ce qui s'est passé.

Si vous faites la même chose pour les cas (1) et (2), la structure de ces éléments n'aura pas l'attribut ParameterCompiledValue

Par observation du plan de requête, on peut déduire pour quel paramètre l'optimiseur a fait une erreur, et modifier l'expression de la requête pour que celle-ci s'exécute de façon stable.
Ceci suppose que la base de données sur laquelle cette anomalie se produit voit ses statistiques de colonne et d'index maintenues régulièrement.

Pour un peu de lecture, je vous invite à lire :

• Capture manuelle de plans de requête
• Plan Caching in SQL Server 2008

Pour aller plus loin, vous pouvez vous en remettre au livre qui est référencé dans ma signature

Pour vous aider, vous pouvez poster le plan de requête si cela vous est permis.

@++