Datenlogger mit MSSQL und Index-Fragmentierung

[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.

[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?