Blog
Monday, 07. December 2020

Techniken zur Abfrageoptimierung in SQL Server

Anna
Teamleitung Website & Content

Problembeschreibung
Das Beheben von fehlerhaften Abfragen und/oder Leistungsproblemen kann stundenlange, aufwendige Arbeit werden. Wir zeigen Ihnen wie man allgemeine Entwurfsmuster identifiziert, die auf eine schlechte Leistung von T-SQL hinweisen.

OR- in der Join Predicate – WHERE über mehrere Spalten hinweg
Ein SQL Server kann mithilfe der WHERE-Klausel und/oder einer Kombination aus verschiedenen Filtern einen Datensatz auf ein gewünschtes Abfrageergebnis effizient filtern. Mit diesem Vorgang werden Dateninformationen in immer kleinere Gruppen unterteilt, bis nur noch die gewünschte Ergebismenge übrig bleibt.

Die OR-Klausel ist im Gegensatz zur WHERE-Klausel nicht so effizient. Die OR-Klausel kann nämlich nicht in einem einzigen Vorgang abgeschlossen werden – stattdessen muss jede Komponente unabhängig betrachtet und bewertet werden. Ist das geschehen, können Ergebnisse verkettet und zurückgegeben werden. Die Leistung der OR-Klausel wird stetig schlechter, je mehr Spalten und Tabellen betroffen sind.

Dieses simple Beispiel zeigt, wie eine OR-Klausel zu einer schlechten TSQL Leistung führen kann:

SELECT DISTINCT
    PRODUCT.ProductID,
    PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.ProductID = DETAIL.ProductID
OR PRODUCT.rowguid = DETAIL.rowguid;

Die Abfrage beinhaltet 2 Tabellen und einen Join, der sowohl die ProductID als auch Rowguid überprüft. Selbst wenn keine dieser Spalten indiziert wäre, würden wir sowohl einen Tabellenscan für Product als auch einen für SalesOrderDetail erwarten. Folgende Leistung resultierte aus der durchgeführten Anfrage:

Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Die Verarbeitung der OR-Klausel erforderte beim Tabellenscan eine enorm hohe Rechenleistung. Insgesamt wurden 1,2 Mio. Lesevorgänge durchgeführt. Addieren wir die Zeilen von Product als auch OrderDetail erreichen wir bei Weitem nicht die Datenmenge, die bei 1,2 Millionen Lesevorgängen betrachtet werden. Insgesamt dauerte die Abfrage auf einem relativ schnellen SSD-Desktop ca. 2 Sekunden.

Dieses Beispiel könnte zur Folge haben, dass ein SQL Server oder eine OR-Klausel nicht über mehrere Spalten hinweg arbeiten kann. Der beste Weg damit umzugehen wäre, die Klausel zu entfernen oder die Abfragen in kleinere Schritte aufzuteilen. Oftmals ist die Aufteilung der Abfrage die beste Wahl:

SELECT
    PRODUCT.ProductID,
    PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.ProductID = DETAIL.ProductID
UNION
SELECT
    PRODUCT.ProductID,
    PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.rowguid = DETAIL.rowguid
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Nun haben wir jede Komponente der OR-Bedingung in eine SELECT Anweisung umgewandelt. UNION verkettet die Ergebnismenge und entfernt alle Duplikate. Unser Ergebnis sieht dann folgendermaßen aus:

Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Die Ausführung wurde dadurch erheblich komplexer, da wir jede Tabelle nun zwei mal abfragen mussten. Allerdings konnten die Lesevorgänge von 1,2 Million auf 750 reduziert werden – und das in unter einer Sekunde. Trotzdem sind weiterhin Unmengen an Indexscans im Abfrageplan enthalten. Je öfter wir die Tabellen im Abfrageplan scannen, desto besser kann die Performance werden.

Sie sollten vorsichtig bei der Erstellung einer Abfrage mit der OR-Klausel sein. Testen Sie regelmäßig, dass die Leistung angemessen und nicht zu hoch ist. Wenn Sie eine schlecht funktionierende Anwendung überprüfen und eine OR Klausel über verschiedene Spalten und Tabellen laufen lassen, können Sie sie dies als mögliche Ursache der Leistungsprobleme festmachen. Dies ist ein relativ leicht zu identifizierendes Abfragemuster, welches häufig zu einer schlechten Leistung führt.

