Performances SQL d’une application .NET, et contextes d’exécution

Vous avez une application .NET qui fonctionne en production sur un environnement avec une charge conséquente, et vous observez des problèmes de performance qui semblent “apparaître” à certains moments seulement. Par exemple, des procédures stockées qui semblent partir en vrille à l’exécution, mais seulement à certains moments.

Armé d’une version récente de SQL Management Studio, vous tentez de reproduire le problème, dans le but d’analyser le plan d’exécution. Et là, c’est la surprise totale! La requête qui s’exécute en plusieurs secondes, voire plusieurs minutes dans votre application s’exécute maintenant presque instantanément ! Et ce, alors que celle-ci peine lorsqu’appelée de l’application !
Et vous vous dites : Mais ça n’a pas de sens, pourquoi l’exécution est-elle si lente dans mon logiciel .NET et là c’est instantané, et ce au même moment, dans les mêmes conditions ? Eh bien, c’est tout simple, vous pourriez être l’une des nombreuses victimes de la différence de contexte entre toute application .NET et Management Studio ! Mais ne vous découragez pas, cet article vous expliquera le pourquoi de la chose !

Plans d’exécution

Bien que cet article n’ait pas pour sujet principal les plans d’exécution, il est essentiel d’en comprendre certaines bases afin de comprendre la suite. Tout d’abord, lors de l’utilisation d’une procédure stockée, SQL Serveur va créer un plan d’exécution, et celui-ci sera ensuite utilisé pour tous les appels suivants, jusqu’à ce qu’un changement au niveau des statistiques, des index, ou une opération de maintenance en provoque la réévaluation. Mais en fait, cette dernière information n’est pas tout à fait vraie : le plan d’exécution sera réutilisé par tous les appelants, oui, mais en autant que ceux-ci utilisent le même contexte d’exécution.

Contexte d’exécution

Tout comme d’autres systèmes, SQL Server utilise une notion de contexte d’exécution, permettant de prendre en considération des paramètres tels que la langue, le format de date, la gestion des exceptions arithmétiques, etc. Certaines de ces variantes du contexte d’exécution vous sont probablement familières (NO COUNT, ANSI ABORT, STATISTICS IO), car elles sont parfois utilisées dans le corps d’une procédure stockée. Ces variables influencent le déroulement d’un appel, par exemple, selon le format de date, on ne traitera pas de la même façon l’expression « 03/04/2013 », qui peut signifier soit le 4 mars, soit le 3 avril 2013.
Il est possible de consulter celles utilisées dans le cadre d’une session Management Studio, dans le menu Query / Query Options…

Query Options

SQL_Options.PNG


ANSI Options


SQL_Ansi_options.PNG

Différents plans selon le contexte

Et c’est là que ça devient intéressant ! Comme certaines options sont susceptibles d’affecter le déroulement d’une requête, SQL Server utilise différent plans d’exécution selon le contexte utilisé. Donc, deux exécutions d’une même procédure stockée pourraient mener, au même moment à deux plans d’exécution complètement indépendants l’un de l’autre.

ADO.NET vs SQL Management Studio : différents contextes

Et c’est exactement ce qui se produit, lorsque vous observez les conditions décrites au début de cet article. Toute application .NET qui se base sur l’ADO.NET (les objets de type SqlConnection, SqlDataReader, SqlCommand, etc.), utilise un contexte d’exécution en tout point semblable à celui utilisé par Management Studio, exception faite d’une variable : ARITHABORT.

À priori, pour vérifier que c’est bien ce qui explique la différence que vous observez, vous pouvez dans Management Studio modifier la valeur de cette option (voir écran précédent) et relancer votre appel. Normalement, vous devriez maintenant utiliser le même plan d’exécution que votre application .NET, et vous pourrez même le demander afin d’analyser ce qui ne va pas.

Et maintenant vous être en mesure de comprendre cette apparente « aberration » de SQL Server.

J’essaierai dans de prochains articles de vous donner plus de détails sur la gestion du plan d’exécution, sur l’importance du premier appel et de surtout de ses paramètres, surtout dans le cas de procédure stockée avec des paramètres nombreux et des schémas d’exécution très variables tel que dans le cas d’une recherche multicritères.

Pour finir, voici l’article de référence qui m’a instruit quant à ce problème, vous y trouverez d’ailleurs de nombreuses informations quant aux plans d’exécution et leurs mystères :

Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.