Erst im letzten Artikel “Warum korrekte Datentypen für WHERE-Klauseln wichtig sind” habe ich die Auswirkungen von erforderlichen Typenkonvertierungen auf das Ausführungsverhalten beschrieben. Kaum geschrieben kam dann auch ein “echter” Fall diese Woche, der zunächst unerklärlich war; ein Blick auf die Ausführungspläne hat dann aber sehr schnell gezeigt, dass ein falsch gelöster “Sortierungskonflikt” die Ursache für das sehr schlechte Ausführungsverhalten der Abfrage war.

Problembeschreibung

In einem Projekt, in dem ich schon ein paar Monate für die Optimierung der Workloads arbeite, werden mittels Prozeduren die Workloads in kleine – überschaubare – Teilkomponenten zerlegt, die - in temporären Tabellen abgelegt – anschließend in einer abschließenden Abfrage zu einem Ergebnis führen, das an die Anwendung geliefert wird. Eine Prozedur ist als “langsam” aufgefallen, die sich von den anderen Abfragen deutlich unterschied – der JOIN wurde nicht über einen numerischen Datentypen sondern über einen Textdatentypen durchgeführt. Die Produktionsdatenbank hatte eine von der Standardsortierung (Server) unterschiedliche Einstellung für die Sortierung.

Beschreibung der Testumgebung

Um das Verhalten nachzustellen, wird eine neue Datenbank mit einer von der Standardsortierung (auf dem Beispielserver “Latin1_General_CI_AS”) unterschiedlichen Sortierung erstellt. In dieser Datenbank wird anschließend eine Tabelle [dbo].[Customers] angelegt, die mit 1.000 Datensätzen gefüllt wird. In der Tabelle gibt es ein Attribut [Customer_Number], für das ein UNIQUE Index für eine bessere Ausführungsgeschwindigkeit erstellt wird. Um die Standardsortierung für Server und Systemdatenbank TEMPDB auszugeben, kann folgende Abfrage verwendet werden:

-- Welche Sortierung für TEMPDB

SELECT  name                        AS DatabaseName,

        collation_name              AS DatabaseCollation,

        SERVERPROPERTY('Collation') AS ServerCollation

FROM    sys.databases AS D

WHERE   D.database_id = 2;

Erstellen der Testumgebung

Zunächst wird eine neue Datenbank mit der Sortierung Latin1_General_BIN erstellt:

CREATE DATABASE demo_db

ON PRIMARY

(

    Name = 'demo_db',

    FILENAME = 'S:\MSSQL11.SQL_2012\MSSQL\DATA\\demo_db.mdf',

    SIZE = 10MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 10MB

)

LOG ON

(

    NAME = 'demo_log',

    FILENAME = 'S:\MSSQL11.SQL_2012\MSSQL\DATA\\demo_db.ldf',

    SIZE = 10MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 10MB

)

COLLATE Latin1_General_BIN;

GO

Anschließend wird in der zuvor angelegten Datenbank [demo_db] eine neue Tabelle [dbo].[Customers] angelegt:

USE demo_db;

GO

CREATE TABLE dbo.Customers

(

    Customer_Id      INT             NOT NULL    IDENTITY (1, 1),

    Customer_Number  CHAR(5)         NOT NULL,

    Customer_Name    VARCHAR(255)    NOT NULL,

    CONSTRAINT pk_Customers_Id PRIMARY KEY CLUSTERED (Customer_Id)

);

GO

-- Additional index on Customer_Number

CREATE UNIQUE INDEX ix_Customers_Number ON dbo.Customers (Customer_Number) INCLUDE (Customer_Name);

GO

Hinweis: Die Datenbank verwendet eine Sortierung, die Groß- und Kleinschreibung unterscheidet. Es ist also für alle weiteren Beispiele wichtig, dass die korrekte Schreibweise beachtet wird!

Nachdem die Tabelle [dbo].[Customers] erstellt ist, können die Metadaten des Objekts überprüft werden:

SELECT  OBJECT_NAME(C.object_id)   AS Table_Name,

        C.name                     AS Column_Name,

        S.name                     AS Type_Name,

        C.column_id,

        C.max_length,

        C.collation_name

FROM    sys.columns AS C INNER JOIN sys.types AS S

        ON (C.system_type_id = S.system_type_id)

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

In der Abbildung ist deutlich zu erkennen, dass numerische Datentypen unabhängig von der Sortierung sind. Textdatentypen unterliegen jedoch alle der Definition einer zuvor festgelegten Sortierung. Abschließend werden 1.000 Datensätze in die Tabelle [dbo].[Customers] eingefügt, die für die späteren Tests verwendet werden.

DECLARE @i INT = 1;

WHILE @i <= 1000

BEGIN

    INSERT INTO dbo.Customers(Customer_Number, Customer_Name)

    VALUES (CAST(10000 + @i AS CHAR(10)), 'Customer: ' + CAST(@i AS VARCHAR(10)))

    SET @i += 1;

END

Simulation des Workloads – Verwendung einer temporären Tabelle

