Migliorare le prestazioni degli indici a testo completo

Si applica a:SQL Server, Database SQL di Azure e Istanza gestita di SQL di Azure.

Questo articolo tratta le cause comuni delle scarse prestazioni degli indici e delle query a testo completo, e come mitigarle.

Cause comuni dei problemi di prestazioni

Questa sezione descrive le cause dei problemi di prestazioni comuni quando si usano indici a testo completo.

Problemi relativi alle risorse hardware

Risorse hardware come memoria, velocità del disco, velocità della CPU e architettura della macchina influenzano le prestazioni dell'indicizzazione a testo completo e delle query a testo completo.

I limiti di risorse hardware causano una riduzione delle prestazioni di indicizzazione del testo intero.

  • CPU. Se l'utilizzo della CPU da parte del processo host del daemon di filtro (fdhost.exe) o del processo SQL Server (sqlservr.exe) è prossimo al 100%, la CPU rappresenta il collo di bottiglia.

  • Memoria. Una carenza di memoria fisica può causare un collo di bottiglia.

  • Disco. Se la lunghezza media della coda in attesa del disco è più del doppio del numero di testine del disco, c'è un collo di bottiglia sul disco. La soluzione alternativa principale consiste nella creazione di cataloghi full-text separati dai file e dai log del database di SQL Server. Posizionare i log, i file di database e i cataloghi full-text su dischi separati. Per migliorare le prestazioni di esecuzione dell'indicizzazione, è inoltre possibile installare dischi più veloci e usare RAID.

Problemi relativi al raggruppamento full-text

Se il sistema non presenta colli di bottiglia hardware, le prestazioni di indicizzazione della ricerca a testo completo dipendono principalmente dai seguenti fattori:

  • Quanto tempo impiega il motore di database a creare batch a testo completo.

  • Quanto velocemente il demone filtro consuma quei lotti.

Problemi relativi al popolamento dell'indice full-text

  • Tipo di popolamento. A differenza della popolazione completa, la popolazione di monitoraggio incrementale, manuale e automatica non è progettata per massimizzare le risorse hardware e raggiungere una velocità più elevata. Pertanto, i suggerimenti di ottimizzazione contenuti in questo articolo potrebbero non migliorare le prestazioni dell'indicizzazione full-text quando viene usato il popolamento con rilevamento delle modifiche incrementale, manuale o automatico.

  • Unione nell'indice master. Quando una popolazione termina, un processo finale di fusione unisce i frammenti dell'indice in un unico indice master a testo completo. Questo processo si traduce in un miglioramento delle prestazioni delle query poiché è necessario interrogare solo l'indice master e non un certo numero di frammenti di indice. Statistiche di punteggio migliori potrebbero essere usate per il ranking di rilevanza. Tuttavia, la fusione master può richiedere un lavoro intensivo di I/O perché è necessario scrivere e leggere grandi quantità di dati quando vengono uniti i frammenti di indice. Tuttavia, non blocca le query in arrivo.

    La fusione di una grande quantità di dati può creare una transazione di lunga durata, ritardando la troncatura del registro delle transazioni durante il checkpoint. In questo caso, le dimensioni del log delle transazioni potrebbero aumentare notevolmente, se si utilizza il modello di recupero con registrazione completa. Prima di riorganizzare un indice full-text di grandi dimensioni in un database che utilizza il modello di recupero completo, è consigliabile verificare che il log delle transazioni contenga spazio sufficiente per una transazione con esecuzione prolungata. Per altre informazioni, vedere Gestire le dimensioni del file di log delle transazioni.

Ottimizzare le prestazioni degli indici a testo completo

