wake-up-neo.com

Problembehandlung bei zeitweiligen SQL-Timeout-Fehlern

Pro Tag gab es einige Instanzen, bei denen wir eine Reihe von SQL-Zeitüberschreitungsfehlern aus mehreren Anwendungen erhalten (System.Data.SqlClient.SqlException: Zeitüberschreitung abgelaufen. Die Zeitüberschreitung ist vor Abschluss des Vorgangs abgelaufen oder der Server antwortet nicht .) Wir haben über 100 verschiedene Anwendungen in unserem Netzwerk, sowohl Web- als auch Desktop-Apps. Alles von VB6 und Classic ASP bis .NET 4. Ich kann alle Arten von Daten finden, die die Nebenwirkungen zeigen, aber nicht genau ermitteln können, was dies verursacht. Unser Datenbankadministrator sagt, dass mit dem SQL-Server nichts falsch ist, und die IT-Abteilung sagt, dass nichts mit den Webservern oder dem Netzwerk falsch ist. Daher bin ich natürlich in der Mitte und versuche, das Problem zu beheben.

Ich suche eigentlich nur nach Vorschlägen, was ich sonst noch tun kann, um dies herauszufinden.

Wir führen SQL Server 2008 R2 in einem Cluster aus. Es gibt eine Handvoll verschiedener Server, die eine Verbindung herstellen, von Windows Server 2003 bis 2008 verschiedener Arten.

Folgendes habe ich bisher gemacht:

  • SQL-Ablaufverfolgung für lange laufende Abfragen und Deadlocks ausführen. Dies zeigt keine Deadlocks zu den Zeitpunkten der Probleme, und lange laufende Abfragen stimmen alle mit unseren Timeout-Fehlern überein, wirken aber als Nebeneffekt und nicht als Ursache. Sehr einfache Abfragen, die in der Regel sofort zurückgegeben werden, benötigen 30, 60 oder 120 Sekunden, um sie auszuführen. Dies geschieht für ein paar Minuten, dann wird alles aufgenommen und funktioniert danach einwandfrei.
  • Verwenden Sie den Leistungsmonitor, um Verbindungspoolverbindungen zu verfolgen. Dies zeigt manchmal einige Spitzen in der Anzahl der Verbindungen in der Nähe der Zeitüberschreitungen, aber immer noch nicht einmal auf halbem Weg zum Standard-Verbindungslimit von 100. Auch hier scheint nichts auf eine Ursache hinzuweisen.
  • Trennen Sie Webanwendungen in verschiedene App Pools. Wir haben versucht, die Apps einzugrenzen, die wir für das Hauptproblem hielten (die meisten gesprächig usw.), und sie in separate Anwendungspools zu legen, aber dies scheint nichts zu beeinflussen oder hilft uns dabei, alles einzuschränken.
  • Überwachen Sie die Festplattennutzung auf SQL Server. Wir haben einige Überwachungen auf dem SQL-Server durchgeführt und sehen keine Spitzen oder Anzeichen von Problemen, wenn diese Zeitüberschreitungen auftreten.
  • Verified TempDB war nicht die Ursache des Problems.

Ich werde wiederkommen und weitere hinzufügen, wenn ich daran denke, was wir sonst noch versucht haben. Bitte lassen Sie mich einige Ideen wissen, was als nächstes zu behandeln ist.

54
Shawn Steward

Führen Sie die SQL-Ablaufverfolgung für lange laufende Abfragen und Deadlocks aus. Dies zeigt keine Deadlocks zu den Zeiten der Probleme, und lange laufende Abfragen alle stimmen mit unseren Timeout-Fehlern überein, sehen aber als Nebeneffekt aus, und nicht die Ursache Sehr einfache Abfragen geben normalerweise .__ zurück. Am Ende dauert es 30, 60 oder 120 Sekunden, um zu laufen. Diese passiert für ein paar Minuten, dann wird alles aufgenommen und funktioniert einwandfrei nachdem.

Es scheint, als würden einige Abfragen/Transaktionen Ihre Datenbank sperren, bis sie fertig sind. Sie müssen herausfinden, welche Abfragen blockieren, und sie erneut schreiben/ausführen, um andere Prozesse nicht zu blockieren. In diesem Moment läuft das Warten nur ab.

