Indexe müssen ständig neu erstellt werden

[jaepen]

Hallo

Ich habe ein kleines Problem mit einer MS SQL Datenbank.

Diese DB wurde von unserem Zulieferer installiert.
Es gibt eine Tabelle die eine art Historie umfasst. Nach ca. 1/2 Jahr 20 000 000 Einträge.
Um diese Datenmenge zu reduzieren, habe ich die gesamte Tabelle gelöscht und exakt die gleiche Tabelle angelegt.
Auch Indexe und Statistiken.

Nun habe ich aber das Problem, dass ich ca. 1mal pro Woche einen bestimmten (häufig verwendet) Index neu anlegen muss, da die Fragmentierung ansteigt.
Ich habe auch schon verschiedene Einstellungen verglichen wie sie vorher waren (altes Backup eingespielt), kann jedoch keine Unterschiede feststellen.

Was kann der Grund für die ständige Fragmentierung dieses Index sein?

Gruß Jaepen

[defo]

Was kann der Grund für die ständige Fragmentierung dieses Index sein?

Ich bin nicht besonders firm bei MS SQL, aber so gewisse Technik ähnelt sich ja.
Bei der Indizierung kann man auch bei MSSQL angeben, wieviel Platz je Block(?) initial verwendet werden soll. Der Rest kann bei späterer Verwendung gefüllt werden. Wird kein Platz reserviert, fragmentiert der Index.
Je schneller also in eine indizierte (fillfactor 100) Tabelle geschrieben wird, desto schneller fragmentiert sie.
Fillfaktor ensprechend verringern und die Fragmentierung sollte nachlassen.

[Ralf Jansen]

Reden wir hier von logischer Fragmentierung (wie defo annimmt) oder von physischer?

Historientabelle bedeutet nur Inserts keine Updates und keine Deletes?
Ist das der PK Index und/oder ein Clustered Index?

[defo]

Reden wir hier von logischer Fragmentierung (wie defo annimmt) oder von physischer?

Historientabelle bedeutet nur Inserts keine Updates und keine Deletes?
Ist das der PK Index und/oder ein Clustered Index?

Ich glaube, ich verstehe diese Nachfrage nicht.
Wenn ein Index eine logische Reihenfolge von Records aufbaut, tut er das idR so, dass auch (zunächst) physikalisch diese Reihenfolge eingehalten wird. Allein die Existenz des Fillfactor legt Nahe, dass hier ein N Baum Verfahren verwendet wird. Selbst mit Fillfactor < 100 und nur Inserts, entartet der Baum / Index irgendwann.

Dass es mit dem Fillfactor zu beheben ist, überhaupt MSSQL, alles nur Vermutungen von mir.

Ansonsten wäre noch fraglich, ob die Argumentation "..muss Index neuanlegen, da die Fragmentierung ansteigt.." genau das Problem trifft.

"bestimmter Index" in Kombie mit Historientabelle klingt außerdem nach einem Index über mehrere Felder. Nicht unbedingt ideale Bedingungen für einen Clustered Index. Aber das ist Spekulation. Der TE verrät vielleicht noch mehr.

[jaepen]

Hallo

Danke für die Antworten so weit.
Zunächst einige Infos:
Es hadelt sich um einen non-unique und non-clusterd index und dieser beinhaltet 2 Spalten.
Es ist auch nicht der PK index.

Der Füllfaktor des Index selbst ist garnicht aktiviert (siehe Anhang) . ALso wird wohl ein Default Wert vom MSSQL Server verwendet.???
Wie hoch ist dieser?

Zu der Historie:
Es kommen immer nur neue Einträge hinzu. Gelöscht werden keine, aber es werden auch viele Updates ausgeführt.

Einige Fragen zu Eurer Diskussion:
"Je schneller also in eine indizierte (fillfactor 100) Tabelle geschrieben wird, desto schneller fragmentiert sie."
index.JPG
- Auf was bezieht sich der Füllfaktor? Die Pages der Tabelle oder des Index?
- Was ist der Unterschie genau zwischen logischer und physikalischer Fragmentierung? Physikalisch sind die eigentlichen Records geordnet nach dem PK? Logisch die Records des Index?

Vielen Danl nochmals für die Hilfe!

Gruß Jaepen

[Ralf Jansen]

Ich glaube, ich verstehe diese Nachfrage nicht.

Ich wollte nur sichergehen das auch wirklich die von der Datenbank gemeldete Fragementierung des Indexes gemeint ist. Und nicht zum Beispiel die, seines möglicherweises speziellen File/Filegruppe für diesen Index, im Dateisystem. Ich bin schon DB Admins begegnet die versucht haben die prinzipbedingte Fragementierung in ihrem SAN zu korrigieren

