Poprawa wydajności indeksów pełnych tekstów

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ten artykuł omawia typowe przyczyny słabej wydajności indeksów i zapytań pełnotekstowych oraz sposoby ich ograniczania.

Typowe przyczyny problemów z wydajnością

Ta sekcja opisuje przyczyny typowych problemów z wydajnością przy użyciu indeksów pełnotekstowych.

Problemy z zasobami sprzętowymi

Zasoby sprzętowe, takie jak pamięć, prędkość dysku, szybkość CPU oraz architektura maszyn, wpływają na wydajność indeksowania pełnego tekstu i zapytań pełnotekstowych.

Ograniczenia zasobów sprzętowych powodują obniżoną wydajność indeksowania pełnego tekstu.

• procesora CPU. Jeśli użycie procesora przez proces hosta demona filtru (fdhost.exe) lub proces SQL Server (sqlservr.exe) zbliża się do 100%, procesor stanowi wąskie gardło.

• Pamięć. Niedobór pamięci fizycznej może stać się wąskim gardłem.

• Disk. Jeśli średnia długość kolejki oczekiwania na dysk jest większa niż dwukrotność liczby głowic dysku, powstaje wąskie gardło na dysku. Podstawowym obejściem jest utworzenie katalogów pełnotekstowych, które są oddzielone od plików i dzienników bazy danych programu SQL Server. Umieść dzienniki, pliki bazy danych i wykazy pełnotekstowe na oddzielnych dyskach. Instalowanie szybszych dysków i używanie macierzy RAID może również pomóc zwiększyć wydajność indeksowania.

Problemy z grupowaniem pełnego tekstu

Jeśli system nie ma sprzętowych wąskich gardeł, wydajność indeksowania w wyszukiwaniu pełnym tekstem zależy głównie od następujących czynników:

• Jak długo Database Engine tworzy partie pełne tekstu.

• Jak szybko demon filtra przetwarza te wsady.

Problemy z populacją indeksu pełnotekstowego

• typ populacji. W przeciwieństwie do pełnej populacji, populacja śledzenia zmian przyrostowych, ręcznych i automatycznych nie są zaprojektowane tak, by maksymalizować zasoby sprzętowe i osiągnąć szybsze tempo. Dlatego sugestie dotyczące strojenia zawarte w tym artykule mogą nie poprawić wydajności indeksowania pełnego tekstu, gdy korzysta on z populacji śledzenia zmian przyrostowych, ręcznych lub automatycznych.

• master merge. Po zakończeniu populacji, ostateczny proces scalania scala fragmenty indeksu w jeden główny pełny tekstowy indeks. Proces ten skutkuje poprawą wydajności zapytań, ponieważ wystarczy zapytać tylko główny indeks, a nie wiele fragmentów indeksu. Lepsze statystyki punktacji mogą być wykorzystywane do rankingu trafności. Jednak master merge może być intensywny pod względem operacji we/wy, ponieważ podczas scalania fragmentów indeksu należy zapisywać i odczytywać duże ilości danych. Chociaż nie blokuje przychodzących zapytań.

  Połączenie dużej ilości danych przez master może stworzyć długotrwałą transakcję, opóźniając obcięcie dziennika transakcji podczas punktu kontrolnego. W takim przypadku w ramach pełnego modelu odzyskiwania dziennik transakcji może znacznie wzrosnąć. Zgodnie z najlepszymi praktykami, zanim przystąpisz do reorganizacji dużego indeksu pełnotekstowego w bazie danych, która korzysta z pełnego modelu odzyskiwania, upewnij się, że dziennik transakcji ma wystarczającą ilość miejsca na długotrwałą transakcję. Aby uzyskać więcej informacji, zobacz Zarządzanie rozmiarem pliku dziennika transakcji.

Dostrajanie wydajności indeksów pełnotekstowych

Aby zmaksymalizować wydajność indeksów pełnotekstowych, zaimplementuj następujące najlepsze rozwiązania:

• Aby maksymalnie wykorzystać wszystkie rdzenie CPU, zmień max full-text crawl range liczbę rdzeni w systemie. Więcej informacji można znaleźć w konfiguracji serwera: maksymalny zakres pełnotekstowego przeszukiwania.

• Upewnij się, że tabela podstawowa ma indeks klastrowany. Użyj typu danych całkowitych dla pierwszej kolumny indeksu klastrowanego. Unikaj używania identyfikatorów GUID w pierwszej kolumnie indeksu klastrowanego. Populacja obejmująca wiele zakresów w indeksie klastrowanym może generować najwyższą szybkość populacji. Użyj typu danych całkowitoliczbowych dla kolumny służącej jako klucz pełnotekstowy.

