Dienstag, 23. April 2013

Verhalten von Non Clustered Indexes bei einem REBUILD eines Clustered Index

Bei der Durchsicht von Datenbank-Wartungsaufträgen in einem SQL Server 2008 R2 ist aufgefallen, dass ein täglich auszuführender Job ausschließlich den REBUILD / REORG von Clustered Indexe vornimmt. Auf die Frage, warum nur die Clustered Indexe neu organisiert / neu aufgebaut werden, wurde erwidert, dass dieser Job von einem Dienstleister mit der folgenden Aussage implementiert wurde: “Es müssen nur die Clustered Indexe überprüft und gewartet werden. Wenn ein Clustered Index neu aufgebaut wird, werden alle anderen Indexe, die von diesem Index abhängig sind (Non Clustered Indexe) ebenfalls neu aufgebaut!”. Der nachfolgende Artikel beschäftigt sich mit dieser Aussage und zeigt die Abhängigkeit von Clustered Index und Non Clustered Index. Der Artikel belegt, warum die getroffene Aussage falsch ist.

Wird ein Non Clustered Index tatsächlich neu aufgebaut, wenn ein Clustered Index mit REBUILD oder REORG neu aufgebaut wird? Die Antwort ist kurz und knapp: Nein! Wenn ein Clustered Index neu aufgebaut wird, sind Non Clustered Indexe davon nicht berührt. Warum das so ist, soll der nachfolgende Beitrag erklären. Zunächst wird eine einfache Relation mit insgesamt vier Attributen erstellt. Diese Relation wird anschließend mit Testdaten gefüllt. Obwohl von der Verwendung einer GUID als Clustered Key abzuraten ist (siehe “Idealer Datentyp für Clustered Index – GUID vs. INT”), wird er zu Demonstrationszwecken im ersten Beispiel verwendet, um eine hohe Fragmentierung zu erreichen.

IF OBJECT_ID('dbo.tbl_Test', 'U') IS NOT NULL
    DROP TABLE dbo.tbl_Test
    GO

CREATE TABLE dbo.tbl_Test
(
    Id
      uniqueidentifier    NOT NULL    DEFAULT (newid()),
    Col1   
char(20)            NOT NULL,
    Col2   
char(20)            NOT NULL,
    col3   
char(256)           NOT NULL,

    CONSTRAINT pk_tbl_Test PRIMARY KEY CLUSTERED (Id)
);

-- Erstellung eines unique non clustered Index und eines
-- non unique non clustered Index
CREATE UNIQUE INDEX ix_tbl_Test_col1_col2 ON dbo.tbl_Test (col1, col2);
CREATE INDEX ix_tbl_Test_col3 ON dbo.tbl_Test (col3);
GO

SET NOCOUNT ON
GO

-- 10.000 Datensätze eintragen
DECLARE @Counter int = 1
WHILE @Counter <= 10000
BEGIN
    INSERT INTO dbo.tbl_Test (col1, col2, col3)
    VALUES (
            'Counter: ' + CAST(@Counter AS varchar(5)),
            'RowNo: ' + CAST(@Counter AS varchar(5)),
            'Just stupid stuff'
            )

    SET @Counter += 1
END

Nachdem die Daten in die Relation übertragen wurden, gilt das Augenmerk zunächst dem Status aller Indexe. Interessant ist – zunächst – die Fragmentierung und damit auch die Größe der Indexe.

SELECT  i.name,
        ps.Index_type_desc,
        ps.Index_level,
        ps.avg_fragmentation_in_percent,
        ps.avg_fragment_size_in_pages,
        ps.fragment_count,
        ps.page_count,
        ps.avg_page_space_used_in_percent,
        ps.record_count
FROM    sys.indexes i INNER JOIN sys.dm_db_Index_physical_stats(db_id(), object_id('dbo.tbl_Test'), DEFAULT, DEFAULT, 'DETAILED') ps
        ON (
             i.object_id = ps.object_id AND
             i.Index_id = ps.Index_id
           )

