Parametrisiertes Insert plötzlich abnormal langsam

[mariusbenz]

Zitat von exon:

Hallo, unabhängig von dem Problem,

Wäre es evtl. sinnvoll, auf Batch-Operation umzustellen?

https://docs.devart.com/unidac/batchops.htm

Nach jedem Insert wird noch ein Update in einer anderen Tabelle gemacht, um im Fehlerfall genau den fehlerhaften Datensatz identifizieren zu können.

Zitat von Frickler:

Wenn sowas "ganz plötzlich" passiert: gab es eventuell ein Update der DB-Server?

Habt ihr ansonsten mal probiert, das Statement umzuschreiben auf MERGE?

Es gab rund um den Zeitraum keine Updates auf betroffenen Systemen. Es war laut Log-Dateien von der 1 zur nächsten Minute plötzlich langsamer.

Zitat von dataspider:

Ist es auch richtig, dass du in der neuen Version Execute anstatt ExecSQL benutzt?
Möglicherweise gibt es da Unterschiede.

Die Zeile mit dem Prepare gehört IMO über das while unmittelbar nach dem Setzen des Statements.

Und Options.AutoPrepare der Query sollte False sein.

Frank

AutoPrepare ist false. Prepare ist vermutlich an der Stelle, weil dann die Datentypen durch die Zuweisungen bekannt sind (wenn ich es davor setze, kommen irgendwelche CONVERT variant Exceptions).
Einen Unterschied zwischen Execute und ExecSQL wollte ich gerade mal testen, nur um festzustellen, dass wieder alles funktioniert??? Das lief seit dem 06.01. 05:01 bis zum Verfassen des Themas durchgehend langsam.

Ich beobachte das jetzt mal weiter.

[Jasocul]

Zitat von mariusbenz:

aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.

Nein, das kann auch "plötzlich" passieren.
Wenn sich an den Statements nichts geändert hat, würde ich dort auch nicht mit der Problemsuche anfangen.
Zuerst würde ich prüfen, ob die Selects oder die Inserts langsamer geworden sind. Ich tippe erstmal auf die Inserts.
Du schreibst etwas von einem globalen Server, wo die Daten landen. Ist die Verbindung dorthin stabil und schnell genug? Wir hatten mal einen defekten Hub, der nur manchmal Fehler verursacht hatte. Wurde vielleicht ein Netzwerkkabel umgesteckt oder ausgetauscht? Falls ja, wo werden die Statements ausgeführt? Hat sich daran vielleicht etwas geändert? Ich meine mich erinnern zu können, dass es unter Hyper-V mal Performance-Probleme gab.
Wie voll sind die Datenbank-Dateien? Stehen die auf automatischer Vergrößerung? Sind die Vergrößerungsschritte groß genug?
Wann wurden die Indexe der betroffenen Tabellen das letzte Mal reorganisiert?
MS-SQL neigt dazu, den gesamten Hauptspeicher in Anspruch zu nehmen. Darum sollten man auf einem MS-SQL-Server möglichst auch keine anderen Anwendungen nutzen.
Hast du auf dem Server die Log-Files schon kontrolliert?
Greift ein AV-Programm evtl. auf die DB-Dateien zu? Kann nach einem Update oder Konfigurationsanpassung passiert sein. Eigentlich sollte in dem Bereich möglichst kein AV-Programm etwas machen.
Ganz banal einen Server-Neustart schon versucht?
Es gibt noch andere Möglichkeiten, aber das wäre der Bereich, den ich erstmal gründlich untersuchen würde.

[mariusbenz]

Zitat von Jasocul:

Zitat von mariusbenz:

aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.

Nein, das kann auch "plötzlich" passieren.
Wenn sich an den Statements nichts geändert hat, würde ich dort auch nicht mit der Problemsuche anfangen.
Zuerst würde ich prüfen, ob die Selects oder die Inserts langsamer geworden sind. Ich tippe erstmal auf die Inserts.
Du schreibst etwas von einem globalen Server, wo die Daten landen. Ist die Verbindung dorthin stabil und schnell genug? Wir hatten mal einen defekten Hub, der nur manchmal Fehler verursacht hatte. Wurde vielleicht ein Netzwerkkabel umgesteckt oder ausgetauscht? Falls ja, wo werden die Statements ausgeführt? Hat sich daran vielleicht etwas geändert? Ich meine mich erinnern zu können, dass es unter Hyper-V mal Performance-Probleme gab.
Wie voll sind die Datenbank-Dateien? Stehen die auf automatischer Vergrößerung? Sind die Vergrößerungsschritte groß genug?
Wann wurden die Indexe der betroffenen Tabellen das letzte Mal reorganisiert?
MS-SQL neigt dazu, den gesamten Hauptspeicher in Anspruch zu nehmen. Darum sollten man auf einem MS-SQL-Server möglichst auch keine anderen Anwendungen nutzen.
Hast du auf dem Server die Log-Files schon kontrolliert?
Greift ein AV-Programm evtl. auf die DB-Dateien zu? Kann nach einem Update oder Konfigurationsanpassung passiert sein. Eigentlich sollte in dem Bereich möglichst kein AV-Programm etwas machen.
Ganz banal einen Server-Neustart schon versucht?
Es gibt noch andere Möglichkeiten, aber das wäre der Bereich, den ich erstmal gründlich untersuchen würde.

Hallo Jasocul, da sind natürlich einige Sachen dabei, die wir uns sicherlich mal anschauen sollten.
- Die Schnittstelle läuft auf einem Hintergrundrechner mit Serverbetriebssystem, bei dem wurden auch Ping Downs vom PRTG gemeldet, den habe ich natürlich mit als erstes neugestartet. Der Datenbankserver an sich lief auch stabil, zumindest habe ich von keinen anderen Fehlverhalten etwas mitbekommen. Von meinem Arbeitsrechner aus hatte ich aber dieselben Probleme bei eben dieser einen Abfrage. Daher schließe ich Hardware/Netzwerk erst mal aus.
- Mit Dateispeichergrößen kenne ich mich nicht gut aus, anbei mal die aktuellen Einstellungen der DB. Das ist auch die mit Abstand größte DB auf dem Server und die Einstellungen wirken falsch auf mich. Hat dafür jemand Empfehlungen?
- Ich weiß leider auch nicht, was man wie bei Indizes genau einstellen muss. Damit habe ich mich noch nicht gut genug beschäftigt.
- Auf dem Datenbankserver (bzw. auf der Instanz) läuft meines Wissens nach nur der SQL Server
- In den SQL-Server Protokollen habe ich auf die schnelle nichts gefunden, habe aber auch nicht intensiv durchforstet
- Unseren blöden Antivirus habe ich mittlerweile grundsätzlich als Hauptverdächtigen, allerdings passt es irgendwie nicht, dass plötzlich eine einzelne Abfrage deswegen 1000x langsamer wird
- SQL-Server-Neustart wäre spätestens dann gekommen, wenn ich keine andere Lösung gefunden hätte. Dafür hat man im Produktivsystem natürlich immer nur ein kleines Zeitfenster

[Bernhard Geyer]

Zitat von Jasocul:

Zitat von mariusbenz:

aber ich hätte jetzt eher damit gerechnet, dass so etwas schleichend langsamer wird.

Nein, das kann auch "plötzlich" passieren.

Yepp. Hatten wir mal bei einem Kunden mit einer größeren Datenbank.
Auf einmal meinte der Query-Analyzer das man für eine relativ einfache Abfrage die sehr wenige (1-10) Datensätze liefert 1GB RAM benötigen würde.
AFAIK hat sich das mit einem Update des SQL-Servers irgendwann gelöst.

Abfrage im SQL Mangement Studio mal analysieren lassen, was da rauskommt bezüglich Abfragepfad und Kosten.

[Jasocul]

Hallo mariusbenz,
also die Datei hat schon 21GB und die Vergrößerung ist auf 1MB gesetzt. Das kommt mir im Verhältnis doch etwas wenig vor. Aber das kann auch völlig in Ordnung sein, wenn der Datenzuwachs sehr gering ist. Um das richtig einzustellen, muss man schon einiges analysieren.
Im letzten Unternehmen, bei dem ich war, wurden auch mehrere MS-SQL-Server eingesetzt. Für das Datawarehouse mit täglich, erheblichem Datenzuwachs, wurde die Datenbank mindestens einmal im Monat einem Optimierungslauf unterworfen. Das wurde über Jobs zum passenden Zeitpunkt automatisch angestoßen. Daher habe ich den passenden Befehl leider auch nicht mehr im Kopf. Wie das so ist: Einmal eingericht -> funktioniert und macht alles richtig -> nie mehr drüber nachdenken. Im jetzigen Unternehmen arbeite ich mit anderen Datenbanken und kann das leider nicht nachsehen.
Beim Schreiben ist es mir gerade wieder eingefallen (Glück gehabt ). Suche mal nach Update Statistics. Das war regelmäßig erforderlich und hat im Prinzip das von dir beschriebene Problem behoben. Der Import von Daten lief dort zwar über Dump-Imports, aber ich kann mir gut vorstellen, dass das bei dir auch helfen kann. Allerdings kann das einige Zeit dauern. Und damit meine ich nicht nur ein paar Minuten. Wenn du die Möglichkeit hast, würde ich das zunächst auf einem Testsystem mit einer Kopie der Datenbank machen. Bei der von dir beschriebenen Größe würde ich von ein paar Stunden ausgehen.

Vorher kannst du natürlich auch erstmal die betroffenen Indexe reorganisieren lassen.

[Delphi.Narium]

Wenn sichergestellt ist, dass das Problem tatsächlich aus Deinem Programm kommt (und nicht eine der vielen Möglichkeiten, die Jasocul aufführt, als Ursache in Betracht kommt):

Wie sieht das Transaktionshandling aus?
Eine Transaktion je ExecSQL oder eine Transaktion je while NOT (Que_L_Export.Eof) do , also alles aus einer Tabelle innerhalb einer Transaktion verarbeiten?

Statt je Datensatz immer wieder alle Parameter und Felder zu suchen, diese vorher als entsprechende Variabeln deklarieren und einmalig suchen lassen. Sowas in der Art:

Delphi-Quellcode:

var
  pIDVonQuelle : TParam;
  pParamID : TParam;
  pDelta : TParam;
  pAnzahl : TParam;
  pZeit : TParam;
  fID : TField;
  fParamID : TField;
  fDelta : TField;
  fAnzahl : TField;
  fGeaendert_am : TField;
begin
  ...
  pIDVonQuelle := Que_G_MengenLogbuch_Insert.Params.FindParam('IDVonQuelle');
  pParamID := Que_G_MengenLogbuch_Insert.Params.FindParam('ParamID');
  pDelta := Que_G_MengenLogbuch_Insert.Params.FindParam('Delta');
  pAnzahl := Que_G_MengenLogbuch_Insert.Params.FindParam('Anzahl');
  pZeit := Que_G_MengenLogbuch_Insert.Params.FindParam('Zeit');
  
  fID := Que_L_Export.FindField('ID');
  fParamID := Que_L_Export.FindField('ParamID');
  fDelta := Que_L_Export.FindField('Delta');
  fAnzahl := Que_L_Export.FindField('Anzahl');
  fGeaendert_am := Que_L_Export.FindField('geaendert_am');

  while NOT (Que_L_Export.Eof) do
   begin
     pIDVonQuelle.AsInteger := fID.AsInteger;
     pParamID.AsInteger := fParamID.AsInteger;
     pDelta.AsInteger := fDelta.AsInteger;
     pAnzahl.AsInteger := fAnzahl.AsInteger;
     fGeaendert_am.AsDateTime := fGeaendert_am.AsDateTime;
     if NOT(Que_G_MengenLogbuch_Insert.Prepared) then Que_G_MengenLogbuch_Insert.Prepare();
     Que_G_MengenLogbuch_Insert.Execute();
     Que_L_Export.Next;
   end; 
  ...

Bei großen Datenmengen kann sich das, auch wenn es erstmal nach banalem, vermeidbarem, Mehraufwand im Quelltext aussieht, (aus meiner Erfahrung heraus) durchaus positiv auf die Laufzeit (auf Clientebene) auswirken.

Bei den Parametern hast Du zusätzlich den Vorteil, dass Du den DataType und den ParamType angeben kannst und nicht darauf hoffen musst, dass die Datenbanksschnittstelle schon das Richtige machen wird.

Mit einh bisserl Glück kannst Du so das

Zitat von mariusbenz:

(wenn ich es davor setze, kommen irgendwelche CONVERT variant Exceptions)

vermeiden.

Also zusätzlich noch sowas in der Art:

Delphi-Quellcode:

  pIDVonQuelle.DataType := ftInteger;
  pParamID.DataType := ftInteger;
  pDelta.DataType := ftInteger;
  pAnzahl.DataType := ftInteger;
  pZeit.DataType := ftDateTime;
  pIDVonQuelle.ParamType := ptInput;
  pParamID.ParamType := ptInput;
  pDelta.ParamType := ptInput;
  pAnzahl.ParamType := ptInput;
  pZeit.ParamType := ptInput;

Damit müsstest Du das Prepare vor die While-Schleife setzen können.
Gibt es eine Garantie, dass das dann im realen (Datenbank)Leben wirklich was bringt? Nein, aber eventuell ist's 'nen Versuch wert.

Zitat von mariusbenz:

Unseren blöden Antivirus habe ich mittlerweile grundsätzlich als Hauptverdächtigen, allerdings passt es irgendwie nicht, dass plötzlich eine einzelne Abfrage deswegen 1000x langsamer wird

Doch, die können bei 'nem Update durchaus auch mal "Bockmist" gebaut haben und nun z. B. Datenbankdateien mit scannen, was sie vor dem Update nicht gemacht haben, irgendwelche konfigurierten Ausnahmeregeln nicht mehr beachten, ... Da kann schon ziemlich viel passieren, auf das Du erstmal keinen Einfluss hast, weil solches "Fehlverhalten" nicht zwingend offensichtlich ist.

Die DB ist laut Screenshot ca. 21 GB groß. Und die wird in Schritten von 1 Megabyte vergrößert?

Welche Datenmengen habt Ihr denn da? Könnte es sein, dass der Datenbankserver "fast ausschließlich" mit dem Vergrößern der Datenbankdatei beschäftigt ist? Und wenn die Platte dann stark fragmentiert ist oder sehr voll, wird sowas durchaus auch schonmal "etwas langsamer".

Das kann auch das "Plötzlich von jetzt auf gleich" für's Langsamwerden begründen. Bis 20 GB Ursprungsgröße war die Datenbankdatei ausreichend dimensioniert und ab 20 GB + 1 MB wird "alle Nase lang" vergrößert.

Berechne bitte mal grob die Datenmenge, die Du pro Aufruf Deiner Routine in etwa in die Datenbank schreiben musst, also nicht nur hier die langsame Routine, sondern auch die Protokollstatements, die in eine andere Tabelle geschrieben werden ... (ggfls. auch andere Prozesse, die in die Datenbank schreiben, beachten). Für wieviele Programmaufrufe reicht dann 1 Megabyte aus? Die 1 Megabyteschritte kämen für mich nur bei einer Datenbank mit marginalem Zuwachs in frage. (Flapsig formuliert: Der Zuwachs sollte schon für die nächsten ein oder zwei Wochen reichen )

Und: Alles was Jasocul schreibt, sollte vorab überprüft bzw. durchgeführt werden.

[masc-online]

Die Schrittweise Erweiterung der Datenbank-Dateien um 1 MB ist einer der hässlichsten Standardwerte, die Microsoft sich ausgedacht hat. Wenn hier ein Schwung Daten eingefügt werden, muss der SQL Server ständig die Anforderung zur Erweiterung ans OS senden. Wenn zudem das Ganze noch in einer virtualisierten Umgebung passiert und die VM-Disks sich ebenfalls aus Platzspargründen so eingestellt sind, dass sie Speicher erst bei Wachstum auf der virtuellen Platte allokieren, hat man weiter hinten auch noch einen zusätzlichen Request - und wenn man viel Glück hat, gilt gleiches auch noch im Storage-System.

--> Wenn das Autoextend aktiv bleiben soll, würde ich etwas größere Blöcke nehmen (irgendwas zwischen 64 MB und 512 MB). In der Praxis würde ich zudem ein "wir erwarten im nächsten Quartal das Wachstum von xx GB" bevorzugen - und dann die Größe direkt zuweisen.


Ansonsten würde ich bei dem Problem empfehlen, einen Blick auf den Activity Monitor des SQL Servers zu werfen. Mit dem kriegt man einen relativ schnellen Einblick, womit sich die Datenbank gerade beschäftigt und kann dann gezielt das Problem angehen.

[TigerLilly]

Yep. Activity Monitor bzw Profiler - dann weißt du was passiert und was wie lange dauert. Für so ein Verhalten kann es - haben ja schon einige gesagt - viele Gründe geben. Aber ohne strukturiertes Vorgehen (=messen!) ist das ein Stochern im Heuhaufen.

Ich würde mit den Ausführungsplänen der SQL Statemnets anfangen. Ich würde auch am Server Performanceprotokolle mitlaufen lassen, da gibt es gute Kennzahlen für den SQL Server, die weiterhelfen.

https://learn.microsoft.com/en-us/sq...l-server-ver16
https://learn.microsoft.com/en-us/sq...l-server-ver16
https://learn.microsoft.com/en-us/sq...l-server-ver16

[mariusbenz]

Vielen Dank für alle eure Antworten.

Also die DB ist seit gestern um gut 20MB gewachsen, hier werde ich definitiv mal einen höheren Wert einstellen. Ebenso die Index-Reorganisierung.

Da sich das Problem gestern ja "von selbst" wieder gelöst hat, als ich einige vorherige Vorschläge am testen war, kann ich jetzt nicht mehr viel analysieren.
Wenn der Antivirus die Ursache war, würde es auch Sinn machen, dass es plötzlich wieder geht. Der wird ja auch ständig im Hintergrund aktualisiert, ohne dass man was direkt davon mitbekommt.
Über den Ausführungsplan haben wir die kritischen Abfragen zumindest schon soweit analysiert und Anpassungen vorgenommen, dass keine fehlenden Indizes mehr angemeckert werden. Was man darüber hinaus aus diesem Diagramm ableiten kann ist mir aber ein Rätsel.