Datenlogger mit MSSQL und Index-Fragmentierung

[Uwe Raabe]

Nur mal so ein wenig out-of-the-box gedacht:

Was wäre wenn du alle Datensätze im Voraus anlegst, diese nach aufsteigender ID per UPDATE befüllst und wenn du beim letzten angekommen bist wieder mit dem ersten weitermachst (ein Ringbuffer halt). In einer separaten Tabelle führst du ID und LogTime des ältesten und neuesten Satzes mit.

Da ja (nach 90 Tagen) für jede Sekunde ein LogTime NOT NULL -Datensatz vorhanden ist, kannst du unter Berücksichtigung des Überlaufs für jede Sekunde die ID bestimmen. Den Index auf LogTime brauchst du nicht mehr und der für ID ändert sich nicht, da beim UPDATE die ID gleich bleibt.

Abfragen nach Zeiträumen errechnen die WHERE Klausel für die entsprechenden ID Werte (in den ersten 90 Tagen noch mit LogTime NOT NULL ) und die Sortierung läuft über ein ORDER BY LogTime .

Das müsste man sicher noch im Detail ausarbeiten, aber es wäre zumindest ein interessanter Ansatz.

[BigAl]

Zitat von Uwe Raabe:

Nur mal so ein wenig out-of-the-box gedacht:

Was wäre wenn du alle Datensätze im Voraus anlegst, diese nach aufsteigender ID per UPDATE befüllst und wenn du beim letzten angekommen bist wieder mit dem ersten weitermachst (ein Ringbuffer halt). In einer separaten Tabelle führst du ID und LogTime des ältesten und neuesten Satzes mit.

Da ja (nach 90 Tagen) für jede Sekunde ein LogTime NOT NULL -Datensatz vorhanden ist, kannst du unter Berücksichtigung des Überlaufs für jede Sekunde die ID bestimmen. Den Index auf LogTime brauchst du nicht mehr und der für ID ändert sich nicht, da beim UPDATE die ID gleich bleibt.

Abfragen nach Zeiträumen errechnen die WHERE Klausel für die entsprechenden ID Werte (in den ersten 90 Tagen noch mit LogTime NOT NULL ) und die Sortierung läuft über ein ORDER BY LogTime .

Das müsste man sicher noch im Detail ausarbeiten, aber es wäre zumindest ein interessanter Ansatz.

Hallo Uwe

Ich bin immer wieder begeistert von Deiner Denkweise. Genau so ein Ansatz ist mir im Kopf herumgeschwirrt. Das könnte funktionieren, wenn da auch mit einer kleinen Hürden für die ich noch keine Lösung habe:

Aktuell speichere ich einen NULL-Datensatz ab, der mir eine Unterbrechung der Aufzeichnung markiert. Wird die Anlage also mal abgeschaltet, dann kann ich damit die Lücke im Graph entsprechend visualisieren:



Wie ich diesen "Sprung" in der vordefinierten Tabell festhalten soll weiß ich momentan noch nicht. Eine Idee wäre eine Spalte wie "NextID" neben der "ID" einzuführen. Diese speichert dann den jeweils nächsten gültigen Index. Die Sprünge müsste ich dann halt mit meiner Software herausfiltern. Da ich in der Anzeige maximal zwei Stunden pro Seite ausgebe wäre das bei 7200 Datensätzen nicht die Welt...

Ich werde aber auf jeden Fall diesen Ansatz weiter verfolgen.

Danke!

[Uwe Raabe]

Die Logik aus einem sekundengenauen Datum eine ID zu berechnen erfordert allerdings genau einen Datensatz für jede Sekunde. Eine solche Unterbrechung würde dann eben auch durch mehrere Null-Sätze in der Tabelle hinterlegt werden. Ob du das dann als einen Eintrag anzeigst bleibt dir ja dann immer noch offen.

[BigAl]

Zitat von Uwe Raabe:

Die Logik aus einem sekundengenauen Datum eine ID zu berechnen erfordert allerdings genau einen Datensatz für jede Sekunde. Eine solche Unterbrechung würde dann eben auch durch mehrere Null-Sätze in der Tabelle hinterlegt werden. Ob du das dann als einen Eintrag anzeigst bleibt dir ja dann immer noch offen.