Per ottimizzare le prestazioni degli indici full-text, implementare le procedure consigliate seguenti:

  • Per usare tutti i core CPU al massimo, si cambia max full-text crawl range il numero di core sul sistema. Per ulteriori informazioni, vedi configurazione del server: intervallo massimo di crawl full-text.

  • Verificare che la tabella di base includa un indice cluster. Utilizzare un tipo di dati integer per la prima colonna dell'indice cluster. Evitare l'utilizzo di GUID nella prima colonna dell'indice cluster. Un popolamento a più intervalli in un indice cluster garantisce la massima velocità di popolamento. Usa un tipo di dato intero per la colonna che funge da chiave di testo completo.

  • Aggiornare le statistiche della tabella di base usando l'istruzione UPDATE STATISTICS . Un'operazione ancora più importante consiste nell'aggiornamento delle statistiche nell'indice raggruppato o nella chiave full-text per una popolazione completa. Questa azione aiuta una popolazione con più intervalli a generare buone partizioni sulla tabella.

  • Prima di eseguire una popolazione completa su un grande computer multi-core, limita temporaneamente la dimensione del pool di buffer impostando il max server memory valore per lasciare abbastanza memoria per l'uso del fdhost.exe processo e del sistema operativo. Per ulteriori informazioni, vedi Stima dei requisiti di memoria del processo host del demone filtro (fdhost.exe), più avanti in questo articolo.

  • Se si usa il popolamento incrementale basato su una colonna timestamp, compilare un indice secondario sulla colonna timestamp per migliorare le prestazioni di esecuzione del popolamento incrementale.

Risoluzione dei problemi relativi alle prestazioni di popolamenti completi

Consulta la sezione seguente per risolvere i problemi di prestazioni con popolazioni complete.

Esaminare i log di indicizzazione del testo completo

Per aiutare a diagnosticare i problemi di prestazioni, rivedere i log di scansione a testo completo.

Quando si verifica un errore durante una ricerca per indicizzazione, la funzionalità di registrazione dei crawl per la ricerca full-text crea e gestisce un log in formato testo normale. Ogni log di tipo ricerca per indicizzazione corrisponde a un catalogo full-text specifico. Per impostazione predefinita, i log di indicizzazione per un'istanza specifica, ad esempio l'istanza predefinita, si trovano nella cartella %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG.

Il file di log di crawling segue il seguente schema di denominazione:

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

Di seguito sono le parti variabili del nome del file del log di crawl.

  • <DatabaseID>: L'ID di un database, come un numero a cinque cifre con zeri all'inizio.

  • <FullTextCatalogID>: ID del catalogo full-text, espresso come numero a cinque cifre con zeri iniziali.

  • <n>: Un intero che indica l'esistenza di uno o più log di scansione dello stesso catalogo full-text.

Ad esempio, SQLFT0000500008.2 è il file di log di ricerca per indicizzazione per un database con ID database = 5 e ID catalogo full-text = 8. Il 2 alla fine del nome del file indica che sono disponibili due file log di crawl per questa coppia database/catalogo.

Controllare l'utilizzo della memoria fisica

Durante un popolamento full-text, il processo fdhost.exe o sqlservr.exe può avere poca memoria disponibile, o addirittura esaurirla.

  • Se il registro di scansione a testo completo mostra che fdhost.exe si riavvia spesso o restituisce codice di errore 8007008, significa che uno di questi processi sta esaurendo la memoria.

  • Se fdhost.exe genera dump, in particolare su sistemi di grandi dimensioni e multi-core, potrebbe esaurire la memoria.

  • Per informazioni sui buffer di memoria utilizzati da un crawl a testo completo, vedi sys.dm_fts_memory_buffers.

