Zlepšit výkon indexů s plným textem

platí pro: SQL Server Azure SQL DatabaseAzure SQL Managed Instance

Tento článek se zabývá běžnými příčinami špatného výkonu u plnotextových indexů a dotazů a jak je zmírnit.

Běžné příčiny problémů s výkonem

Tato sekce popisuje příčiny běžných problémů s výkonem při používání indexů s plným textem.

Problémy s hardwarovými prostředky

Hardwarové zdroje jako paměť, rychlost disku, rychlost CPU a architektura stroje ovlivňují výkon indexování plného textu a dotazů na plný text.

Omezení hardwarových zdrojů způsobují snížený výkon indexování plného textu.

• procesoru. Pokud se využití procesoru hostitelským procesem filtračního démona (fdhost.exe) nebo procesem SQL Serveru (sqlservr.exe) blíží 100 %, je procesor úzkým hrdlem.

• paměť. Nedostatek fyzické paměti může způsobit úzké hrdlo.

• disku. Pokud je průměrná délka čekající fronty na disku více než dvojnásobek počtu hlav disku, dochází k úzkému hrdlu na disku. Hlavním alternativním řešením je vytvořit fulltextové katalogy, které jsou oddělené od souborů a protokolů databáze SQL Serveru. Umístěte protokoly, soubory databáze a fulltextové katalogy na samostatné disky. Instalace rychlejších disků a použití raidu může také pomoct zlepšit výkon indexování.

Problémy s dávkovým zpracováním fulltextu

Pokud systém nemá hardwarová úzká místa, výkon indexace fulltextového vyhledávání závisí především na následujících faktorech:

• Jak dlouho trvá, než Databázový stroj vytvoří plnotextové dávky.

• Jak rychle filtrační démon tyto dávky spotřebuje.

Problémy s populací fulltextového indexu

• typ populace. Na rozdíl od plné populace nejsou inkrementální, manuální a automatické sledování změn navrženy tak, aby maximalizovaly hardwarové zdroje pro dosažení vyšší rychlosti. Proto doporučení ladění v tomto článku nemusí zlepšit výkon indexování plného textu, pokud používá inkrementální, manuální nebo automatické sledování změn.

• hlavní sloučení. Když populace skončí, proces finálního sloučení sloučí fragmenty indexu do jednoho hlavního plnotextového indexu. Tento proces vede ke zlepšení výkonu dotazů, protože je potřeba dotazovat pouze hlavní index, nikoli počet fragmentů indexu. Lepší statistiky skórování by mohly být použity pro hodnocení relevance. Nicméně hlavní sloučení může být náročné na vstup/výstup, protože při sloučení indexových fragmentů je nutné zapsat a číst velké množství dat. I když neblokuje příchozí dotazy.

  Slučování velkého množství dat pomocí master může vytvořit dlouhotrvající transakci, což zpozdí zkrácení transakčního logu během kontrolního bodu. V tomto případě se v rámci úplného modelu obnovení může transakční protokol výrazně zvětšit. Osvědčeným postupem je před změnou uspořádání velkého fulltextového indexu v databázi, která používá úplný model obnovení, zajistit, aby váš transakční protokol obsahoval dostatek místa pro dlouho běžící transakci. Další informace naleznete v tématu Správa velikosti souboru transakčního protokolu.

Ladění výkonu fulltextových indexů

Pokud chcete maximalizovat výkon fulltextových indexů, implementujte následující osvědčené postupy:

• Pro maximální využití všech jader CPU změňte max full-text crawl range počet jader v systému. Další informace naleznete v části Konfigurace serveru: maximální rozsah procházení fulltextu.

• Ujistěte se, že základní tabulka obsahuje clusterovaný index. Pro první sloupec clusterovaného indexu použijte celočíselné datové typy. Nepoužívejte identifikátory GUID v prvním sloupci clusterovaného indexu. Počet obyvatel s více rozsahy v clusterovém indexu může způsobit nejvyšší rychlost populace. Použijte celočíselný datový typ pro sloupec sloužící jako klíč pro plný text.

• Pomocí příkazu aktualizujte statistiku základní tabulky UPDATE STATISTICS . Je důležitější aktualizovat statistiky pro clusterovaný index nebo fulltextový klíč pro úplnou populaci. Tato akce pomáhá populaci s více intervaly vytvářet v tabulce vhodné oddíly.

• Než provedete plnou populaci na velkém vícejádrovém počítači, dočasně omezte velikost bufferového poolu tím, že nastavíte max server memory hodnotu tak, aby zůstala dostatek paměti pro fdhost.exe proces a použití operačního systému. Další informace najdete v části Odhad požadavků na paměť hostitelského procesu démona filtru (fdhost.exe) dále v tomto článku.

• Pokud použijete přírůstkovou populaci založenou na sloupci časového razítka, vytvořte sekundární index na časové razítko sloupci, abyste zlepšili výkon přírůstkového souboru.

Diagnostikovat výkon kompletních populací

Pro řešení problémů s výkonem u plných populací se podívejte na následující sekci.

Projděte si protokoly úplného procházení textu