Ein weiterer Punkt, an dem Sie interessiert sind, ist die Größe Ihres Transaktionsprotokolls und Ihrer Datenbank. Legen Sie sie auf eine feste Größe statt auf einen Prozentsatz der aktuellen Dateien fest. Wenn Dateien größer werden, wird die Zeit, die zum Zuweisen von ausreichend Speicherplatz benötigt wird, mit dem Zeitlimit der Transaktion möglicherweise länger. Und deine DB kommt zum Stillstand.

22
Peter

Leistungsprobleme sind auf CPU-, E/A- oder Sperrenkonflikte zurückzuführen. Es klingt, als hätten Sie IO ausgeschlossen. Ich würde vermuten, dass die CPU kein Problem darstellt, da dies eine Datenbank ist, kein Zahlen-Cruncher. Das hinterlässt also Kontroversen.

Wenn Sie ein sp_who2 ausführen können, während für die Abfragen eine Zeitüberschreitung auftritt, können Sie die BlkBy-Spalte verwenden, um die Sperre zurückzusetzen, in der die Sperre gespeichert ist, auf die alle anderen warten. Da dies nur einige Male am Tag der Fall ist, haben Sie möglicherweise Probleme, genügend Daten zu finden, wenn Sie diese manuell ausführen. Daher empfehle ich Ihnen, ein automatisiertes System festzulegen, um diese Ausgabe regelmäßig zu sichern oder möglicherweise durch das System auszulösen Anwendungs-Timeout-Ausnahmen. Sie können den Aktivitätsmonitor auch verwenden, um die Verschlechterung der Abfrageantwort in Echtzeit zu überwachen, wie von Peer vorgeschlagen.

Wenn Sie die lang andauernde Abfrage und die Anwendung, die sie ausführt, gefunden haben, können Sie das Domino von Zeitüberschreitungen sofort auflösen, indem Sie das Zeitlimit für diese einzelne Anwendung unter allen anderen reduzieren (im Moment muss es länger sein). Anschließend sollten Sie den Code überprüfen, um eine bessere Lösung zu ermitteln. Sie können die Dauer der Sperre reduzieren, indem Sie die Transaktion früher innerhalb eines Sproc abschließen, oder die für die Leseabfrage erforderliche Sperre mit Hinweisen wie NOLOCK oder UPDLOCK verringern.

Lesen Sie mehr zu sp_who2: http://sqlserverplanet.com/dba/using-sp_who2/

Und Abfragehinweise: http://msdn.Microsoft.com/en-us/library/ms181714.aspxhttp://msdn.Microsoft.com/en-us/library /ms187373.aspx

10
Matt Faus

Ein bisschen weit weg, aber in einem Labor vor einiger Zeit hatten wir eine Situation, in der ein SQL Server nicht mehr reagierte, nicht weil wir die CPU oder etwas, das wir innerhalb von SQL Server nachverfolgen könnten, erhöht hatte. Dies schien allen Tests funktionsfähig zu sein, aber die Verbindungen scheiterten unter irgendeiner Last.

Es stellte sich heraus, dass das Problem mit dem Datenverkehr auf dem Server verbunden war. Dies hatte zur Folge, dass der integrierte Syn-Angriffsflutschutz von Windows in Windows ausgelöst wurde. Wenn Sie dies treffen, ist es verblüffend, dass innerhalb des Windows-Servers oder innerhalb von SQL keine protokollierte Nachricht vorhanden ist. Sie sehen nur die Symtpoms, bei denen Verbindungen nicht hergestellt werden können. Vom Standpunkt der Verbindung aus scheint der Server nicht zu antworten, wenn er sollte (er bestätigt nicht einmal die angekommene Nachricht). 

http://msdn.Microsoft.com/de-de/library/ee377084(v=bts.10).aspx

Führen Sie einen Bildlauf nach unten zu SynAttackProtect durch. In Windows Server 2003 SP1 wird standardmäßig die Aktivierung dieser Funktion standardmäßig angezeigt. Es handelt sich hierbei um einen DDOS-Schutzmechanismus. Aufgrund der fehlenden Protokollierung ist es sehr schwer zu erkennen, wann Ihr Server dies tut.

