Datenlogger mit MSSQL und Index-Fragmentierung

[Blup]

Hallo,
wird diese Datenbank auch regelmäßig ausgewertet oder nur im Fehlerfall?
Denn dann sollten die für die Auswertung nötigen Indizes auch erst dann angelegt werden.
Das Schreiben der Datensätze geht dadurch viel schneller.
Statt Datensätze einzeln zu löschen, lege für jeden Tag oder Monat eine neue Datenbank an.

[BigAl]

Zitat von Blup:

Hallo,
wird diese Datenbank auch regelmäßig ausgewertet oder nur im Fehlerfall?
Denn dann sollten die für die Auswertung nötigen Indizes auch erst dann angelegt werden.

Ja, die Daten werden permanent angezeigt. Zwar nur ein Teil der Kanäle (ca. 20) aber die anderen müssen immer verfügbar sein. Der Nutzer kann jederzeit in den Detailansichten auf diese zugreifen.

Zitat von Blup:

Das Schreiben der Datensätze geht dadurch viel schneller.

Die Geschwindigkeit beim Schreiben ist kein Problem. Ich habe da mal einen Test gemacht und könnte mit dem Client ca. 3500 bis 4000 Datensätze pro Sekunde schreiben. Und dabei scheint der Flaschenhals noch außerhalb des SQL-Servers zu liegen. Dessen Last ist dann gerade mal bei 10% bis 15%...

Zitat von Blup:

Statt Datensätze einzeln zu löschen, lege für jeden Tag oder Monat eine neue Datenbank an.

Auf die Idee bin ich noch nicht gekommen. Da muss ich mir mal Gedanken machen. Es geht ja - wie gesagt - nur um diese eine Tabelle. Evtl. noch um eine Alarm-Tabelle, welche aufgrund der Konsistenz dazu gepackt werden könnte. Alle anderen Daten sind fest, bzw. ändern sich nur sehr sehr selten...

[HolgerX]

Hmm..

Oder nach dem Delete ein ALTER INDEX ALL auf die Tabelle.

https://learn.microsoft.com/de-de/sq...l-server-ver17

Einfaches Syntaxbeispiel

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

[BigAl]

Zitat von HolgerX:

Hmm..

Oder nach dem Delete ein ALTER INDEX ALL auf die Tabelle.

https://learn.microsoft.com/de-de/sq...l-server-ver17

Einfaches Syntaxbeispiel

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Ein REBUILD blockiert die Tabelle relative lange (aktuell ca. 1,5 bis 2 Minuten). Evtl. ein REORGANIZE, welches auch relativ lange benötigt, aber das Schreiben und Lesen weiterhin zulässt. Ob es aber zielführend ist das in kurzen Intervallen auszuführen ist fraglich...

[MyRealName]

Alte Datensätze müssen doch nicht jede Minute geprüft und gecheckt werden (es sollten 60 sein). Mach es einmal am Tag, am Besten zu einer Zeit, wo Du ohne Probleme auch den Index wieder herrichten kannst. Nachts um 2 zum Bsp.

[BigAl]

Zitat von MyRealName:

Alte Datensätze müssen doch nicht jede Minute geprüft und gecheckt werden (es sollten 60 sein). Mach es einmal am Tag, am Besten zu einer Zeit, wo Du ohne Probleme auch den Index wieder herrichten kannst. Nachts um 2 zum Bsp.

Naja. Einmal pro Minute max. 100 Datensätze löschen hat nicht wirklich Einfluss auf die Performance. Einmal pro Stunde müsste ich dann schon > 3600 alte Datensätze löschen. Das geht dann auch wieder zu Lasten des transaction-log-Files...

Nachts ist keine gute Idee. Dann lieber zu einer Zeit zu der die Ingenieure greifbar sind. Sonst gibt es nachts Anrufe

[MyRealName]

Bei 7.7 mio DS zu jeder Zeit in der DB machen 3600 DS nichts aus, zumal es indiziert ist. Und selbst 24*3600 DS macht da nicht viel aus. Es geht ja auch nicht ums Löschen, sondern um den Index neu bauen. Mach das 1x pro Tag zu einer ruhigen Stunde. Weil Dir geht es ja eh nur um den Speicherplatz, der Index funktioniert ja korrekt auch nach dem Löschen.

[Bernhard Geyer]

evtl. anderes DBMS nehmen?
z.B. ein PostgreSQL welche einen Mechanismus hat automatisch immer wieder indizes zu defragmentieren hat, so das hier das evtl. gar nicht auffällt wenn das passiert.

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

Ach ja,

komischerweise belegt der Primärindex Unmengen an Seiten:

Code:

TABLE   INDEX                     FRAGMENTATION [%] PAGE COUNT
------- ----------------------- ----------------- ----------
Trends  PK_Trends_ID                         59,80     590734
Trends  IDX_Trends_LogTime                    0,04      21326