Datenbank wächst · SQL Server Datenbank zu groß · Archivierung

Die Datenbank wächst und wächst – und alles wird langsamer

Jeden Tag kommen Buchungen, Belege und Protokolle hinzu, und nichts geht je wieder heraus. Irgendwann heißt es, die SQL Server Datenbank sei zu groß und die Software deshalb langsamer. Wenn die Datenbank wächst und die Performance sinkt, ist die Größe allein nach meiner Erfahrung selten die Ursache: Meist liest eine Abfrage mehr Zeilen, als sie braucht, und mit jedem Jahr mehr Daten wird das spürbarer.

Die Datenbank wird immer größer, und die Frage lautet: Daten löschen oder behalten? Archivierung im SQL Server, Löschen oder Partitionieren hilft nur, wenn es gemessen und gewollt ist. Eine große Datenbank ist beherrschbar; ob sie langsam bleibt, entscheidet sich an den Abfragen und am Umgang mit alten Daten.

Problem schildern Was dahinter steckt

Daten bleiben bei Ihnen Messung vorher und nachher Eingriff nur nach Ihrer Freigabe Geheimhaltung (NDA) vorab
In Ihren Worten

So beschreiben es Kunden

Wachstum beschreiben Kunden selten mit Fachworten. Erkennen Sie sich in einem dieser Sätze wieder?

Was dahinter meistens steckt

Datenbank wächst: Warum die Performance sinkt – und was sich messen lässt

Die Abfrage liest mit der Datenmenge mit

Fehlt der passende Index, durchsucht SQL Server bei jeder Anfrage die ganze Tabelle, und mit mehr Daten dauert jede Anfrage länger. Im Labor sanken bei zehn Millionen Zeilen die Lesezugriffe von 349.716 auf 11 Seiten, sobald der passende Index da war.

Messmatrix im Labor →

Das Löschen alter Daten dauert ewig

Ein DELETE schreibt jede Zeile ins Protokoll und pflegt jeden Index mit. Im Labor dauerte es bei einhundert Millionen Buchungen, einen Monat zu entfernen, 14.082 Millisekunden mit Datumsindex und 67.298 Millisekunden ohne; das Leeren der Monatspartition brauchte 8,5 Millisekunden.

Messreihe: DELETE gegen Partition leeren →

Partitionierung ist kein Beschleuniger

Partitionierung dient der Datenpflege, nicht der Abfragegeschwindigkeit: Die Monatssumme brauchte partitioniert 244 Millisekunden, mit einfachem Datumsindex 173, und die Kontoabfrage 285 gegen 45 Millisekunden.

Messreihe: Partitionierung und Abfragen →

Sehr große Datenbanken sind beherrschbar

Bei einem Halbleiterhersteller prüfte ich im Betrieb rund um die Uhr monatlich rund 1,4 Milliarden neue Datensätze gegen 64 Milliarden Bestandszeilen (4 TB), ohne Produktionsstillstand. Entscheidend war die Architektur, nicht die Größe.

Praxisfall NXP Semiconductors →

Entscheidung

Daten löschen oder behalten? Drei Schritte, in dieser Reihenfolge

Archivierung im SQL Server ist erst die Folge einer Entscheidung. Dahinter steht die Frage, wie lange welche Daten im schnellen Zugriff bleiben müssen.

Messen Welche Daten liest die Anwendung wirklich, welche nie? Die Zugriffe auf alte Zeilen zeigen es, ohne Tabelleninhalte zu lesen.
Entscheiden Was behalten, was archivieren, was löschen, entscheidet Ihr Fachbereich mit den Aufbewahrungsfristen. Das entscheide ich nicht für Sie.
Technik wählen Passender Index, Archivtabelle oder leere Monatspartition, je nachdem, was die Messung zeigt. Nach Ihrer Freigabe und mit Rückweg.
Ablauf

Was ich tue – und was nicht

Was ich tue

  • Messen, welche Abfragen mit der Datenmenge mitwachsen und welche Daten kaum noch gelesen werden.
  • Die wirksamste Maßnahme, meist ein Index oder eine angepasste Abfrage, nach Ihrer Freigabe einspielen.
  • Vorher und nachher unter gleichen Bedingungen messen. Typisch dauert der Check zwei bis drei Tage.

Was ich nicht tue

  • Für Sie entscheiden, was Sie löschen dürfen: Aufbewahrungsfristen klären Sie mit Ihrem Fachbereich.
  • Partitionieren, weil es modern klingt: Es kommt nur, wo die Messung es rechtfertigt.
  • Daten mitnehmen: Messprotokolle und Produktionsdaten bleiben bei Ihnen.
