Das folgende Szenario basiert auf einer Anfrage in den Microsoftforen, in der die Frage in den Raum gestellt wurde, ob die Verwendung von FILLFACTOR für einen non clustered index, der als Optimierung für eine Fremdschlüsselbeziehung erstellt wurde, effektiv ist und Abfragen beschleunigt. Die Standardaussage: “It depends” gilt nicht, wenn der Clustered Key fortlaufend ist. Der nachfolgende Artikel verdeutlicht die Problematik im Detail.

Testumgebung

Um den Sachverhalt besser zu demonstrieren und die Details zu beschreiben, wird das folgende Datenmodell verwendet.

 1: -- reference table

 2: CREATE TABLE dbo.tbl_lookup

 3: (

 4:     Id    tinyint    NOT NULL    identity (1,1),

 5:     c1    char(20)   NOT NULL    DEFAULT ('just stuff'),

 6:  

 7:     CONSTRAINT pk_lookup_Id PRIMARY KEY CLUSTERED (Id)

 8: );

 9: GO

 10:  

 11: CREATE TABLE dbo.tbl_child

 12: (

 13:     Id    int        NOT NULL    IDENTITY (1, 1),

 14:     fk    tinyint    NOT NULL,

 15:     c1    char(200)  NOT NULL    DEFAULT ('happy stuff'),

 16:  

 17:     -- Primary key clustered

 18:     CONSTRAINT pk_child_Id PRIMARY KEY CLUSTERED (Id),

 19:     CONSTRAINT fk_lookup_Id FOREIGN KEY (fk)

 20:     REFERENCES dbo.tbl_lookup (Id)

 21: );

 22: GO

Im Datenmodell wird eine Referenztabelle [dbo].[tbl_lookup] verwendet, um sie als Fremdschlüssel in der Prozesstabelle [dbo].[tbl_child] zu verwenden. Beide Tabellen besitzen einen Clustered Index, der auf dem Attribut [Id] liegt.

Obwohl eine Fremdschlüsselbeziehung erstellt wird (Zeile 19), legt Microsoft SQL Server nicht automatisch einen Index an (wie es z. B. Microsoft Access macht). Das tatsächlich nur die Clustered Indexe existieren, zeigt eine Abfrage auf die in den Tabellen vorhandenen Indexe.

 1: SELECT  OBJECT_NAME(i.object_id)        AS    table_name,

 2:         i.name,

 3:         i.type_desc

 4: FROM    sys.indexes i

 5: WHERE   i.object_id IN (OBJECT_ID('dbo.tbl_lookup', 'U'), OBJECT_ID('dbo.tbl_child', 'U'));

Um die Ausführung von Abfragen auf das Attribut [fk] zu optimieren, wird ein zusätzlicher Index für die Tabelle [dbo].[tbl_Child] angelegt, der folgende Struktur besitzt:

 1: CREATE NONCLUSTERED INDEX ix_tbl_child_fk ON dbo.tbl_Child (fk);

 2: GO

Testdaten für beide Tabellen werden mi dem folgenden Script erstellt. Hierbei werden 10 Einträge in die Referenztabelle sowie 10.000 Datensätze in die Prozesstabelle eingetragen.

 1: INSERT INTO dbo.tbl_lookup DEFAULT VALUES;

 2: GO 10

 3:  

 4: DECLARE    @i int = 1;

 5: WHILE @i <= 10000

 6: BEGIN

 7:     INSERT INTO dbo.tbl_child (fk)

 8:     VALUES (@i % 10 + 1)

 9:  

 10:     SET @i += 1

 11: END

Analyse der Indexe

Die anschließende Analyse der Indexe für das Objekt [dbo].[tbl_child] zeigt deutlich, dass der Clustered Index so gut wie keine Fragmente aufweist. Dieses Ergebnis war zu erwarten, da die Schlüsselwerte für die Tabelle fortlaufend eingetragen werden (IDENTITY). Anders hingegen sieht es beim Index für den Fremdschlüssel aus. Betrachtet man den obigen Workload, ist auffällig, dass mit jeden INSERT-Vorgang der Fremdschlüsselwert um 1 erhöht wurde jedoch nach dem Eintrag des Wertes 10 wieder bei 1 beginnt. Dieses rotierende Verfahren führt im Ergebnis zu einem hohen Fragmentierungswert sowie einer geringen Datendichte pro Seite.

 1: SELECT i.name,

 2:        i.type_desc,

 3:        ps.page_count,

 4:        ps.record_count,

 5:        ps.avg_page_space_used_in_percent

 6: FROM   sys.indexes i CROSS APPLY sys.dm_db_index_physical_stats(db_id(), i.object_id, i.index_id, NULL, 'DETAILED') ps

 7: WHERE  i.object_id  = OBJECT_ID('dbo.tbl_child', 'U') AND

 8:        ps.index_level = 0;

