Mittwoch, 14. November 2012

Optimierung von Datenbankmodellen – Richtige Wahl von Datentypen und Indexen (Teil 4)

Mit diesem Beitrag möchte ich aufzeigen, welche Auswirkungen die richtige Auswahl von Attributen für die Definition eines Indexes hat. Während meiner langjährigen Praxis habe ich immer wieder Datenmodelle gefunden, bei denen auf nahezu jedem Attribut der Relation ein dedizierter Index angewendet wurde. Warum hat sich die Performance eher verschlechtert als verbessert? Diesem Fehler möchte ich auf den Grund gehen und durch Beispiele die Wichtigkeit der sorgfältigen Planung von Indexen aufzeigen.

Wie schon in den drei vorherigen Beiträgen zu dem Thema verwende ich wieder die Relation dbo.tbl_Companies der Beispieldatenbank [Indexing]. Die Struktur der Relation stellt sich nach den bisherigen Optimierungen – ohne Indexe – wie folgt dar:

CREATE TABLE dbo.tbl_Companies
(
    Id         int IDENTITY(1,1) NOT NULL,
    Name       nvarchar(128)     NULL,
    TaxNo      varchar(24)       NULL,
    CostCenter char(7)           NULL,
    UpdateBy   varchar(20)       NULL,

    CONSTRAINT ci_tbl_Companies PRIMARY KEY CLUSTERED (Id)
)

Das Datenmodell besitzt aktuell nur eine Einschränkung [ci_tbl_Companies] als “Clustered Index”. Auf diese Tabelle wird nun die nachfolgende Abfrage ausgeführt und der Ausführungsplan begutachtet:

-- Step 1: Analyse der Tabelle ohne Einschränkungen
SET STATISTICS IO ON;
SET STATISTICS XML ON;
GO

SELECT Id, Name, TaxNo, CostCenter, UpdateBy FROM dbo.tbl_Companies WHERE name = 'db Berater GmbH'
GO

SET STATISTICS XML OFF;
SET STATISTICS IO OFF;

Der Abfrageplan sowie die IO-Analyse zu der obigen Abfrage (sie liefert nur einen Datensatz!) zeigen, welches Problem auftritt.

Abfrageplan - 1
IO – Analyse
tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 35, physische Lesevorgänge 0

Die Analyse zeigt deutlich, dass ein Index Scan durchgeführt wird. Das bedeutet nichts anderes, als dass die Relation komplett durchsucht werden muss. Alle Einträge, die einen Wert ‘db Berater GmbH’ im Attribut [name] besitzen, werden ausgegeben. Das die vollständige Relation durchsucht werden muss, zeigt die IO-Analyse. 35 Lesevorgänge wurden für die vollständige Tabelle ausgeführt. Ein Index-Scan ist immer schlechter als ein Index-Seek, da bei einem Scan die Leaf-Level des Indexes durchsucht werden müssen.

Um die Abfrage zu optimieren, sollte ein Index auf dem Attribut [name] hilfreich sein. Also erstellen wir den Index und schauen uns das Ergebnis der gleichen Abfrage erneut an.

-- Step 2: Erstellen eines Indexes auf dem Attribut [name]
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.tbl_Companies', 'U') AND name = 'ix_tbl_Companies_Name')
    CREATE INDEX ix_tbl_Companies_Name ON dbo.tbl_Companies (Name);
    GO

SET STATISTICS IO ON;
SET STATISTICS XML ON;
GO

SELECT Id, Name, TaxNo, CostCenter, UpdateBy FROM dbo.tbl_Companies WHERE name = 'db Berater GmbH'
GO

SET STATISTICS XML OFF;
SET STATISTICS IO OFF;

Schauen wir uns den Abfrageplan sowie die IO-Statistiken an und wir können feststellen, dass zunächst einmal ein signifikanter Unterschied besteht:

Abfrageplan - 2
IO – Analyse
tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische Lesevorgänge 0

Wie man deutlich erkennen kann, wird nun ein Index-Seek ausgeführt. der den zuvor angelegten Index [ix_tbl_Companies_Name] verwendet. Auch die Lesevorgänge wurden um fast 90% reduziert! Bisher ein sehr gutes Ergebnis, jedoch stört ganz signifikant, dass für die Ausgabe der Daten zusätzlich noch ein KeyLookup (Bookmark Lookup) benötigt wird. Um zu verstehen, warum SQL Server für die Ausführung der Abfrage noch zusätzliche Informationen benötigt, müssen wir einen Blick hinter die Kulissen werfen.

Schauen wir uns zunächst einmal das Ergebnis der Abfrage an.

ResultSet - 1