Wie bereits oben beschrieben, hat man – um die Abfragen in kleinere Teilschritte zu zerlegen – den Einsatz von temporären Tabellen in Erwägung gezogen. Wie in einer Benutzerdatenbank gilt bei der Erstellung von temporären Tabellen auch, dass bei Nichtangabe einer Sortierung immer die Sortierung der Datenbank gilt, in der das Objekt erstellt wird. Für den zu simulierenden Workload wird eine temporäre Tabelle ohne explizite Spezifikation einer Sortierung erstellt und das Ergebnis zeigt, dass die Sortierung von TEMPDB übernommen wurde:

CREATE TABLE #Customer_Number (Customer_Number CHAR(5) PRIMARY KEY);

GO

SELECT  OBJECT_NAME(C.object_id, 2)  AS Table_Name,

        C.name                       AS Column_Name,

        S.name                       AS Type_Name,

        C.column_id,

        C.max_length,

        C.collation_name

FROM    tempdb.sys.columns AS C INNER JOIN tempdb.sys.types AS S

        ON (C.system_type_id = S.system_type_id)

WHERE   object_id = object_id('tempdb..#Customer_Number', 'U');

In die Tabelle werden für die anschließenden Tests ein paar Einzelwerte eingetragen.

INSERT INTO #Customer_Number (Customer_Number)

VALUES

    ('10005'),

    ('10010'),

    ('10009'),

    ('10002');

GO

Hinweis: Für alle nachfolgenden Testszenarien werden sowohl Statistiken, Ausführungsplan und Ausführungszeit ausgegeben. Dies kann generell mit folgendem SQL-Statement zu Beginn aktiviert werden:

SET STATISTICS IO, XML, TIME ON;

Test 1 – JOIN ohne COLLATE Hinweise

Im ersten Test wird ein SELECT mit einem INNER JOIN der zuvor erstellten temporären Tabelle #Customer_Number mit der Tabelle [dbo].[Customers] ausgeführt. Das Ergebnis ist ernüchternd, da der Laufzeitfehler 468 erzeugt wird:

SELECT  C.*

FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN

        ON (C.Customer_Number = CN.Customer_Number);

Meldung 468, Ebene 16, Status 9, Zeile 3
Ein Sortierungskonflikt zwischen 'Latin1_General_CI_AS' und 'Latin1_General_BIN' im equal to-Vorgang kann nicht aufgelöst werden.

Die Fehlermeldung ist selbsterklärend – ein JOIN kann nicht durchgeführt werden, da die beiden Sortierungen nicht kompatibel zueinander sind. Dieser Fehler ist prinzipiell der Einstieg in eine Optimierung, die – ungewollt – zu erheblichen Performanceeinbußen führt. Das zeigen die nächsten Tests.

Test 2 – JOIN mit COLLATE auf [dbo].[Customers]

Wenn Spalten nicht mit einem JOIN verbunden werden können, kann für jedes Attribut explizit mittels “COLLATE Sortierung” eine Sortierung erzwungen werden. Dieser Weg wird häufig gewählt, wenn die Metadaten der Tabellen nicht geändert werden können.

SELECT  C.*

FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN

        ON (C.Customer_Number COLLATE Latin1_General_CI_AS = CN.Customer_Number);