Le possibili cause di problemi di memoria bassa o di scarsa memoria includono i seguenti elementi:

  • Memoria insufficiente. Se la quantità di memoria fisica disponibile durante una popolazione completa è zero, il buffer pool del motore di database potrebbe consumare la maggior parte della memoria fisica del sistema.

    Il processo sqlservr.exe tenta di acquisire tutta la memoria disponibile per il pool di buffer, fino alla quantità massima di memoria del server configurata. Se l'allocazione max server memory è troppo grande, possono verificarsi condizioni di esaurimento di memoria e mancata allocazione della memoria condivisa per il fdhost.exe processo.

    Imposta il max server memory valore del pool buffer del motore di database in modo appropriato per risolvere questo problema. Per ulteriori informazioni, vedi Stima dei requisiti di memoria del processo host del demone filtro (fdhost.exe), più avanti in questo articolo. Ridurre la dimensione del lotto utilizzata per l'indicizzazione del testo completo potrebbe aiutare.

  • Contesa di memoria. Durante un popolamento full-text in un sistema multi-core, fdhost.exe e sqlservr.exe possono competere per la memoria del buffer pool. La conseguente mancanza di memoria condivisa causa batch retries, memory thrashing e dump da parte del fdhost.exe processo.

  • Problemi di impaginazione. Un file di paging di dimensioni insufficienti, ad esempio in un sistema con un file di paging di piccole dimensioni e con crescita limitata, può inoltre causare l'esaurimento della memoria del processo fdhost.exe o sqlservr.exe. Se i log di crawl non indicano errori legati alla memoria, è probabile che un paging eccessivo stia causando rallentamenti.

Stima dei requisiti di memoria del processo host del demone filtro (fdhost.exe)

La quantità di memoria di cui il fdhost.exe processo ha bisogno per popolare dipende principalmente dal numero di intervalli di scansione a testo intero utilizzato, dalla dimensione della memoria condivisa in entrata (ISM) e dal numero massimo di istanze ISM.

Puoi stimare approssimativamente il consumo di memoria dell'host del demone filtro usando la seguente formula:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

I valori predefiniti delle variabili nella formula precedente sono i seguenti:

Variabile Valore predefinito
numero_di_intervalli_di_scansione Numero di core CPU
ism_size 1 MB per computer x86

4 MB, 8 MB o 16 MB per computer x64, a seconda della memoria fisica totale
max_outstanding_isms 25 per computer x86

5 per computer x64

La tabella seguente presenta linee guida per stimare i requisiti di memoria di fdhost.exe. Le formule contenute in questa tabella utilizzano i valori riportati di seguito.

  • F, che è una stima della memoria necessaria da fdhost.exe (in MB).

  • T: memoria fisica totale disponibile nel sistema (in MB).

  • M, che è l'impostazione ottimale max server memory .

Per informazioni essenziali sulle formule seguenti, vedere le note dopo la tabella.

Piattaforma Stima fdhost.exe dei requisiti di memoria in MB: F^1 Formula per calcolare la memoria massima del server: M^2
x86 F = Numero di intervalli di crawling * 50 M = min(T, 2000) - F - 500
x64 F = Numero di intervalli di scansione * 10 * 8 M = T - F - 500

  1. Se sono in corso più popolazioni complete, calcola i fdhost.exe requisiti di memoria di ciascuna separatamente, come F1, F2 e così via. Poi calcola M come T - Σ(Fi).

  2. 500 MB è una stima della memoria necessaria per gli altri processi del sistema. Se nel sistema sono in corso processi aggiuntivi, aumentare questo valore di conseguenza.

  3. ism_size si presume sia di 8 MB per piattaforme x64.

Esempio: stimare i requisiti di memoria di fdhost.exe

Questo esempio è per un computer a 64 bit che ha 8 GB di RAM e 4 processori dual-core. Il primo calcolo stima la memoria necessaria a fdhost.exeF. Il numero di intervalli di scansione è 8.

F = 8 * 10 * 8 = 640

Il calcolo successivo ottiene il valore ottimale per max server memory (M). La memoria fisica totale disponibile su questo sistema in MB, (T), è 8192.

M = 8192 - 640 - 500 = 7052

Esempio: Set max server memory

