(Ursprünglich auf DBA.StackExchange.com gepostet, aber geschlossen, hoffentlich hier relevanter.)
Alexander und die schrecklichen, furchtbaren, ganz und gar schlechten ... Backups.
Die Einrichtung:
Ich habe eine Vor-Ort-SQL Server 2016 Standard EditionInstanz auf einemvirtuelle Maschinevon VMWare.
@@Ausführung:
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) – 13.0.5888.11 (X64) 19. März 2021 19:41:38 Copyright (c) Microsoft Corporation Standard Edition (64-Bit) auf Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
Der Server selbst ist derzeit belegt8 virtuelle Prozessoren, hat32 GB Arbeitsspeicherund alleFestplatten sind NVMedie herumkommen1 GB/s E/A. Die Datenbanken selbst befinden sich auf dem Laufwerk G: und die Backups werden separat auf dem Laufwerk P: gespeichert. Die Gesamtgröße aller Datenbanken beträgt etwa 500 GB (vor der Komprimierung in die Backup-Dateien selbst).
Der Wartungsplan wird einmal pro Nacht (gegen 22:30 Uhr) ausgeführt, um eine vollständige Sicherung aller Datenbanken auf dem Server durchzuführen. Auf dem Server läuft sonst nichts Ungewöhnliches, und auch sonst läuft zu dieser Zeit nichts Besonderes. Der Energiesparplan des Servers ist auf „Ausgeglichen“ eingestellt (und „Festplatte ausschalten nach“ ist auf 0 Minuten eingestellt, d. h. nie ausschalten).
Was ist passiert:
Im letzten Jahr betrug die Gesamtlaufzeit des Wartungsplanjobs etwa 15Protokollinsgesamt zu vervollständigen. Seit letzter Woche ist es sprunghaft angestiegen und dauert ungefähr 40x so lange, ungefähr 15Std.fertigstellen.
Das Einzige, was sich meines Wissens am selben Tag, an dem der Wartungsplan langsamer wurde, geändert hat, war, dass vor Ausführung des Wartungsplans die folgenden Windows-Updates auf der Maschine installiert wurden:
Wir haben auch eine andere, ähnlich bereitgestellte SQL Server-Instanz auf einer anderen VM, die denselben Windows-Updates unterzogen wurde und danach ebenfalls langsamere Backups aufwies. Da wir dachten, dass die Windows-Updates direkt die Ursache waren, haben wir sie vollständig zurückgesetzt, und der Wartungsplan für Backups läuft trotzdem immer noch extrem langsam. Seltsamerweise erfolgt die Wiederherstellung der Backups für eine bestimmte Datenbank sehr schnell und nutzt fast die vollen 1 GB/s I/O auf den NVMes.
Dinge, die ich ausprobiert habe:
Bei Verwendung von sp_whoisactive von Adam Mechanic habe ich festgestellt, dass die letzten Wartetypen der Sicherungsprozesse immer auf ein Problem mit der Festplattenleistung hinweisen. Ich sehe immer Wartetypen BACKUPBUFFERund BACKUPIOzusätzlich ASYNC_IO_COMPLETION:
Beim Betrachten des Ressourcenmonitors auf dem Server selbst wird während der Sicherungen im Abschnitt „Festplatten-E/A“ angezeigt, dass die gesamte E/A-Nutzung nur etwa 14 MB/s beträgt (das Höchste, was ich seit dem Auftreten dieses Problems gesehen habe, sind 30 MB/s):
Nachdem ich über diese hilfreicheArtikel von Brent Ozar zur Verwendung von DiskSpd, ich habe versucht, es selbst unter ähnlichen Parametern auszuführen (nur die Anzahl der Threads auf 8 zu reduzieren, da ich 8 virtuelle Prozessoren auf dem Server habe, und die Schreibvorgänge auf 50 % einzustellen). Dies ist der genaue Befehl diskspd.exe -b2M -d60 -o32 -h -L -t8 -W -w50 "C:\Users\...\Desktop\Microsoft DiskSpd\Test\LargeFile.txt". Ich habe eine Textdatei verwendet, die ich manuell generiert habe und die knapp 1 GB groß ist. Ich glaube, die gemessenen E/A scheinen in Ordnung zu sein, aber die Festplattenlatenzen zeigten einige lächerliche Zahlen:
Die DiskSpd-Ergebnisse scheinen buchstäblich unglaublich. Nach weiterem Lesen stieß ich auf eine Abfrage von Paul Randall, die Festplattenlatenzmetriken pro Datenbank zurückgibt. Dies waren die Ergebnisse:
Die schlechteste Schreiblatenz betrug 63 Millisekunden und die schlechteste Leselatenz 6 Millisekunden, das scheint also eine große Abweichung von DiskSpd zu sein und scheint nicht schlimm genug, um die Grundursache meines Problems zu sein. Um die Dinge weiter zu überprüfen, habe ich einige PerfMon-Zähler auf dem Server selbst ausgeführt, prodieser Microsoft-Artikel, und dies waren die Ergebnisse:
Hier ist nichts Außergewöhnliches, der Maximalwert aller von mir gemessenen Zähler war 0,007 (was, glaube ich, Millisekunden sind?). Schließlich ließ ich mein Infrastrukturteam die Festplattenlatenzmetriken überprüfen, die VMWare während des Backup-Jobs protokollierte, und dies waren die Ergebnisse:
Im schlimmsten Fall schien es gegen Mitternacht eine Latenzspitze von etwa 200 Millisekunden zu geben und der höchste I/O-Wert lag bei 600 KB/s (was ich nicht wirklich verstehe, da der Ressourcenmonitor anzeigt, dass die Backups mindestens etwa 14 MB/s I/O nutzten).
Andere Dinge, die ich versucht habe:
Ich habe gerade versucht, eine der größeren Datenbanken (ca. 250 GB) wiederherzustellen, und die Wiederherstellung hat insgesamt nur ca. 8 Minuten gedauert. Dann habe ich versucht, DBCC CHECKDBsie auszuführen, und das hat insgesamt 16 Minuten gedauert (ich bin nicht sicher, ob das normal ist), aber der Ressourcenmonitor hat ähnliche E/A-Probleme angezeigt (die höchste E/A, die jemals genutzt wurde, war 100 MB/s), während nichts anderes ausgeführt wurde:
Hier sind die Ergebnisse von sp_whoisactive beim ersten Ausführen DBCC CHECKDBund dann, nachdem es zu 5 % abgeschlossen war. Beachten Sie, dass die geschätzte verbleibende Zeit um etwa 5 Minuten anstieg, obwohl es bereits zu 5 % abgeschlossen war.
Start:
5 % erledigt:
Ich vermute, das ist normal, da es sich nur um eine Schätzung handelt, und 16 Minuten scheinen für eine 250 GB große Datenbank nicht so schlimm zu sein (obwohl ich nicht sicher bin, ob das normal ist), aber wiederum war der E/A-Zustand nur bei etwa 10 % der Kapazität des Laufwerks maximal, und auf dem Server oder der SQL-Instanz lief nichts anderes.
Das sind die Ergebnissevon DBCC CHECKDB, keine Fehler gemeldet.
Ich habe auch seltsame Langsamkeitsprobleme mit dem SHRINKBefehl festgestellt. Ich habe gerade versucht, SHRINKdie Datenbank zu öffnen, die 5 % Speicherplatz freizugeben hatte (ca. 14 GB). Es dauerte nur etwa 1 Minute, bis 90 % davon abgeschlossen waren SHRINK:
Ungefähr 5 Minuten später hängt es immer noch beim gleichen Prozentsatz der Fertigstellung fest und meine Transaktionsprotokollsicherungen (die normalerweise in 1-2 Sekunden fertig sind) sind seit ungefähr 30 Sekunden umstritten:
15 Minuten später ist es SHRINKgerade fertig, während die Transaktionsprotokollsicherungen seit etwa 6 Minuten immer noch umstritten sind und erst zu 50 % abgeschlossen sind. Ich glaube, sie wurden gleich danach fertig, da sie SHRINKfertig waren. Die ganze Zeit zeigte der Ressourcenmonitor an, dass die E/A immer noch nervte:
SHRINKDann erhielt ich nach Abschluss des Befehls eine Fehlermeldung :
Ich habe es SHRINKnoch einmal versucht und das Ergebnis war genau das gleiche wie oben.
Dann habe ich versucht, manuell ein T-SQL-Backup in eine Datei auf dem Laufwerk P: zu skripten, und das lief genauso langsam wie der Backup-Job des Wartungsplans:
Ich habe es nach etwa 3 Minuten abgebrochen und es wurde sofort zurückgesetzt.
Zusammenfassung:
Zufällig wurde der Job des Wartungsplans für Backups jede Nacht etwa 40-mal langsamer (von 15 Minuten auf 15 Stunden), direkt nachdem Windows-Updates installiert wurden. Das Zurücksetzen dieser Windows-Updates hat das Problem nicht behoben. SQL Server Wait Types, Resource Monitor und Microsoft DiskSpd weisen auf ein Festplattenproblem hin (insbesondere I/O), aber alle anderen Messungen aus Paul Randalls Abfrage, PerfMon und VMWare Logs melden keine Probleme mit den Festplatten. Das Wiederherstellen der Backups für eine bestimmte Datenbank geht schnell und nutzt fast die volle I/O von 1 GB/s. Ich kratze mir am Kopf ...
Antwort1
In diesem Fall hatten wir tatsächlich ein Festplattenproblem und es war kein internes Problem von SQL Server, für diese spezielle VM. Es war tatsächlich ein Fehlerfall, auf den wir bei Veeam und VMWare gestoßen sind.
Um mein Verständnis dessen, was passiert ist, zusammenzufassen: Anscheinend wurden unsere Veeam-Backups von VMWare nicht als abgeschlossen bestätigt. Jeden Tag, wenn es Zeit war, den Server zu sichern, wies VMWare Veeam an, am Vortag erneut ein Backup zu erstellen, was sich im Laufe von zwei Wochen zu diesem immer größer werdenden Problem entwickelte. (Ich bin sicher, dass ich diese Erklärung verpfuscht habe, aber das ist so ziemlich alles, was ich weiß.)
Veeam / VMWare mussten jede Snapshot-Datei löschen, wobei die Datei jedes Tages größer war als die vorherige, sodass der Level-3-Support etwa 26 Stunden benötigte, um fertig zu werden. Danach lief die VM wieder einwandfrei. Laut dem technischen Support ist dies offenbar kein ungewöhnliches Problem.
Tut mir leid, das war ein sehr spezielles Problem und wird wahrscheinlich vielen anderen da draußen nicht helfen, aber ich hoffe es hilft.