Es dauerte 3 Tage im MS-Labor, bis es herausgefunden wurde.

Sie haben 100 Konenctions erwähnt, wir hatten eine App, die ständig verbunden war, Abfragen durchführte und dann getrennt wurde. Sie hielt die Verbindungen nicht offen. Dies bedeutete, dass sich auf jeder Maschinenverbindung mehrere Threads befanden, zehn Maschinen, mehrere Threads pro Maschine, und es wurde davon ausgegangen, dass ausreichend unterschiedliche Verbindungen hergestellt wurden, um die Verteidigung auszulösen.

Ob Sie sich auf diesem Niveau befinden (da es sich bei MS nicht um einen eindeutig definierten Schwellenwert handelt), ist schwer zu sagen.

8
Andrew

Wie die anderen Poster vorgeschlagen haben, klingt das so, als hätten Sie ein Problem mit dem Sperrwettbewerb. Wir hatten vor ein paar Wochen ein ähnliches Problem. Unserer war jedoch viel unregelmäßiger und wurde oft aufgeräumt, bevor wir einen DBA auf den Server bringen konnten, um sp_who2 auszuführen, um das Problem zu verfolgen.

Am Ende implementierten wir eine E-Mail-Benachrichtigung, wenn eine Sperre einen bestimmten Schwellenwert überschritt. Nachdem wir dies eingerichtet hatten, konnten wir die Prozesse identifizieren, die gesperrt waren, und die Isolationsstufe so ändern, dass sie unbestimmt gelesen wird, um das Problem zu beheben.

Hier ist ein Artikel, der einen Überblick über die Konfiguration dieser Art von Benachrichtigung gibt.

Wenn sich das Sperren als das Problem herausstellt und Sie dies nicht bereits tun, würde ich empfehlen, die - Zeilenversionierungs-basierte Isolationsstufe zu konfigurieren .

4

Da ich im Rahmen meiner Arbeit täglich Fehlerbehebungen durchführen möchte, möchte ich Folgendes tun:

  1. Da es sich um SQL Server 2008 R2 handelt, können Sie SQLDiag ausführen, das Bestandteil des Produkts ist. Sie können Bücher online für weitere Details beziehen. Kurz gesagt, erfassen Sie das serverseitige Trace- und Blocker-Skript.

  2. Suchen Sie nach dem Erfassen der Spur nach dem Ereignis "Aufmerksamkeit". Das wäre der Spid, der den Fehler erhalten hat. Wenn Sie nach SPID filtern, wird das Ereignis RPC: Completed vor "Attention" angezeigt. Überprüfen Sie die Zeit dort. Ist das etwa 30 Sekunden? Wenn ja, dann hat der Client 30 Sekunden gewartet, um eine Antwort von SQL zu erhalten, und hat "Zeitüberschreitung" erhalten.

  3. Prüfen Sie nun, ob die Abfrage, die ausgeführt wurde, wirklich 30 Sekunden dauern sollte. 

  4. Wenn ja, optimieren Sie die Abfrage oder erhöhen Sie die Timeout-Einstellung vom Client.

  5. Wenn nein, muss diese Abfrage auf einige Ressourcen warten (blockiert)

  6. Gehen Sie an dieser Stelle zurück zu Blocker Script und überprüfen Sie den Zeitrahmen, wenn "Attention" kam

Oben wird davon ausgegangen, dass das Problem mit SQL Server und nicht mit dem Netzwerk zusammenhängt!

1

Sieht so aus, als hätten Sie vielleicht schon eine Antwort, aber falls Sie einen weiteren Platz zum Suchen benötigen, sollten Sie die Größe und Aktivität Ihrer temporären Datenbank überprüfen. Wir hatten einmal ein Problem wie dieses bei einem Kunden, bei dem einige Male am Tag die Leistung schrecklich beeinträchtigt wurde und gelegentlich Timeout auftrat. Es stellte sich heraus, dass das Problem eine separate Anwendung war, die die temporäre Datenbank so stark beeinträchtigte, dass sie die allgemeine Serverleistung beeinträchtigte. 

Viel Glück bei der weiteren Fehlersuche! 

1
Carth