Questo esempio utilizza le istruzioni sp_configure e RECONFIGURE Transact-SQL per impostare max server memory al valore calcolato per M nell'esempio precedente, 7052:

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Per maggiori informazioni sulle opzioni di memoria del server, vedi opzioni di configurazione della memoria del server.

Controllare l'utilizzo della CPU

Le prestazioni delle popolazioni complete non sono ottimali quando il consumo medio della CPU è inferiore a circa il 30 percento. Di seguito vengono illustrati alcuni fattori che influiscono sull'utilizzo della CPU.

  • Pagine con lunghi tempi di attesa

    Per determinare il tempo di attesa delle pagine, esegui l'istruzione Transact-SQL seguente:

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

    La tabella seguente descrive i tipi di attesa rilevanti.

    Tipo di attesa Descrizione Possibile soluzione
    PAGEIO_LATCH_SH (_EX o _UP) Questo tipo di attesa potrebbe indicare un collo di bottiglia di I/O, nel qual caso si osserva in genere anche una lunghezza media elevata della coda del disco. Spostare l'indice full-text in un altro filegroup su un disco diverso potrebbe aiutare a ridurre il collo di bottiglia dell'I/O.
    PAGELATCH_EX (o _UP) Questo tipo di attesa potrebbe indicare molta controversia tra thread che cercano di scrivere nello stesso file di database. Aggiungere file al gruppo file su cui risiede l'indice del testo completo potrebbe aiutare ad alleviare tali contesi.

    Per altre informazioni, vedere sys.dm_os_wait_stats.

  • Inefficienze nella scansione della tabella di base

    Un popolamento completo esegue l'analisi della tabella di base per produrre batch. Questa scansione di tabella potrebbe essere inefficiente nei seguenti scenari:

Risoluzione dei problemi relativi all'indicizzazione lenta dei documenti

Nota

Questa sezione descrive un problema che riguarda solo gli utenti che indicizzano documenti (ad esempio documenti di Microsoft Word) in cui sono incorporati altri tipi di documento.

Il motore di ricerca full-text utilizza due tipi di filtri durante il popolamento di un indice full-text: filtri multithread e filtri a thread singolo.

  • Alcuni documenti, come quelli Word, utilizzano filtri multithread.
  • Altri documenti, come Adobe Acrobat Portable Document Format (PDF), utilizzano filtri a singolo thread.

Ai fini della sicurezza, i filtri vengono caricati dai processi dell'host del daemon di filtri. In un'istanza del server viene utilizzato un processo a thread multipli per tutti i filtri a thread multipli e un processo a thread singolo per tutti i filtri a thread singolo. Quando un documento che utilizza un filtro multithread contiene un documento incorporato che utilizza un filtro a thread singolo, il motore di ricerca full-text avvia un processo a thread singolo per il documento incorporato. Nel caso di un documento di Word che contiene un documento PDF, il motore full-text usa il processo multithread per esaminare il contenuto in formato Word e avvia un processo a thread singolo per esaminare il contenuto in formato PDF. Tuttavia, un filtro a singolo filo potrebbe non funzionare bene in questo ambiente e potrebbe destabilizzare il processo di filtraggio.

In certe circostanze in cui tale incorporazione è comune, la destabilizzazione potrebbe causare arresti anomali del processo. Quando si verifica questa condizione, il motore Full-Text reindirizza qualsiasi documento fallito (ad esempio, un documento Word che contiene contenuti PDF incorporati) al processo di filtraggio a singolo thread. Se il reindirizzamento viene eseguito di frequente, le prestazioni del processo di indicizzazione full-text risultano ridotte.

Per aggirare questo problema, segna il filtro del documento container (il documento Word, in questo esempio) come un filtro a singolo thread. Per contrassegnare un filtro come filtro a singolo thread, imposta il ThreadingModel valore del registro del filtro su Apartment Threaded. Per informazioni sugli apartment a thread singolo, vedere Comprendere e usare i modelli di threading di COM.

Contenuti correlati

  • Ultimo aggiornamento: