MS SQL Server: Sortierungskonflikte und deren Auswirkungen auf Ausführungspläne
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 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 Abb.1 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);
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 eine Typenkonvertierung für die Spalte [Customer_Number] durchgeführt werden muss. Ebenfalls auffällig ist, dass ein – teurer – Hash Join [1] verwendet wird, obwohl nur eine kleine Menge an Daten (5 Datensätze) zurückgeliefert 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] eine 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 IO 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 der temporären Tabelle [#Customer_Number] identisch ist mit der verwendeten Sortierung in der Tabelle [dbo].[Customers], wird keine explizite Angabe der Sortierung mehr benötigt – der Ausführungsplan zeigt diese Änderung eindrucksvoll.
Das IO 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 Prozent) 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 IO 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.