Die obigen Abbildungen zeigen sowohl das IO (Herzlichen Dank an Richie Rump für seine tolle Idee zur Darstellung von IO unter http://www.statisticsparser.com) als auch den Ausführungsplan für die Abfrage.

Auffällig bei der Analyse des Ausführungsplans ist, dass kein – effizienter – Index Seek auf [dbo].[Customers] angewendet werden kann. Ursächlich für den Index Scan ist der Umstand, dass einen Typenkonvertierung für die Spalte [Customer_Number] durchgeführt werden muss. Ebenfalls auffällig ist, dass ein – teurer – Hash Join verwendet wird, obwohl nur eine kleine Menge an Daten (5 Datensätze) zurück geliefert werden. Der Query Optimizer von Microsoft SQL Server entscheidet sich für einen Hash Join, da von der “Probe Phase” alle Datensätze erwartet werden. Dieser Umstand ist der expliziten Typenkonvertierung (durch Änderung der Sortierung) geschuldet, die durch die Angabe einer COLLATE-Anweisung hinter dem Attribut erzwungen wird. Damit wird Microsoft SQL Server gezwungen, in JEDEM Datensatz aus [dbo].[Customer] für das Attribut [Customer_Number] einen Typenkonvertierung durchzuführen.

Technisch wird im Arbeitsspeicher eine Hash-Tabelle angelegt, in der alle [Customer_Number] der temporären Tabelle abgelegt werden (Build-Phase). Anschließend vergleicht Microsoft SQL Server JEDEN Eintrag in [Customer_Number] aus [dbo].[Customers] mit den Einträgen in der Hash-Tabelle und bei Übereinstimmung wird der entsprechende Datensatz ausgewählt (Probe-Phase).

Test 3 – JOIN mit COLLATE auf [#Customer]

Eine Möglichkeit, die Laufzeit für die Abfrage deutlich zu verkürzen, ist es, den Konvertierungsvorgang von der “größeren” Tabelle [dbo].[Customers] auf die temporäre Tabelle [#Customer] zu verlagern, da in dieser Tabelle lediglich 5 Datensätze vorhanden sind.

SELECT  C.*

FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN

        ON (C.Customer_Number = CN.Customer_Number COLLATE Latin1_General_BIN);

Die Abbildung zeigt, dass sich auf Grund der geänderten Konstellation für die Sortierung der Ausführungsplan deutlich geändert hat. Die Anzahl des IO ist zwar höher, kann aber in diesem Fall vernachlässigt werden, da der – teure – HASH JOIN durch einen NESTED LOOP ausgetauscht wurde. Durch den NESTED LOOP werden die IO für [dbo].[Customers] funktional bedingt höher sein, da für jeden Eintrag der äußeren Tabelle [#Customer_Number] der B-Tree des verwendeten Index von [dbo].[Customer] durchsucht werden muss. Vier Werte (10002, 10005, 10009, 10010) in der “äußeren” Tabelle [#Customer_Number] erzeugen jeweils 2 IO (1 IO = Root, 1 IO = Data) für den Zugriff auf die auszugebenen Daten.

Obwohl das I/O höher ist, ändert sich die Ausführungszeit für diesen Zugriff erheblich. Die nachfolgende Abbildung zeigt das Verhältnis der Ausführungen (geschätzte Werte). Ein NESTED LOOP ist eine deutlich schnellere Operation.

Unabhängig von der gewählten Variante bleibt jedoch ein Punkt festzuhalten, der immer zu beachten ist: Jede Variante hindert den Query Optimizer von Microsoft SQL Server daran, Statistiken zu verwenden, die eine optimale Abfragestrategie zulassen.

Test 4: Definition der Sortierung in der temporären Tabelle

Statt – wie in den vorherigen Beispielen gezeigt – die Sortierung in der Abfrage zu bestimmen, sollte die Sortierung bereits bei der Erstellung der temporären Tabelle definiert werden. Das folgende Beispiel zeigt, welchen Einfluss diese Variante auf den Ausführungsplan hat:

DROP TABLE #Customer_Number;

GO

CREATE TABLE #Customer_Number (Customer_Number CHAR(5) COLLATE Latin1_General_BIN PRIMARY KEY);

GO

INSERT  INTO #Customer_Number

        ( Customer_Number )

VALUES  ( '10005' ),

        ( '10010' ),

        ( '10009' ),

        ( '10002' );

SELECT  C.*

FROM    dbo.Customers AS C INNER JOIN #Customer_Number AS CN

        ON (C.Customer_Number = CN.Customer_Number);

Die gültige Sortierung der Datenbank wird bei der Definition der temporären Tabelle angegeben. Anschließend werden erneut die Beispieldaten geladen und die Abfrage wird ausgeführt. Da nun die Sortierung von temporärer Tabelle [#Customer_Number] identisch ist mit der verwendeten Sortierung in der Tabelle [dbo].[Customers], wird keine explizite Angabe der Sortierung mehr benötigt – der Abfrageplan zeigt diese Änderung eindrucksvoll.

Das I/O hat sich gegenüber Test 3 nicht verändert (warum auch, die Abfragestrategie hat sich ja nicht geändert). Dennoch ist diese Abfrage noch einmal deutlich schneller (~90%) als die Abfrage in Test 3. Die Berechnungsoperation (COMPUTE SCALAR) ist also doch nicht so “billig”, wie im Ausführungsplan gezeigt.

Zusammenfassung

Konflikte in der Sortierung lassen sich nicht vermeiden, wenn die eigene Datenbank von der Standardsortierung von Microsoft SQL Server abweicht und mit temporären Tabellen gearbeitet wird. Um diese Konflikte zu lösen, besteht die Möglichkeit, unmittelbar mit COLLATE das Verhalten einer Abfrage zu steuern. Ist man gezwungen, die Sortierung selbst zu steuern, sollten zwei Möglichkeiten immer im Fokus stehen:

• Definition der benötigten Sortierung bereits bei der Definition der temporären Tabelle (ideal)
• Definition der Sortierung im JOIN auf der Seite mit der kleineren Datenmenge (in der Regel die temporäre Tabelle), um das I/O möglichst gering zu halten (INDEX SCAN).

Die oben beschriebenen Konflikte in der Sortierung gelten natürlich nicht nur für JOIN Operatoren; auch bei Einschränkungen (WHERE-Klausel) gilt es, die obigen Regeln zu beachten. Deshalb IMMER den Ausführungsplan und seine Eigenschaften im Auge behalten, wenn man den Eindruck hat, dass eine Abfrage schneller ausgeführt werden könnte – eventuell liegt ja ein Konflikt in der Sortierung vor, wenn es Textvergleiche betrifft.

Herzlichen Dank fürs Lesen!