Wildcard String Searches
Die effiziente Suche nach Zeichenfolgen kann herausfordernd sein. Hierbei gibt es einige Möglichkeiten.

Folgende Voraussetzungen zur Abfrage von Zeichenfolgen müssen gegeben sein:

  • Indexe sind in den durchsuchten Spalten vorhanden
  • Indexe können verwendet werden
  • Falls nicht, können dann Volltextindexe verwendet werden?
  • Falls nicht, können wir Hashes, n-Grams oder eine andere Lösung verwenden?

Ohne die Verwendung zusätzlicher Funktionen oder Entwürfen kann SQL Server nicht nach Zeichenfolgen suchen. Das heißt, wenn wir die Existenz einer Zeichenfolge an einer beliebigen Position innerhalb einer Spalte erkennen möchten, ist die Verwendung der nachfolgenden Abfrage nicht sinnvoll:

SELECT
    Person.BusinessEntityID,
    Person.FirstName,
    Person.LastName,
    Person.MiddleName
FROM Person.Person
WHERE Person.LastName LIKE '%For%';

Bei dieser Zeichenfolge überprüfen wir innerhalb der Spalte LastName das Auftreten von „For“. Wenn sich ein „%“ am Anfang des Strings befindet, ist es quasi unmöglich einen aufsteigenden Index zu verwenden. Wenn sich das „%“ am Ende der Zeichenfolge befindet, ist die Verwendung eines absteigenden Index nicht möglich.
Die obige Abfrage führt zu folgendem Ergebnis:

Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Wie erwartet führt die Abfrage zu einem Scan von Person.Person. Die einzige Möglichkeit festzustellen, ob eine Teilzeichenfolge in einer Textspalte vorhanden ist, besteht darin, jedes Zeichen in der Abfolge zu suchen. Bei einer kleineren Tabelle mag dies noch machbar sein, aber bei großen Datenmengen ist das schier unmöglich.

Es gibt jedoch verschiedene Möglichkeiten die Situation zu umgehen:

  • Bewerten Sie die Anwendung neu. Müssen wir wirklich eine Platzhaltersuche durchführen? Möchten wir wirklich alle Teile dieser Spalte nach einer bestimmten Zeichenfolge durchsuchen?

  • Können wir, bevor der Vergleich durchgeführt wird, andere Filter anwenden, um die Datenmenge zu reduzieren?

  • Können wir anstelle der Platzhaltersuche eine Zeichenfolgensuche durchführen?

  • Können wir „%for%“ in „%for“ ändern?

  • Ist die Volltextindizierung eine verfügbare Option?

  • Können wir eine Hash-Abfrage oder N-Gramm Lösung implementieren?

    Die ersten 3 Optionen sind dabei eher eine Design-/Architekturüberlegung. Um die Abfrage durchführen zu können benötigt es nämlich ein gewisses Maß an Anwendungskenntnissen oder die Fähigkeit zurückgegebene Daten ändern zu können. Sind die Kenntnisse und Fähigkeiten nicht vorhanden, wird es schwer ohne weitere Ressourcen die Suche nach den benötigten Zeichenfolgen durchzuführen.

Die Volltextindizierung ist eine weitere Funktion in SQL Server, mit der Indexe für eine flexible Suche nach Zeichenfolgen in Textspalten generiert werden können. Diese Funktion umfasst das Platzhalter-Suchen, aber auch die Suche nach Regeln, die eine bestimmte Sprache verwenden. Diese Sprache trifft Entscheidungen, ob bestimmte Abfragen ähnlich genug sind, um als Übereinstimmung angesehen zu werden. Für String-zentrierte Anwendungen kann dies die perfekte Lösung sein.

