In einer Anfrage in den Microsoft Foren (link) ging es darum, warum Microsoft SQL Server trotz einer SEEK-Operation alle Datenseiten einer Tabelle durchsucht hat. Tatsächlich kann eine SEEK-Operation die vollständige Tabelle betreffen, wenn bestimmte Voraussetzungen nicht erfüllt sind. Wie wichtig zum Beispiel die korrekte Verwendung von Datentypen bei Einschränkungen sind, zeigt der nachfolgende Artikel.

Testumgebung

Als Beispiel soll eine Kundentabelle dienen, die Mandantenfähigkeiten simulieren soll. Das bedeutet in der Umsetzung, dass zu jedem Kunden immer eine Mandanten-Id gespeichert werden muss. Beide Attribute zusammen bilden den Clustered Key in der Tabelle. Erschwerend kommt hinzu, dass als [Customer_Id] ein Textdatentyp verwendet wird.

CREATE TABLE dbo.Customers

(

    Mandant_Id      INT        NOT NULL,

    Customer_Id     CHAR(5)    NOT NULL,

    CustomerName    CHAR(255)  NOT NULL DEFAULT ('Filler'),

    CONSTRAINT pk_Customers PRIMARY KEY CLUSTERED

    (

        Mandant_Id,

        Customer_Id

    )

);

Das Attribut [CustomerName] dient im Beispiel lediglich dazu, ein paar Datenseiten zu erzeugen. Für dieses Beispiel ist das Attribut [Customer_Id] besonders hervor zu heben; es handelt sich nicht um einen numerischen Datentypen sondern um einen Textdatentyp.

DECLARE @i int = 10000;

WHILE @i <= 11000

BEGIN

    INSERT INTO dbo.Customers (Mandant_Id, Customer_Id)

    VALUES (1, @i);

    SET @i += 1;

END

GO

ALTER INDEX pk_Customers ON dbo.Customers REBUILD;

GO

Nachdem die Tabelle aufgebaut und die Daten eingefügt wurden, belegt diese Tabelle 37 Datenseiten in der Datenbank

SELECT OBJECT_NAME(p.object_id) AS object_name,

       au.type_desc,

       au.data_pages,

       au.used_pages,

       au.total_pages

FROM   sys.allocation_units AS au INNER JOIN sys.partitions AS p

       ON (au.container_id = p.partition_id)

WHERE  p.object_id = OBJECT_ID('dbo.Customers', 'U');

GO

Das Ergebnis lässt sich wie folgt interpretieren:

• Die Daten selbst werden in 35 Datenseiten gespeichert
• Insgesamt werden 37 Datenseiten allokiert:
  • Eine Datenseite für die IAM (Index Allocation Map)
  • Eine Datenseite für den Root-Knoten (B-Tree)
• Da Microsoft SQL Server nur die ersten 8 Datenseiten in “mixed extents” speichert werden alle weiteren Datenseiten in “uniformed Extents” (jeweils 8 Datenseiten) gespeichert. 5 Datenseiten des 5. Extent stehen also noch für Daten zur Verfügung, bevor Microsoft SQL Server ein neues Extent allokiert.

Szenario #1

Da für das Attribut [Customer_Id] nur numerische Werte vorhanden sind (unabhängig davon, dass es sich um einen Textdatentyp handelt), kommt man natürlich sehr schnell in die Verlegenheit, statt eines “Textwertes” einen numerischen Wert zu verwenden, wie das folgende Beispiel zeigt:

SET STATISTICS IO ON;

GO

SELECT * FROM dbo.Customers WHERE  Mandant_Id = 1 AND Customer_Id = 10001;

GO

SET STATISTICS IO OFF

GO

Das obige Ergebnis liefert exakt einen Datensatz zurück, da der Clustered Key der Tabelle verwendet werden kann. Ein Blick auf den Ausführungsplan zeigt einen INDEX SEEK als Operator für die Suche nach den betroffenen Datensätzen.

Verwendet man das SQL Server Management Studio 11.0.xxx, so wird beim SELECT-Operator ein gelbes Ausrufezeichen angezeigt; ältere Versionen von SQL Server Management Studio unterstützen diesen Fehlerhinweis leider nicht. Was genau es mit diesem Fehler auf sich hat, zeigen die IO-Statistiken für die obige Operation.

Table 'Customers'. Scan count 1, logical reads 37, physical reads 0,

Das IO zeigt einen ungewöhnlichen Wert für die SEEK-Operation; besagt dieser Wert doch, dass die vollständige Tabelle durchsucht worden ist. Die SEEK-Operation ist also vom Prinzip her nichts anderes als ein SCAN! Die Ursache dafür liegt in der Verarbeitung der WHERE-Klausel. Bewegt man die Maus auf den SEEK-Operator, werden die folgenden Informationen angezeigt:

Auf der linken Seite werden die Eigenschaften des SEEK-Operators gezeigt. Ein erster Blick gilt der Option [Geschätzte Anzahl von Zeilen]. Der gezeigte Wert zeigt deutlich, dass die Statistiken für das verwendete Index-Objekt [pk_Customers] stimmen.

Der QueryOptimizer geht von einem Datensatz aus, den die Abfrage-Einschränkung (WHERE) eingrenzt. Tatsächlich wird nur ein Datensatz geliefert, wie die weiter oben gezeigte Eigenschaft [Tatsächliche Anzahl von Zeilen] zeigt.

