Freitag, 16. November 2012

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

Mit diesem Beitrag möchte ich aufzeigen, wie wichtig es ist, auch bei Abfragen immer darauf zu achten, dass bei der Verwendung von Ausdrücken in der Einschränkung (WHERE-Klausel) Sorgfalt geboten ist. Wenn man Abfragepläne untersucht und stellt fest, dass ein Indexscan statt eines Indexseek verwendet wird, kann es an der Verwendung falscher Datentypen in der WHERE-Klausel liegen

Für die Demonstration des Verhaltens eines Index bei falschen Datentypen soll die nachfolgende Datenstruktur dienen:

CREATE TABLE dbo.tbl_Employees
(
    Employee_Id
  int             NOT NULL    IDENTITY    PRIMARY KEY CLUSTERED,
    FirstName    nvarchar(50)    NOT NULL,
    LastName   
nvarchar(50)    NOT NULL,
    SocialSecNo
  char(10)        NOT NULL
);
GO

In der Relation [dbo].[tbl_Employees] befinden sich insgesamt 40417 Datensätze. Das Attribut [Employee_Id] ist Primärschlüssel und ein “clustered Index”. Das Attribut [SocialSecNo] beinhaltet die Sozialversicherungsnummer und ist ein eindeutiges Attribut. Bevor jedoch ein Index auf dieses Attribut gelegt wird, schauen wir und für die nachfolgende Abfrage nach einer Sozialversicherungsnummer den Ausführungsplan an:

SET STATISTICS IO ON;
SELECT * FROM dbo.tbl_Employees WHERE SocialSecNo = 1000007944;
SET STATISTICS IO OFF;

Abfrageplan - 1 tbl_Employees-Tabelle. Scananzahl 1, logische Lesevorgänge 280, physische Lesevorgange 0, …

Es wird erwartungsgemäß ein Indexscan auf dem clustered Index ausgeführt. Insgesamt müssen 280 Lesevorgänge durchgeführt werden. Diese Anzahl ist korrekt, wenn man sich die physikalischen Daten des Indexes etwas genauer anschaut:

SELECT record_count,
        page_count,
        record_count / CAST(page_count AS numeric(10, 2))    AS    avg_records_per_page

FROM    sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.tbl_Employees', 'U'), 1, NULL, 'DETAILED')

ResultSet - index_physical_stats

Die Summe ergibt zwar nur 279 Seiten jedoch muss auch die IAM-Seite beim Lesevorgang mit berücksichtigt werden. Grundsätzlich bedeutet die obige Aussage, dass die komplette Relation nach der Sozialversicherungsnummer 1000007944 durchsucht werden muss. Das macht Sinn, da ja dieses Attribut nicht indexiert ist!

Also wird nun auf dem Attribut [SocialSecNo] in der Relation [dbo].[tbl_Companies] ein eindeutiger (UNIQUE) Index erstellt, da eine Sozialversicherungsnummer nicht mehrfach vorkommen kann. Anschließend wird die obige Abfrage erneut ausgeführt und das Ergebnis begutachtet.

CREATE UNIQUE INDEX ix_tbl_Employees_SocialSecNo ON dbo.tbl_Employees(SocialSecNo);
GO

SET STATISTICS IO ON;
SELECT * FROM dbo.tbl_Employees WHERE SocialSecNo = 1000007944;
ET STATISTICS IO OFF;

Auch hier wird wieder der Abfrageplan sowie die IO-Statistiken überprüft. Das Ergebnis ist verblüffend:

Abfrageplan - 2 tbl_Employees-Tabelle. Scananzahl 1, logische Lesevorgänge 105, physische Lesevorgänge 0, …

Zunächst einmal fällt auf, dass für die Suche tatsächlich der zuvor angelegte Index [ix_tbl_Employee_SocialSecNo] verwendet wird. Jedoch verwundert, dass statt eines Indexseek ein Indexscan durchgeführt wird. Der IO bestätigt dieses Verhalten, insgesamt wurden 105 Lesevorgänge durchgeführt; es wurden also 105 Seiten durchsucht. Im Klartext bedeutet das, dass zwar der Index verwendet wird, aber der Ausdruck (Predicate) scheinbar nicht eindeutig im Index zugeordnet werden kann.

Abfrageplan - 2 - Analysis Schaut man sich die Eigenschaften des Abfrageknotens etwas genauer an, wird man feststellen, dass die Ursache für den Indexscan in der Definition der Einschränkung (WHERE-Klausel) liegt.
Die ursprüngliche WHERE-Klausel wird implizit von SQL Server umgewandelt. Das [Predicate] bedeutet, man hätte die Abfrage auch wie folgt erfassen können:

SELECT * FROM dbo.tbl_Employees
WHERE CAST(SocialSecNo AS int) = 1000007944

Bei der Definition des Index wurde das Attribut [SocialSecNo] angegeben. Dessen Datentyp ist CHAR und nicht INT. Somit muss eine Typenumwandlung durchgeführt werden.

Auf Grund der Tatsache, dass im Attribut [SocialSecNo] gesucht werden soll, konnte SQL Server zwar definieren, dass der angelegte Index verwendet werden soll; nur konnte keine Suche durchgeführt werden, weil die Datentypen nicht identisch sind.

Zuletzt wird die Abfrage noch einmal modifiziert. Die Sozialversicherungsnummern wird nun als varchar / char übergeben und anschließend wird der Abfrageplan sowie das IO erneut untersucht.

SET STATISTICS IO ON;
SELECT * FROM dbo.tbl_Employees WHERE SocialSecNo = '1000007944';
SET STATISTICS IO OFF;

Abfrageplan - 3 tbl_Employees-Tabelle. Scananzahl 0, logische Lesevorgänge 4, physische Lesevorgänge 0, …

Na also, nun haben wir das gewünschte Ergebnis. das zeigen auch die detaillierten Informationen zum Abfrageknoten.

Abfrageplan - 3 - Analysis

Der Index wird genutzt und es wird eine SEEK-Operation statt eines SCAN durchgeführt. Insgesamt hat sich das IO nur durch die Berücksichtigung des Datentyps erheblich verringert. Wie man zu Guter Letzt auch noch den Bookmark-Lookup entfernt kann man im Artikel “http://db-berater.blogspot.de/2012/11/optimierung-von-datenbankmodellen_14.html” noch einmal nachlesen.

Fazit:

Ein Index ist ein Garant für eine performante Ausführung von Abfrage. Dennoch gilt es, auch darauf zu achten, dass bei der Auswahl der Einschränkungen (WHERE-Klausel) der korrekte Datentyp verwendet wird. Leider ist das von mir gezeigte Beispiel seit SQL Server 2000 möglich. In vorherigen Versionen wäre eine solche Abfrage mit dem Hinweis auf den falschen Datentypen abgebrochen.

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

 

sys.dm_db_index_physical_stats http://msdn.microsoft.com/de-de/library/ms188917.aspx
IAM-Pages http://msdn.microsoft.com/de-de/library/ms187501(v=sql.105).aspx
SET STATISTICS IO ON / OFF http://msdn.microsoft.com/en-us/library/ms184361.aspx
CREATE INDEX http://msdn.microsoft.com/de-de/library/ms188783.aspx

Keine Kommentare :

Kommentar veröffentlichen