Eine letzte mögliche Option könnte eine passende Lösung für kürzere Zeichenfolgenspalten sein. N-grams sind Segmente, die getrennt von den gesuchten Daten gespeichert werden. Sie ermöglichen die Suche nach Zeichenfolgen ohne gleich eine ganze Tabelle scannen zu müssen. Auch hier gibt es wieder Überlegungen, die wir im Vorhinein bewerten sollten:

  • Gibt es eine minimale oder maximale Anzahl an Zeichen, die zulässig sind?

  • Sind leere Suchvorgänge zulässig?

  • Sind mehrere Wörter/Sätze erlaubt?

  • Müssen wir Textzeichenfolgen am Anfang eines Strings speichern?

    Haben wir die Situation beurteilt, können wir eine Zeichenfolgenspalte in Segmente aufteilen. Beispielsweise suchen wir nach dem gespeicherten Wort „Garten“, bei einer Suchlänge von mindestens 3 Zeichen. Die Indexsuche würde uns folgende Ergebnisse präsentieren: art, rte, ten. Der Anfang der Zeichenfolge wird in dem Fall ignoriert. Wenn wir jetzt eine separate Tabelle erstellen, in der jede dieser Teilzeichenfolge (= n-gram) gespeichert ist, können wir dieses n-gram mit der Zeile unserer großen Tabelle verknüpfen, die das Wort „Garten“ enthält. Anstatt in einer großen Tabelle nach Ergebnissen zu suchen, können wir so eine Gleichheitssuche für die n-gram Tabelle durchführen und kommen somit schneller zum gewünschten Ergebnis. Führen wir zusätzlich eine Platzhaltersuche nach „Garten“ durch, kann die Suche folgendermaßen umgeleitet werden:

SELECT
    n_gram_table.my_big_table_id_column
FROM dbo.n_gram_table
WHERE n_gram_table.n_gram_data = 'Garten';

Angenommen n-gram-data ist indiziert, werden aus der großen Tabelle alle IDs, die das Wort „Garten“ enthalten, ausgegeben. Die n-gram Tabelle können wir mithilfe der oben definierten Anwendungsregel auf 2 Spalten begrenzen. Damit garantieren wir, bei einer noch so großen Tabelle, schnelle Suchergebnisse.

Der Nachteil dabei ist, dass die n-gram Tabelle stetig aktualisiert werden muss, sobald eine neue Zeile eingefügt, gelöscht oder darin enthaltene Zeichenfolgen geändert wurden. Daher empfiehlt sich die n-gram Tabelle hauptsächlich für kürzere Zeichenfolgen wie z.B. Namen, Postleitzahlen oder Telefonnummern und weniger für komplexere längere Zeichenfolgen wie z.B. Email Texte, Freiform und Beschreibungen. Zusammenfassend können wir also festhalten, dass Platzhaltersuchen aufwendiger sind. Die besten Maßnahmen dagegen sind beispielsweise die frühe Eliminierung von „%“, eine radikale Einschränkung der Suche und Implementierung von Filtern. Bei kleinen Tabellen und kurzen Zeichenfolgen empfiehlt sich die Nutzung von n-gram Daten, die eine aufwendigere Implementierung von diversen Filtern umgehen würde.

Große Schreibvorgänge
Wir haben festgestellt, dass häufigere Iterationen zu schlechten Leistungen führen können. Folgend untersuchen wir ein Szenario, in dem jede Iteration die Leistung verbessern könnte.

Wenn wir eine Abfrage starten, werden in der Regel Sperren für bestimmte Datenmengen gesetzt, die garantieren, dass die Ergebnisse am Ende konsistent sind und eventuell gleichzeitig geführte Abfragen das Ergebnis nicht beeinträchtigen. Diese Blockaden sind insofern hilfreich, da sie Daten vor Korruption schützen und fehlerhafte oder schlechte Ergebnisse verhindern. Ist die Abfrage aber so umfangreich, dass sie einen langen Zeitraum in Anspruch nimmt, müssen andere wichtige Abfragen warten, da sie ggf. auf dieselbe Datenmenge zugreifen müssen. Große Schreibvorgänge sind maßgeblich für Konflikte verantwortlich, da sie häufig große Datenmengen oder gleich ganze Tabellen sperren. Somit können in dieser Zeit keine Änderungen oder Aktualisierungen vorgenommen werden.

