SQL Server Volltext Suche einige weniger bekannte Details

Der FTS (Fulltext Search Prozess) ist im SQL Server ein separater Prozess der mir dem SQLSERVER.EXE über eine SharedMemory Segment kommuniziert.

Wenn der FTS lange nicht benötigt wird, kann es sein, das aufgrund von Memoryanforderungen anderer Prozesse (SQLSERVER.EXE z.B.) auf ein Minimum zurückgefahren wird.

Er wird nicht rausgepaged, sondern er wird aufgefordert, Memory freizugeben, was der FTS i.d.R auch tut.

Wenn nun eine neue FTS Abfrage kommt, muss der FTS erst sein Memory wieder allokieren. Wenn dieser Vorgang lange dauert (mehrere Sekunden) deutet es darauf hin, dass das System erst den SQL Server auffordern muss wieder Memory freizugeben und anschließend diesen dem FTS zur Verfügung zu stellen.

Wenn also das System zu wenig Hauptspeicher hat, muss man Maßnahmen ergreifen, dass der FTS stets ausreichen Memory zur Verfügung hat.

Das kann erreicht werden durch eine Beschränkung des Maximalen Hauptspeichers für den SQL Server (sp_configure ‘max memory‘ ...)

Alternativ können ein SQL Server Agent Job anlegen der alle 5 Minuten eine beliebige FTS Abfrage ausführt, damit der FTS nicht in die Versuchung gerät Memory freizugeben.

Es kann aber auch sein, das die Engine die Signatur nicht verifizieren kann siehe der zugehörige KB Artikel.

Ein anders Thema sind gelegentlich auftretende Locks in Verbindung mit Volltext indizierten Tabellen.

Auch diese können bei FTS zur Verminderung der Abfragegeschwindigkeit führen.

In diesem Fall empfiehlt sich der Einsatz der SNAPSHOT ISOLATION auf Datenbank Ebene.

Damit wird die Blockierung von lesenden Operationen durch schreibenden Operationen ausgeschaltet, aber die tempdb wird aber stärker belasstet, da dort die before images abgelegt werden. Man muss also die tempdb tunnen.

Hier ist eine Beschreibung für die Best Practices rund um das Thema Volltext Indizierung.

Auch zum Thema Neuerungen des Volltext Indizierung im SQL Server 2008 gibt es ein sehr aufschlußreiches Dokument von Fernando, dem PM zuständigen PM.

Interessant ist auch das Debugging von FTS-Abfragen. Mit SQL Server 2008 kann man folgende Abfrage und deren Ergebnis untersuchen:

select * from sys.dm_fts_parser('AT&T', 1031,0,0)

--- Ergebnis:

0x00610074 1 0 1 Exact Match at 0 AT

0x0074 2 0 1 Noise Word t 0 T

Man sieht das Term AT&T zerlegt wird und nicht als zusammenhängendes Wort erkannt wird.

Das leigt daran das der Parser das Amphesant Zeichen in ein AND umwandelt.

Diese kann vermieden werden, durch entsprechende Hochkommata:

select * from sys.dm_fts_parser('”AT&T”', 1031,0,0)

Ebenso wird die Tilde ~ in ein NEAR gewandelt.

Eine noch nicht Dokumentierte Neuerung ist die Möglichkeit ein Custom Dictionary zu verwenden.

Siehe diesen Artikel zum Custom Dictionary

Ein Custom Dictonary ist eine Unicode Datei, die für besondere Worte genutzt werden kann, die in der Sprache der Wahl als ganzes Wort angesehen werden soll. Diese Custom Directories sind nicht standardmäßig aktiv. Um die Wordbraker für das Custom Directory zu aktivieren, müssen je Sprache entsprechende Dateien erzeugt werden, die aber bitte nicht größer als 2GB je Datei werden sollten.

Für den Sprach-neutralen Wordbreaker ist es nicht möglich ein Custom Directory zu nutzen.

Beispiel der Term ‚P64-C6‘ soll als eigenes Wort behandelt werden.

select [display_term] from sys.dm_fts_parser('P64-C6', 1031,0,0)

Hier die Schritte zur Aktivierung des Custom Directories für SQL Server 2008:

1. Anmelden als Administrator an dem System

2. Notepad starten und die Worte der Wahl eintragen. Jedes Wort muss auf einer eignen Zeile stehen, getrennt mit Carriage Return (CR) und Line Feed (LF).

3. Speichern der Datei als File mit der UNICODE Kodierung.

4. Name der Datei Customxxxx.lex wobei xxxxx der hexcode der jeweiligen Sprache ist, für das das Custom Directory erstellt werden soll. Die Liste der gültigen Werte steht in dem Artikel zum Custom Directory.

5. Abspeichern der so erstellten Datei im Standard Verzeichnis des SQL Servers z.B. C:\Programme\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSQL\Binn

6. Jetzt speichern.

7. Neustart des FTS Host Prozesses mit der T-SQL Anweisung in der betroffenen SQL Server Instanz:

1. exec sp_fulltext_service 'restart_all_fdhosts'

8. Testen, ob die eingetragenen Worte nun richtig, als eigenes Wort, behandelt werden:

select [display_term] from sys.dm_fts_parser('P64-C6', 1031,0,0)

Noch ein Hinweis: Das funktioniert nur für die neuen Wordbreaker, also für Deutsch z.B., nicht aber für English. Die neuen Wordbraker kommen mit Vista / Windows Server 2008 und Windows 7 / Windows Server 2008 R2. Man kann Sie an den Namen der DLL’s erkennen: NaturalLanguage6, NLS*.dll usw. Für English siehe hier.

-Ralph Kemperdick