Verwendung von FILLFACTOR?

Abhängig von der Art der Workloads wurde vorgeschlagen, für den Index [ix_tbl_child_fk] einen FILLFACTOR einzusetzen. Die Möglichkeit der Verwendung von FILLFACTOR ist jedoch nur bedingt wirksam, führt in bestimmten Szenarien sogar eher zu Nachteilen wie die Analyse der Daten zeigt. Die nächste Abfrage zeigt mit Hilfe von sys.fn_PhysLocFormatter, in welcher physikalischen Datenseite sich der Inhalt befindet, nachdem der Index mit einem FILLFACTOR von 80 neu aufgebaut wurde.

 1: ALTER INDEX ix_tbl_child_fk ON dbo.tbl_child REBUILD WITH (FILLFACTOR = 80);

 2:  

 3: SELECT  sys.fn_PhysLocFormatter(%%physloc%%) AS Location,

 4:         *

 5: FROM    dbo.tbl_child WITH (INDEX = 2)

 6: WHERE   fk = 5;

Die Abbildung zeigt, dass sich 588 Datensätze des Index auf der Datenseite 3144 befinden und anschließend die Daten des Index auf Datenseite 3145 weiter geführt werden. Im vorliegenden Beispiel verteilen sich die Daten für den Indexwert = 5 wie folgt auf die Datenseiten:

In der obigen Abbildung zeigt sich die Datenverteilung bei 1.000 Datensätzen mit dem FK = 5 so, dass sowohl auf Seite 3143 als auch auf Seite 3145 angrenzende Indexwerte (FK = 4 und FK = 6) möglich sind während die Datenseite 3144 vollständig mit dem Indexwert FK = 5 belegt ist.

Das Attribut [ID] in der Tabelle [dbo].{tbl_child] besitzt gemäß Definition die IDENTITY-Eigenschaft. Damit geht einher, dass die Werte des Clustered Keys fortlaufend weiter gezählt wird. Wird beim aktuellen Datenbestand (10.000 Datensätze) ein weiterer Datensatz hinzugefügt werden, wird der Clustered Key bei 10.0001 weitergeführt.

Auf Datenseite 3143 könnten noch weitere Datensätze mit FK = 4 aufgenommen werden. Auf Datenseite 3145 wäre die Speicherung von weiteren Datensätzen mit FK = 5 oder aber FK = 6 möglich. Obwohl auf Seite 3144 noch 20% Datenvolumen zur Verfügung stehen, ist eine weitere Befüllung nicht mehr möglich, da der nächste Datensatz mit FK = 5 aus der Kombination FK = 5 und ID = 10001 besteht. Die mittels FILLFACTOR zur Verfügung gestellten Datenvolumina können also teilweise nicht mehr verwendet werden. Lediglich an den Rändern des Indexwertes bilden sich Hotspots.

Fazit

Die Option FILLFACTOR ist eine ideale Möglichkeit, für OLTP-Systeme die Workloads zu optimieren. Dennoch sollte bei der Verwendung von FILLFACTOR sorgfältig neben dem Workload auch die Struktur des Index berücksichtigt werden. Insbesondere, wie das obige Beispiel gezeigt hat, ist auf die Verteilung der Daten zu achten, wenn FILLFACTOR in die Überlegung mit einbezogen werden soll. Bei falscher Anwendung werden ansonsten freie Bereiche geschaffen, die ungenutzt bleiben, da die Struktur des Index eine Befüllung nicht mehr zulässt.

Weitere vertiefende Informationen zur Anwendung von FILLFACTOR wurden im Artikel “FILLFACTOR – Vor- und Nachteile” veröffentlicht.

Herzlichen Dank fürs Lesen!