Edit:

Was ist der Unterschie genau zwischen logischer und physikalischer Fragmentierung? Physikalisch sind die eigentlichen Records geordnet nach dem PK? Logisch die Records des Index?

Sorry für die gestiftete Verwirrung. Ich wollte nur wissen wo du den Wert der Fragmentierung entnommen hast der DB oder dem Filesystem. Bei der Frage nach ~physikalisch~ ging es also nur darum ob du vielleicht noch über die zusätzliche Abstraktion eines speziellen Filesystems stolperst. Deiner Antwort entnehme ich mal das dem nicht so ist

[Ralf Jansen]

- Auf was bezieht sich der Füllfaktor? Die Pages der Tabelle oder des Index?

Tabellen und Indizes haben jeweils ihren eigenen individuellen Fillfaktor. Der Default bei Indizes ist 0 (entspricht 100%).

Ob es tatsächlich hilft den Füllfaktor zu reduzieren, also das Problem das neue Pages die im Index entstehen hinten angehängt werden und somit die Pages fragmentieren dadurch zu ersetzen das ich eine ~inner~ Fragementierung vornehmen und von vornherein die Daten auf mehr Pages verteile und dadurch wenn ich mehrere Daten abrufe auch mehr Pages laden muss ersetzte ist denke ich Contextabhängig. Also z.B. wie gut der Index durch die Maschine im Speicher gecached werden kann, wie sich gesuchte Daten über den Index verteilen, ob alle gesuchten Daten schon im Index stehen oder danach noch ein Tabellenzugriff notwendig ist etc.

Oder kurz gesagt wenn möglich praxisnah ausprobieren

[jaepen]

Hmm... Ja, das könnte eventuell helfen. Eventuell könnte es aber auch schlechter werden?
Was ich nur seltsam finde, in der alten Tabelle (vor dem löschen) war der Füllfaktor auch 100%. Es gab jedoch keine Probleme obwohl ca die 20fache Datenmenge in der Tabelle enthalten war.

Gruß

[defo]

Hmm... Ja, das könnte eventuell helfen. Eventuell könnte es aber auch schlechter werden?
Was ich nur seltsam finde, in der alten Tabelle (vor dem löschen) war der Füllfaktor auch 100%. Es gab jedoch keine Probleme obwohl ca die 20fache Datenmenge in der Tabelle enthalten war.

Gruß

Ich würde mit meinem Halbwissen an der Stelle mal sagen, dass die Entartung des Index mit der Größe abnimmt und die Wirksamkeit zunimmt....

[Ralf Jansen]

Wenn da schon 20mal mehr (aufgeräumte Daten) drin waren sollte sich der Fragementierungsgad auch nicht so schnell ändern wie wenn du die selbe Menge neue Daten in eine leere Tabelle wirfst

Was sind eigentlich die Probleme genau? Der Fragementierungsgrad ist ja nicht per se schlecht sondern vielleicht nur ein Symptom (zum Beispiel von obigem Effekt) und das Problem liegt woanders.

[jaepen]

Zum Problem:
Ich habe eine Applikation (für mich eine Black Box) die in dieser Tabelle mit diesem Index etwas sucht.
Irgendwann (nach einigen Tagen) bekommt die Applikation einen Timeout. Mit dem Profiler habe ich gesehen das diese Suche lange dauert.
Erstelle ich den Index neu, ist das Problem weg..... bis in einigen Tagen...

Das seltsame, das ist seit ich diese Tabelle gelöscht und neu angelegt habe....

[Ralf Jansen]

Der Tabelle und der Index liegen an der gleichen Stelle wie vorher? Also zusammen und am selben Ort wie vorher?

Was man prüfen könnte
a.) Wenn du noch das Backup hast kannst du für beide Tabellen (für einen Vorher-Nachher Vergleich) mal aus dem Mangement Studio die Tabelle als Script erstellen. Wenn du das Kontextmenü der Tabelle im Object Explorer öffnest solltest du den Punkt 'Script für Tabelle als..' finden. Die sollten am genauesten vergleichbar sein.
b.) Wenn du eh schon den Profiler verwendest könnte es helfen sich dort auch den Explain Plan anzeigen zu lassen. Vielleicht ändert der sich zu dem Zeitpunkt ab dem es langsam wird (Wenn das langsamer werden plötzlich passiert und nicht schleichend)

[jaepen]

Hallo

Zu a.
Genau so habe ich die Tabelle erzeugt. Problem: Indexe und Statistiken müssen manuell angelegt werden.
zu b.
Wo finde ich den Explain Plan?

Gruß

[Ralf Jansen]

Einer von diesen Events sollte es sein. Vermutlich ist 'Showplan Statistics Profile' am geeignetsten.