• Zaktualizuj statystyki tabeli podstawowej przy użyciu instrukcji UPDATE STATISTICS . Co ważniejsze, zaktualizuj statystyki dotyczące indeksu klastrowanego lub klucza pełnotekstowego dla pełnej populacji. To działanie pomaga populacji wieloprzedziałowej tworzyć dobre podziały w tabeli.

• Zanim wykonasz pełną populację na dużym komputerze wielordzeniowym, tymczasowo ogranicz rozmiar puli bufora, ustawiając max server memory wartość tak, by pozostawiła wystarczająco dużo pamięci dla fdhost.exe procesu i systemu operacyjnego. Aby uzyskać więcej informacji, zobacz Szacowanie wymagań dotyczących pamięci procesu hosta demona filtru (fdhost.exe) w dalszej części tego artykułu.

• Jeśli używasz populacji przyrostowej opartej na kolumnie ze znacznikiem czasu, utwórz indeks wtórny na kolumnie znacznika czasu, aby zwiększyć wydajność populacji przyrostowej.

Rozwiązywanie problemów z wydajnością całych populacji

Zapoznaj się z poniższą sekcją, aby rozwiązać problemy z wydajnością w pełnych populacjach.

Przejrzyj dzienniki pełnotekstowych przeszukiwań

Aby pomóc zdiagnozować problemy z wydajnością, przejrzyj pełnotekstowe logi indeksowania.

Gdy wystąpi błąd podczas przeszukiwania, funkcja rejestrowania przeszukiwań Full-Text tworzy i utrzymuje dziennik przeszukiwań, który jest plikiem zwykłego tekstu. Każdy dziennik przeszukiwania odpowiada określonemu wykazowi pełnotekstowemu. Domyślnie dzienniki przeszukiwania dla danego wystąpienia (w tym przykładzie wystąpienie domyślne) znajdują się w folderze %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG.

Plik dziennika przeszukiwania jest zgodny z następującym schematem nazewnictwa:

SQLFT<DatabaseID><FullTextCatalogID>.[<n>]

Poniżej przedstawiono zmienne części nazwy pliku dziennika przeszukiwania.

• <DatabaseID>: ID bazy danych, jako pięciocyfrowy numer z wiodącymi zerami.

• <FullTextCatalogID>: Pełnotekstowy identyfikator katalogu, jako pięciocyfrowy numer z wiodącymi zerami.

• <n>: Liczba całkowita wskazująca, że istnieje co najmniej jeden dziennik indeksowania tego samego katalogu pełnotekstowego.

Na przykład SQLFT0000500008.2 jest plikiem dziennika przeszukiwania bazy danych o identyfikatorze bazy danych = 5, a identyfikatorem wykazu pełnotekstowego = 8. Wartość 2 na końcu nazwy pliku wskazuje, że istnieją dwa pliki dziennika przeszukiwania dla tej pary bazy danych/katalogu.

Sprawdzanie użycia pamięci fizycznej

Podczas pełnego wypełniania indeksu pełnotekstowego proces fdhost.exe lub sqlservr.exe może mieć za mało pamięci, a nawet całkowicie ją wyczerpać.

• Jeśli dziennik pełnotekstowego indeksowania pokazuje, że fdhost.exe często uruchamia się ponownie lub zwraca kod błędu 8007008, oznacza to, że jednemu z tych procesów kończy się pamięć.

• Jeśli fdhost.exe generuje zrzuty, szczególnie w dużych systemach wielordzeniowych, może brakować pamięci.

• Aby uzyskać informacje o buforach pamięci używanych przez pełne przeszukiwanie tekstu, zobacz sys.dm_fts_memory_buffers.

Możliwe przyczyny problemów z niską pamięcią lub jej brakiem obejmują następujące elementy:

• niewystarczająca ilość pamięci. Jeśli ilość fizycznej pamięci dostępnej podczas pełnej populacji wynosi zero, pula buforów Database Engine może zużywać większość fizycznej pamięci w systemie.

  Proces sqlservr.exe próbuje zarezerwować całą dostępną pamięć dla puli buforów, aż do dostępnej skonfigurowanej maksymalnej pamięci serwera. Jeśli alokacja max server memory jest zbyt duża, mogą wystąpić warunki związane z brakiem pamięci i brak możliwości alokacji współdzielonej pamięci dla fdhost.exe procesu.

  Ustaw max server memory wartość puli buforów Database Engine odpowiednio, aby rozwiązać ten problem. Aby uzyskać więcej informacji, zobacz Szacowanie wymagań dotyczących pamięci procesu hosta demona filtru (fdhost.exe) w dalszej części tego artykułu. Zmniejszenie rozmiaru partii używanego do indeksowania pełnego tekstu również może pomóc.