„Groß“ ist hier allerdings relativ, denn es kommt auf die in der Tabelle enthaltenen Einschränkungen an. Hier ist die einzige Möglichkeit, den Vorgang zu testen, zu beobachten und entsprechend zu reagieren. Behalten Sie zusätzlich das Transaktionsprotokoll im Auge, denn stark wachsende Protokolldateien führen zu großen Datenmengen und im schlimmsten Fall zu einer Füllung des physischen Speichertorts. An einigen großen Schreibvorgängen sind wir selbst Schuld. Software Releases, Data Warehouse Ladeprozesse oder Vergleichbares müssen oftmals große Datenmengen schreiben. Da liegt es an uns, inwiefern wir große Datenmengen an einem ausgelasteten Produktionsstandort schreiben lassen oder ob wir nicht eher die Vorgänge reduzieren indem wir Konflikte ermitteln und sie im Voraus versuchen zu vermeiden.

Folgende Vorgänge führen zu großen Schreibvorgängen:

  • Hinzufügen einer neuen Spalte in einer Tabelle
  • Aktualisierung einer gesamten Spalte
  • Änderung des Datentyps einer Spalte
  • Importieren von großen Mengen neuer Daten
  • Archivierung oder Löschung einer großen Menge alter Daten

Fehlende Indexe
SQL Server informiert uns über das Management Studio, wenn bestimmte Indexe fehlen, die möglicherweise zu einer besseren Leistung der Abfrage beitragen könnten.

Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Dieser Hinweis ist insofern hilfreich, da er uns über mögliche bessere Lösungen zur Verbesserung der Abfrageleistung unterrichtet. Sichtbar wird dies im grünen Text auf dem oberen Bild, der uns einen vermeintlich besseren Index anzeigt. Doch bevor wir den vorgeschlagenen Index nutzen, sollten wir folgende Dinge hinterfragen:

  • Gibt es ähnliche Indexe, die die Anwendung durchführen könnten?
  • Benötigen wir alle Include-Spalten?
  • Wäre ein Index nur für die Sortierung der Spalten gut genug?
  • Wie hoch ist die Auswirkung des Index und gibt es eine tatsächliche Verbesserung der Abfrage?
  • Existiert dieser Index bereits, wird aber nicht vom Abfrageoptimierer ausgewählt?

Oftmals sind die vorgeschlagene Indexe zu maßlos, wie in dem im obigen Beispiel vorgeschlagenen Befehl zum Erstellen eines Indexes deutlich wird:

CREATE NONCLUSTERED INDEX <Name of Missing Index, sysname,>
ON Sales.SalesOrderHeader (Status,SalesPersonID)
INCLUDE (SalesOrderID,SubTotal)

In diesem Fall gibt es bereits einen Index für SalesPersonID. Status ist zufällig auch eine Spalte, in der in der Regel Werte enthalten sind, sodass die Sortierspalte kein aussagekräftiges Ergebnis liefert. Wir haben hier eine Verbesserung von knapp 19% erreicht, was nicht beeindruckend ist. Letztlich müssen wir jedoch hinterfragen, ob sich die Verbesserung von 19% rechtfertigt und sich dafür de Aufwand lohnt.

Betrachten wir nun eine alternative Indexempfehlung:

Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Hier wird folgender fehlender Index vorgeschlagen:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Person].[Person] ([FirstName])
INCLUDE ([BusinessEntityID],[Title])

In diesem Fall würden wir eine Verbesserung von 93% erreichen und die nicht indizierte Spalte (First Name) behandeln. Wenn die ausgeführte Abfrage häufiger durchgeführt wird, wäre die Verwendung des Indexes recht hilfreich und würde zu einer verbesserten Leistung führen. Fügen wir aber zusätzlich als Include-Spalten z.B. BusinessEntitiyID und Title hinzu, können wir die Leistung der Schreibvorgänge eventuell noch weiter verbessern.

Folgende Fehler sollten wir dennoch versuchen zu vermeiden:

Überindizierung einer Tabelle
Enthält eine Tabelle zu viele Indexe, verlangsamen sich die Schreibvorgänge, da jede Änderung eine indizierte Spalte berührt, die die Indexe wiederum aktualisieren müssen.

Unterindizierung einer Tabelle
Eine unterindizierte Tabelle ist für die Leseabfragen selten effektiv. Im Idealfall sollten häufige Abfragen von Indexen profitieren. Sie sollten demnach über ausreichend Indexe in Ihren Tabellen verfügen, um eine gute Leistung gewähren zu können.