Pro lepší diagnostiku problémů s výkonem si prohlédněte plnotextové záznamy z procházení.

Když během procházení dojde k chybě, zařízení pro protokolování prohledávání Full-Text vytvoří a udržuje protokol procházení, což je soubor ve formátu prostého textu. Každý protokol procházení odpovídá určitému fulltextovému katalogu. Ve výchozím nastavení se protokoly procházení pro danou instanci (v tomto příkladu výchozí instance) nacházejí ve složce %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG.

Schéma pojmenování souboru protokolu procházení je následující:

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

Proměnné části názvu souboru protokolu procházení jsou následující.

• <DatabaseID>: ID databáze, jako pětimístné číslo s úvodními nulami.

• <FullTextCatalogID>: Plnotextové katalogové ID, jako pětimístné číslo s úvodními nulami.

• <n>: Celé číslo, které označuje existenci jednoho nebo více crawl logů stejného plnotextového katalogu.

Například SQLFT0000500008.2 je soubor protokolu procházení databáze s ID databáze = 5 a ID fulltextového katalogu = 8. Hodnota 2 na konci názvu souboru označuje, že pro tuto dvojici databáze nebo katalogu existují dva soubory protokolu procházení.

Kontrola využití fyzické paměti

Během naplňování fulltextového indexu může mít proces fdhost.exe nebo sqlservr.exe nedostatek paměti, nebo mu může paměť dokonce zcela dojít.

• Pokud protokol fulltextového procházení ukazuje, že se fdhost.exe často restartuje nebo vrací chybový kód 8007008, znamená to, že jednomu z těchto procesů dochází paměť.

• Pokud vytváří fdhost.exe dumpy, zejména u velkých vícejádrových systémů, může mu docházet paměť.

• Pro informace o paměťových bufferech používaných full-textovým procházením viz sys.dm_fts_memory_buffers.

Možné příčiny nízké paměti nebo problémů s nedostatkem paměti zahrnují následující položky:

• nedostatek paměti. Pokud je množství fyzické paměti dostupné během plné populace nulové, může buffer pool Database Engine spotřebovávat většinu fyzické paměti systému.

  Proces sqlservr.exe se pokusí získat veškerou dostupnou paměť pro fond vyrovnávací paměti až do nakonfigurované maximální paměti serveru. Pokud je alokace max server memory příliš velká, může dojít k nedostatku paměti a selhání alokace sdílené paměti pro fdhost.exe celý proces.

  Nastavte max server memory hodnotu buffer poolu Database Engine vhodně, abyste tento problém vyřešili. Další informace najdete v části Odhad požadavků na paměť hostitelského procesu démona filtru (fdhost.exe) dále v tomto článku. Snížení velikosti dávky používané pro indexování plného textu by také mohlo pomoci.

• konflikt přístupu k paměti. Během populace plného textu na vícejádrovém systému fdhost.exe může soupeřit sqlservr.exe o paměť v bufferu. Výsledný nedostatek sdílené paměti způsobuje opakované pokusy o dávkové zpracování, nadměrné přesouvání stránek v paměti a výpisy generované procesem fdhost.exe.

• problémy se stránkováním. Nedostatečná velikost stránkovacího souboru, například v systému s malým stránkovacím souborem s omezenou možností zvětšení, může také způsobit, že procesu fdhost.exe nebo sqlservr.exe dojde paměť. Pokud logy procházení neukazují žádné poruchy související s pamětí, nadměrné stránkování pravděpodobně způsobuje zpomalený výkon.

Odhadněte požadavky na paměť procesu hostitele filtru (fdhost.exe)

Množství paměti, kterou proces potřebuje fdhost.exe k naplňování, závisí především na počtu rozsahů plnotextového procházení, které používá, velikosti příchozí sdílené paměti (ISM) a maximálním počtu instancí ISM.

Spotřebu paměti hostitele filtračního démona můžete přibližně odhadnout pomocí následujícího vzorce:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

Výchozí hodnoty proměnných v předchozím vzorci jsou následující:

Následující tabulka uvádí pokyny pro odhad paměťových požadavků .fdhost.exe Vzorce v této tabulce používají následující hodnoty:

• F, což je odhad paměti potřebné ( fdhost.exe v MB).

• T, což je celková fyzická paměť dostupná v systému (v MB).

• M, což je optimální max server memory nastavení.

Základní informace o následujících vzorcích najdete v poznámkách, které následují v tabulce.

1. Pokud současně probíhá více úplných populací, vypočítejte paměťové požadavky fdhost.exe každé z nich zvlášť a označte je jako F1, F2 atd. Poté vypočítejte M jako T - Σ(Fi).

2. 500 MB je odhad paměti vyžadované jinými procesy v systému. Pokud systém provádí další práci, odpovídajícím způsobem tuto hodnotu zvyšte.

3. ism_size se předpokládá na 8 MB pro x64 platformy.

Příklad: Odhadněte požadavky na paměť fdhost.exe

Tento příklad se týká 64bitového počítače s 8 GB RAM a čtyřmi dvoujádrovými procesory. První výpočet odhaduje paměť potřebnou pro fdhost.exeF. Počet rozsahů procházení je 8.