• Zawężenie pamięci. Podczas wypełniania indeksu pełnotekstowego w systemie wielordzeniowym elementy fdhost.exe i sqlservr.exe mogą konkurować o pamięć puli buforów. Wynikający z tego brak pamięci współdzielonej powoduje ponowne próby przetwarzania wsadów, nadmierne przełączanie stron pamięci i zrzuty przez proces fdhost.exe.

• problemy ze stronicowaniem. Niewystarczający rozmiar pliku stronicowania, na przykład w systemie z małym plikiem stronicowania i ograniczoną możliwością jego powiększania, może również spowodować, że procesowi fdhost.exe lub sqlservr.exe zabraknie pamięci. Jeśli dzienniki przeszukiwania nie wskazują na błędy związane z pamięcią, prawdopodobną przyczyną niskiej wydajności jest nadmierne stronicowanie pamięci.

Oszacuj wymagania pamięciowe procesu hosta demona filtru (fdhost.exe)

Ilość pamięci potrzebnej procesowi fdhost.exe do wypełniania zależy głównie od liczby używanych zakresów przeszukiwania pełnotekstowego, rozmiaru wejściowej pamięci współdzielonej (ISM) oraz maksymalnej liczby instancji ISM.

Możesz z grubsza oszacować zużycie pamięci hosta demona filtra, stosując następujący wzór:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

Domyślne wartości zmiennych w poprzednim wzorze są następujące:

Poniższa tabela zawiera wytyczne dotyczące szacowania zapotrzebowania na pamięć fdhost.exe. Formuły w tej tabeli używają następujących wartości:

• F, czyli szacunkowa ilość pamięci potrzebnej przez fdhost.exe (w MB).

• T, czyli całkowitej pamięci fizycznej dostępnej w systemie (w MB).

• M, co jest ustawieniem optymalnym max server memory.

Aby uzyskać podstawowe informacje o poniższych formułach, zobacz uwagi znajdujące się za tabelą.

1. Jeśli w toku jest kilka pełnych populacji, oblicz fdhost.exe wymagania pamięci każdej osobno, jako F1, F2 i tak dalej. Następnie oblicz M jako T - Σ(Fi).

2. 500 MB to oszacowanie pamięci wymaganej przez inne procesy w systemie. Jeśli system wykonuje dodatkową pracę, zwiększ tę wartość odpowiednio.

3. ism_size zakłada się, że dla platform x64 wynosi 8 MB.

Przykład: Oszacuj wymagania pamięci fdhost.exe

Ten przykład dotyczy komputera 64-bitowego, który posiada 8 GB RAM i 4 procesory dwurdzeniowe. Pierwsze obliczenie szacuje pamięć potrzebną przez fdhost.exeF. Liczba zakresów crawl wynosi .8

F = 8 * 10 * 8 = 640

Następne obliczenie daje wartość optymalną dla max server memory (M). Całkowita dostępna na tym systemie fizyczna pamięć w MB, (T) wynosi 8192.

M = 8192 - 640 - 500 = 7052

Przykład: Ustaw max server memory

W tym przykładzie używa instrukcji sp_configure i RECONFIGURE Transact-SQL, aby ustawić max server memory je na wartość obliczoną dla M w poprzednim przykładzie: 7052

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Aby uzyskać więcej informacji o opcjach pamięci serwerowej, zobacz opcje konfiguracji pamięci serwera.

Sprawdź użycie procesora

Wydajność pełnych populacji nie jest optymalna, gdy średnie zużycie CPU jest niższe niż około 30 procent. Poniżej przedstawiono niektóre czynniki wpływające na użycie procesora CPU.

