In einem Kundenprojekt wurden eine sogenannte “Partitioned View” eingesetzt, um Daten, die auf mehrere Relationen aufgeteilt waren, in einem abzufragenden Objekt zusammen zu fassen. Die Ausführung dieser Abfrage benötigt von Jahr zu Jahr mehr Zeit für die Ausführung. Die Analyse – und vor allen Dingen die Optimierung - dieser Abfrage war etwas komplizierter. Dieser Artikel zeigt, wie man nicht nur durch Indizierung Ausführungspläne von komplexen Systemen wie z. B. einer Partitioned View optimieren kann. 

Was ist eine Partitioned View

Bei einer partitionierten Sicht handelt es sich um eine Sicht, die durch eine UNION ALL-Anweisung von Relationen definiert ist, die eine identische Struktur aufweisen, jedoch getrennt voneinander gespeichert werden. Dieses Konzept kam jedoch mit der Einführung von "Partitionierten Tabellen” immer weniger zum Einsatz. Da die hier beschriebene Anwendung noch unter SQL Server 2000 entwickelt wurde und nun auf einem SQL Server 2008 R2 ihre Arbeit verrichtet, beließ man es bei dem Konzept, um keine Anpassungen in der Applikation selbst durchzuführen. Für die Demonstration des Sachverhalts werden Beispielrelationen verwendet, die das Problem verdeutlichen sollen!

Ausgangslage

Gegeben sind für die Demonstration der Problematik zwei Relationen, die Mitgliederdaten pro Jahr des Beitritts speichert. Diese Relationen werden nach dem Jahr des Eintrittsdatums benannt:

CREATE TABLE dbo.tbl_Members_2013
(
    SId          int         NOT NULL,
    SortName     char(256)   NOT NULL,
    MemberType   char(3)     NOT NULL,
    MemberSince  date        NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX ix_Members_2013 ON dbo.tbl_Members_2013(SId);
CREATE INDEX ix_Members_2013_MemberSince ON dbo.tbl_Members_2013(MemberSince);

CREATE TABLE dbo.tbl_Members_2012
(
    SId          int         NOT NULL,
    SortName     char(256)   NOT NULL,
    MemberType   char(3)     NOT NULL,
    MemberSince  date        NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX ix_Members_2012 ON dbo.tbl_Members_2012(SId);
CREATE INDEX ix_Members_2012_MemberSince ON dbo.tbl_Members_2012(MemberSince);

In beiden Relationen werden jeweils ~25.000 Datensätze gespeichert. Basierend auf der obigen Struktur gibt es eine “Partitioned View”, die alle Mitglieder in einem Abfrageobjekt zusammenführt:

CREATE VIEW dbo.view_Members
AS
    SELECT SId, SortName, MemberType, MemberSince FROM dbo.tbl_Members_2012
    UNION ALL
    SELECT SId, SortName, MemberType, MemberSince FROM dbo.tbl_Members_2013
GO

Testszenarien

Auf die erstellte View werden Abfragen durchgeführt, die sich nur auf ein Jahr beziehen oder aber jahresübergreifend Informationen anfordern. Für die jeweiligen Abfragen werden die Ausführungspläne unter den Abfragen angezeigt:

-- Abfrage für den Monat August 2012
SELECT * FROM dbo.view_Members WHERE MemberSince >= '20120801' AND MemberSince < '20120901';

-- Abfrage für Dezember 2012 bis Januar 2013
SELECT * FROM dbo.view_Members WHERE MemberSince >= '20121201' AND MemberSince < '20130201';

Wie zu erkennen ist, sind beide Ausführungspläne eher unperformant. Die Abfrage, die sich über einen Monat erstreckt, verwendet teure Keylookups für die Ausgabe der Daten. Obwohl ausschließlich Daten für 2012 gesucht werden, wird auf die Relation dbo.tbl_Members_2013 zugegriffen.

Die zweite Abfrage erstreckt sich auf beide Relationen, da sowohl Daten von 2012 als auch von 2013 ausgegeben werden sollen. Statt jedoch den Index zu verwenden, der für das Attribut [MemberSince] definiert wurde, wird ein kostenintensiver Clustered Index Scan durchgeführt. Damit muss der gesamte Inhalt beider Relationen vollständig gelesen werden.

Zunächst wurden die KeyLookups eliminiert, indem die fehlenden Attribute zu den Indexen hinzugefügt wurden:

DROP INDEX ix_Members_2012_MemberSince ON dbo.tbl_Members_2012;
CREATE INDEX ix_Members_2012_MemberSince ON dbo.tbl_Members_2012(MemberSince) INCLUDE (SortName, MemberType);
GO

DROP INDEX ix_Members_2013_MemberSince ON dbo.tbl_Members_2013;
CREATE INDEX ix_Members_2013_MemberSince ON dbo.tbl_Members_2013(MemberSince) INCLUDE (SortName, MemberType);
GO

Das Ausführungsverhalten der beiden Abfragen hat sich anschließend wie folgt verändert:

-- Abfrage für den Monat August 2012
SELECT * FROM dbo.view_Members WHERE MemberSince >= '20120801' AND MemberSince < '20120901';

-- Abfrage für Dezember 2012 bis Januar 2013
SELECT * FROM dbo.view_Members WHERE MemberSince >= '20121201' AND MemberSince < '20130201';

Für die zweite Abfrage ist das Ausführungsverhalten in Ordnung und kann nicht weiter verbessert werden. Jedoch ist bei Abfrage 1 störend, dass – trotz Einschränkung des Datumsbereichs auf Werte des Jahres 2012 – die Relation [dbo].{tbl_Members_2013] ebenfalls durchsucht wird. Ein Blick auf das Ausführungsprofil zeigt auch, warum:

SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;
GO

SELECT * FROM dbo.view_Members WHERE MemberSince >= '20120801' AND MemberSince < '20120901';

Obwohl KEINE Daten aus der Relation [dbo].[tbl_Members_2013 zurück geliefert werden (wir wussten das ja), ist Microsoft SQL Server davon ausgegangen, dass mindestens eine Zeile zu erwarten ist ([EstimateRows]). Also war die erste Idee, die Statistiken zu aktualisieren, um Microsoft SQL Server aktuelle Zahlen zu den vorhandenen Werten in den Relationen zu geben.

UPDATE STATISTICS dbo.tbl_Members_2012 ix_Members_2012_MemberSince WITH FULLSCAN;
UPDATE STATISTICS dbo.tbl_Members_2013 ix_Members_2013_MemberSince WITH FULLSCAN;
DBCC SHOW_STATISTICS ('dbo.tbl_Members_2012', 'ix_Members_2012_MemberSince') WITH HISTOGRAM; DBCC SHOW_STATISTICS ('dbo.tbl_Members_2013', 'ix_Members_2013_MemberSince') WITH HISTOGRAM;

Wie man sehr deutlich erkennen kann, gibt es in den Statistiken keine “übergreifenden” Werte; jede Relation besitzt ausschließlich Datumswerte aus den ihnen zugewiesenen Jahren. Dennoch geht Microsoft SQL Server immer davon aus, dass ein Datensatz vermutet wird. Dieses Verhalten ist “by design” und gilt für jede Art der Abfrage, die durch Predicates eingeschränkt werden soll. Obwohl Microsoft SQ Server für einen guten Ausführungsplan die Statistiken verwendet, muss Microsoft SQL Server aber bei Predicates, die nicht über die Statistiken zu eruieren sind, davon ausgehen, dass mindestens ein Datensatz vorhanden ist, der diesem Kriterium entspricht, um die Abfrage ausführen zu können!

Das Verhalten kann man selbst testen, indem man für ein Attribut, das man durch ein Predicate einschränkt, einen unsinnigen Wert eingibt und sich dann den zugehörigen Ausführungsplan anschaut:

SELECT * FROM dbo.tbl_Members_2012 WHERE MemberSince = '99991231';

Wie aus der obigen Abbildung unschwer zu erkennen ist, geht Microsoft SQL Server bei seiner Analyse über die Anzahl der Datensätze davon aus, dass mindestens ein Datensatz vorhanden ist. Gibt man einen Wert ein, dessen Menge über die Statistiken abrufbar sind, stimmen die [geschätzte Anzahl von Zeilen] mit [Tatsächliche Anzahl von Zeilen] überein:

SELECT * FROM dbo.tbl_Members_2013 WHERE MemberSince = '20130106';

Die obige Abfrage grenzt die Datenmenge auf den 06.01.2013 ein und wie man weiter oben bei den Statistiken erkennen kann, gibt es 3.570 Datensätze, die diesem Kriterium entsprechen. Microsoft SQL Server zeigt für den Ausführungsplan genau diesen Wert als [Geschätzte Anzahl von Zeilen] an; ein Indiz für die Verwendung von Statistiken.

Abschließend kann man also sagen, dass Microsoft SQL Server IMMER von einem Wert von 1 ausgeht, wenn eine Abfrage durch ein Predicate eingeschränkt wird und dieses Predicate nicht durch Statistiken abgedeckt ist. Bedingt durch dieses Verhalten macht dann der Ausführungsplan basierend auf der View wieder Sinn. Die View umfasst beide Relationen und Microsoft SQL Server muss ALLE in der View mittels UNION ALL zusammengefassten Relationen berücksichtigen.

Um zu verstehen, wie man das Verhalten von Microsoft SQL Server ändern kann, muss man wissen, wie “Partitioned Tables” arbeiten. Die Daten partitionierter Tabellen werden in Einheiten aufgeteilt, die über mehrere Dateigruppen in einer Datenbank verteilt sein können. Die Daten werden horizontal partitioniert, sodass Gruppen von Zeilen einzelnen Partitionen zugeordnet werden. Für partitionierte Tabellen gibt es zunächst die Partitionierungsfunktion. Sie definiert, wie die Zeilen einer Relation basierend auf den Werten bestimmter Attribute, den so genannten Partitionierungsspalten, einer Partition zugeordnet werden. Die Partitionsfunktion definiert die Anzahl von Partitionen, über die die Tabelle verfügt, und wie die Begrenzungen der Partitionen definiert werden.

Hinter den Kulissen wird eine Relation in mehrere Elemente aufgeteilt und wenn neue Datensätze hinzugefügt werden, müssen sie durch die Partitionsfunktion einem Element zugewiesen werden. Die Partitionsfunktion ist vom Prinzip her nichts anderes als eine Einschränkung für Werte, die in Attribute vorhanden sein können.

Genau das kann man in dem obigen Beispiel ebenfalls bestimmen; in die Relation [dbo].[tbl_Members_2012] durch nur Datensätze eingetragen werden, deren Mitgliedsdatum zwischen dem 01.01.2012 und dem 31.12.2012 liegen; ähnliches gilt für [dbo].[tbl_Members_2013]. Hier dürfen ausschließlich Mitgliedsdaten für den Zeitraum zwischen dem 01.01.2013 und dem 31.12.2013 vorhanden sein. Diese Einschränkungen sind Filter, die ähnlich einer Partitionsfunktion klare Grenzen bestimmen, in dessen Wertebereich sich die Datensätze befinden. Wenn solche Einschränkungen definiert werden, kann Microsoft SQL Server – genau wie wir – davon ausgehen, dass es keine anderen Wertebereiche geben kann; somit würde sich die Suche auf Grund dieser Einschränkung erübrigen.

-- Einschränkung des Wertebereichs auf das Jahr 2012
ALTER TABLE dbo.tbl_Members_2012 WITH CHECK ADD CONSTRAINT [CK_tbl_Members_2012] CHECK (MemberSince >= '20120101' AND MemberSince < '20130101');

-- Einschränkung des Wertebereichs auf das Jahr 2013
ALTER TABLE dbo.tbl_Members_2013 WITH CHECK ADD CONSTRAINT [CK_tbl_Members_2013] CHECK (MemberSince >= '20130101' AND MemberSince < '20140101');

Schaut man sich nun den Ausführungsplan für die Abfragen nach Mitgliedern für August 2012 erneut an, sieht man sofort die Verbesserung:

-- Abfrage für den Monat August 2012
SELECT * FROM dbo.view_Members WHERE MemberSince >= '20120801' AND MemberSince < '20120901';

Perfekt – der “unnötige” Zugriff auf die Relation [dbo].[tbl_Members_2013] wurde eliminiert. Microsoft SQL Server berücksichtigt bei der Ausführung die Einschränkungen und kann somit bereits im Vorfeld erkennen, dass ein Zugriff auf die zweite Relation nutzlos ist.

Aber ACHTUNG; für eine CHECK-Einschränkung im obigen Sachverhalt muss darauf geachtet werden, dass keine nondeterministische Funktionen verwendet werden. In dem Fall muss Microsoft SQL Server nämlich für jede Datenzeile die Bedingungen überprüfen und der Vorteil geht verloren. Das nachfolgende Script zeigt den Unterschied. Statt eines Anfangs- und eines Enddatums wird die Funktion YEAR verwendet, um aus dem Mitgliedsdatum das Jahr zu extrahieren. Daraus wird dann die Bedingung gebildet.

ALTER TABLE dbo.tbl_Members_2012 DROP CONSTRAINT CK_tbl_Members_2012;
ALTER TABLE dbo.tbl_Members_2013 DROP CONSTRAINT CK_tbl_Members_2013;

ALTER TABLE dbo.tbl_Members_2012 WITH CHECK ADD CONSTRAINT CK_tbl_Members_2012 CHECK (YEAR(MemberSince) = 2012);

ALTER TABLE dbo.tbl_Members_2013 WITH CHECK ADD CONSTRAINT CK_tbl_Members_2013 CHECK (YEAR(MemberSince) = 2013);

Führt man anschließend die Abfrage erneut ab, sieht der Ausführungsplan wie folgt aus:

-- Abfrage für den Monat August 2012
SELECT * FROM dbo.view_Members WHERE MemberSince >= '20120801' AND MemberSince < '20120901';

Es ist deutlich zu erkennen, dass erneut auch der Index ix_Members_2013 verwendet werden muss. Ursächlich für dieses Verhalten ist, dass Microsoft SQL Server erst die Funktion YEAR ausführen muss, um einen Wert zu definieren. Prinzipiell gilt, dass man bei der Verwendung von CHECK-Einschränkungen Funktionen vermeiden sollte; Microsoft SQL Server verhält sich sonst, wie bei NONSARGable Abfragen. Zu diesem Thema gibt es weitere Informationen in dem von mir verfassten Artikel “Optimierung von Datenbankmodellen-SARGable Abfragen”

Fazit

Es ist interessant, dass Microsoft SQL Server so “intelligent” ist und mögliche Einschränkungen auf indizierte Attribute für die Entscheidung einer Ausführungsstrategie berücksichtigt. Weitere Details zu diesem Thema finden sich hier: http://msdn.microsoft.com/en-us/library/aa933141.aspx

Herzlichen Dank fürs Lesen!