Miglioramento delle prestazioni di indici full-text

  • Articolo

Le prestazioni di esecuzione dell'indicizzazione e delle query full-text possono dipendere da risorse hardware quali memoria e velocità del disco e della CPU, nonché dall'architettura del computer.

Contenuto dell'argomento

  • Cause comuni di problemi di prestazioni

  • Ottimizzazione delle prestazioni di indici full-text

  • Risoluzione dei problemi relativi alle prestazioni di popolamenti completi

  • Risoluzione dei problemi relativo alla riduzione delle prestazioni di indicizzazione a causa di filtri

Cause comuni di problemi di prestazioni

La causa principale del calo delle prestazioni di esecuzione dell'indicizzazione full-text è data dai limiti delle risorse hardware:

  • Se l'utilizzo della CPU da parte del processo host del daemon di filtri (fdhost.exe) o del processo SQL Server (sqlservr.exe) ha quasi raggiunto il 100%, il collo di bottiglia è rappresentato dalla CPU stessa.

  • Se la lunghezza media della coda di attesa del disco è superiore al doppio del numero di testine, il collo di bottiglia è rappresentato dal 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 acquistare dischi più veloci e utilizzare RAID.

  • In caso di memoria fisica insufficiente (limite di 3 GB), il collo di bottiglia è rappresentato dalla memoria. I limiti di memoria fisica sono possibili in tutti i sistemi e nei sistemi a 32 bit la pressione della memoria virtuale può rallentare l'indicizzazione full-text.

    [!NOTA]

    A partire da SQL Server 2008, il motore di ricerca full-text, in quanto parte del processo sqlservr.exe, può utilizzare memoria AWE.

Se nel sistema non vengono rilevati colli di bottiglia a livello dell'hardware, le prestazioni di indicizzazione della ricerca full-text dipendono principalmente dagli elementi seguenti:

  • Tempo necessario per la creazione di batch full-text in SQL Server.

  • Velocità di utilizzo di tali batch da parte del daemon di filtri.

[!NOTA]

A differenza del popolamento completo, i popolamenti incrementale, manuale e con rilevamento automatico delle modifiche non sono progettati per ottimizzare le risorse hardware ai fini di una maggiore velocità. Di conseguenza, questi suggerimenti di ottimizzazione potrebbero non migliorare le prestazioni di esecuzione dell'indicizzazione full-text.

Al termine di un popolamento, viene attivato un processo di unione conclusivo che associa i frammenti di indice in un singolo indice full-text master. Ciò consente prestazioni di query superiori poiché è necessario eseguire query solo sull'indice master anziché su alcuni frammenti di indice ed è possibile utilizzare statistiche di punteggio migliori per la classificazione della pertinenza. Si noti che l'unione nell'indice master può richiedere l'esecuzione di molte operazioni di I/O, in quanto è necessario leggere e scrivere grandi quantità di dati, ma questa operazione non blocca le query in entrata.

Nota importanteImportante

L'unione nell'indice master di una grande quantità di dati può comportare la creazione di una transazione con esecuzione prolungata, con il conseguente ritardo del troncamento del log 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. È consigliabile verificare che il log delle transazioni contenga spazio sufficiente per una transazione con esecuzione prolungata prima di riorganizzare un indice full-text di grandi dimensioni in un database in cui viene utilizzato il modello di recupero con registrazione completa. Per ulteriori informazioni, vedere Gestione delle dimensioni del file di log delle transazioni.