Kein Clustered Index / Primärschlüssel
Clustered Indexe weisen meistens eine gute Leistung vor, daher sollten sie bestenfalls in einer Tabelle vorhanden sein. Sie bieten eine erforderliche Infrastruktur innerhalb der Tabelle und sorgen für effiziente Leistungen. Der Primärschlüssel liefert dazu dem Abfrageoptimierer wertvolle Informationen zur Erstellung von Ausführungsplänen. Sie sollten zusätzlich wissen, dass die GUI Ihnen nur den obersten Indexvorschlag präsentiert. Für weitere Vorschläge empfehlen wir Ihnen, sich die unformatierte XML des Ausführungsplans anzuschauen.

High Table Count
Das Abfrageoptimierungsprogramm in SQL Server steht wie jedes relationale Abfrageprogramm vor der gleichen Herausforderung: Es muss innerhalb kürzester Zeit, in Unmengen an Optionen, den besten Ausführungsplan finden. Je mehr Tabellen in der Abfrage existieren, desto mehr Aufwand muss das Optimierungsprogramm betreiben. Jeder Satz von Tabellen muss mit entsprechenden Anforderungen verknüpft werden. Dafür gibt es zwei Grundformen, wie eine solche Verknüpfung aussehen kann:

  • Left-Deep-Tree: A -> B, B -> C, C -> D, usw. Diese Abfrage verbindet die Tabellen nacheinander
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server
  • Bushy Tree: A -> B, A -> C, B ->D, C-> E usw. In dieser Abfrage werden Tabellen in mehrere logische Einheiten aufgeteilt und miteinander verknüpft.
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Der Left-Deep-Tree ist natürlicher geordnet und gibt im Vergleich zum BushyTree eine geringeres Ergebnis zu den Kandidatenausführungsplänen aus.

Hinter diesen Tabellen steckt komplexe Mathematik, die wir mit folgender Abfrage verdeutlichen möchten:

SELECT TOP 25
    Product.ProductID,
    Product.Name AS ProductName,
    Product.ProductNumber,
    CostMeasure.UnitMeasureCode,
    CostMeasure.Name AS CostMeasureName,
    ProductVendor.AverageLeadTime,
    ProductVendor.StandardPrice,
    ProductReview.ReviewerName,
    ProductReview.Rating,
    ProductCategory.Name AS CategoryName,
    ProductSubCategory.Name AS SubCategoryName
FROM Production.Product
INNER JOIN Production.ProductSubCategory
ON ProductSubCategory.ProductSubcategoryID = Product.ProductSubcategoryID
INNER JOIN Production.ProductCategory
ON ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
INNER JOIN Production.UnitMeasure SizeUnitMeasureCode
ON Product.SizeUnitMeasureCode = SizeUnitMeasureCode.UnitMeasureCode
INNER JOIN Production.UnitMeasure WeightUnitMeasureCode
ON Product.WeightUnitMeasureCode = WeightUnitMeasureCode.UnitMeasureCode
INNER JOIN Production.ProductModel
ON ProductModel.ProductModelID = Product.ProductModelID
LEFT JOIN Production.ProductModelIllustration
ON ProductModel.ProductModelID = ProductModelIllustration.ProductModelID
LEFT JOIN Production.ProductModelProductDescriptionCulture
ON ProductModelProductDescriptionCulture.ProductModelID = ProductModel.ProductModelID
LEFT JOIN Production.ProductDescription
ON ProductDescription.ProductDescriptionID = ProductModelProductDescriptionCulture.ProductDescriptionID
LEFT JOIN Production.ProductReview
ON ProductReview.ProductID = Product.ProductID
LEFT JOIN Purchasing.ProductVendor
ON ProductVendor.ProductID = Product.ProductID
LEFT JOIN Production.UnitMeasure CostMeasure
ON ProductVendor.UnitMeasureCode = CostMeasure.UnitMeasureCode
ORDER BY Product.ProductID DESC;

Bei 12 Tabellen würde folgende mathematische Formel folgende Anzahl an Ausführungsplänen ergeben : (2n-2)!/ (n-1)! = (2*12-1)! /(12-1)! = 28.158.588.057.600

Wäre die Abfrage linearer Natur gewesen, würde sich folgende mathematische Formel ergeben: n! = 12! = 479.001.600