Der rot umrandete Bereich bildet die Struktur der Attribute ab, die durch den Index [ix_tbl_Companies_Name] abgedeckt werden. Der blau umrandete Bereich bildet die Daten ab, die ausgegeben werden sollen. Es fällt auf, dass die zusätzlichen benötigten Informationen nicht Bestandteil des Indexes [ix_tbl_Companies_Name] sind – sie sind alle Bestandteil des “Clustered Index”. Das bedeutet also für SQL Server, dass nach dem Auffinden der Datensätze, die dem Suchkriterium entsprechen, eine zusätzliche Abfrage auf die Tabelle (Clustered Index) gemacht werden muss, um alle Informationen für die Ausgabe zu sammeln. Interessant hierbei ist die IO-Statistik. Sie besagt, dass insgesamt vier Lesevorgänge durchgeführt wurden. Genau diese Lesevorgänge werden wir nun Schritt für Schritt nachvollziehen.

Schritt 1: Lesen des Root-Levels für Index [ix_tbl_Companies_Name]

SQL Server muss zunächst einen Einstieg finden, ab dem nach dem Suchbegriff gesucht werden kann. der Abfrageplan zeigt, dass als “Einstiegspunkt” der Index [ix_tbl_Companies_Name] verwendet wird. Also benötigen wir die Informationen, auf welcher Datenseite der Index beginnt (Root-Level). Für die Evaluierung wird das nachfolgende Script ausgeführt, das die Indexinformationen in eine Relation mit dem Namen sp_table_pages überträgt. Das Konzept habe ich von Kimberly L. Tripp übernommen und im Beitrag “Optimierung von Datenbankmodellen – Richtige Wahl von Datentypen und Indexen (Teil 2)” beschrieben!

DECLARE @index_id int

SELECT @Index_id = index_id
FROM   sys.indexes
WHERE  object_id = OBJECT_ID('dbo.tbl_Companies', 'U') AND
       name = 'ix_tbl_Companies_Name'

BEGIN TRANSACTION
    TRUNCATE TABLE sp_table_pages
   
    INSERT INTO sp_table_pages
    EXEC sp_executeSQL N'DBCC IND(Indexing, ''dbo.tbl_Companies'', @Index_Id)', N'@index_id int', @Index_Id = @Index_Id

    SELECT  IndexId,
            PagePId,

            IndexLevel,
            NextPagePId,
            PrevPagePId
    FROM    sp_table_pages
    ORDER BY
           
IndexLevel DESC,
            PrevPagePId
COMMIT TRANSACTION

Das Ergebnis dieser Abfrage sieht anschließend wie folgt aus (bitte beachten Sie, dass die Seitenangaben sich von Ihrem Ergebnis unterscheiden können!)

ResultSet - sp_table_pages - 1

In der obigen Abbildung ist das Root-Level (höchster IndexLevel) auf Seite 200. Die gilt es nun zu inspizieren. Hierzu wird der nicht dokumentierte Befehl DBCC PAGE verwendet. Bitte achten Sie darauf, dass Traceflag 3604 aktiviert sein muss, um Informationen auf den Clientcomputer umzuleiten.

DBCC TRACEON (3604)
DBCC PAGE (Indexing, 1, 200, 3);

ResultSet - dbcc page - 1

Der Inhalt der Seite 200 wird ausgegeben und man sieht bereits indizierte Werte. Bitte achten Sie darauf, dass auch IMMER der Schlüsselwert des “Clustered Index” zu jeden Wert mitgespeichert wird [Id (Key)] Die Interpretation des Ergebnisses ist relativ simpel. Da wir uns im Root-Level befinden, zeigt das Ergebnis der obigen Abfrage, auf welcher Seite sich der Eintrag [Name (key)] befindet. Grundsätzlich werden im Root-Level sowie in den B-Tree-Leveln nur der jeweils ERSTE Eintrag einer Seite gespeichert. Für unsere Suche nach den Begriff “db Berater GmbH” bedeutet das Ergebnis, dass sich der Wert auf der Seite 500 befinden muss, da auf Seite 501 bereits mit “DEN…” begonnen wird.

DER ERSTE LESEVORGANG IST ABGESCHLOSSEN!

Schritt 2: Lesen des Leaf-Levels für Index [ix_tbl_Companies_Name]

Nachdem aus dem ersten Lesevorgang ersichtlich ist, dass sich der von uns gesuchte Begriff auf der Seite 500 befindet, wird die entsprechende Seite ebenfalls ausgewertet

DBCC TRACEON (3604)
DBCC PAGE (Indexing, 1, 500, 3);

ResultSet - dbcc page - 2