[TORNA ALL'INIZIO]

Ottimizzazione delle prestazioni di indici full-text

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

  • Per utilizzare al meglio tutti i processori o i core, impostare sp_configure ‘max full-text crawl ranges’ sul numero di CPU nel sistema. Per informazioni su questa opzione di configurazione, vedere Opzione di configurazione del server max full-text crawl range.

  • 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. È consigliabile che la colonna utilizzata come chiave full-text sia di un tipo di dati integer.

  • Aggiornare le statistiche della tabella di base utilizzando l'istruzione UPDATE STATISTICS. Un'operazione ancora più importante consiste nell'aggiornamento delle statistiche nell'indice cluster o nella chiave full-text per un popolamento completo. In questo modo, tramite un popolamento a più intervalli è possibile generare partizioni ottimali nella tabella.

  • Compilare un indice secondario in una colonna timestamp per migliorare le prestazioni di esecuzione del popolamento incrementale.

  • Prima di eseguire un popolamento completo in un computer di grandi dimensioni con più CPU, è consigliabile limitare temporaneamente la dimensione del pool di buffer impostando il valore max server memory in modo tale da lasciare una quantità di memoria sufficiente per il processo fdhost.exe e il sistema operativo. Per ulteriori informazioni, vedere "Stima dei requisiti di memoria del processo host del daemon di filtri (fdhost.exe)" più avanti in questo argomento.

[TORNA ALL'INIZIO]

Risoluzione dei problemi relativi alle prestazioni di popolamenti completi

Per diagnosticare problemi di prestazioni, analizzare i log della ricerca per indicizzazione full-text. Per informazioni sui log di ricerca per indicizzazione, vedere Popolamento degli indici full-text.

Nel caso in cui le prestazioni dei popolamenti completi non raggiungano livelli soddisfacenti, è consigliabile eseguire la procedura di risoluzione dei problemi illustrata di seguito nell'ordine in cui è riportata.

Utilizzo della memoria fisica

Durante un popolamento full-text, è possibile che la memoria disponibile per fdhost.exe o sqlservr.exe diventi insufficiente o si esaurisca. Se il log delle ricerche per indicizzazione full-text indica il riavvio frequente del processo fdhost.exe o la restituzione frequente del codice di errore 8007008, uno di questi processi non dispone di memoria sufficiente. Se fdhost.exe produce dump, in particolare in computer di grandi dimensioni con più CPU, è possibile che la memoria si esaurisca.

[!NOTA]

Per informazioni sui buffer di memoria utilizzati da una ricerca per indicizzazione full-text, vedere sys.dm_fts_memory_buffers (Transact-SQL).

I motivi possibili sono i seguenti:

  • Se la quantità di memoria fisica disponibile durante un popolamento completo è pari a zero, è possibile che il pool di buffer di SQL Server stia utilizzando la maggior parte della memoria fisica presente nel 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 di max server memory è eccessiva, per il processo fdhost.exe possono verificarsi condizioni di memoria insufficiente e l'impossibilità di allocare memoria condivisa.

    [!NOTA]

    Durante un popolamento full-text in un computer con più CPU, tra fdhost.exe e sqlservr.exe può verificarsi una contesa per la memoria del pool di buffer. La conseguente mancanza di memoria condivisa genera tentativi batch, sovraccarico della memoria e dump da parte del processo fdhost.exe.

    È possibile risolvere questo problema impostando in modo appropriato il valore max server memory del pool di buffer di SQL Server. Per ulteriori informazioni, vedere "Stima dei requisiti di memoria del processo host del daemon di filtri (fdhost.exe)" più avanti in questo argomento. Può inoltre risultare utile ridurre la dimensione del batch per l'indicizzazione full-text.

  • Problema di paging

    Anche le dimensioni insufficienti del file di paging, ad esempio in un sistema con un file di paging ridotto con crescita limitata, possono generare condizioni di memoria insufficiente per fdhost.exe o sqlservr.exe.

    Se nei log delle ricerche per indicizzazione full-text non sono riportati errori di memoria, è probabile che le prestazioni non siano ottimali a causa del paging eccessivo.

[TORNA ALL'INIZIO]

Stima dei requisiti di memoria per il processo host del daemon di filtri (fdhost.exe)

La quantità di memoria richiesta dal processo fdhost.exe per un popolamento dipende principalmente dal numero di intervalli di ricerca per indicizzazione full-text utilizzati, dalla dimensione della memoria condivisa in ingresso e dal numero massimo di istanze di tale memoria.

È possibile stimare approssimativamente la quantità di memoria (in byte) utilizzata dall'host del daemon di filtri tramite la formula seguente:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

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

Variabile

Valore predefinito

number_of_crawl_ranges

Numero di 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

Nella tabella seguente vengono illustrate le linee guida da seguire per stimare i requisiti di memoria di fdhost.exe. Le formule contenute in questa tabella utilizzano i valori riportati di seguito.

  • F: stima della memoria richiesta da fdhost.exe (in MB).

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

  • M: impostazione max server memory ottimale.

Nota importanteImportante

Per informazioni essenziali sulle formule, vedere 1, 2 e 3 di seguito.

Piattaforma

Stima dei requisiti di memoria di fdhost.exe in MB: F1

Formula per il calcolo del valore max server memory: M2

x86

F = Number of crawl ranges * 50

M = minimum(T, 2000) – F – 500

x64

F = Number of crawl ranges * 10 * 8

M = T – F – 500

1 Se sono in corso più popolamenti completi, calcolare i requisiti di memoria di fdhost.exe per ciascuno separatamente, come F1, F2 e così via, quindi calcolare M come T**.** sigma**(Fi)**.

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

3. Si presuppone che ism_size sia di 8 MB per le piattaforme x64.

Esempio: Stima dei requisiti di memoria di fdhost.exe

Questo esempio è relativo a un computer AMD64 con 8 GB di RAM e 4 processori dual core. Il primo calcolo consente di stimare i requisiti di memoria di fdhost.exe, ovvero F. Il numero di intervalli di ricerca per indicizzazione è 8.

F = 8*10*8=640

Il calcolo successivo ottiene il valore ottimale per max server memory.M. T - Memoria fisica totale disponibile nel sistema in - T- è 8192.

M = 8192-640-500=7052

Esempio: Impostazione del valore max server memory

In questo esempio vengono utilizzate le istruzioni Transact-SQL sp_configure e RECONFIGURE per impostare max server memory sul valore calcolato per M nell'esempio precedente, 7052:

USE master;
GO
EXEC sp_configure 'max server memory', 7052;
GO
RECONFIGURE;
GO

Per impostare l'opzione di configurazione max server memory

[TORNA ALL'INIZIO]

Fattori che possono ridurre l'utilizzo della CPU

È previsto che le prestazioni di esecuzione dei popolamenti completi non siano ottimali quando l'utilizzo medio della CPU è inferiore al 30 percento. In questa sezione vengono illustrati alcuni fattori che influiscono sull'utilizzo della CPU.

  • Lunga attesa di pagine

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

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

    Nella tabella seguente vengono descritti i tipi di attesa relativi a questo contesto.

    Tipo di attesa

    Descrizione

    Possibile soluzione

    PAGEIO_LATCH_SH (_EX o _UP)

    Può indicare un collo di bottiglia a livello di IO, caso in cui anche la lunghezza media della coda del disco sarebbe elevata.

    Lo spostamento dell'indice full-text in un filegroup diverso in un disco diverso potrebbe contribuire a ridurre il collo di bottiglia a livello di IO.

    PAGELATCH_EX (o _UP)

    Può indicare contese tra i thread che tentano di scrivere nello stesso file di database.

    L'aggiunta di file al filegroup in cui risiede l'indice full-text potrebbe contribuire ad attenuare queste contese.

    Per ulteriori informazioni, vedere sys.dm_os_wait_stats (Transact-SQL).

  • Analisi inefficaci della tabella di base

    Un popolamento completo esegue l'analisi della tabella di base per produrre batch. Tali analisi potrebbero risultare inefficaci negli scenari seguenti:

    • Se la tabella di base dispone di una percentuale elevata di colonne esterne alle righe sottoposte a indicizzazione full-text, il collo di bottiglia potrebbe essere causato proprio dall'analisi della tabella di base per produrre batch. In questo caso, lo spostamento dei dati di dimensioni inferiori all'interno delle righe tramite varchar(max) o nvarchar(max) potrebbe risolvere il problema.

    • Se la tabella di base è molto frammentata, l'analisi potrebbe risultare inefficace. Per informazioni sul calcolo dei dati esterni alle righe e sulla frammentazione dell'indice, vedere sys.dm_db_partition_stats (Transact-SQL) e sys.dm_db_index_physical_stats (Transact-SQL).

      Per ridurre la frammentazione, è possibile riorganizzare o ricompilare l'indice cluster. Per ulteriori informazioni, vedere Riorganizzare e ricompilare gli indici.

[TORNA ALL'INIZIO]

Risoluzione dei problemi relativo alla riduzione delle prestazioni di indicizzazione a causa di filtri

Durante il popolamento di un indice full-text, il motore di ricerca full-text utilizza due tipi di filtri, a thread singolo e multithread. Alcuni documenti, quali i documenti di Microsoft Word, vengono filtrati utilizzando un filtro multithread, mentre altri, ad esempio i documenti PDF (Portable Document Format) di Adobe Acrobat, vengono filtrati utilizzando un filtro a thread singolo.

Ai fini della sicurezza, i filtri vengono caricati dai processi dell'host del daemon di filtri. Un'istanza del server utilizza un processo multithread per tutti i filtri multithread 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 di ricerca full-text utilizza il processo multithread per esaminare il contenuto in formato Word e avvia un processo a thread singolo per esaminare il contenuto in formato PDF. Un filtro a thread singolo potrebbe tuttavia non funzionare in modo corretto in questo ambiente e potrebbe destabilizzare il processo di filtraggio. In alcune situazioni in cui i documenti incorporati rappresentano una prassi comune, la destabilizzazione potrebbe provocare errori irreversibili nel processo di filtraggio. In questo caso, il motore di ricerca full-text reindirizza tutti i documenti che hanno provocato l'errore, ad esempio un documento di Word in cui è incorporato contenuto in formato PDF, al processo di filtraggio a thread singolo. Se il reindirizzamento viene eseguito di frequente, le prestazioni del processo di indicizzazione full-text risultano ridotte.

Per risolvere questo problema, è necessario contrassegnare il filtro per il documento contenitore, in questo caso il documento di Word, come filtro a thread singolo. È possibile modificare il valore del Registro di sistema per il filtro per contrassegnare un filtro specifico come filtro a thread singolo. Per contrassegnare un filtro come filtro a thread singolo, è necessario impostare il valore ThreadingModel del Registro di sistema per il filtro su Apartment Threaded. Per informazioni sugli apartment a thread singolo, vedere il white paper Understanding and Using COM Threading Models.

[TORNA ALL'INIZIO]

Vedere anche

Attivitá

Risoluzione dei problemi nell'indicizzazione full-text

Riferimento

sys.dm_fts_memory_buffers (Transact-SQL)

sys.dm_fts_memory_pools (Transact-SQL)

Concetti

Opzioni di configurazione del server Server Memory

Opzione di configurazione del server max full-text crawl range

Popolamento degli indici full-text

Creazione e gestione di indici full-text