Ich habe gesehen, dass ähnliche Probleme auftreten, wenn auf dem SQL-Server Antivirus installiert wurde. Die Auto-Update-Funktionen des AV-Servers führten zur Taktung des Servers und erlaubten nicht genügend CPU für SQL Server.

Haben Sie auf dem SQL-Server selbst eine kleine Anwendung installiert, die überprüft, ob Verbindungen hergestellt werden können oder sehr einfache SQL wie "SELECT GETDATE ();" Dies würde die Netzwerkmöglichkeiten beseitigen.

1

Sie sind mit Ihrer Verfolgung und Profilierung auf dem richtigen Weg. Was Sie tun müssen, ist, nach den Gemeinsamkeiten der Abfragen zu suchen. Wahrscheinlich werden sie alle eine kleine Teilmenge von Tabellen oder Indizes treffen. Ich habe den Verdacht, dass einige Anwendungen ein langes Update/Insert haben, das sich auf Abfragen in Tabellen auswirkt, die von den Updates/Inserts betroffene Indizes verwenden. 

Sie müssen ein wenig rückwärts arbeiten - angesichts der Teilmenge der Tabellen, für die Zeitüberschreitungen angezeigt werden, können Sie feststellen, welche Indizes diese Tabellen enthalten. Suchen Sie nach anderen Abfragen, die zur selben Zeit ausgeführt werden, die diese Tabellen/Indizes berühren. Ich wette, Sie werden eine kleine Anzahl von Updates/Inserts finden, die dies tun.

Dann müssen Sie einige Entscheidungen treffen. Eine Möglichkeit besteht darin, die Sperrhinweise für die Abfragen zu ändern, für die ein Zeitlimit überschritten wird. Aber das ist in der Regel eine schlechte Praxis, weil sie das eigentliche Problem für eine Weile überdecken wird. Während Sie sehen, dass die Zeitüberschreitungen für eine Weile verschwinden, kann es je nach ausgewähltem Hinweis zu fehlerhaften Lesevorgängen und dann zu falschen Daten kommen, die aus diesen Abfragen stammen. Das könnte sich als schlimmer als die Timeouts herausstellen - schwer zu sagen.

Am besten ist es herauszufinden, welche Ihrer Anwendungen die gefundenen Updates/Einfügungen einreichen, und Dig in, um herauszufinden, warum sie so lange brauchen.

1
n8wrl

Ich schlage vor, Sie schauen sich die Funktion Dynamic Management Views von SQL Server an:

Dynamische Verwaltungsansichten und Funktionen geben Informationen zum Serverstatus zurück das kann verwendet werden, um den Zustand einer Serverinstanz zu überwachen, diagnostizieren Sie Probleme und die Leistung einstellen.

Dieser Artikel ist ein guter Anfang mit DMVs, obwohl er für SQL 2005 geschrieben wurde (DMVs erscheinen zuerst): Problembehandlung bei Leistungsproblemen in SQL Server 2005 , insbesondere die Kapitel "Blockieren".

1
Simon Mourier

Meine Erfahrung mit diesen Problemen (allerdings nicht in SQL Server) ist, dass das übertriebene Multi-Tasking häufig die Ursache des Problems ist. Wenn ähnliche/verbundene Daten/Tabellen (fast) zur gleichen Zeit von vielen Verbindungen abgefragt werden, hat das DBMS möglicherweise Probleme, die gesamte Isolation bei der Überprüfung zu erhalten. Dies ist nicht so sehr ein Problem der Festplattennutzung, als dass einige Verbindungen warten, bis andere Dinge erledigen. Die Synchronisierung ist hinsichtlich der CPU-Auslastung sehr teuer.

Die 100 Verbindungen sind meiner Meinung nach viel zu viel. (Nach meiner Erfahrung wieder) sind sogar 20 Verbindungen, die von einer Maschine ausgeführt werden sollen, möglicherweise zu optimistisch.

1
MarianP