.. und dies bei nur 12 Tabellen. Stellen Sie sich vor, Sie haben mehr als 12 Tabellen.

Es gibt einige Möglichkeiten, die Abfragen zu optimieren:

  • Verschieben Sie die Metadaten in eine separate Abfrage, die die Daten in eine temporäre Tabelle einfügt
  • Verknüpfungen können in einen anderen Parameter oder eine Variable verschoben werden
  • Teilen Sie große Abfragen in kleinere Abfragen auf, deren Datensätze später zusammengefügt werden können
  • Berücksichtigen Sie eine indizierte Ansicht der Abfragen
  • Entfernen Sie nicht benötigte Tabellen, Unterabfragen und Verknüpfungen

Während des Aufteilens einer großen Abfrage in eine kleinere, dürfen keine Daten geändert werden, da ansonsten eine ungültige Ergebnismenge resultieren würde. Die Beispielabfrage, wie oben mit 12 Tabellen durchgeführt, würde dann entsprechend in zwei separate Abfragen aufgeteilt werden und folgendermaßen aussehen:

SELECT TOP 25
    Product.ProductID,
    Product.Name AS ProductName,
    Product.ProductNumber,
    ProductCategory.Name AS ProductCategory,
    ProductSubCategory.Name AS ProductSubCategory,
    Product.ProductModelID
INTO #Product
FROM Production.Product
INNER JOIN Production.ProductSubCategory
ON ProductSubCategory.ProductSubcategoryID = Product.ProductSubcategoryID
INNER JOIN Production.ProductCategory
ON ProductCategory.ProductCategoryID = ProductSubCategory.ProductCategoryID
ORDER BY Product.ModifiedDate DESC;

SELECT
    Product.ProductID,
    Product.ProductName,
    Product.ProductNumber,
    CostMeasure.UnitMeasureCode,
    CostMeasure.Name AS CostMeasureName,
    ProductVendor.AverageLeadTime,
    ProductVendor.StandardPrice,
    ProductReview.ReviewerName,
    ProductReview.Rating,
    Product.ProductCategory,
    Product.ProductSubCategory
FROM #Product Product
INNER JOIN Production.ProductModel
ON ProductModel.ProductModelID = Product.ProductModelID
LEFT JOIN Production.ProductReview
ON ProductReview.ProductID = Product.ProductID
LEFT JOIN Purchasing.ProductVendor
ON ProductVendor.ProductID = Product.ProductID
LEFT JOIN Production.UnitMeasure CostMeasure
ON ProductVendor.UnitMeasureCode = CostMeasure.UnitMeasureCode;

DROP TABLE #Product;

Diese Separation ermöglicht uns komplexe Abfragen zu vereinfachen. Denn wie wir bereits herausgefunden haben, trägt eine hohe Anzahl an Tabellen innerhalb einer Abfrage zu schlechten Ausführungsplänen des Abfrageoptimierers bei.

Query Hints
Ein Query Hint ist eine explizite Anweisung von uns an den Optimierer. Wir umgehen damit einige Regeln, die vom Abfrageoptimierer verwendet werden. Die Query Hints werden häufig bei Leistungsproblemen verwendet, um sie zu beseitigen. Sie wirken sich unter anderem auf Isolationsstufen, Verknüpfungstypen und Tabellensperrungen aus. Jedoch können sie auch Gefahren verursachen, wie nachfolgend aufgelistet:

  • Zukünftige Änderungen an Daten oder dem Schema können dazu führen, dass der Hinweis nicht anwendbar ist und behindert wird
  • Hints können größere Probleme wie fehlende Indexe oder übergroße Datenanforderungen verschleiern.
  • Query Hints können zu unerwarteten, fehlerhaften Lesevorgängen führen
  • Die Anwendung des Query Hints könnte zu Leistungseinbußen führen

Daher lautet die allgemeine Faustregel, Query Hints so wenig wie möglich anzuwenden und wenn überhaupt, dann erst nach ausgiebiger Untersuchung. Folgend liefern wir Ihnen einige Informationen zu den häufig verwendeten Query Hints:

