Procédure stockée lente lorsqu'elle est appelée depuis le web, rapide depuis Management Studio

Question

J'ai une procédure stockée qui s'arrête à chaque fois qu'elle est appelée depuis l'application web.

J'ai lancé le Sql Profiler et tracé les appels qui sont interrompus et j'ai finalement trouvé ces choses :

1. J'ai exécuté les instructions depuis MS SQL Management Studio, avec les mêmes arguments (en fait, j'ai copié l'appel de procédure depuis sql profile trace et je l'ai exécuté) : Il se termine en 5~6 secondes en moyenne.
2. Mais lorsqu'il est appelé à partir d'une application Web, il prend plus de 30 secondes (dans la trace), de sorte que ma page Web se termine à ce moment-là.

Mis à part le fait que mon application web a son propre utilisateur, tout est identique (même base de données, connexion, serveur, etc.). J'ai également essayé d'exécuter la requête directement dans le studio avec l'utilisateur de l'application web et cela ne prend pas plus de 6 secondes.

Comment puis-je savoir ce qui se passe ?

Je suppose que cela n'a rien à voir avec le fait que nous utilisons des couches BLL > DAL ou des adaptateurs de table, car la trace montre clairement que le retard se situe dans la procédure réelle. C'est tout ce que je peux imaginer.

EDITAR J'ai découvert dans ce lien qu'ADO.NET met ARITHABORT à true - ce qui est bon la plupart du temps mais parfois cela arrive, et la solution suggérée est d'ajouter with recompile à la procédure stockée. Dans mon cas, cela ne fonctionne pas mais je soupçonne que c'est quelque chose de très similaire à ceci. Quelqu'un sait-il ce que ADO.NET fait d'autre ou où je peux trouver la spécification ?

Answer 1

J'ai eu un problème similaire dans le passé, donc je suis impatient de voir une résolution à cette question. Le commentaire d'Aaron Bertrand sur l'OP a conduit à La requête s'interrompt lorsqu'elle est exécutée à partir du Web, mais elle est très rapide lorsqu'elle est exécutée à partir de SSMS. et, bien que la question ne soit pas un doublon, la réponse peut très bien s'appliquer à votre situation.

En fait, il semble que le serveur SQL ait un plan d'exécution en cache corrompu. Votre serveur Web frappe le mauvais plan, mais SSMS tombe sur un plan différent car il y a un paramètre différent sur le drapeau ARITHABORT (qui n'aurait autrement aucun impact sur votre requête/procédure stockée particulière).

Ver L'appel d'une procédure stockée T-SQL par ADO.NET provoque une SqlTimeoutException pour un autre exemple, avec une explication et une résolution plus complètes.

Answer 2

J'ai également constaté que les requêtes s'exécutaient lentement à partir du Web et rapidement dans SSMS. J'ai fini par découvrir que le problème était dû à ce qu'on appelle le reniflage des paramètres.

La solution pour moi a été de changer tous les paramètres qui sont utilisés dans le sproc en variables locales.

Par exemple, le changement :

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
SELECT * FROM Table WHERE ID = @param1 

à :

ALTER PROCEDURE [dbo].[sproc] 
    @param1 int,
AS
DECLARE @param1a int
SET @param1a = @param1
SELECT * FROM Table WHERE ID = @param1a

Ça semble étrange, mais ça a réglé mon problème.

Answer 3

Se pourrait-il que d'autres appels à la base de données effectués avant que l'application web appelle le SP maintiennent une transaction ouverte ? Cela pourrait être une raison pour laquelle le SP attend lorsqu'il est appelé par l'application web. Je dirais qu'il faut isoler l'appel dans l'application web (le mettre sur une nouvelle page) pour s'assurer qu'une action préalable dans l'application web est à l'origine de ce problème.

Answer 4

Il y a de nombreuses raisons pour lesquelles vous pouvez constater des différences de performances.

1. la charge sur la base de données au moment où l'application web effectue l'appel.

2. La disponibilité de la mémoire sur le serveur de la base de données. Si votre application, ou d'autres, a effectué de nombreux autres appels, il se peut que les tables concernées par cette requête ne soient pas chargées en RAM.

3. Taille des données. S'il s'agit d'une grande quantité de données, le serveur web peut bloquer jusqu'à ce que l'ensemble des résultats soit transféré.

4. Avec la taille des données : la configuration du réseau entre le serveur de base de données et le serveur web. Par exemple, si la carte réseau du serveur web ne transfère que 10 Mo, elle sera naturellement plus lente qu'un ordinateur de bureau transférant à 1 Go. D'autres considérations entrent en ligne de compte, comme le fait que la chaîne de connexion soit configurée pour le cryptage ou non.

5. Temps entre deux exécutions de la procédure. Si l'exécution initiale de la procédure prend 30 secondes, mais que vous l'exécutez à nouveau peu après et qu'elle est beaucoup plus rapide, cela pourrait indiquer que le serveur SQL est capable de conserver une copie en cache des tables utilisées en mémoire pour servir la deuxième requête. Regardez à la fois la longueur de la file d'attente sur le disque dur et l'utilisation de la mémoire sur le serveur SQL AVANT chaque exécution de procédure.

6. Une mauvaise conception de l'application web (voir le lien fourni par @Jayantha).

7. Un malentendu sur ce qui se passe dans Management Studio. Est-ce 6 secondes jusqu'à ce que vous commenciez à recevoir des résultats OU est-ce 6 secondes à partir du moment où vous cliquez sur Run jusqu'à ce que la fenêtre de requête ait signalé l'achèvement total ?

Je suggère que la première chose à faire est de couper toutes les connexions actives à votre serveur SQL et de le laisser tourner au ralenti pendant un moment. Ensuite, exécutez vos tests sur le processeur. Voyez s'il manque des index ou si vous pouvez structurer la procédure pour qu'elle s'exécute plus rapidement. Limitez le nombre de colonnes et/ou la quantité de données à ce qui est réellement nécessaire à renvoyer au serveur web.

Si cela ne vous mène nulle part, postez une copie de la procédure ici. Quoi qu'il en soit, vous devriez être en mesure de reproduire vos résultats.

Answer 5

http://forums.asp.net/t/1051579.aspx/1 Vérifiez si cela peut vous aider.