none
Abfrage nach Index Rebuild extrem langsam, vorher 2 Sec, jetzt 4 Min RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo Zusammen,

    ich habe eine 1,4 TB große DB  auf einem SQL 2017 Standard CU17 laufen. Die Daten bestehen nur aus "Text", keine Bilder oder Ähnliches. 

    Problem:

    Bevor ich ein Index Rebuild gemacht habe, hat meine Standard Procedure ca. 2 Sekunden gedauert. Nach dem Index Rebuild dauert die gleiche Procedure jetzt 4 - 6 Minuten (abhängig vom DB Level). Dazu kommt, dass die Indizes jetzt deutlich größer geworden sind (DB vorher ca 830 GB, jetzt 1,4 TB, Index 59 % Speicherplatz).

    Die schnellste Zeit erreiche ich mit einem DB Level von 110 (SQL 2012), dort komme ich auf 2 Min. Allerdings immer noch meilenweit von dem alten Wert entfernt ... .

    Zu erwähnen wäre noch, das mehrere Rebuild Vorgänge abgebrochen wurden, da die Platte vollgelaufen ist. Der letzte Lauf war allerdings erfolgreich.

    Statistiken wurden auch aktualisiert.

    Der Ablaufplan zeigt, dass ein  Clustered Index Scans ausgeführt wird (ca 6 Mio Zeilen) mit ca 125 Sec "Tatsächlich verstrichener Zeit.

    Auch nach mehreren Stunden Suche und lesen von vielen Artikeln finde ich keine Lösung.

    Hat jemand eine Idee?

    Vielen Dank.

    Grüße JP

    www.systemcenterblog.de



    • Bearbeitet JPietsch Samstag, 7. Dezember 2019 17:34
    Samstag, 7. Dezember 2019 17:31
    |

Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Zusammen,

    ich habe den Fehler gefunden, bzw. das Problem gelöst:

    Schuld war die Standardeinstellung des Wartungstask im SQL Management Studio. Wenn dort kein eigener Füllfaktor definiert wird, steht dieser auf 1. Das ist keine "Sondereinstellung" oder Ähnliches, sondern bedeutet tatsächlich 99 % freien, reservierten Speicher.... . Die Einstellung konnte ich aufgrund des Fehlerprotokolls finden:

    USE [XYZ]
    GO
    ALTER INDEX [idx_BLaBLa] ON [dbo].[tbl_BlaBlub] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 1)


    Dadurch dauert der Indexscan extrem lange und die DB wird unverhältnismäßig groß (wurde anscheinend vorher schon mal so ausgeführt, dann sind weitere Indizes dazu gekommen).

    Ein Index Rebuild über alle Indizes der DB mit momentanem Füllfaktor von 100 % (für das verkleinern des Index) hat zwei Effekte gehabt, Reduktion und Beschleunigung des Index und damit eine Verkleinerung der DB von 1,4 TB auf 160 GB (nach Shrink mit 20 % Puffer).

    Was eine falsche Standardeinstellung alles bewirken kann. Hoffentlich liest jemand von MS das.

    PS: Die DB hatte ich so übernommen!

    Schöne Grüße

    JP

    www.systemcenterblog.de

    • Als Antwort markiert JPietsch Sonntag, 8. Dezember 2019 18:53
    Sonntag, 8. Dezember 2019 18:53
    |

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hallo Zusammen,

    ich habe den Fehler gefunden, bzw. das Problem gelöst:

    Schuld war die Standardeinstellung des Wartungstask im SQL Management Studio. Wenn dort kein eigener Füllfaktor definiert wird, steht dieser auf 1. Das ist keine "Sondereinstellung" oder Ähnliches, sondern bedeutet tatsächlich 99 % freien, reservierten Speicher.... . Die Einstellung konnte ich aufgrund des Fehlerprotokolls finden:

    USE [XYZ]
    GO
    ALTER INDEX [idx_BLaBLa] ON [dbo].[tbl_BlaBlub] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 1)


    Dadurch dauert der Indexscan extrem lange und die DB wird unverhältnismäßig groß (wurde anscheinend vorher schon mal so ausgeführt, dann sind weitere Indizes dazu gekommen).

    Ein Index Rebuild über alle Indizes der DB mit momentanem Füllfaktor von 100 % (für das verkleinern des Index) hat zwei Effekte gehabt, Reduktion und Beschleunigung des Index und damit eine Verkleinerung der DB von 1,4 TB auf 160 GB (nach Shrink mit 20 % Puffer).

    Was eine falsche Standardeinstellung alles bewirken kann. Hoffentlich liest jemand von MS das.

    PS: Die DB hatte ich so übernommen!

    Schöne Grüße

    JP

    www.systemcenterblog.de

    • Als Antwort markiert JPietsch Sonntag, 8. Dezember 2019 18:53
    Sonntag, 8. Dezember 2019 18:53
    |
  • Question
    Anmelden
    0
    Anmelden

    Da scheint aber jemand was vollkommen misverstanden zu haben:
    https://docs.microsoft.com/de-de/sql/relational-databases/indexes/specify-fill-factor-for-an-index?view=sql-server-ver15

    Der Standardwert ist 0!
    D.h., dass ein Indexblock zu 100% gefüllt wird.

    Interessant ist dies eigentlich nur bei Indizes, bei denen Daten eingefügt werden müssen und da dann eben bei Clustered-Indizes, da hier die Datenebene bereits eine Indexebene ist.
    Wenn aber z.B. wegen Identity-Columns Inserts nur am Ende stattfinden, lohnt sich ein freihalten für Inserts überhaupt nicht. Delete und Update schafft indirekt wieder Freiräume, da ein Update via Delete/Insert erledigt wird.

    Eine pauschale Freihaltung bei großen Tabellen, die u.a. eben mehr unveränderbare historische Daten enthalten ist da auch eher Platzverschwendung und performancenegativ.

    Montag, 9. Dezember 2019 09:26
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo,

    die Funktion des Füllfaktors habe ich schon verstanden.

    Den Artikel hatte ich auch gelesen und auch verstanden!

    Deswegen war ich so erstaunt, dass der Füllfaktor auf 1 gesetzt war. Dies habe ich nicht explizit angegeben und bin auch von 0 ausgegangen.

    Gibt es eine Möglichkeit sich den Code des Wartungsplans anzuschauen? Dies wird als SSIS Paket abgelegt, keine Ahnung wo. Wenn mann ansonsten auf die Eigenschaften klickt gibt es noch einen Button T-SQL, dieser bringt allerdings eine Warnung und erzeugt kein Script.

    Schöne Grüße

    JP

    www.systemcenterblog.de

    Montag, 9. Dezember 2019 09:41
    |