Wir haben dies mit SQL Server 2012/SP3 erlebt, als wir eine Abfrage über ein SqlCommand-Objekt in einer C # -Anwendung ausführen. Der Befehl war ein einfacher Aufruf einer gespeicherten Prozedur mit einem Tabellenparameter. Wir haben eine Liste mit etwa 300 ganzen Zahlen übergeben. Die Prozedur wiederum rief drei benutzerdefinierte Funktionen auf und übergab die Tabelle als Parameter an jede von ihnen. Der CommandTimeout wurde auf 90 Sekunden festgelegt.

Wenn genau die gleiche gespeicherte Prozedur mit demselben Argument in SQL Server Management Studio ausgeführt wurde, wurde die Abfrage innerhalb von 15 Sekunden ausgeführt. Bei der Ausführung in unserer Anwendung mit dem obigen Setup trat beim SqlCommand jedoch ein Zeitlimit auf. Derselbe SqlCommand (mit unterschiedlichen, aber vergleichbaren Daten) wurde bereits seit Wochen erfolgreich ausgeführt. Nun schlug er jedoch mit einem Tabellenargument aus, das mehr als 20 Ganzzahlen enthielt. Wir führten eine Ablaufverfolgung durch und stellten fest, dass die Datenbank beim Ausführen aus dem SqlCommand-Objekt die gesamten 90 Sekunden für das Erfassen von Sperren aufgewendet hat und die Prozedur nur zum Zeitpunkt des Timeouts aufruft. Wir haben die CommandTimeout-Zeit geändert, und egal, zu welcher Zeit wir die gespeicherte Prozedur ausgewählt haben, wird nur am Ende dieses Zeitraums aufgerufen. Wir vermuten also, dass SQL Server immer und immer wieder die gleichen Sperren erlangt hat und dass nur das Timeout des Command-Objekts dazu geführt hat, dass SQL Server seine Endlosschleife anhält und mit der Ausführung der Abfrage beginnt. Eine Simulation desselben Prozesses auf einem ähnlichen Server mit ähnlichen Daten zeigte kein solches Problem. Unsere Lösung bestand darin, den gesamten Datenbankserver neu zu starten, woraufhin das Problem behoben wurde.

Es scheint also, dass es ein Problem in SQL Server gibt, bei dem einige Ressourcen kumulativ verbraucht und niemals freigegeben werden. Wenn eine Verbindung über eine SqlConnection hergestellt wird und ein SqlCommand mit einem Tabellenparameter ausgeführt wird, geht der SQL Server schließlich in eine Endlosschleife, die Sperren abruft. Die Schleife wird durch das Timeout des SqlCommand-Objekts beendet. Die Lösung besteht darin, einen Neustart durchzuführen, der scheinbar (vorübergehend?) In SQL Server wiederhergestellt wird.

0
Dave Ziffer

Sind diese Server virtualisiert? In einem anderen Beitrag habe ich gelesen, dass ein SQL-Server manchmal sehr langsam läuft, weil nicht genügend Speicher vorhanden ist. Dies wurde wiederum durch einen sogenannten Memory-Balloon verursacht, mit dem der Virtualizer die von diesem virtuellen Server belegte Speichermenge einschränkte. Es war schwer zu finden, da der Druck auf den physischen Speicher nichts mit dem SQL-Server selbst zu tun hatte.

Eine andere häufige Ursache für einen vorübergehenden Leistungsabfall kann ein Virenscanner sein. Wenn eine neue Virendefinition installiert wird, leiden alle anderen Prozesse und werden sehr langsam ausgeführt. Überprüfen Sie alle anderen automatischen Aktualisierungsvorgänge. Dies kann auch unerwartet viele Ressourcen erfordern. Viel Glück damit!

0
Dony

Das Problem liegt an einer fehlerhaften Abfrage, die Zeit zum Ausführen der Abfrage dauert mehr als 60 Sekunden oder eine Sperre für die Tabelle

Das Problem sieht so aus, als ob ein Deadlock auftritt. Wir haben Abfragen, die die Abfragen rechtzeitig blockieren. Das Standardzeitlimit für eine Abfrage beträgt 60 Sekunden. Danach wird die SQLException für das Zeitlimit verwendet.

Überprüfen Sie die SQL Server-Protokolle auf Deadlocks. Die andere Möglichkeit, das Problem zu lösen, um das Zeitlimit für das Befehlsobjekt (Temp Solution) zu erhöhen.

0
Amit Bagga