• Długi czas oczekiwania dla stron

  Aby dowiedzieć się, czy czas oczekiwania strony jest wysoki, uruchom następującą instrukcję Transact-SQL:

  SELECT TOP 10 *
  FROM sys.dm_os_wait_stats
  ORDER BY wait_time_ms DESC;
  

  Poniższa tabela opisuje istotne typy oczekiwania.

  Typ oczekiwania	Opis	Możliwe rozwiązanie
  PAGEIO_LATCH_SH (_EX lub _UP)	Ten typ oczekiwania może wskazywać na wąskie gardło I/O, w takim przypadku zazwyczaj widzisz również wysoką średnią długość kolejki dyskowej.	Przeniesienie pełnego indeksu tekstowego do innej grupy plików na innym dysku mogłoby pomóc zmniejszyć wąskie gardło wejścia i wyjścia.
  PAGELATCH_EX (lub _UP)	Ten typ oczekiwania może wskazywać na dużą rywalizację o zasoby między wątkami, które próbują zapisywać do tego samego pliku bazy danych.	Dodanie plików do grupy plików, na której znajduje się indeks pełnego tekstu, mogłoby pomóc złagodzić takie spory.

  Aby uzyskać więcej informacji, zobacz sys.dm_os_wait_stats.

• Nieefektywność podczas skanowania tabeli bazowej

  Pełna populacja skanuje tabelę podstawową w celu utworzenia partii. To skanowanie tabel może być nieefektywne w następujących scenariuszach:

  • Jeśli tabela podstawowa ma wysoki procent kolumn przechowywanych poza wierszem, które są indeksowane pełnotekstowo, skanowanie tabeli podstawowej w celu utworzenia partii danych może być wąskim gardłem. W takim przypadku może pomóc przeniesienie mniejszych danych do rzędu za pomocą varchar(max) lub nvarchar(max).

  • Jeśli tabela podstawowa jest bardzo pofragmentowana, skanowanie może być nieefektywne. Aby uzyskać informacje o obliczaniu danych poza wierszami i fragmentacji indeksów, zobacz sys.dm_db_partition_stats i sys.dm_db_index_physical_stats.

    Aby zmniejszyć fragmentację, można zreorganizować lub ponownie skompilować indeks klastrowany. Aby uzyskać więcej informacji, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

Rozwiązywanie problemów z powolnym indeksowaniem dokumentów

Silnik Full-Text używa dwóch typów filtrów, gdy wypełnia indeks pełnotekstowy: filtry wielowątkowe i filtry jednowątkowe.

• Niektóre dokumenty, takie jak dokumenty Word, wykorzystują filtry wielowątkowe.
• Inne dokumenty, takie jak dokumenty Adobe Acrobat Portable Document Format (PDF), wykorzystują filtry jednowątkowe.

Ze względów bezpieczeństwa filtry są ładowane przez procesy hosta demona filtru. Wystąpienie serwera używa procesu wielowątkowego dla wszystkich filtrów wielowątkowych i procesu jednowątkowego dla wszystkich filtrów jednowątkowych. Gdy dokument korzystający z filtru wielowątkowego zawiera osadzony dokument, który używa filtru jednowątkowego, aparat Full-Text uruchamia proces jednowątkowy dla osadzonego dokumentu. Na przykład podczas napotkania dokumentu programu Word zawierającego dokument PDF, silnik Full-Text używa wielowątkowego procesu dla zawartości programu Word i uruchamia jednowątkowy proces dla zawartości PDF. Jednak filtr jednowątkowy może nie działać dobrze w takim środowisku i może destabilizować proces filtrowania.

W pewnych okolicznościach, gdy takie osadzanie jest powszechne, destabilizacja może prowadzić do awarii procesu. Gdy ten stan wystąpi, Full-Text Engine przekierowuje każdy uszkodzony dokument (na przykład dokument Word zawierający osadzone treści PDF) do procesu filtrowania jednowątkowego. W przypadku częstego ponownego routingu powoduje obniżenie wydajności procesu indeksowania pełnotekstowego.

Aby obejść ten problem, oznacz filtr dokumentu kontenerowego (dokument Word, w tym przykładzie) jako filtr jednowątkowy. Aby oznaczyć filtr jako filtr jednowątkowy, ustaw ThreadingModel wartość rejestru filtra na .Apartment Threaded Informacje o mieszkaniach jednowątkowych można znaleźć w artykule Understanding and Using COM Threading Models.

Treści powiązane

• Opcje konfiguracji pamięci serwera
• Konfiguracja serwera: maksymalny zakres pełnotekstowego przeszukiwania
• Wypełnianie indeksów Full-Text
• Tworzenie indeksów pełnotekstowych i zarządzanie nimi
• sys.dm_fts_memory_buffers (Transact-SQL)
• sys.dm_fts_memory_pools (Transact-SQL)
• Rozwiązywanie problemów z indeksowaniem pełnym tekstem
• architektura wyszukiwania Full-Text