Gespeicherte Prozedur langsam beim Aufruf aus dem Web, schnell aus Management Studio

Ich habe eine Prozedur gespeichert, die jedes Mal, wenn sie aus der Webanwendung aufgerufen wird, irrsinnig abläuft.

Ich habe den Sql Profiler angezündet und die Anrufe nach der Zeit verfolgt und schließlich herausgefunden:

  1. Bei der Ausführung der statementen aus dem MS SQL Management Studio mit denselben Argumenten (tatsächlich habe ich den Prozeduraufruf aus dem sql-Profil trace kopiert und ausgeführt): Er endet in 5 ~ 6 Sekunden im Durchschnitt.
  2. Aber wenn es von einer Webanwendung aus aufgerufen wird, dauert es mehr als 30 Sekunden (in der Ablaufverfolgung), so dass meine Webseite bis zu diesem Zeitpunkt abläuft.

Abgesehen davon, dass meine Webanwendung einen eigenen Benutzer hat, ist alles gleich (gleiche database, Verbindung, Server usw.). Ich habe auch versucht, die Abfrage direkt im Studio mit dem Benutzer der Webanwendung auszuführen, und es dauert nicht mehr als 6 sek.

Wie finde ich heraus, was passiert?

Ich gehe davon aus, dass es nichts mit der Tatsache zu tun hat, dass wir BLL> DAL-Layer oder Table-Adapter verwenden, da die Kurve deutlich zeigt, dass die Verzögerung im eigentlichen process liegt. Das ist alles, woran ich denken kann.

BEARBEITEN Ich habe in diesem Link herausgefunden, dass ADO.NET ARITHABORT auf true setzt – was für die meiste Zeit gut ist, aber manchmal passiert dies, und die vorgeschlagene ARITHABORT darin, dem gespeicherten Proc die Option with recompile hinzuzufügen. In meinem Fall funktioniert es nicht, aber ich vermute, dass es etwas sehr ähnlich ist. Weiß jemand, was ADO.NET sonst noch tut oder wo ich die Spezifikation finde?

Ich hatte ein ähnliches Problem in der Vergangenheit, daher bin ich gespannt auf eine Lösung dieser Frage. Aaron Bertrands Kommentar zum OP führte dazu, dass Query-Zeiten bei der Ausführung aus dem Internet abbrachen, aber superschnell, wenn sie von SSMS ausgeführt wurden , und obwohl die Frage kein Duplikat ist, kann die Antwort sehr gut auf Ihre Situation zutreffen.

Im Wesentlichen klingt es wie SQL Server möglicherweise einen beschädigten zwischengespeicherten Ausführungsplan. Sie treffen den schlechten Plan mit Ihrem Webserver, aber SSMS landet auf einem anderen Plan, da es eine andere Einstellung für das ARITHABORT-Flag gibt (die sich sonst nicht auf Ihre bestimmte Abfrage / Ihren gespeicherten process auswirken würde).

Weitere Informationen finden Sie unter ADO.NET-Aufruf von T-SQL Stored Procedure verursacht eine SqlTimeoutException für ein anderes Beispiel mit einer vollständigeren Erläuterung und Lösung.

Ich habe auch festgestellt, dass Abfragen langsam aus dem Internet und schnell in SSMS ausgeführt wurden, und ich fand schließlich heraus, dass das Problem etwas namens Parameter Sniffing war.

Die Lösung für mich war, alle Parameter, die in dem Sproc verwendet werden, zu lokalen Variablen zu ändern.

z.B. Veränderung:

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

zu:

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

Es scheint seltsam, aber es hat mein Problem behoben.

Nicht als Spam, sondern als hoffentlich hilfreiche Lösung für andere, sah unser System einen hohen Grad an Timeouts.

Ich habe versucht, die gespeicherte Prozedur neu zu kompilieren, indem Sie sp_recompile und das Problem für das eine SP behoben.

Letztendlich gab es eine größere Anzahl von DBCC DROPCLEANBUFFERS , von denen viele das noch nie zuvor getan hatten. Durch die Verwendung von DBCC DROPCLEANBUFFERS und DBBC FREEPROCCACHE die DBBC FREEPROCCACHE von Timeouts stark gesunken – es gibt immer noch vereinzelte Vorkommnisse, einige davon vermute ich Plan Regeneration dauert eine Weile, und einige, wo die SPs wirklich unterperformant sind und eine Neubewertung benötigen.

Könnte es sein, dass einige andere DB-Aufrufe, die ausgeführt werden, bevor die Webanwendung den SP aufruft, eine Transaktion offen halten? Dies könnte ein Grund dafür sein, dass dieser SP beim Aufruf durch die Webanwendung wartet. Ich sage isolieren den Anruf in der Web-Anwendung (auf einer neuen Seite), um sicherzustellen, dass einige vorherige Aktion in der Web-Anwendung dieses Problem verursacht.

Einfach die gespeicherte Prozedur neu kompilieren (Tabellenfunktion in meinem Fall) hat für mich funktioniert

wie @Zane sagte, es könnte wegen Parameter Sniffing sein. Ich habe das gleiche Verhalten erlebt und ich habe mir den Ausführungsplan der Prozedur und alle statementen des SP in einer Reihe angeschaut (alle statementen der Prozedur kopiert, die Parameter als Variablen deklariert und die gleichen Werte für die Variable as zugewiesen) die Parameter hatten). Der Ausführungsplan sah jedoch völlig anders aus. Die sp-Ausführung dauerte 3-4 Sekunden und die statementen in einer Zeile mit den exakt gleichen Werten wurden sofort zurückgegeben.

Ausführungsplan

Nach etwas Googeln fand ich eine interessante Lektüre über dieses Verhalten: Langsam in der Anwendung, Schnell in SSMS?

Beim Kompilieren der Prozedur weiß SQL Server nicht, dass sich der Wert von @fromdate ändert, sondern kompiliert die Prozedur unter der Annahme, dass @fromdate den Wert NULL hat. Da alle Vergleiche mit NULL UNKNOWN ergeben, kann die Abfrage überhaupt keine Zeilen zurückgeben, wenn @fromdate zur Laufzeit noch diesen Wert hat. Wenn SQL Server den Eingabewert als letzte Wahrheit nehmen würde, könnte es einen Plan mit nur einem konstanten Scan erstellen, der überhaupt nicht auf die Tabelle zugreift (führen Sie die Abfrage SELECT * FROM Bestellungen WHERE OrderDate> NULL aus, um ein Beispiel dafür zu sehen) . SQL Server muss jedoch einen Plan generieren, der das richtige Ergebnis zurückgibt, unabhängig davon, welchen Wert @fromdate zur Laufzeit hat. Auf der anderen Seite besteht keine Verpflichtung, einen für alle Werte besten Plan zu erstellen. Da die Annahme besteht, dass keine Zeilen zurückgegeben werden, legt SQL Server den Index Seek fest.

Das Problem war, dass ich Parameter hatte, die Null bleiben konnten und wenn sie als Null übergeben wurden, würde das mit einem Standardwert initialisiert werden.

 create procedure dbo.procedure @dateTo datetime = null begin if (@dateTo is null) begin select @dateTo = GETUTCDATE() end select foo from dbo.table where createdDate < @dateTo end 

Nachdem ich es geändert habe

 create procedure dbo.procedure @dateTo datetime = null begin declare @to datetime = coalesce(@dateTo, getutcdate()) select foo from dbo.table where createdDate < @to end 

Es funktionierte wieder wie ein Zauber.