ORDER BY
        ps.Index_id ASC,
        ps.Index_level ASC;

Indexes - physical stats - fragmented

Sehr deutlich ist zu erkennen, dass die Relation – wie zu erwarten – sehr stark fragmentiert ist. Bevor im nächsten Schritt der Index neu erstellt wird, wird die Struktur aller Indexe“unter der Motorhaube” zusätzlich unter die Lupe genommen. Der Clustered Index hat immer die Index_id 1. Die [Id] der beiden zusätzlich angelegten Indexe können über [sys].[indexes] abgefragt werden. Die Struktur selbst kann für jeden Index mit dem folgende T-SQL Befehl abgerufen werden:

SELECT  Index_id,
        page_type,
        page_level,
        page_type_desc,
        allocation_unit_type,
        allocated_page_page_id,
        next_page_page_id,
        previous_page_page_id
FROM    sys.dm_db_database_page_allocations(db_id(), OBJECT_ID('dbo.tbl_Test', 'U'), 1, NULL, 'DETAILED')
ORDER BY
        page_type DESC,
        page_level DESC,
        previous_page_page_id ASC,
        allocated_page_page_id;

Hinweis: Die DMF sys.dm_db_database_page_allocations ist erst ab SQL Server 2012 vorhanden! Ich habe sie für das Beispiel verwendet, da es einfacher ist als mit DBCC IND. Der Workaround für DBCC IND von Kimberly Tripp ist hier beschrieben “Optimierung von Datenbankmodellen – Richtige Wahl von Datentypen und Indexen (2)”. Die nachfolgende Abbildung zeigt die Struktur der Pages bei Ausführung des Befehls für alle drei Indexe ([pk_tbl_Test], [ix_tbl_Test_col1_col2] und [ix_tbl_Test_col3])

Indexes - pages in all three indexes

Nun wird der Clustered Index neu erstellt

ALTER INDEX pk_tbl_test ON dbo.tbl_Test REBUILD;

Führt man die Abfrage erneut aus, zeigt die erneute Analyse der Struktur der Pages für alle 3 Indexe deutlich, dass ausschließlich der Clustered Index selbst neu erstellt wurde. Die Pagezuordnungen der beiden non clustered Indexes haben sich nicht verändert!

Indexes - pages in all three indexes after REBUILD

Die – berechtigte – Frage, die sich hier stellt; warum werden bei der Neuerstellung eines Clustered Index nicht auch die Non Clustered Indexe neu aufgebaut? Schließlich erhält ja der Clustered Index einen komplett neuen Datenbereich (Pages) zugewiesen. Die Antwort ist relativ einfach – in einem Non Clustered Index werden nicht Referenzen zu Pages gespeichert sondern ausschließlich der sogenannte “Row Locator” des Clustered Index. Diese “Beziehung” zueinander soll das nachfolgende Beispiel verdeutlichen.

In der obigen Abbildung – nach dem Neuaufbau – ist zu erkennen, dass der Index [ix_tbl_Test_col1_col2] unter anderem die Page 2320 belegt. Den Inhalt dieser Page kann man wie folgt “sichtbar” machen (Bitte darauf achten, dass der Name der Datenbank als auch die Page entsprechend auf Ihre Umgebung angepasst werden muss):

DBCC TRACEON (3604);
DBCC PAGE ('BJ_UR'
, 1, 2320, 3);

Indexes - Page 2320 - Indexdetails

Der Aufbau eines non clustered Index folgt immer dem gleichen Muster. Die Indexattribute (in diesem Fall [col1] und [col2]) beinhalten die zu durchsuchenden Attribute. JEDER non clustered Index führt IMMER das Schlüsselattribut (Clustered Key) des clustered Index mit (in diesem Fall [Id]). Ist der Clustered Key nicht eindeutig, wird zusätzlich zum Clustered Key noch ein Uniquifier [int] für die Sicherstellung der Eindeutigkeit mit gespeichert. Diese Informationen werden als Row Locator bezeichnet. Da im obigen Beispiel der Clustered Index als UNIQUE Index definiert wurde, ist der Schlüssel selbst eindeutiges Kriterium.

