Durch unser SQL-Server Assessment wird in vielen Datenbanken folgende Fehlkonfiguration entdeckt: Es existieren Tabellenspalten mit Textinhalt, also beispielsweise dem Datentyp nvarchar. Diese Spalte wird offensichtlich sehr häufig abgefragt und der Entwickler hat diese Spalte zur Optimierung der Abfrage indiziert. Grundsätzlich ist Indizierung ein sehr wirksames Mittel zur Leistungssteigerung von Abfragen, aber im Bereich von textuellen Suchen eher kontraproduktiv. Details, warum das so ist und welche Alternative es gibt, werden in diesem Beitrag behandelt.
Verwendung herkömmlicher Indizes für Textspalten
Die Verwendung herkömmlicher Indizes für Textspalten kann einen positiven Effekt auf die Performance von Abfragen haben, ist aber in den meisten Fällen unwirksam: Applikationen bzw. Applikationsbenutzer verwenden zur Abfrage von Daten mit bestimmten Eigenschaften oft Wildcards, also Platzhalter für ein beliebiges ‘_' oder mehrere beliebige Zeichen ‘%’. Befinden sich diese am Beginn der Zeichenkette, so kann der Index nicht performant genutzt werden. Es ist ein dann ein relativ langsamer Index Scan erforderlich, während andernfalls ein performanterer Index Seek verwendet werden kann. Als Beispiel wird hier die Tabelle [Person].[Address] der Microsoft Beispieldatenbank AdventureWorks2022 verwendet in deren Spalte AddressLine1 die Hausnummer und ein Straßenname zu einer Adresse gespeichert sind. Hier existiert eine Zeile mit dem Spalteninhalt '1226 Shoe St.’.
Wird hier eine Suche nach dem Straßennamen mit like ‘1226 Shoe%’ verwendet, so ergibt sich der folgende Ausführungsplan:
Der Ausführungsplan zeigt einen Index Seek (performant) dessen Ergebnisse über einen Key Lookup in der Tabelle Address ermittelt werden. Die Gesamtkosten des Zugriffs betragen 0,0082568.
Verwendet man das Prozentzeichen zusätzlich zu Beginn der zu suchenden Adresse, so ergibt sich die Kombination aus einem Index Scan (nicht performant) und einem Key Lookup. Die Gesamtkosten betragen 0,203387, also etwa ein 25-faches der ersten Abfrage. Das führende Wildcard-Zeichen hat hier also zu einem erwarteten Performanceverlust geführt. “Erwartet”, weil der Index wie das Nachschlagen eines Namens in einem Telefonbuch funktioniert und auch hier wäre eine Durchsicht des gesamten Telefonbuchs erforderlich, wenn nach einer Person gesucht würde, deren Nachname beispielsweise ein ‘ller’ bzw. ‘%ller%’ (für Haller, Müller, Schiller, …) enthielte.
Wenn Sie sich Aufbau und Funktionsweise eines Index ansehen, werden Sie sehen, dass der hier beobachtete Performanceunterschied nicht überraschend ist. Im Fall von Indizes auf Textspalten kommt eine Eigenschaft hinzu, die sich ungünstig auswirkt: Textspalten können unter Umständen sehr lang werden und blähen den Index dementsprechend auf. In der hier betrachteten Tabelle Person.Address kommt hinzu, dass der Index nicht nur auf die Spalte AddressLine1, sondern gleich auf 5 Spalten, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode gelegt wurde.
Der Index belegt mit 212 Seiten ca. 38% des Platzes, den die Tabelle selbst, also der Clustered Index benötigt (342 Seiten). Zur Information: Der vorhandene Index auf der rowguid-Spalte hat eine Größe von 65 Seiten.
Umstellung auf Volltextindizierung
Die Umstellung auf Suchen mit Volltext-Indizes erfordert einige Schritte, die in den folgenden Unterkapiteln beschrieben sind. Insbesondere muss aber auch berücksichtigt werden, dass ihre Verwendung eine Syntaxänderung erfordert. Die o.a. Abfragen mit Wildcards erfolgen über die like-Klausel, während Volltextsuche über die Prädikate:
CONTAINS()FREETEXT()CONTAINSTABLE()
und
FREETEXTTABLE()
erfolgen.
Installation der Volltextsuche
Sofern diese Komponente nicht bereits bei der Erstinstallation Ihrer SQL-Server Instanz mitselektiert wurde, muss die Volltext- und semantische Extraktion für die Suche nachinstalliert werden. In der nachfolgenden Abbildung wird dies für eine Express Edition gezeigt.
Einrichten eines Volltext-Kataloges
Anders als bei herkömmlichen Indizes werden Volltext-Indizes in Volltext-Katalogen organisiert. Hierbei handelt es sich um logische Container in denen diese Indexart organisiert und gruppiert werden kann. Wie bei Oracle sind auch bei SQL-Server die Algorithmen und internen Strukturen die zur Implementierung der Volltextsuche verwendet werden nicht offengelegt. Bei Oracle hat man aber zumindest Zugriff auf die zugrundeliegenden Tabellen, die ein Gewirr aus verschiedenen miteinander verknüpften Tabellen darstellen. Hier kann erahnen, wie komplex die Volltextsuche ist.
Zurück zu SQL-Server: Eine Datenbank kann mehrere solcher Kataloge haben, die mit den Eigenschaften default(-Katalog), Accent Sensitive und mit einem Eigentümer versehen werden können. Außerdem können sie bei ihrer Einrichtung einer dedizierten Dateigruppe zugeordnet werden, so dass verschiedene Volltextindizes bei Vorhandensein auf verschiedene Platten verteilt werden können. Genau einer dieser Kataloge ist als Default-Katalog konfiguriert. Wird die Default-Eigenschaft einem anderen Katalog zugeordnet so wird die Default-Eigenschaft des vorherigen Default-Katalogs automatisch auf False gesetzt. Der Versuch, einen Default-Katalog in der GUI des MSSQL-Management Studio auf nicht-default zu setzen wird von der GUI fehlerfrei akzeptiert, hat aber keine Auswirkung, es gibt auch in der T-SQL Syntax kein Konstrukt, mit dem man die Default-Eigenschaft deaktivieren kann.
Die Syntax zum Wechsel des Default-Katalogs beispielhaft:
Im Management Studio sind Volltext-Kataloge in dem Full Text Catalogs Unterknoten des Storage-Knotens zu finden.
Anlegen und Administrieren eines Volltext-Index
Wir wollen hier einen Volltext Index auf der Spalte JobTitle der Tabelle HumanResources.Employee erstellen.
Im Management Studio existiert im Kontextmenü (=rechte Maustaste über einer Tabelle) die Möglichkeit einen solchen Index anzulegen und zu administrieren.
Über das oben gezeigte Kontextmenü wird der Wizard zum Anlegen eines neuen Volltext-Index gestartet.
Die erste Seite stellt nur eine Willkommensseite dar, sie kann für kommende Aufrufe deaktiviert werden.
Auf der zweiten Seite muss ein eindeutiger Index der ausgewählten Tabelle selektiert werden. Die Verwendung eines solchen Index ist für Volltext-Indizes obligatorisch. Hier wurde der Primärschlüssel der Tabelle verwendet.
Auf der nächsten Seite müssen die Spalten selektiert werden, die der Index umfassen soll. Die Auswahl beschränkt sich auf Spalten mit Zeichen- oder Bild-basierten Inhalten/Datentypen. Optional kann eine Sprache gewählt werden, die für den sogenannten Word Breaker, auch als Tokenizer bezeichnet, verwendet werden soll. Hierbei handelt es sich um einen Mechanismus, der die Wörter innerhalb der jeweiligen Spalte(n) sprachspezifisch in Silben o.ä. Wortfragmente aufbricht, um diese dann effizient indizieren zu können.
Für dieses Beispiel wird nur die Spalte JobTitle mit Englischer Sprache verwendet. Die hier gewählte Option für Statistical Semantics setzt eine entsprechende zusätzliche Datenbank voraus, so dass sie hier nicht verwendet wird.
Auf der nächsten Seite kann ausgewählt werden, wie der Index aktualisiert werden soll: Die Pflege und Aktualisierung der Volltextindizes ist ein relativ aufwändiger Prozess, der in einer häufig aktualisierten Tabelle zu Performanceproblemen führen kann. Aus diesem Grund existiert die Möglichkeit, auszuwählen, ob die Aktualisierung automatisch, manuell oder überhaupt nicht erfolgen soll. Hier wählen wir für das Beispiel die Automatik.
Die folgende Seite bietet einige weitere Einstellungen, u.a. zu der zu verwendenden Stopliste. Eine Stopliste ist eine Liste von Wörtern, die bei der Verwendung der Textsuche nicht berücksichtigt werden sollen. Eine typische Stopliste in der deutschen Sprache enthielte beispielsweise bestimmte und unbestimmte Artikel, Konjunktionen usw.
Stoplisten können individuell konfiguriert werden. Stoplisten sind wie die Volltext-Kataloge über den Storage-Knoten im Management Studio erreichbar. Die Abfrage der Elemente der Stopliste für die deutsche Sprache kann über das folgende Statement erfolgen:
Auf der nächsten Seite kann ein Zeitplan für die Aktualisierung von Tabellen- und Katalogdaten angegeben werden.
Zum Abschluss erhält man einen Überblick über die durchgeführten Einstellungen:
Besonderheiten in Abfragen
Nicht nur die Syntax ändert sich mit der Volltext-Suche, sondern auch Art und Weise, wie die Suche durchgeführt wird. Während die like-Suche lediglich Zeichen und Zeichenketten kennt, kennt die Volltextsuche sprachspezifische Stammwörter und deren Flexion (Singular, Plural, Deklination, Konjugation). Dies macht die Textsuche sehr flexibel, was allerdings auch eine detaillierte Kenntnis über die syntaktischen Möglichkeiten und zugehörigen Fähigkeiten voraussetzt.
Hier ein sehr einfach gehaltenes Beispiel:
Für Details zur Syntax und zu den vielen Möglichkeiten die der CONTAINS-Operator bietet, sei auf die entsprechende Microsoft Dokumentation verwiesen. Hier findet man auch, wie oftmals in Microsofts Syntaxdiagrammen, am Ende der Seite einige Beispiele.
Verwendung von Synonymen
Auch die Möglichkeiten, nach Synonymen zu suchen, wird von der Volltextsuche angeboten. Hierzu muss man jedoch im Vorfeld die Listen möglicher ähnlicher Begriffe in einer Thesaurusdatei zusammenstellen. Diese sind in sprachspezifischen XML-Dateien im Ordner C:\Program Files\Microsoft SQL Server\MSSQL<Versionsnr.>.<Instanzname>\MSSQL\FTData zu finden. Alle Dateien mit dem Namen ts<Länderkürzel>.xml (tseng.xml für englisch, tsdeu.xml für deutsch) enthalten ein Grundmuster zur Angabe von Synonymen, das jedoch auskommentiert ist. Beginnen Sie also mit Ihrer Liste von Synonymen erst im Anschluss an diesen Kommentarblock, d.h. in der vorletzten Zeile oder entfernen Sie die beiden Zeilen mit dem Inhalt <!--Commented out“ und “-->.
Eine sprachspezifisch vorkonfigurierte Thesaurusdatei existiert übrigens leider nicht.
Zum Test wurden in der deutschen Synonymliste / Thesaurusdatei drei Synonymgruppen für Fahrzeug, Flugzeug und Schiff entsprechend folgendem Block angelegt:
Bei dieser XML-Datei erhält man als Ergebnis einer Suche nach Fahrzeug alle Inhalte mit den Begriffen Auto, Taxi oder Zug, aber nicht Fahrzeug. Soll auch der Begriff Fahrzeug gefunden werden, müssen die Tags <expansion> bzw. <sub> verwendet werden. Eine detaillierter Beschreibung des Inhalts von Thesaurusdateien finden Sie unter folgendem Link: Konfigurieren und Verwalten von Thesaurusdateien für die Volltextsuche - SQL Server
Nach Änderungen von Thesaurusdateien müssen diese neu in den MSSQL-Server geladen werden. Hierzu verwendet man die Stored Procedure sp_fulltext_load_thesaurus mit der sprachspezifischen ID (“LCID”). Die LCID kann in sys.fulltext_languages ermittelt werden.
Beispiele
Der folgende Codeblock enthält beispielhaft einige einfache Volltext-Abfragen mit dem Prädikat CONTAINS():
Performance
Im Bereich Performance gab es sehr interessante und überraschende Ergebnisse und für diejenigen Leser, die nun die Ausführungspläne der o.a. zwei Beispiel-Statements miteinander vergleichen und feststellen, dass das like niedrigere Kosten im Ausführungsplan besitzt (0,0081034 vs. 0,0176447): Das liegt lediglich an der relativ kleinen Employee-Tabelle mit nur 290 Zeilen. Die aufgeführten Kosten für eine Tabelle die aus dem 256-fachen der Zeilen aus HumanResources.Employee gebildet wurde sind 1,44942 bzw. 1,62522.
Bei 593920 Zeilen, also dem 2048-fachen, lagen die Kosten für das like ebenfalls leicht unterhalb derer für das CONTAINS. Eigenartigerweise lag die tatsächliche Ausführungszeit jetzt aber bei ca. 8 Sekunden für das like und ca. 1 Sekunde für das CONTAINS. Und bei 4.751.360 bzw. dem 16.384-fachen änderten sich die Kosten zu Gunsten des CONTAINS. Diese lagen jetzt bei 92,5875 vs. 43,8294, und Ausführungszeiten 1:03 beim like, während CONTAINS auf lediglich 8 Sekunden anstieg.
Eigenartig, dass sich hier die Kosten nicht in entsprechend hohen Ausführungszeiten reflektierten. Möglicherweise liegt hier aber auch ein Bug im Optimizer bei der Auswertung von Statistikdaten vor oder die Ursache liegt darin, dass die Freitextsuche durch externe DLLs erfolgen.
Bei dem hier festgestellten Verhalten musste natürlich auch das QueryStress Tool von Erik Ejlskov herhalten, das wir in unseren Blogartikeln schon sehr oft für Performancemessungen verwendet haben. Hiermit wurde das Verhalten für zehn parallele Sessions jeweils für like und für CONTAINS geprüft. Die Tabelle die eine 16.384-fache Kopie der Employee-Tabelle darstellt, haben wir vereinfacht ET genannt.
Die folgenden beiden Abbildungen zeigen die Ergebnisse, die Ergebnisse sprechen für sich.
Vielleicht auch interessant bei Performancebetrachtungen
Nach Datenänderungen in der Tabelle ET erschien in einigen Fällen ein Ausrufezeichen/eine Warnung im linken zusammenfassenden Knoten des CONTAINS-Ausführungsplan bezüglich des Memory Grant. Memory Grants werden i.d.R. vom Optimizer auf Basis der vorhandenen Daten, Ausführungspläne und Statistiken geschätzt und stellen den Hauptspeicher dar, der für temporäre Datenverarbeitung wie beispielsweise Sortieroperationen voraussichtlich benötigt wird. Liegen die Schätzwerte unterhalb der tatsächlich benötigen Werte, so muss im Verlauf der Abfrage zusätzlicher Speicher angefordert werden, was einerseits einen gewissen Overhead bedeutet, andererseits auch darin resultieren kann, dass der benötigte Speicher nicht mehr verfügbar ist. Hierauf bezieht sich die Formulierung “which may impact the reliability”.
Nach einem erneuten Aufruf des gleichen Statements hatte der Optimizer seinen Irrtum bei der Schätzung bemerkt und die Warnung trat nicht mehr auf. Das entsprechende Feature des Optimizers nennt sich Memory grant Feedback. In dem hier verlinkten Dokument kann man auch finden, dass in bestimmten Fällen Features im Zusammenhang mit dem Memory grant Feedback nur verfügbar sind, wenn der Query Store aktiviert und im Read/Write Mode betrieben wird. Ein nichtaktivierter Query Store ist übrigens einer der vielen Punkte, die unser Assessment prüft und vor dem es ggf. warnt.
Einige Views zur Freitextsuche
Die folgende Tabelle listet einige Views auf, die im Zusammenhang mit der Volltextsuche benötigt werden könnten. Volltext-Indizes sind keine eigenständigen Objekte, sondern sind eindeutig den entsprechenden Tabellen zugeordnet.
InhaltListe der Volltext-Kataloge einer Datenbank und ihrer EinstellungenListe der von SQL-Server unterstützten Dateitypen und Pfad der dll-Datei, mit der die Volltextsuche in dem entsprechenden Dokument durchgeführt wirdListe aller Tabellen, für die Volltext-Indizes existieren, der Index-IDs und zugehöriger Volltext-Kataloge (als IDs)Liste der Tabellenspalten und Tabellen die über einen Volltextindex durchsucht werden können. Wurden für eine Tabelle <N> Spalten für die Volltextsuche eingerichtet, so enthält diese View <N> Zeilen zu dieser Tabelle. Zu den Einträgen ist neben der Sprache für die der Index eingerichtet wurde auch der Dokumenttyp, vgl. sys.fulltext_document_types, als ID hinterlegt.Liste der Volltext-Indizes einer Datenbank und ihrer EinstellungenZuordnung von Ländern zu LCIDssys.fulltext_system_stopwordsListe aller bekannten Stopwörter aller Sprachen. In der vorliegenden Testinstanz existieren 46 verschiedene Sprachen.Liste aller deutschen Stoppwörter über select * from sys.fulltext_stopwords where language_id = 1031Liste aller in der Datenbank eingerichteten Volltext-Kataloge und dem Status-Flag für den Default-Katalog.
Weitere von contains() angebotene Möglichkeiten
contains() und das verwandte Prädikat containstable() bieten vielfältige Möglichkeiten der Suche
- erwartungsgemäß die logischen Verknüpfungen and, or, not
- near kann verwendet werden, um Datensätze zu ermitteln in denen zwei oder mehr Begriffe in der gleichen Zeile vorkommen oder einen maximalen vorzugebenden Abstand innerhalb dieser Zeile haben. Dabei kann über einen booleschen Parameter angegeben werden ob hierbei die Reihenfolge der Suchbegriffe berücksichtigt werden soll oder nicht.
- isabout erlaubt es theoretisch, Gewichtungen für Suchbegriffe im Bereich 0.0 bis 0.9 anzugeben, so dass die Ergebnisse entprechend dieser Gewichtung sortiert ausgegeben werden. Ein Test hat leider nicht zu dem gewünschten bzw. erwarteten Ergebnis geführt und die Ergebniszeilen wurden ohne Berücksichtigung der Gewichtungen ausgegeben. Haben wir hier etwas falsch verstanden? Haben Sie andere Erfahrung gemacht? Entsprechendes Feedback wäre schön. Das Statement das wir verwendet haben:
Der Brent Ozar Test
Zum Ende der Erstellung dieses Beitrags wurden wir auf einen Beitrag von Brent Ozar mit dem Titel Why Full Text's CONTAINS Queries Are So Slow - Brent Ozar Unlimited® aufmerksam, der bereits vor vier Jahren geschrieben wurde. In dem verlinkten Artikel schreibt Brent in dem Kapitel Full text search doesn’t integrate well in query plans
However, the more complex your query plan becomes, like the more filtering that’s being done on other tables, AND the more common your search keywords are, the angrier you’ll become with full text performance. SQL Server will choose to perform the full text search across all of the rows regardless of your other filters, then drag a ton of data back into the execution plan and force the rest of the operators to deal with it.
Wir waren verblüfft und mussten natürlich seinen Test vor Veröffentlichung unseres Beitrags nachvollziehen. Trifft seine Aussage auch heute nach mindestens 4 Jahren Optimizer-Optimierung immer noch zu?
Wir richteten also die StackOverflow2013 Datenbank ein, stellten den Kompatibilitätsmodus auf SQL-Server 2022 ein (der initiale Kompatibilitätsmodus der heruntergeladenen Datenbank war auf 2008 eingestellt und wir wollten ja nicht mit einem alten Optimizer testen). Anschließend wurden die von Brent Ozar verwendeten Statements ausgeführt und die tatsächlichen Ausführungspläne mit denen der o.a. Website verglichen. Alles passte, zumindest graphisch. Dann der Test mit dem QueryStress Tool. Der Cache war also bereits durch die Vergleiche der Ausführungspläne mit Life-Daten gefüllt. Die Ergebnisse des Einzelaufrufs bestätigten zunächst Brent Ozars Aussage: Die like Abfrage war geringfügig schneller. Bei Verwendung mehrerer Iterationen in fünf parallelen Sessions sah das Ergebnis aber deutlich anders aus: Hier benötigte das like fast doppelt so viel Zeit wie contains().
Und es geht noch besser
Brent Ozars Tests waren nicht miteinander vergleichbar und er hatte in seinen Tests eine Vorfilterung der Daten auf
- einen bestimmten Zeitraum ('2013-01-01' and '2013-12-31') der durch einen Index unterstützt wurde
- Betrachtung von lediglich 100 Zeilen
verwendet.
Die Abfragen waren nicht miteinander vergleichbar, weil das like-Statement sich auf die Zeichenfolge j-a-v-a bzw. s-q-l bezog. Damit wurden beispielsweise auch Zeilen mit Vorkommen von
- “SQLite” und “javasqlite” Datensatz mit der ID 41233
- “sqlite” und “JavaScript” Datensatz mit der ID 61972
- “SqlResultSetMapping” und “javac” Datensatz mit der ID 66528
gefunden.
Bei der Suche nach den Worten java und sql hätten mit dem like also auch Wortgrenzen (Leerzeichen, Satzzeichen) mit berücksichtigt werden müssen.
Like lieferte insgesamt mit der vorgegebenen Abfrage 5033 Datensätze, mit der präziseren contains()-Abfrage 1896 Datensätze.
Ein Vergleich der Client-Statistik zwischen like und contains() ohne Verwendung der Top 100 ist in der folgenden Abbildung wiedergegeben, “Trial 1” ist hierbei die like-Abfrage, “Trial 2” die mit contains():
Das Ergebnis hatte uns angespornt und wir wollten den Test für alle Datensätze der Tabelle Posts anwenden, so dass wir den Datumsfilter zusätzlich entfernten. Die entsprechenden Client Statistik-Werte befinden sich in der folgenden Abbildung:
Und was ist mit dem Kompatibilitätsmodus
Auch bezüglich des Kompatibilitätsmodus haben wir im Abschluss einen Test durchgeführt, der hier nicht detailliert dargestellt ist. Das Ergebnis bestätigte eine bessere Performance des Kompatibilitätsmodus 2022 (160) gegenüber 2008 (100) um ca. 30%.
Fazit
Die Freitextsuche bietet sehr viele Möglichkeiten, Textspalten und Spalten, die Dokumente enthalten (vgl. hierzu die View sys.fulltext_document_types) zu durchsuchen. Je nach Größe der zu durchsuchenden Daten kann durch die Volltext-Suche ein erheblicher Performancevorteil gegenüber einer Suche mit like erreicht werden. Bei Performancebetrachtungen ist nicht nur in diesem Beispiel, sondern grundsätzlich, die genaue Situation zu prüfen, welche Daten die Applikation tatsächlich benötigt und wie die Datenkonstellation ist. Werden die Prädikate CONTAINS() und Co. verwendet, so entsteht bei Bestandsapplikationen, also wenn es sich nicht um Neuentwicklungen handelt, eventuell erheblicher Mehraufwand für die Umformulierung der Abfragen.
UND:
- Beispiele aus dem Internet können veraltet sein
- Wenn Sie Beispiele aus dem Internet verwenden, betrachten Sie stets die Einstellungen, genauen Konstellationen und führen Sie ausgiebige Tests in Ihrer Testumgebung mit einem Datenbestand aus, der mit dem Ihrer Produktionsumgebung vergleichbar ist.
- Arbeiten Sie nach Möglichkeit mit der neuesten MSSQL Version. Zum Upgrade gibt es viele Beiträge im Internet, natürlich auch von uns.
- Verwenden Sie nach Möglichkeit immer den neuesten Kompatibilitätsmodus
Wenn Sie mehr über dieses Thema erfahren möchten, stehen Ihnen unsere Expert:innen gerne zur Verfügung. Vereinbaren Sie gerne ein unverbindliches Beratungsgespräch über unser Kontaktformular.
Wir helfen Ihnen gerne weiter!
