Neu SQL DB Speicherplatz vs. Vergangenheitsdaten

[schraubenking]

Hallo Zusammen,
Da wir nun seit ~2011 mit WAWI arbeiten, haben sich in vielen Tabellen Einträge aus der Urzeit gesammelt, welche die SQL DB unnötig aufblasen und daher hab ich mir mal die größten Tabellen rausgesucht.

tbestellpos ....1.768GB
tArtikelhistory ...742MB
tPicklistePos ...535MB
tWarenLagerAusgang....373MB
.....
und viele andere mehr, die zu einer recht großen DB beitragen und dazu führen, dass die Nutzung (trotz vernünftiger Infrastruktur) recht mühsam wird.

Jetzt meine Fragen:
a) tPicklistePos und auch die tPicklistePosStatus: hier hab ich in der ..Pos Tabelle fast 3 Mio Einträge ! Da wir jedoch WMS nicht nutzen und ich auch sonst keine Möglichkeit kenne im Wawi (nach Pickliste liefern) auf eine alte Pickliste zuzugreifen, könnte man diese Tabellen nicht einfach bis zu einem bestimmten Stichtag (z.B. 01.01.2019) entleeren.

b) tbestellpos: eigentlich gleiche Geschichte...mich interessiert nicht was vor 12 Jahren im einzelnen bestellt wurde und lt. Aufbewahrungspflichten für das Finanzamt, sind auch nur 7 Jahre vorzuhalten.
Dies gilt übrigens auch für die Tabellen tWarenLagerAusgang und auch tArtikelhistory

Da wir den Server bereits wöchtenlich neu starten und auch die WAWI DB Verwaltung fast täglich laufen lassen, jedoch das Arbeiten immer mühsamer wird, überlege ich solche Schritte um das System wieder zu beschleunigen.

Auf eine höhere Version möchte ich aktuell noch nicht umstellen, da ich von vielen Performance Problemen gelesen hab und die 1.5.55er für uns eigentlich alles bieten was wir benötigen.

Gibt es hierzu Tipps bzw. hat sowas schon mal wer gemacht ?

Ich bedanke mich im Voraus für eure Beiträge.
Markus

 

[Stephan K.]

Man könnte tArtikelhistory z.B. effizient als Kompromiss straffen: Man nimmt jeden Eintrag summiert für die Vergangenheit (-50x Korrekturbuchung, 1712 Wareneingang, 699 Warenausgang, etc) und löscht dann alle einzelnen Einträge, die über die individuelle Aufbewahrungszeit hinaus gehen. So hat man summierte Vergangenheitswerte, ohne die Daten vollständig zu löschen.
Es würde auch bei der Statistik das Alter in Tagen/Monaten der summierten Daten erfasst werden müssen, um hier keine Durchschnittswerte zu verlieren für lange Zeiträume.
Einer der ältesten Artikel in unserem Dauersortiment ist 7 Jahre alt. Da würde ich auch ungern auf den Wert von vor 10 Jahren verzichten müssen, aber ein grober Durchschnitt wäre nach so einer langen Zeit ja auch okay. Ließe sich ggf. gleich individualisieren ("x Tage Aufbewahrungszeit über die gesetzliche GoBD-Zeit von 10 Jahren hinaus")

Da könnte man in JTL nen Hebel für die Aufbewahrungszeit einbauen, der dies steuert. Aber in 5 Minuten ist das nicht gemacht, weil es umfangreicher ist, als es aussieht.
Wäre aber sehr geil, denn auch wir haben seit 2013 die Wawi am Start und da könnte man generell ne Datenbank-Hygiene einführen, die auch für long-term user geeignet ist. 😅

 

[mh1]

Die wichtigste Zutat für einen performanten SQL-Server ist RAM, nicht die Anzahl der Datensätze in der Datenbank.

 

[recent.digital]

+ die CPU-Performance.