Wichtig für das Verständnis der Fragmentierung ist, dass eben nicht der Verweis auf eine Page des Clustered Index gespeichert wird sondern das Schlüsselattribut des Clustered Index. Somit ist für SQL Server die Reorganisation eines Non Clustered Index nicht notwendig. Der Suchalgorithmus ist vom Neuaufbau vollkommen unabhängig. Als Beispiel soll die folgende Abfrage dienen:

SELECT * FROM dbo.tbl_Test WHERE Col1 = 'Counter: 149' AND Col2 = 'RowNo: 149';

Indexes - ExecutionPlan 1

Der Ausführungsplan für die Abfrage zeigt, dass der Index [ix_tbl_Test_col1_col2] verwendet wurde. Um jedoch Informationen zu [Col3] abrufen zu können, müssen Informationen aus dem Clustered Index selbst gelesen werden [KeyLookup]. Simpel ausgedrückt besagt der obige Ausführungsplan, dass alle Attribute aus dbo.tbl_Test ausgegeben werden sollen; dabei können die Attribute [Id], [col1] und [col2] direkt aus dem Non Clustered Index entnommen werden. Um Informationen zu [Col3] zu erhalten, muss im Clustered Index [pk_tbl_Test] gesucht werden. Hierzu wird das Schlüsselattribut [Id] als Alleinstellungsmerkmal im Clustered Index verwendet.  Um die Werte aus dem Clustered Index zu lesen, muss wieder im Root-Level begonnen werden. Insgesamt benötigt die obige Abfrage im Beispiel 5 IO-Operationen. Betrachtet man die Abfrage noch einmal etwas genauer unter Berücksichtigung des Ausführungsplans, besteht die Abfrage strukturell aus zwei Abfragen!

SELECT Id, Col1, Col2 FROM dbo.tbl_Test WHERE Col1 = 'Counter: 149' AND Col2 = 'RowNo: 149';
SELECT Col3 FROM dbo.tbl_Test WHERE Id = 'C6292A43-82FA-4C03-B1DA-23C3C277A8DA';

Bei der Analyse dieser Abfrage teilen sich die 5 Lesevorgänge wie folgt auf:

tbl_Test-Tabelle. Scananzahl 0, logische Lesevorgänge 2, physische Lesevorgänge 0, ...
tbl_Test-Tabelle. Scananzahl 0, logische Lesevorgänge 3, physische Lesevorgänge 0, ...

Für die Ermittlung der Informationen zu [Id], [Col1] und [Col2] sind zwei Lesevorgänge erforderlich. Anschließend müssen für die fehlende Information zu [Col3] drei weitere Lesevorgänge im Clustered Index durchgeführt werden. Würde ein Non Clustered Index tatsächlich eine Referenz zu einer Page im Leaf-Level speichern, in der sich der Datensatz befindet, so wären keine drei Lesevorgänge notwendig sondern lediglich ein Zugriff auf die Page selbst.

Unter Berücksichtigung dieser Vorgehensweise erklärt sich von selbst, dass der Neuaufbau eines Clustered Index andere – non clustered Indexe – nicht betrifft.

Herzlichen Dank fürs Lesen!

Beschreibung Link
Clustered Index http://msdn.microsoft.com/de-de/library/ms177443(v=sql.105).aspx
Non Clustered Index http://msdn.microsoft.com/de-de/library/ms177484(v=sql.105).aspx
sys.indexes http://msdn.microsoft.com/de-de/library/ms173760.aspx
sys.dm_db_Index_physical_stats() http://msdn.microsof t.com/de-de/library/ms188917.aspx
DBCC TRACE (3604) http://msdn.microsoft.com/de-de/library/ms187329.aspx
DBCC PAGE () http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

Keine Kommentare :

Kommentar veröffentlichen