Ottimizzazione delle prestazioni degli indici full-text
Il livello delle prestazioni di esecuzione dell'indicizzazione e delle query full-text può dipendere da risorse hardware quali memoria e velocità del disco e della CPU, nonché dall'architettura del computer. 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.
.gif "Nota importante") Importante |
|---|
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. |
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 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.
Risoluzione dei problemi relativi alle prestazioni di un popolamento completo
Per diagnosticare problemi di prestazioni, analizzare i log della ricerca per indicizzazione full-text. Per informazioni su tali log, vedere Risoluzione di errori in un popolamento full-text (ricerca per indicizzazione).
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, ad esempio un sistema IA64 a 64 vie, tra fdhost.exe e sqlservr.exe può verificarsi un conflitto 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.
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.
| Importante |
|---|
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 con AWE disabilitato |
F=Number of crawl ranges* 50 |
M=minimum(T, 2000)–F– 500 |
x86 con AWE abilitato |
F=Number of crawl ranges* 50 |
M=T–F– 500 |
x64 o IA643 |
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 .ism_size si presuppone di dimensioni 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 consente di ottenere il valore ottimale per max server memory -M. TLa memoria fisica totale nel sistema in MB -T - è 8192.
M = 8192-640-500=7052
Esempio: Impostazione del valore max server memory
In questo esempio vengono utilizzate le istruzioni sp_configure e RECONFIGURETransact-SQL 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
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 conflitti 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 questi conflitti.
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 Riorganizzazione e ricompilazione degli indici.
Vedere anche