5
SQL Server Index Fraqmentierung
Hallo zusammen,
ich hatte bei einer meiner Anwendungen die auf eine Datenbank kürzlich festgestellt dass diese sporadisch bei Abfragen in einen Timeout geht (>30s keine Antwort)
Nach einem Rebuild und Reorg (beides durchgeführt, sicher ist sicher
) antwortet die Abfrage wieder.
Die Abfrage besteht aus ein paar Subselects und Joins.
Anschließend habe ich mir mal die Index Fragmentierung angesehen. Hier habe ich bei den Indexen der relevanten Tabellen Werte > 80% und > 90%.
Nach einem Reorg/Rebuild sinken diese Werte kurzzeitig auf <30.
Im Hintergrund werden die Tabellen aber weiterhin mit Inserts und Updates befeuert.
Nach kurzer Zeit liegt die Fragmentierung wieder bei den Werten > 80.
Dieses Phänomen ist unabhängig von der Datenbank Version. Hab ich bei 2008, 2012 und 2014 feststellen können.
Jetzt aber zu den eigentlichen Fragen:
Was kann man machen um die Fragmentierung der Indexe nicht so schnell ansteigen zu lassen?
Gibt es Best Practices um dies zu vermeiden?
Kann man in SQL Server Tabellen defragmentieren?
Wie oft sollte man Rebuilds/Reorgs laufen lassen?
Vielen Dank
Gruß
Martin
ich hatte bei einer meiner Anwendungen die auf eine Datenbank kürzlich festgestellt dass diese sporadisch bei Abfragen in einen Timeout geht (>30s keine Antwort)
Nach einem Rebuild und Reorg (beides durchgeführt, sicher ist sicher
) antwortet die Abfrage wieder.Die Abfrage besteht aus ein paar Subselects und Joins.
Anschließend habe ich mir mal die Index Fragmentierung angesehen. Hier habe ich bei den Indexen der relevanten Tabellen Werte > 80% und > 90%.
Nach einem Reorg/Rebuild sinken diese Werte kurzzeitig auf <30.
Im Hintergrund werden die Tabellen aber weiterhin mit Inserts und Updates befeuert.
Nach kurzer Zeit liegt die Fragmentierung wieder bei den Werten > 80.
Dieses Phänomen ist unabhängig von der Datenbank Version. Hab ich bei 2008, 2012 und 2014 feststellen können.
Jetzt aber zu den eigentlichen Fragen:
Was kann man machen um die Fragmentierung der Indexe nicht so schnell ansteigen zu lassen?
Gibt es Best Practices um dies zu vermeiden?
Kann man in SQL Server Tabellen defragmentieren?
Wie oft sollte man Rebuilds/Reorgs laufen lassen?
Vielen Dank
Gruß
Martin
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 399843
Url: https://administrator.de/contentid/399843
Printed on: May 4, 2024 at 01:05 o'clock
5 Comments
Latest comment
hi
Dazu muss man aber wissen wie Clustered und Non-Clustered Indexe so ticken. Was für ein Typ ist dein Index?
Zitat von @martinlentzsch:
Was kann man machen um die Fragmentierung der Indexe nicht so schnell ansteigen zu lassen?
Gibt es Best Practices um dies zu vermeiden?
Die Indexe nicht beschissen setzen Was kann man machen um die Fragmentierung der Indexe nicht so schnell ansteigen zu lassen?
Gibt es Best Practices um dies zu vermeiden?
Dazu muss man aber wissen wie Clustered und Non-Clustered Indexe so ticken. Was für ein Typ ist dein Index?Kann man in SQL Server Tabellen defragmentieren?
Nein.Wie oft sollte man Rebuilds/Reorgs laufen lassen?
idR sagt man bei Indexen die >30% Fragmentiert sind. Besser ist allerdings, die Indexe so zu gestalten, das dies erst gar nicht passiert
Hallo Martin,
ich lasse auf unserem DB-Server täglich die Indizies je nach Fragmentierung neuorganisieren oder neuerstellen.
Am besten sind hier die Skripts von OLA Hallengren:
https://ola.hallengren.com/
da solltest du eigentlich alles finden ;)
ich lasse auf unserem DB-Server täglich die Indizies je nach Fragmentierung neuorganisieren oder neuerstellen.
Am besten sind hier die Skripts von OLA Hallengren:
https://ola.hallengren.com/
da solltest du eigentlich alles finden ;)
OK, da ich das Datenbank Konstrukt nicht erfunden, sondern so übernommen habe sag ich mal dass die Indexe beschissen gesetzt sind
Es handelt sich u.a. um den PRIMARY KEY der CLUSTERED ist und um eigene die NONCLUSTERED sind.
Seltsamerweise defragmentiert sich der Index auf dem PRIMARY obwohl keine INSERTS auf der Tabelle vorgenommen werden und der Wert in der Spalte somit definitiv auch nicht verändert.
Ich werde mich mal in die Problematik CLUSTERED / NONCLUSTERED einlesen.
Danke erstmal
Es handelt sich u.a. um den PRIMARY KEY der CLUSTERED ist und um eigene die NONCLUSTERED sind.
Seltsamerweise defragmentiert sich der Index auf dem PRIMARY obwohl keine INSERTS auf der Tabelle vorgenommen werden und der Wert in der Spalte somit definitiv auch nicht verändert.
Ich werde mich mal in die Problematik CLUSTERED / NONCLUSTERED einlesen.
Danke erstmal
Ist ein halbwegs komplexes Thema, deshalb die Grobstfassung:
Clustered Indexe steuern die physische sortierung auf der Platte. Wenn der Clustered also auf einem Text liegt und dieser Text geändert wird, dann würde der Eintrag physisch wandern.
Ein Clustered Index hängt bei den Non-Clustered immer als Daten an.
Ändert sich was an diesen Feldern des Clustered Indexes, müssen also auch immer die Daten der Non-Clustered Indexe angefasst werden.
Deshalb kann man frei raus sagen: Ein Clustered Index hängt auf der Unique ID. Sonst nirgends. Der Clustered Index sollte immer nur auf EINEM Feld liegen und dieses Feld sollte immer ein Integer sein. Dieses Feld sollte sich nach dem Anlegen auch niemals ändern. Kurz: Clustered Indexe NUR auf einem (Autoincrement) ID\GUID Feld!
Ist der Index so gesetzt, das sich die Daten nicht oder nur selten ändern, dann setzt du den Fillfactor auf 100%
Ändern sich die Daten eines Nonclustered Indexes ab und zu, dann (kommt immer auf den Einzelfall an, aber so als Grobe Regel) setzt du den Fillfactor auf 90-95%, bei häufigen Änderungen auf maximal 70%.
Dann sollte das gröbste bei der Indexfragmentierung erledigt sein.
Wie gesagt: Das mit Abstand wichtigste ist ein sauberer Clustered index!
Wenn Nonclustered fragmentieren, sollte man gucken ob denn wirklich alle Felder im index nötig sind, besonders wenn es Texte sind.
Wenn sich dessen Daten dann noch ändern, spieöt man etwas mit dem Fillfactor, das genug Platz für den index vorgehalten wird. Hier aber sehr Feinfühlig agieren, da ein Fillfactor zu deutlich mehr IO führen kann
Clustered Indexe steuern die physische sortierung auf der Platte. Wenn der Clustered also auf einem Text liegt und dieser Text geändert wird, dann würde der Eintrag physisch wandern.
Ein Clustered Index hängt bei den Non-Clustered immer als Daten an.
Ändert sich was an diesen Feldern des Clustered Indexes, müssen also auch immer die Daten der Non-Clustered Indexe angefasst werden.
Deshalb kann man frei raus sagen: Ein Clustered Index hängt auf der Unique ID. Sonst nirgends. Der Clustered Index sollte immer nur auf EINEM Feld liegen und dieses Feld sollte immer ein Integer sein. Dieses Feld sollte sich nach dem Anlegen auch niemals ändern. Kurz: Clustered Indexe NUR auf einem (Autoincrement) ID\GUID Feld!
Ist der Index so gesetzt, das sich die Daten nicht oder nur selten ändern, dann setzt du den Fillfactor auf 100%
Ändern sich die Daten eines Nonclustered Indexes ab und zu, dann (kommt immer auf den Einzelfall an, aber so als Grobe Regel) setzt du den Fillfactor auf 90-95%, bei häufigen Änderungen auf maximal 70%.
Dann sollte das gröbste bei der Indexfragmentierung erledigt sein.
Wie gesagt: Das mit Abstand wichtigste ist ein sauberer Clustered index!
Wenn Nonclustered fragmentieren, sollte man gucken ob denn wirklich alle Felder im index nötig sind, besonders wenn es Texte sind.
Wenn sich dessen Daten dann noch ändern, spieöt man etwas mit dem Fillfactor, das genug Platz für den index vorgehalten wird. Hier aber sehr Feinfühlig agieren, da ein Fillfactor zu deutlich mehr IO führen kann
Vielen Dank erstmal.
Unglücklicherweise ist der Clustered Index auf dem ID Feld (PK) welches eine Nummer ist, die zwar nicht als Autoincrement gesetzt ist, aber dafür gesorgt wird dass sie nicht verändert wird und unique ist.
Alle Indexe sind auf Fill Factor 0 (was ja wohl identisch zu 100 ist) auch der Index auf dem PK. Sollte sich dann SQL Server automatisch drum kümmern. Aber unter anderem bei dem PK Index zu der Tabelle ist der Füllgrad bei 35-40 und die Fragmentierung auf gut 92.
Die ganzen anderen Unclustered sind notwendig da sie von anderen Abfragen verwendet werden die einigermaßen zeitkritisch sind.
Leider ist der gesamte Quellcode der auf die Datenbank greift um die 300.000 Zeilen groß. Vielleicht hab ich da was übersehen. Gibt viel Optimierungsbedarf dort....
danke
Gruß
Martin
Unglücklicherweise ist der Clustered Index auf dem ID Feld (PK) welches eine Nummer ist, die zwar nicht als Autoincrement gesetzt ist, aber dafür gesorgt wird dass sie nicht verändert wird und unique ist.
Alle Indexe sind auf Fill Factor 0 (was ja wohl identisch zu 100 ist) auch der Index auf dem PK. Sollte sich dann SQL Server automatisch drum kümmern. Aber unter anderem bei dem PK Index zu der Tabelle ist der Füllgrad bei 35-40 und die Fragmentierung auf gut 92.
Die ganzen anderen Unclustered sind notwendig da sie von anderen Abfragen verwendet werden die einigermaßen zeitkritisch sind.
Leider ist der gesamte Quellcode der auf die Datenbank greift um die 300.000 Zeilen groß. Vielleicht hab ich da was übersehen. Gibt viel Optimierungsbedarf dort....
danke
Gruß
Martin
