Datenlogger mit MSSQL und Index-Fragmentierung

[Delphi.Narium]

Ok, wenn theoretisch doppelte Datensätze entstehen können, weil durch Zeitumstellung, Uhrenkorrektur, ... eine Zeit mehrfach vorkommen kann, entfällt das Update auf den ältesten Datensatz über den Datums-/Zeitwert, da dieser ja mehrfach vorhanden sein könnte.

Dann könnte man für die ID eine Sequenz nehmen und die beim Update immer auf den nächsten Wert der Sequenz setzen. Damit wäre es immernoch möglich das Update (wie oben skizziert) auf den Satz mit der ältesten (kleinsten) ID zu machen, das Berechnen der ID des zu aktuallisierenden Satzes kann dann trotzdem entfallen und die korrekte Sortierung der Datensätze bleibt beim Order By ID bestehen, auch wenn Zeiten mehrfach vorhanden sein sollten.

Ungefähr sowas:

SQL-Code:

CREATE SEQUENCE Seq_LogTableID AS BIGINT START WITH 1 INCREMENT BY 1;

CREATE OR ALTER TRIGGER trg_LogTableID ON LogTable AFTER UPDATE AS 
BEGIN
  SET NOCOUNT ON;
  -- Jede vom UPDATE betroffene Zeile bekommt jetzt eine neue ID, durch neuen Sequencewert.
  UPDATE LogTable SET LogTable.ID = NEXT VALUE FOR Seq_LogTableID
  FROM LogTable
  INNER JOIN inserted ON LogTable.ID = inserted.ID;
END

[Uwe Raabe]

Wenn ich das richtig sehe, bleibt aber das Problem wenn ein Index auf ID liegt und somit aktualisiert werden muss. Der fluktuierende Index war doch eigentlich der Auslöser für die Eingangsfrage.

[Delphi.Narium]

Du zielst mit Deinem Vorschlag also darauf ab, dass die ID einmalig für die 7,7 Mio. Sätze angelegt wird, dann der Index einmal reorganisiert wird und anschließend niemehr schreibend angefasst wird? Das ist natürlich auch ein sinnvolles Vorgehen.

Wenn man dann die ID des zu änderenden Satzes immer richtig ermittelt, kann man aber nichtmehr per Order By ID die Datensätze korrekt nach LogTime sortiert ausgeben, schon garnicht, wenn LogTime doppelte Werte enthalten sollte. Dann müsste man nach LogTime und ID sortieren, was einen Index auf diese Spalten verlangen würde. Wir haben dann daher einen nicht zu reorganisierenden Index und einen zu reorganisierenden Index. Das Problem der Indexreorganisation bleibt bestehen. Da wir mehrere Indizes benötigen, steigt der insgesamt benötigte Speicherplatz und bringt uns näher an die 10 GB-Grenze.

Oder irre ich da?

[BigAl]

Hallo Uwe und Delphi.Narium,

ihr habt beide recht. Das Problem ist recht verzwickt.

Ich habe momentan einen Test laufen, der mir die entsprechenden Datensätze generiert. Zusätzlich logge ich in größeren Abständen die Datenbank und Tabellengröße, die Indexfragmentierungen sowie den Geschwindigkeitsverlust und die Auslastung des Servers bei zunehmender Fragmentierung. Die Ergebnisse sind dabei recht interessant:

Der Speicherverbrauch pendelt sich irgendwann ein. Die Datenbank wächst also nicht weiter und auch die Index-Größen bleiben irgendwann konstant. Die Fragmentierung des Primärschlüssels (ID) bleibt aktuell auch bei ca. 0,7%, ist also vernachlässigbar. Lediglich die Fragmentierung des Zeit-Index wird kontinuierlich schlechter. Allerdings kann ich momentan noch nicht sagen wohin der sich letztendlich entwickelt.

Bezüglich der Abfragegeschwindigkeit (die basiert immer auf der Zeit) kann ich aktuell keine Verschlechterung darstellen. Im Monitorbetrieb werden aber immer max. 7200 Datensätze (2 Stunden) abgefragt. Und das auch nur bei der ersten Anzeige der Graphen. Danach werden im Live-Betrieb immer nur die aktuellsten Werte abgerufen. Lediglich beim Export oder beim Analysieren der historischen Daten werden größere Datenmengen eingelesen. Aber auch da ist Momentan noch keine Verschlechterung zu beobachten.

Die Frage ist halt nun in wie fern ich mir um die Fragmentierung überhaupt Gedanken machen muss. Heutzutage ist das ja alles auf SSDs abgelegt. Und da die Daten aufgrund des "Clustered" Primärschlüssels ja immer hintereinander liegen kommt im Prinzip immer nur der Aufwand für das Lesen des Zeit-Schlüssels zum Tragen. Keine Ahnung wie intelligent der SQL-Server das intern Managed aber wenn das nicht ganz daneben ist, dann sollte die Fragmentierung des Index evtl. gar keine so große Rolle spielen. Ob der nun 100 µs oder 200 µs oder 1 ms braucht merkt man ja nicht wirklich.

Ich lassen den Test auf jeden Fall noch ein paar Tage laufen...

[Delphi.Narium]

Nur Gedankenspiele:

Wenn Du die letzten zwei Stunden der Daten haben möchtest, könnte die Abfrage bei meiner geänderten ID in etwa so aussehen:

SQL-Code:

SELECT *
FROM LogTable
WHERE ID > (
  SELECT current_value - 7200
  FROM sys.sequences WHERE name = 'DeinSequenzname'
)
ORDER BY ID;

Für den 250ms-Kunden wären es dann halt 7200 * 4, was ja über Abfrageparameter zu realisieren wäre.

Hast Du nun diese Daten und möchtest nur alle 10 Sekunden das Neue "dazuholen" wäre die 7200 durch 10 zu ersetzen.

Die ID für einen beliebig weit zurückliegenden Zeitraum ließe sich entsprechend berechnen, so dass ein Index auf LogTime entfallen kann. (Immer vorausgesetzt, dass wirklich exakt ein Satz pro Sekunde geschrieben wird.) Per Top 7200 gibt es dann immer einen Zeitraum von exakt zwei Stunden. Hier ließe sich durch Änderung der 7200 auch ein beliebig kurzer oder langer Zeitraum ausgeben.

Beim MSSQL-Server musst Du mit einer Fragmentierung bis etwa 30% nicht mit ernsthaften Performanceprobleme rechnen.

Wenn wir davon ausgehen, dass mein Vorschlag der Sortierung per ID und Sequenz umgesetzt wird: Ist dann der Zeit-Index überhaupt erforderlich?