Interessant wäre auch welche SQL Version/Edition (2017/2019/2022 bzw. (Express/Standard) im Einsatz ist. Der Wechsel von der 1.5 auf 1.6/1.7/1.8 bringt mehr Speicherbedarf mit sich.

 

[_simone_]

Die wichtigste Zutat für einen performanten SQL-Server ist RAM, nicht die Anzahl der Datensätze in der Datenbank.

Ok, aber damit die DB in der Express-Version funktioniert, ist auch die wachsende Anzahl der Datensätze zu beachten.

Mit Datensätzen aus 2009, neuester Wawi und über 8 GB Datenbank stoßen wir so langsam an die Grenze. Die Performance ist nach wie vor top, aber es wäre schon sehr schick wenn die alten Daten entsorgt werden könnte. Einen Vorteil für uns sehe ich nicht in der Vollversion.

btw: thx an den TO für den Thread

 

[schraubenking]

Hallo,

lt. unserem Netzwerktechniker langweiligen sich die CPU, der RAM und auch die Netzwerkauslastung ist minimal....

Zur Konfiguration :
Intel XEON Silver 4114 CPU (2 Prozessoren)
24 GB Ram
schnelle SSD Platten
SQL Server 2019 Standard
max. 10 Nutzer aktiv über die Firma

Ich hab mir die Überwachung bei den Komponenten auch angesehen und wir kommen eigentlich nirgendwo auf eine nennenswerte Auslastung bzw. gibt es kein Anzeichen für ein physisches Ressourcenproblem.

Auch haben wir eine 2ten Datenbank (BMD für Buchhaltung) auf dem Server laufen und dort laufen die Vorgänge flüssig und ohne größere Wartezeiten (obwohl diese DB auch 25 GB groß ist). Da hier nur 1e Person vormittags zugreift, kann ich auch ausschließen, dass es an den 2 DB ( WAWI und BMD) liegt, denn am Nachmittag ist WAWI genauso zäh wie am Vormittag.

Es scheint auch als ob die Response Time von WAWI immer länger wird je öfter man einen Vorgang durchführt. Dies fällt besonders in der Einkaufsliste auf....hier aktualisieren wir vor der Bestellung alle Preise (d.h. Artikel öffnen - neue EKs eintragen - speichern & zum nächsten Artikel). Ab dem 3-4 Artikel bleibt dann das Artikelfenster oft eine gefühlte Ewigkeit am Bildschirm stehen bis wir wieder in der EK Liste zurück sind. Bei täglich 200-300 Posten die wir zu bestellen haben, nervt das schon richtig... .

Aber zurück zum Thema....
Welche Tabellen habt ihr schon bereinigt bzw. wäre auch wünschenswert von Seiten JTL mal zu erfahren, ob es hier Pläne für die Zukunft gibt....DB Bereinigungstool ?

Markus

 

[mvh]

Moin.
Es gibt tatsächlich eine interne SQL-Server Prozedur, die die "alten" Kundendaten samt Kunden löschen soll, heißt Maintenance.spKundenDatenDelete
Den Aufruf-Punkt in meiner WaWi (1.7) habe ich nicht gefunden, vielleicht kommt es noch.

Zu deinem anderen Problem.
Wenn Du nur einen Mandanten und den Zugang zum SQL Server Management Studio hast -
rufe bitte diese Indexfragmentierung-Prüfroutine auf. Kommen da viele Datensätze ?

USE eazybusiness
SELECT object_name(IPS.object_id) AS [TableName],
   SCH.name AS [SchemaName],
   ISNULL(SI.name,'') AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   --IPS.avg_page_space_used_in_percent,
   --IPS.record_count,
   --IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages,
   CASE WHEN IPS.Index_type_desc='HEAP' THEN CONCAT('ALTER TABLE ',SCH.name,'.',object_name(IPS.object_id), ' REBUILD') ELSE
   CONCAT ('ALTER INDEX ',SI.name,' ON ',SCH.name,'.',object_name(IPS.object_id), IIF(avg_fragmentation_in_percent>30,' REBUILD',' REORGANIZE')) END as sSQL
FROM sys.dm_db_index_physical_stats(db_id(N'eazybusiness'), NULL, NULL, NULL , NULL) IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.schemas SCH ON SCH.schema_id = ST.schema_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE avg_fragmentation_in_percent>=10
ORDER BY TableName, IndexName

 

[schraubenking]

Danke...werde ich machen und melde mich dann im Forum
Markus

 

[wawi-dl]

Moin.
Es gibt tatsächlich eine interne SQL-Server Prozedur, die die "alten" Kundendaten samt Kunden löschen soll, heißt Maintenance.spKundenDatenDelete
Den Aufruf-Punkt in meiner WaWi (1.7) habe ich nicht gefunden, vielleicht kommt es noch.

Zu deinem anderen Problem.
Wenn Du nur einen Mandanten und den Zugang zum SQL Server Management Studio hast -
rufe bitte diese Indexfragmentierung-Prüfroutine auf. Kommen da viele Datensätze ?

USE eazybusiness
SELECT object_name(IPS.object_id) AS [TableName],
   SCH.name AS [SchemaName],
   ISNULL(SI.name,'') AS [IndexName],
   IPS.Index_type_desc,
   IPS.avg_fragmentation_in_percent,
   IPS.avg_fragment_size_in_pages,
   --IPS.avg_page_space_used_in_percent,
   --IPS.record_count,
   --IPS.ghost_record_count,
   IPS.fragment_count,
   IPS.avg_fragment_size_in_pages,
   CASE WHEN IPS.Index_type_desc='HEAP' THEN CONCAT('ALTER TABLE ',SCH.name,'.',object_name(IPS.object_id), ' REBUILD') ELSE
   CONCAT ('ALTER INDEX ',SI.name,' ON ',SCH.name,'.',object_name(IPS.object_id), IIF(avg_fragmentation_in_percent>30,' REBUILD',' REORGANIZE')) END as sSQL
FROM sys.dm_db_index_physical_stats(db_id(N'eazybusiness'), NULL, NULL, NULL , NULL) IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.schemas SCH ON SCH.schema_id = ST.schema_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE avg_fragmentation_in_percent>=10
ORDER BY TableName, IndexName

Da ist das MVH Team wieder, DANKE

Wir haben hier 299 Treffer, heisst das, man sollte die DB immer mal wieder "defragmentieren"? Wie?
http://olafhelper.over-blog.de/article-33856922.html

Bringt das soviel Performance?

 

[wawi-dl]

...
Es scheint auch als ob die Response Time von WAWI immer länger wird je öfter man einen Vorgang durchführt. Dies fällt besonders in der Einkaufsliste auf....hier aktualisieren wir vor der Bestellung alle Preise (d.h. Artikel öffnen - neue EKs eintragen - speichern & zum nächsten Artikel). Ab dem 3-4 Artikel bleibt dann das Artikelfenster oft eine gefühlte Ewigkeit am Bildschirm stehen bis wir wieder in der EK Liste zurück sind. Bei täglich 200-300 Posten die wir zu bestellen haben, nervt das schon richtig... .
...

Bei euren Produkten macht man das doch nicht manuell???
Wir haben uns dazu Excel-Listen gebaut, wir kopieren die Daten vom Lieferanten rein, Knopp drücken und ein VBA schuckt die Ameise an und lädt alle Infos hoch.

Wir erhalten so monatlich Preislisten per CSV und updaten diese halb automatisiert.


SQL Befehl setzt günstigsten Lieferanten an Standard, in der JTL Einkaufsliste den Lieferant Standard setzen, bestellen, fertig.

 

[mvh]

Da ist das MVH Team wieder, DANKE

Wir haben hier 299 Treffer, heisst das, man sollte die DB immer mal wieder "defragmentieren"? Wie?
http://olafhelper.over-blog.de/article-33856922.html

Bringt das soviel Performance?

Ja. Manchmal sogar spürbar.
Wir führen täglich den Skript von Ola Hallengren aus (wurde von JTL in spOptimieren integriert)
aber zusätzlich dazu 1 x Wöchentlich unseren Skript.
In der Spalte sSQL stehen die fertigen SQL-Aufrufe.
Aber wie gesagt - lieber Abends (kann recht lange dauern)
und manchmal auch mehrmals.

 

[wawi-dl]

EDIT:
Also diesen BEfehl "EXEC eazybusiness.dbo.spOptimieren" haben wir, dass ist ja der von Ola.

Nur die Frage, ob wir defragmentieren müssen, anbei mal ein Screenshot, da wir viel mit Ameise machen.

 

[schraubenking]

Bei euren Produkten macht man das doch nicht manuell???
Wir haben uns dazu Excel-Listen gebaut, wir kopieren die Daten vom Lieferanten rein, Knopp drücken und ein VBA schuckt die Ameise an und lädt alle Infos hoch.

Wir erhalten so monatlich Preislisten per CSV und updaten diese halb automatisiert.


SQL Befehl setzt günstigsten Lieferanten an Standard, in der JTL Einkaufsliste den Lieferant Standard setzen, bestellen, fertig.

Hallo,
Ja, leider schon...kaum ein Lieferant ist in der Lage das zu liefern und in der Branche ändern sich die Preis eigentlich täglich. Bei heute 10tsd. Artikel mit in Summe 50 Lieferanten mit meist 2-3 Lieferanten je Artikel wäre der Aufwand die Preise täglich zu importieren sehr hoch. Da wir so zwischen 4-8 Wochen Mindestbestand vorhalten, interessieren mich die aktuellen Preis auch nur quasi alle 4-8 Wochen....daher dieser Weg.
Markus

 

[mvh]

EDIT:
Also diesen BEfehl "EXEC eazybusiness.dbo.spOptimieren" haben wir, dass ist ja der von Ola.

Nur die Frage, ob wir defragmentieren müssen, anbei mal ein Screenshot, da wir viel mit Ameise machen.
Den Anhang 103678 betrachten

Moin.
Ja, wir hatten davor auch nur spOptimieren.
Und haben irgendwann festgestellt, dass bis zum nächsten WaWi-Update wird die Datenbank-Performance
immer schlechter, besonders die Bestellvorschläge und Amazon wurden mit der Zeit immer langsamer.
Dann habe ich diesen Skript erstellt und ausprobiert und wir waren positiv überrascht.
Die Schritte sind so:
1. Alle Abfragen werden in der sSQL-Spalte angezeigt, diese kopieren und als eine Abfrage ausführen, kann manchmal recht lange dauern.
2. Danach wieder diesen SQL ausführen, und es sollten jetzt viel weniger "fragmentierte" Indizes sein. Eventuell Schritt 1 wiederholen.
Ich würde mich sehr freuen, wenn jemand es ausprobiert und hier über die Ergebnisse berichtet.

 

[wawi-dl]

@mvh
Ich habe den SELECT-Befehl abgesetzt und erhalte 295 Zeilen!

Soll ich also jetzt alle 295 sSQL ALTER-Befehle in der letzten Spalte nacheinander ausführen?
Oder meinst du damit, ich soll diese Spalte kopieren und dann alle laufen lassen?

Ich lese REBUILD, das heisst der Befehl reorganisiert alles, Tabelle für Tabelle und defragmentiert sozusagen?

Möchte nur verstehen was passiert und ob etwas "kaputt" gehen kann.

 

[schraubenking]

Hallo,
Wir haben nun auch die Abfrage gemacht und ich komm auf 248 Zeilen !?
Auch ich hab den Plan dahinter noch nicht verstanden und kann die Frage von WAWI-DL nur wiederholen.

Ich bitte daher um kurze Erklärung - Danke.
Markus

 

[mvh]

Hallo.
Zuerst kurze Erklärung, worum es überhaupt geht.
Ein Index ist eine an den Datensatz und an ausgewählte Spalten gekoppelte Struktur, die die Suche für diese Spalten beschleunigt und effizienter macht.
Die bekanntesten Indexe sind s.g. primäre Datensatzschlüssel, die den Datensatz quasi "sofort" finden lassen.
Die Daten selbst werden in die Speicherblöcke (Pages) geladen und der Index wird zum logischen Verweis darauf.
Meistens werden Indexe durch B-TREE (binäre Bäume) repräsentiert.
Leider werden mit der Laufe der Zeit die physischen Speicherblöcke immer "entfernter" von diesen Verweisen,
die Daten werden nicht "nacheinander" durchsucht und gefunden, sondern DBMS muss immer springen, das heißt Fragmentierung
Um das Problem zu lösen, könnte die DBMS (Datenbank) die Daten in die freien/unbenutzten Speicherblöcke verschieben, die aber einander liegen,
dass heißt REORGANIZE. Oder den Index "verwerfen" und neu erstellen, das heißt REBUILD.

Ich sage nicht, dass die Fragmentierung "schlecht" ist oder, dass unser Skript alle Probleme mit der Suche "löst".
Jeder kann es selbst ausprobieren, "kaputt" geht normalerweise nichts.
So sieht die Ausgabe in SSMS aus:


Ich kopiere die Zeilen aus der Spalte sSQL in ein neues Abfragefenster. Ihr könnt auch Alle nehmen.

und führe die Abfrage aus. Das war es.
Viele Grüße, Ihr MVH-Team

 

[wawi-dl]

Vielen Dank!

Waru macht man dann einmal "REBUILD" und "REORGANIZE"?
Habt ihr das für euch entschieden, ab wieviel Prozent dann lieber neu aufgebaut wird?


P.S. wir haben es laufen lassen, von 297 Tabellen auf 57, weiter gehts nichts. Performance überwachen wir jetzt mal.
Datenbankgröße wurde von 5,4GB auf 5,2GB kleiner, auch schon mal etwas.

 

[mvh]

Vielen Dank!

Waru macht man dann einmal "REBUILD" und "REORGANIZE"?
Habt ihr das für euch entschieden, ab wieviel Prozent dann lieber neu aufgebaut wird?


P.S. wir haben es laufen lassen, von 297 Tabellen auf 57, weiter gehts nichts. Performance überwachen wir jetzt mal.
Datenbankgröße wurde von 5,4GB auf 5,2GB kleiner, auch schon mal etwas.

Unter 10% Fragmentierung machen wir nichts - lohnt sich nicht,
zwischen 10 und 30 - REORGANIZE, ist schneller als REBUILD und meistens erfolgreich
ab 30% REBUILD. Das sind alles unsere Erfahrungswerte.
Aber der Skript ist frei zu nutzen, jeder kann es für sich selbst anpassen.

 

[wawi-dl]

Danke für die Aufklärung, wir testen schon auf dem Testsystem, schaut gut aus

REBUILD wäre aber grundsätzlich dann wohl besser?