Ergebnis

Was Sie danach haben

Sie haben eine belegte Antwort: ob die Größe der Datenbank das Problem ist oder eine einzelne Abfrage, welche Daten Sie sinnvoll archivieren können und was jede Maßnahme bewirkt hat, vorher und nachher gemessen. Dazu gehören eine Kurzfassung für die Leitung, eine nach Wirkung geordnete Maßnahmenliste mit Rückweg und alle verwendeten Skripte.

Ein Beispiel finden Sie hier: Beispielbefund (PDF, Labordaten).

Häufige Fragen

Drei Fragen zur wachsenden Datenbank

Muss die Datenbank kleiner werden, damit die Software schneller wird?

Nicht unbedingt. Eine große Datenbank ist nicht automatisch langsam: Mit dem passenden Index liest eine Abfrage nur das, was sie braucht, egal wie viele Zeilen daneben liegen. Im Labor sanken bei zehn Millionen Zeilen die Lesezugriffe von 349.716 auf 11 Seiten. Kleiner wird die Datenbank dadurch nicht, aber die Anwendung wird schneller. Sinnvoll ist das Entfernen alter Daten dort, wo sie Wartung, Sicherung oder Speicher belasten, und wo Ihr Fachbereich es erlaubt. Ob die Größe oder eine einzelne Abfrage bremst, zeigt eine Messung, bevor Sie archivieren oder löschen. Wenn die Messung zeigt, dass es nicht die Datenmenge ist, sage ich Ihnen das. Vor dem Löschen lohnt außerdem die Frage, welche Daten Ihr Fachbereich aus gesetzlichen oder fachlichen Gründen behalten muss: Das entscheidet die Fachseite, nicht die Technik.

Hilft Partitionierung, wenn die Datenbank wächst?

Für die Datenpflege oft ja, für die Abfragegeschwindigkeit nicht automatisch. Im Labor ließ sich bei einhundert Millionen Buchungen ein Monat durch Leeren der Partition in 8,5 Millisekunden entfernen, ein DELETE brauchte 14.082 Millisekunden. Die Abfragen waren dagegen nicht schneller: Die Kontoabfrage brauchte partitioniert 285 Millisekunden, mit einfachem Index 45. Partitionierung lohnt sich, wenn regelmäßig ganze Zeiträume entfernt werden, und sie setzt voraus, dass der Monat genau in einer Partition liegt und alle Indizes gleich partitioniert sind. Für Abfragen ersetzt sie den passenden Index nicht. Ich prüfe vorab, ob sie sich für Ihre Datenbank rechnet. Gemessen wurde mit synthetischen Labordaten; Ihre Zahlen können abweichen, deshalb messe ich an Ihrem System. Auch ein Index nach Datum beschleunigt das Entfernen eines Monats: Ohne Datumsindex brauchte das DELETE im Labor 67.298 Millisekunden.

Wie groß darf eine SQL-Server-Datenbank werden?

Eine feste Obergrenze nenne ich nicht, weil sie von Zugriffsmustern, Hardware und Architektur abhängt. Aus der Praxis: Bei NXP Semiconductors lief eine Datenbank mit 64 Milliarden Bestandszeilen (4 TB) im Betrieb rund um die Uhr, und monatlich kamen rund 1,4 Milliarden neue hinzu. Dafür waren eine Insert-only-Architektur mit temporaler Datenhaltung und entkoppelte Warteschlangen nötig, und die Daten wurden batchweise migriert, ohne die Produktion anzuhalten. Für ein Mittelstandssystem ist das nach meiner Erfahrung selten nötig; dort genügen meist passende Indizes, angepasste Abfragen und eine Entscheidung über alte Daten. Wo Ihre Grenze liegt, zeigt eine Messung an Ihrem System und nicht eine Faustregel. Ob sich ein solcher Aufwand für Sie lohnt, hängt vom Wachstum und von der Art der Zugriffe ab; die Messung zeigt es, bevor Sie etwas umbauen.

Schildern Sie mir in drei Sätzen, was mit der Datenmenge langsamer wird

Welche Maske, welcher Bericht, welcher Lauf, und seit wann? Im Erstgespräch klären wir, ob sich eine Messung lohnt.

Problem schildern → So läuft der Performance-Check

Mehr dazu: Praxisfall NXP Semiconductors · Labor: DELETE gegen Partition leeren · SQL Server langsam · Datenbank optimieren · SQL Server Performance-Analyse