Klar. Es gibt halt zwei Möglichkeiten:

1. Nach einer Unterbrechung die übersprungenen Datensätze mit entsprechenden Null-Werten füllen.

oder

2. Eben mit der "NextID"-Markierung entsprechend markieren wie viele Datensätze zu überspringen sind.

Bei Lösung 2 müsste halt nur ein Datensatz aktualisiert werden und man könnte die Null-Daten nach dem Einlesen schneller bearbeiten (überspringen), da nicht jeder Datensatz betrachtet werden müsste. Allerdings wäre dann das Problem, dass man im schlimmsten Fall innerhalb der Null-Werte anfängt zu lesen und nicht weiß, dass es sich um Null-Werte handelt. Da ist dann wieder Lösung 1 besser...

[TigerLilly]

Die Idee mit dem Vorausfüllen ist gut. Mich würde nur der Aufwand abschrecken und der Overhead, der dadurch entsteht.

Ein REORGANIZE müsstest du nicht nach jedem Löschen ausführen. Ich schätze mal, dass 1x am Tag genügt. REORGANIZE ist Standard-MS-SQL, das kann schon mehrfach aufrufen.

Vielleicht kann man auch Hardwareseitig optimieren: Mehr RAM + den Index auf eine eigene SSD.

[BigAl]

Zitat von TigerLilly:

Die Idee mit dem Vorausfüllen ist gut. Mich würde nur der Aufwand abschrecken und der Overhead, der dadurch entsteht.

Ein REORGANIZE müsstest du nicht nach jedem Löschen ausführen. Ich schätze mal, dass 1x am Tag genügt. REORGANIZE ist Standard-MS-SQL, das kann schon mehrfach aufrufen.

Vielleicht kann man auch Hardwareseitig optimieren: Mehr RAM + den Index auf eine eigene SSD.

Das mit dem Ausfüllen ist eigentlich nicht so schlimm: "UPDATE ... WHERE ID >= x AND ID <= x+n" oder so ähnlich. Die Null-Kennung kann man ja an einem Feld festmachen. Es muss ja nicht alles genullt werden...

Das mit dem Speicher ist halt ein Problem. Die Express-Version des SQL-Servers ist auf 10 GB Größe begrenzt. Da knallt es sehr schnell (habe es ausprobiert). Selbst meine "großen" Version des Servers (ich nutze hier den Development-Server) ist da eine Weile beschäftigt und erzeugt Unmengen an Daten...

[QuickAndDirty]

Macht es einen Unterschied wenn AUTO_CREATE_STATISTCS für diese Tabelle aif OFF stellt?

Code:

EXEC sp_autostats 'LOGTABELLE', 'OFF'

[BigAl]

Zitat von QuickAndDirty:

Macht es einen Unterschied wenn AUTO_CREATE_STATISTCS für diese Tabelle aif OFF stellt?

Code:

EXEC sp_autostats 'LOGTABELLE', 'OFF'

Nein.

Auto-Stats sind bei mir generell eingeschaltet. Allerdings kontrolliere ich zyklisch, ob irgendwelche Schlüssel auf Basis der Abfragestatistik erstellt wurden. Ich prüfe dann normalerweise den Hintergrund. Sind die Schlüssel sinnvoll, dann lösche ich die automatisch erstellten Schlüssel und definiere die manuell. Manchmal sind auch "unglückliche" Abfragen meinerseits dafür verantwortlich. In dem Fall optimiere ich die Abfragen...

[Delphi.Narium]

Warum muss bei dem Vorhandensein von allen Datensätzen die ID berechnet werden? Der neueste Satz kommt immer in den ältesten Satz.

Ungefähr sowas:

SQL-Code:

UPDATE LogTable
SET
    Column1 = 'NeuerWert1',
    Column2 = 'NeuerWert2',
    LogTime = '2025-07-19 20:00:00'
WHERE LogTime = (
    SELECT MIN(LogTime) FROM LogTable
)

Ist die ID dann überhaupt noch erforderlich?