F = 8 * 10 * 8 = 640

Další výpočet získá optimální hodnotu pro max server memory (M). Celková fyzická paměť dostupná v tomto systému v MB, (T) je .8192

M = 8192 - 640 - 500 = 7052

Příklad: Nastavit max server memory

Tento příklad používá příkazy sp_configure a RECONFIGURE Transact-SQL tak, aby byly max server memory nastaveny na hodnotu vypočítanou pro M v předchozím příkladu: 7052

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Pro více informací o možnostech serverové paměti viz možnosti konfigurace serverové paměti.

Kontrola využití procesoru

Výkon plných populací není optimální, když je průměrná spotřeba CPU nižší než asi 30 procent. Tady jsou některé faktory, které ovlivňují spotřebu procesoru.

• Vysoká doba čekání na stránky

  Pokud chcete zjistit, jestli je doba čekání na stránku vysoká, spusťte následující příkaz Transact-SQL:

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

  Následující tabulka popisuje typy čekání na úroky.

  Typ čekání	Popis	Možné řešení
  PAGEIO_LATCH_SH (_EX nebo _UP)	Tento typ čekání může naznačovat úzké místo v operacích V/V; v takovém případě se obvykle také projeví vysoká průměrná délka fronty disku.	Přesun full-textového indexu do jiné skupiny souborů na jiném disku by mohl pomoci omezit úzké místo v operacích I/O.
  PAGELATCH_EX (nebo _UP)	Tento typ čekání může naznačovat velkou soutěž mezi vlákny, která se snaží zapisovat do stejného databázového souboru.	Přidání souborů do souborové skupiny, kde se nachází index plného textu, by mohlo pomoci zmírnit takové spory.

  Další informace najdete v tématu sys.dm_os_wait_stats.

• Neefektivita při skenování základní tabulky

  Úplná populace prohledá základní tabulku a vytvoří dávky. Toto skenování tabulek může být neefektivní v následujících situacích:

  • Pokud základní tabulka obsahuje vysoké procento sloupců uložených mimo řádky, které jsou indexovány fulltextem, může být skenování základní tabulky pro účely vytváření dávek omezením výkonu. V tomto případě by mohlo pomoci přesunout menší data do řádku pomocí varchar(max) nebo nvarchar(max).

  • Pokud je základní tabulka velmi fragmentovaná, může být prohledávání neefektivní. Pro informace o výpočtu dat mimo řádky a fragmentaci indexů viz sys.dm_db_partition_stats a sys.dm_db_index_physical_stats.

    Pokud chcete snížit fragmentaci, můžete přeuspořádat nebo znovu sestavit clusterovaný index. Další informace najdete v tématu Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků.

Řešení potíží s pomalým indexováním dokumentů

Modul Full-Text používá při naplnění fulltextového indexu dva typy filtrů: filtry s více vlákny a filtry s jedním vláknem.

• Některé dokumenty, například Word dokumenty, používají vícevláknové filtry.
• Jiné dokumenty, například dokumenty Adobe Acrobat Portable Document Format (PDF), používají jednovláknové filtry.

Pro bezpečnostní důvody jsou filtry načítány procesy hostitele démona filtru. Instance serveru používá vícevláknový proces pro všechny vícevláknové filtry a proces s jedním vláknem pro všechny filtry s jedním vláknem. Pokud dokument, který používá vícevláknový filtr obsahuje vložený dokument, který používá filtr s jedním vláknem, spustí modul Full-Text proces s jedním vláknem pro vložený dokument. Například při výskytu wordového dokumentu, který obsahuje dokument PDF, modul Full-Text používá vícevláknový proces pro obsah Wordu a spustí proces s jedním vláknem pro obsah PDF. Nicméně jednovláknový filtr nemusí v tomto prostředí dobře fungovat a může destabilizovat proces filtrování.

Za určitých okolností, kdy je takové vkládání běžné, může destabilizace vést k pádu procesu. Když nastane tento stav, Full-Text Engine přesměruje jakýkoli neúspěšný dokument (například Word dokument obsahující vložený PDF obsah) do procesu filtrování s jedním vláknem. Pokud k opětovnému směrování dochází často, vede ke snížení výkonu procesu fulltextového indexování.

Abychom tento problém obešli, označte filtr kontejnerového dokumentu (v tomto příkladu Word dokument) jako jednovláknový filtr. Pro označení filtru jako jednovláknového filtru nastavte ThreadingModel hodnotu registru filtru na Apartment Threaded. Informace o jednovláknových apartmánech naleznete v tématu Principy a použití modelů vláken COM.

Související obsah

• možnosti konfigurace paměti serveru
• Konfigurace serveru: maximální rozsah full-textového procházení
• naplnění indexů Full-Text
• Vytváření a správa fulltextových indexů
• sys.dm_fts_memory_buffers (Transact-SQL)
• sys.dm_fts_memory_pools (Transact-SQL)
• Řešení problémů s indexováním plného textu
• architektura vyhledávání Full-Text