Kriterien für Index Fragmentierung - avg_framentation_in_percent vs. avg_fragment_size_in_pages

Question

Hallo zusammen,

ich in gerade dabei unsere Database Maintenance etwas auszubauen. Wir sind jüngst auf eine Fasttrack Referenzarchitektur umgestiegen und dort wird empfohlen auf Basis der avg_fragment_size_in_pages einen Index Rebuild durchzuführen (natürlich nur bei entsprechend großen Indizes). Ziel ist ein möglichst hoher Wert, bestenfalls >400.

Alle "fertigen" Skripte, die ich für die Index Defragmentierung gefunden habe (z.B. Ola Hallengrens SQL Server Maintenance Solution) setzen aber auf die Kennzahl avg_framentation_in_percent. Ich habe gelesen, dass die beiden Kennzahlen umgekehrt proportional zueinander sind, was man auch sehen kann, wenn ich z.B. einen Rebuild durchführe, sinkt die Prozentzahl, die Fragment Size steigt. Soweit so gut.

Jetzt habe ich aber Tabellen (größer als 2 GB), die eine niedrige prozentuale Fragmentierung aufweisen, gleichzeitig aber auch eine niedrige Fragmentgröße. Ein Beispiel:

Der Clustered Index einer meiner Tabellen hat eine avg_framentation_in_percent von 1.8% und eine avg_fragment_size_in_pages von 9 (fragment count=36496, page count=352801, Indexgröße ist 2756MB.

Diese Indizes werden von den Skripten standardmäßig nicht erfasst, weil diese lediglich die prozentuale Fragmentierung berücksichtigen, die ja aber sehr niedrig ist. Nach Fasttrack-Empfehlung müsste ich sie aber defragmentieren. Nach einem Rebuild dieses Index bekomme ich avg_framentation_in_percent=0.1% und avg_fragment_size_in_pages=223. Das ist ja auch das, was ich erreichen möchte.

Meine Fragen sind also: Sollte ich (vor allem in Hinblick auf die Fasttrack-Architektur) die Index-Defragmentierung auf Basis der Fragment Size machen, oder ist eine niedrige Fragment Size bei gleichzeitig niedriger prozentualer Fragmentierung in Ordnung? Kann mir jemand den Zusammenhang zwischen den beiden Kennzahlen erklären?

Vielen Dank und besten Gruß,

Boris

Answer 1

...

 Ich habe gelesen, dass die beiden Kennzahlen umgekehrt proportional zueinander sind, was man auch sehen kann, wenn ich z.B. einen Rebuild durchführe, sinkt die Prozentzahl, die Fragment Size steigt.

Meine Fragen sind also: Sollte ich (vor allem in Hinblick auf die Fasttrack-Architektur) die Index-Defragmentierung auf Basis der Fragment Size machen, oder ist eine niedrige Fragment Size bei gleichzeitig niedriger prozentualer Fragmentierung in Ordnung? Kann mir jemand den Zusammenhang zwischen den beiden Kennzahlen erklären?

...

1. Jein. Also meistens ist das so, aber nicht linear und zwingend.

2. Die avg_fragment_size_in_pages sagt ja aus, wie viele zusammenhängende (contiguous) Pages/Seiten in einem Fragment auf einem bestimmten Index-Level liegen. (http://msdn.microsoft.com/en-us/library/ms188917(v=sql.110).aspx)

Mehr Seiten „am Stück“ sind also natürlich besser. Gerade wenn man die Enterprise-Edition verwendet, kann man dort wunderbar von dem Read-Ahead profitieren, der so richtig Performance bringt (!). Hier ist das Minimum 64 Pages (also ein Extent/Block), ansonsten kickt Read-Ahead nicht ein.

Um also Deine Frage zu beantworten: Ja, eine Optimierung der Fragment-Size macht für Datawarehouse absolut Sinn. Die Fragmentierung als Grenze alleine hilft da nicht unbedingt, wenn man das System ausreizen möchte – und das ist ja der Sinn von FastTrack.:-)

Um diese Werte zu erreichen, sollte man sich mal ansehen, ob man es sich (zeitlich) leisten kann, die Indexe mit verringerter bzw. am besten gar keiner Parallelisierung neu aufzubauen/zu defragmentieren. In aller Regel sollte das kein Problem sein, und die Fragment-Sizes werden deutlich höher liegen (!).

Also in etwa so:

ALTER INDEX [PKCL_SalesOrderHeader_Big_SalesOrderID] ON [Sales].[SalesOrderHeader_Big]
REBUILD PARTITION
= ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE =
OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON

, MAXDOP=1)

GO

  

---

Andreas Wolter | Microsoft Certified Master SQL Server

Blog: www.insidesql.org/blogs/andreaswolter
Web: www.andreas-wolter.com | www.SarpedonQualityLab.com

Answer 2

Hallo Andreas,

danke für deine Antwort. So hatte ich mir das gedacht. Ich teste gerade wieviel Zeit das System mit MAXDOP=1 benötigt. Ich mache zusätzlich noch den Sort in der TempDB.

Was ich noch nicht ganz verstehe ist ehrlich gesagt der Zusammenhang der beiden Kennzahlen avg_framentation_in_percent und avg_fragment_size_in_pages.

Zitat von http://technet.microsoft.com/en-us/library/ms188917.aspx:
"This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page."

Meine Schlussfolgerung wäre eigentlich,  dass, wenn ich 36496 Fragmente bei 352801 Pages habe (s.Bsp.o.), müsste meine Fragmentierung mindestens 10% (=36496/352801, weil ja mindestens 36496 Pages nicht contigous sind) betragen, wofür dann ja auch die Fragment Size von 9 spricht. Die Fragmentierung meiner Tabelle wurde aber mit 1,8% ausgewiesen.

Ich werde aber jetzt mal zusehen, dass ich die Auswahlregeln entsprechend anpasse.

Besten Gruß,

Boris