NOLOCK: Sind Daten gesperrt, weist dieser Hinweis SQL Server an, den zuletzt bekannten Wert zu lesen. Auch als Dirty read bekannt.

  • RECOMPILE: Wenn Sie den Befehl am Ende einer Abfrage hinzufügen, wird bei jeder Ausführung dieser Abfrage ein neuer Ausführungsplan generiert. Dies empfiehlt sich jedoch nur für Abfragen, die nicht häufig durchgeführt werden.

  • MERGE / HASH / LOOP: Mit diesem Befehl wird der Abfrageoptimierer angewiesen, einen bestimmten Join-Typ zu verwenden. Allerdings ist dieses Vorhaben recht riskant, da sich die Verknüpfung verändert, wenn Daten, Schema oder Parameter sich im Laufe der Zeit ändern.

  • OPTIMIZE FOR: Dieser Befehl gibt einen Parameterwert an, für den die Abfrage optimiert werden soll. Meistens wird er für häufig durchgeführte Abfragen genutzt, damit der Cache nicht unnötig belastet werden muss.

Betrachten wir in diesem Beispiel unsere vorherige Abfrage:

SELECT 
  e.BusinessEntityID,
  p.Title,
  p.FirstName,
  p.LastName
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE FirstName LIKE 'E%'

Erzwingen wir nun einen MERGE-Join in unserer Abfrage, können wir unter Umständen eine bessere Leistung beobachten:

SELECT 
  e.BusinessEntityID,
  p.Title,
  p.FirstName,
  p.LastName
FROM HumanResources.Employee e
INNER MERGE JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE FirstName LIKE 'E%'
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Für eine einfache Abfrage ist das Ergebnis nicht sonderlich repräsentativ, denn unser Join-Typ hat nur eine begrenzte Indexnutzung. Durch den MERGE-Join wurden dem Ausführungsplan zusätzliche Operatoren hinzugefügt, die die Ausgaben der Ergebnismenge sortieren. Dasselbe können wir auch mit dem HASH-Join erreichen:

SELECT 
  e.BusinessEntityID,
  p.Title,
  p.FirstName,
  p.LastName
FROM HumanResources.Employee e
INNER HASH JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
WHERE FirstName LIKE 'E%'
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server
Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Wir bekommen nun eine Warnung auf der Registerkarte „Ausgabe“, die uns mitteilt, dass die Verknüpfungsreihenfolge durch unsere Auswahl erzwungen wurde. Das ist in diesem Fall hilfreich, da der von uns ausgewählte Join-Typ die Möglichkeit zum Ordnen der Tabelle eingeschränkt hat. Im Wesentlichen haben wir also einige nützliche Tools entfernt und den Optimierer dazu gezwungen, mit deutlich weniger Optionen zu arbeiten, als es erforderlich ist.

Haben wir alle Hints entfernt, nutzt der Abfrageoptimierer einen NESTED-LOOP Join und erzielt folgende Leistung:

Mainzer Datenfabrik - Techniken zur Abfrageoptimierung in SQL Server

Solche Hints werden häufig als schnelle Lösung für komplexe Probleme angewandt. Man sollte diese jedoch eher als letzte Möglichkeit in der Hinterhand haben, denn Hints sind zusätzliche Abfrageelemente, die regelmäßig gewartet und überprüft werden müssen. Hilfreich ist es, die Verwendung der Hints für die Zukunft zu dokumentieren.

Fazit

In diesem umfangreichen Artikel haben wir Ihnen einige Abfragefehler aufgezeigt, die zu einer schlechten Leistung in SQL Server führen können. Wir können damit vermeiden, dass sich unsere Reaktionszeit auf Latenz- & Leistungsnotfälle unnötig verlängert. Durch die Bereinigung von JOINs, WHERE-Klauseln und das Aufteilen der Abfragen in kleinere Abschnitte, verbessern wir zusätzlich die Qualität unserer Ergebnisse.

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

Kontaktieren Sie uns gerne über das Kontaktformular und vereinbaren ein unverbindliches Beratungsgespräch mit unseren Berater:innen zur Bedarfsevaluierung. Gemeinsam optimieren wir Ihre Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!

Taunusstraße 72
55118 Mainz
info@madafa.de
+49 6131 3331612
Bürozeiten
Montag bis Donnerstag:
9:00 - 17:00 Uhr MEZ

Freitags:
9:30 - 14:00 Uhr MEZ