Brauchst Du zwingend eine ID und hast zwingend jede Sekunde einen Satz, dann könnte die ID auch ein UnixTimeStamp sein (Sekunden seit 1.1.1970), das geht dann bis zum dem 19. Januar 2038, 03:14:07 UTC gut. Bei BIGINT hättest Du noch etwa 584 Milliarden Jahren Zeit, bis ein Überlaufproblem auftritt.

Bei den Lücken setzt Du nur die ID auf den UnixTimeStamp, um die Reihenfolge beibehalten zu können, die LogTime aber auf Null. Damit kannst Du dann die Sortierung der Datensätze beibehalten und trotzdem die Lücken erkennen. (Auch, wenn es sich strenggenommen bei der ID als UnixTimeStamp und LogTime als DateTime um eine Redundanz handelt, könnte das hier sinnvoll und/oder praktikabel sein.)

Das Update müsste dann aber abgeändert werden:

SQL-Code:

UPDATE LogTable
SET
    Column1 = 'NeuerWert1',
    Column2 = 'NeuerWert2',
    LogTime = '2025-07-19 20:00:00'
    ID = UnixTimeStampVonLogTime
WHERE ID = (
    SELECT MIN(ID) FROM LogTable
)

Um den Index nicht zu sehr anwachsen zu lassen kannst Du per

SQL-Code:

SELECT
    index_type_desc,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('LogTabelle'), NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10

prüfen, ob er fragmentiert ist, wenn ja, rufst Du ein ALTER INDEX IX_Timestamp ON LogTabelle REORGANIZE; auf. Welcher Wert hier für avg_fragmentation_in_percent > 10 sinnvoll ist, müsstest Du ggfls. ausprobieren. Ein Index auf die ID dürfte dann ausreichen, ein zweiter Index auf LogTime wäre dann nicht erforderlich, da ja beide Spalten den gleichen Wert repräsentieren.

Ist jetzt nur so 'ne Idee, ob's umsetzbar ist, weiß ich nicht bzw. kann ich nicht beurteilen, da ich den genauen Aufbau des Systems nicht kenne.

[BigAl]

Zitat von Delphi.Narium:

Ist die ID dann überhaupt noch erforderlich?

Brauchst Du zwingend eine ID und hast zwingend jede Sekunde einen Satz, dann könnte die ID auch ein UnixTimeStamp sein (Sekunden seit 1.1.1970), das geht dann bis zum dem 19. Januar 2038, 03:14:07 UTC gut. Bei BIGINT hättest Du noch etwa 584 Milliarden Jahren Zeit, bis ein Überlaufproblem auftritt.

Die ID brauche ich aktuell wirklich nicht. Die ist eigentlich nur da, damit ich eine eindeutige Referenz auf die Datensätze habe. Theoretisch können doppelte Datensatze entstehen. Auch bei UTC, wenn z.B. die Zeit durch den Zeit-Server korrigiert wird.

Ansonsten ist es eigentlich relativ egal welchen Weg man geht. Sobald man den Zeitstempel in der Log-Datei hat muss ein Index verwaltet werden. Das mit dem Reorganize ist an sich auch kein Problem, solange ich das nicht durch die Logger-Task oder den Vordergrund ausführen lasse. Das läuft halt mehrere Minuten. Verloren gehen würde aber eh nichts, da der Datensammler selbst in eine Queue schreibt und eine separate Writer-Threat sich um die Datenbank kümmert. Sowohl die Writer-Threat als auch den Vordergrund möchte ich halt zu keiner Zeit (aus o.g. Gründen) blockieren.

Und ja, es muss jede Sekunden geschrieben werden. Das ist das obere Limit. Ich habe auch einen Kunden in den USA da muss ich mit 250 ms erfassen. Da ist aber ein "großer" Server dahinter und den Ablauf habe ich da etwas anders implementiert. Es geht bei der Software um die Überwachung von Sicherheitsrelevanten Funktionen. Den Sicherheitsteil übernimmt aber eine Steuerung (fehlersichere Siemens SPS). Das Delphi-Programm wird zum Visualisieren, Dokumentieren und Parametrieren verwendet.