Besondere Beachtung gilt den eigentlichen Suchkriterien. Während die Einschränkung auf [Mandant_ID] tatsächlich einen SEEK-Operator verwenden kann, kann das Attribut [Customer_Id] – obwohl Bestandteil des Clustered Keys – nicht davon profitieren. Kann ein Prädikat nicht in einer SEEK-Operation verwendet werden, spricht man von einem “Residual Predicate”. Der Grund dafür wird bei näherem Hinsehen sofort deutlich – Microsoft SQL Server muss einen Typenkonvertierung für das Attribut selbst durchführen!

Wäre – wie im SEEK-Prädikat ersichtlich – lediglich eine Konvertierung der Einschränkung selbst erforderlich, könnte die Einschränkung für Mandant_Id ebenfalls vom SEEK-Operator profitieren; da aber der Inhalt der Spalte selbst konvertiert werden muss, muss immer der Teil der Tabelle durchsucht werden, für die bereits auf die Mandant_Id gefilterten wurde.

Im vorliegenden Beispiel gibt es nur einen Mandanten, somit muss die vollständige Tabelle durchsucht werden! Das Microsoft SQL Server tatsächlich für die Ergebnismenge [Mandant_Id] = 1 anschließend eine Filterung durchführen muss, kann mit Traceflag 9130 sichtbar gemacht werden:

SELECT * FROM dbo.Customers WHERE Mandant_Id = 1 AND Customer_Id = 10001 OPTION (QUERYTRACEON 9130);

Der oben gezeigte Ausführungsplan zeigt deutlich, dass zunächst eine sehr große Datenmenge (in diesem Fall 1.000 Datensätze) durch den SEEK-Operator an den [Filter]-Operator durchgereicht werden. Der [Filter]-Operator muss anschließend alle 1.000 Datensätze aus dem vorherigen Operator annehmen und durch besagte Typen-Konvertierung einzeln prüfen. Das Ergebnis ist dann 1 Datensatz!

Das oben gezeigte Phänomen kennt man auch, wenn man mit “non-sargable” Argumenten arbeitet, wie ich im Artikel “Optimierung von Datenbankmodellen – SARGable Abfragen” beschrieben habe.

Szenario #2

Wie Microsoft SQL Server die Einschränkungen verarbeitet, wenn die korrekten Datentypen verwendet werden, zeigt das nächste Beispiel. Alle Beispiele werden mit dem oben genannten Traceflag 9130 ausgeführt, um zu zeigen, dass keine dedizierte [Filter]-Operation verwendet wird.

SELECT * FROM dbo.Customers WHERE Mandant_Id = 1 AND Customer_Id = '10001' OPTION (QUERYTRACEON 9130);

Sowohl die erste Einschränkung [Mandant_Id] als auch die zweite Einschränkung auf [Customer_Id] werden als SEEK-Prädikate verwendet. Durch diese Operation verbleibt kein “Residual Prädikat”, das eine weitere Filterung bedeutet hätte.

Die Abfrage läuft nun optimal, wie auch die folgende IO-Statistik zeigt. Statt einer vollständigen Suche über die Tabelle (37 IO) kann Microsoft SQL Server den B-Tree für einen optimalen Zugriff verwenden

Table 'Customers'. Scan count 0, logical reads 2, physical reads 0, 

Da Microsoft SQL Server nun keine Konvertierung der Werte im Attribut vornehmen muss, kann effizient gesucht werden.

Zusammenfassung

Für effektive Abfragen sind nicht allein Indexe verantwortlich – für eine effektive Verwendung ist es erforderlich, dass Microsoft SQL Server bereits bei der Formulierung der Abfrage die korrekten Datentypen für die Einschränkungen erhält. Sobald Microsoft SQL Server selbst eine Typenkonvertierung durchführen muss, kann ein kostengünstiger SEEK schnell zu einem teuren SCAN werden, wenn diese Typenkonvertierung das Attribut selbst betrifft. Nur die Auswertung eines Ausführungsplans reicht unter Umständen nicht aus, eine Bewertung vorzunehmen, ob eine Abfrage effektiv ausgeführt wird; auch ein Blick auf das IO und – insbesondere – auf die Eigenschaften der Abfrageoperatoren gibt einen deutlichen Hinweis auf die Effektivität der Abfrage.

Herzlichen Dank fürs Lesen!

Links

sys.allocation_units: http://msdn.microsoft.com/de-de/library/ms189792.aspx

sys.partitions: http://msdn.microsoft.com/de-de/library/ms175012.aspx

INDEX SEEK: http://technet.microsoft.com/en-us/library/aa178398.aspx

INDEX SCAN: http://technet.microsoft.com/en-us/library/aa178393.aspx

SET STATISTICS: http://msdn.microsoft.com/de-de/library/ms184361.aspx

QUERYTRACEON: http://support.microsoft.com/kb/2801413/en-us

TF 9130: http://sqlblog.com/blogs/paul_white/archive/2012/10/15/cardinality-estimation-bug-with-lookups-in-sql-server-2008-onward.aspx

Pages and Extents: http://technet.microsoft.com/en-us/library/ms190969.aspx