Perfekt, wir haben den aus dem Abfrageplan gewählten “index seek” abgeschlossen. Jedoch fordert die Ausgabe der Abfrage noch weitere Attribute, die NICHT im Index [ix_tbl_Companies_Name] hinterlegt sind. Wir benötigen weiterhin die Attribute [Id], [TaxNo], [CostCenter], [UpdateBy]. Diese Informationen befinden sich aber in der Relation selbst (also im “Clustered Index”). Wie man in der Ausgabe sehr gut erkennt (blau gekennzeichnet!), wird das Schlüsselattribut des “clustered Index” immer in einem Index mitgespeichert. Um nun also an die fehlenden Daten zu gelangen, müssen Informationen zur [Id] 246 aus dem “clustered Index” abgerufen werden.

DER ZWEITE LESEVORGANG IST ABGESCHLOSSEN!

Schritt 3: Lesen des Root-Levels für Index [ci_tbl_Companies]

Grundsätzlich – wie bereits oben ausgeführt – beginnt die Suche wieder im Root-Level des Index. Dazu werden erneut die Informationen zum Index in der Relation sp_table_pages ausgewertet. Das oben bereits angegebene Script wird nur marginal verändert.

DECLARE @index_id int

SELECT @Index_id = index_id
FROM   sys.indexes
WHERE  object_id = OBJECT_ID('dbo.tbl_Companies', 'U') AND
       name = 'ci_tbl_Companies'  -- <—Der Name des Index wird ausgetauscht!

BEGIN TRANSACTION
    TRUNCATE TABLE sp_table_pages

    INSERT INTO sp_table_pages
    EXEC sp_executeSQL N'DBCC IND(Indexing, ''dbo.tbl_Companies'', @Index_Id)', N'@index_id int', @Index_Id = @Index_Id

    SELECT IndexId,
           PagePId,

           IndexLevel,
           NextPagePId,
           PrevPagePId
    FROM   sp_table_pages
    ORDER BY
           IndexLevel DESC,
           PrevPagePId
COMMIT TRANSACTION

ResultSet - dbcc page - 3

Das Ergebnis der Ausführung des obigen Codes zeigt (bitte beachten Sie, dass die Seitenangaben sich von Ihrem Ergebnis unterscheiden können!), dass der Einstiegspunkt (Root-Level) auf Seite 80 ist. Diese Seite gilt es zunächst zu inspizieren (bitte denken Sie daran, dass wir nach dem Wert 246 im Schlüsselattribut suchen!)

DBCC TRACEON (3604)
DBCC PAGE (Indexing, 1, 80, 3);

ResultSet - dbcc page - 4

Wie man in der obigen Abbildung erkennen kann, muss der von uns gesuchte Wert (246) auf Seite 403 gespeichert sein. Auf Seite 403 beginnt der erste Eintrag mit dem Wert 211 und auf Seite 404 wird mit 284 begonnen. Das bedeutet also im nächsten Schritt die Inspektion der Site 403.

DER DRITTE LESEVORGANG IST ABGESCHLOSSEN!

Schritt 4: Lesen des Leaf-Levels von Index [ci_tbl_Companies]

Im letzten Schritt werden die Informationen zu unserem gesuchten Datensatz abgerufen, indem der Inhalt der Seite 403 überprüft wird. Hierbei handelt es sich um einen Leaf-Level. Um die Daten der Seite in einer lesbaren Form auszugeben, wird der Befehl DBCC TRACEON wie folgt ausgeführt:

DBCC TRACEON (3604)
DBCC PAGE (Indexing, 1, 80, 3) WITH TABLERESULTS;

ResultSet - dbcc page - 5

Bei der Untersuchung der Seite findet man dann die Informationen zum gesuchten Datensatz. Man kann sehr deutlich erkennen, dass ALLE Attribute der Relation auf der Datenseite gespeichert sind. Nun kann die Abfrage die entsprechenden Informationen aus dem gesuchten Datensatz entnehmen und zur Anzeige bringen.

DER VIERTE LESEVORGANG IST ABGESCHLOSSEN!

Ergebnis der Ausführung

Wenn man versteht, wie Indexe in SQL Server aufgebaut sind und verwaltet werden, lassen sich Abfragepläne relativ leicht analysieren. Das obige Beispiel hat darlegt, wie der Index-Seek funktioniert und warum ein Bookmark-Lookup durchgeführt werden musste.

Um Bookmark-Lookups zu vermeiden, muss – das hat das obige Beispiel gezeigt – dafür gesorgt werden, dass die Informationen für die Ausgabe ebenfalls auf den Indexseiten vorhanden sind. Für diese Lösung gibt es zwei Ansätze, die gegenüber gestellt und bewertet werden sollen:

Methode 1: Erstellung eines Indexes mit allen Ausgabeattributen

Mit dieser Methode wird ein Index erstellt, der nicht nur das Attribut [Name] sondern alle anderen Informationen für die Ausgabe beinhaltet. Dadurch werden nicht nur die Firmennamen sondern ALLE Attribute der Relation für die Ausgabe Bestandteil des Index.

-- Full covered index
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.tbl_Companies', 'U') AND name = 'ix_tbl_companies_name')
    DROP INDEX ix_tbl_companies_name ON dbo.tbl_Companies;
   
GO

CREATE NONCLUSTERED INDEX ix_tbl_companies_name ON dbo.tbl_Companies
(
    Name,
    TaxNo,
    CostCenter,
    UpdateBy
);

Nachdem der Index neu angelegt wurde, wird die Abfrage ausgeführt und sowohl IO als auch die Abfragepläne miteinander verglichen.

Vorher

Nachher

Abfrageplan - 2 Abfrageplan - 3

IO-Analyse

IO-Analyse

tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische Lesevorgänge 0 tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 2, physische Lesevorgänge 0

Das Ergebnis ist beeindruckend. Durch die richtige Auswahl von Indexattributen wurde das IO erneut um 50% reduziert. Von ursprünglich 35 Lesevorgängen wurde der vollständige IO auf 2 Lesevorgänge reduziert! Interessant ist jedoch in diesem Zusammenhang auch die Betrachtung der Indexseiten. Für den Datensatz von “db Berater GmbH” sieht der Leaf-Level des Indexes [ix_tbl_Companies_Name] nach der Änderung wie folgt aus:

ResultSet - dbcc page - 6

Methode 2: Erstellung eines Index mit eingeschlossenen Attributen

Mit der Einführung von SQL Server 2005 wurde eine neue Option für die Erstellung von nicht gruppierten Indexen implementiert. Die Verwendung von INCLUDE hat – im Gegensatz zu den “full covered indexes” folgende Vorteile:

  • INCLUDE-Attribute werden nur im Leaf-Level gespeichert
  • INCLUDE wird verwendet, um Größenbegrenzungen für Indexes (16 Attribute / Index, Max. 900 Bytes) zu umgehen

Der Unterschied der Speicherung im Root-Level / B-Tree-Level ist deutlich zu erkennen:

Full covered Index

ResultSet - dbcc page - 7

Index mit INCLUDE-Option

ResultSet - dbcc page - 8

Wie sieht es nun mit dem Abfrageplan aus – ist auch hier noch eine Optimierung möglich. Zunächst wird der Index erstellt und anschließend sowohl der Abfrageplan als auch die IO-Analyse verglichen.

-- INCLUDE index
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.tbl_Companies', 'U') AND name = 'ix_tbl_companies_name')
    DROP INDEX ix_tbl_companies_name ON dbo.tbl_Companies;
    GO

CREATE NONCLUSTERED INDEX ix_tbl_companies_name ON dbo.tbl_Companies
(Name)
INCLUDE
(
    TaxNo,
    CostCenter,
    UpdateBy
);

Abfrageplan - 3 tbl_Companies-Tabelle. Scananzahl 1, logische Lesevorgänge 2, physische Lesevorgänge 0

Das Ergebnis zeigt keine Unterschiede zur Verwendung von “full covered Indexes”. Die Vorteile der INCLUDE-Optionen sind also lediglich die bereits oben genannten Punkte.

Fazit

Indexe optimieren Abfragen erheblich. Jedoch ist bei der Auswahl der Indexe immer auch nach der Implementierung die Performance zu analysieren. Nur, wenn man das Zusammenspiel von Abfrageplänen / IO / Indexkonstruktionen im Zusammenhang versteht, lassen sich durch die richtige Kombination von Attributen im Index performante Systeme erstellen.

Herzlichen Dank für’s Lesen.
Das Script für die Datenbank und die Testdaten können sie hier herunterladen:
http://www.db-berater.de/files/database-indexing.zip

SET STATISTICS IO ON / OFF http://msdn.microsoft.com/en-us/library/ms184361.aspx
SET STATISTICS XML ON / OFF http://msdn.microsoft.com/en-us/library/ms176107.aspx
DBCC IND http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-IND.aspx
DBCC PAGE http://www.sascha-dittmann.de/post/Undokumentierter-SQL-Befehl-DBCC-PAGE.aspx
CREATE INDEX http://msdn.microsoft.com/de-de/library/ms188783.aspx
CREATE INDEX – INCLUDE() http://msdn.microsoft.com/de-de/library/ms190806(v=sql.90).aspx

Keine Kommentare :

Kommentar veröffentlichen