Melhorar o desempenho de índices Full-Text

Aplica-se a:SQL ServerBanco de Dados SQL do Azure

Este tópico descreve algumas das causas comuns de baixo desempenho para índices e consultas de texto completo. Ele também fornece algumas sugestões para mitigar esses problemas e melhorar o desempenho.

Causas comuns de problemas de desempenho

Problemas de recursos de hardware

O desempenho da indexação de texto completo e das consultas de texto completo é influenciado por recursos de hardware, como memória, velocidade do disco, velocidade da CPU e arquitetura da máquina.

A principal causa para o desempenho reduzido da indexação de texto completo são os limites de recursos de hardware.

• CPU. Se a utilização da CPU pelo processo de host do daemon de filtragem (fdhost.exe) ou pelo processo do SQL Server (sqlservr.exe) estiver próxima de 100%, a CPU é o gargalo.

• Memória. Se houver falta de memória física, a memória pode ser o gargalo.

• Disk. Se o comprimento médio da fila de espera do disco for mais de duas vezes o número de cabeças de disco, há um afunilamento no disco. A principal solução é criar catálogos de texto completo separados dos logs e arquivos de banco de dados do SQL Server. Coloque os logs, arquivos de banco de dados e catálogos de texto completo em discos separados. A instalação de discos mais rápidos e o uso de RAID também podem ajudar a melhorar o desempenho da indexação.

  Observação

  A partir do SQL Server 2008 (10.0.x), o mecanismo de Full-Text pode usar memória AWE porque o mecanismo de Full-Text faz parte do processo de sqlservr.exe. Para obter mais informações, consulte Full-Text Arquitetura de pesquisa.

Problemas de processamento em lote de texto completo

Se o sistema não tiver gargalos de hardware, o desempenho de indexação da pesquisa de texto completo depende principalmente do seguinte:

• Quanto tempo leva o SQL Server para criar lotes de texto completo.

• A rapidez com que o daemon de filtro pode consumir esses lotes.

Problemas de população do índice de texto completo

• Tipo de população. Ao contrário da população total, a população de rastreamento de alterações incrementais, manuais e automáticas não foi projetada para maximizar os recursos de hardware para alcançar uma velocidade mais rápida. Portanto, as sugestões de ajuste neste tópico podem não melhorar o desempenho da indexação de texto completo quando ele usa a população de controle de alterações incremental, manual ou automática.

• Mescla principal. Quando uma compilação é concluída, é acionado um processo de mesclagem final que combina os fragmentos de índice em um índice mestre de texto completo. Isso resulta em um melhor desempenho da consulta, uma vez que apenas o índice mestre precisa ser consultado, em vez de vários fragmentos de índice, e melhores estatísticas de pontuação podem ser usadas para classificação de relevância. No entanto, a mesclagem mestre pode ser intensiva em E/S, porque grandes quantidades de dados devem ser gravadas e lidas quando fragmentos de índice são mesclados, embora não bloqueie consultas de entrada.

  A mesclagem mestre de uma grande quantidade de dados pode criar uma transação de longa duração, atrasando o truncamento do log de transações durante o ponto de verificação. Nesse caso, sob o modelo de recuperação completa, o log de transações pode crescer significativamente. Como prática recomendada, antes de reorganizar um índice de texto completo grande em um banco de dados que usa o modelo de recuperação completa, verifique se o log de transações contém espaço suficiente para uma transação de longa duração. Para obter mais informações, consulte gerenciar o tamanho do arquivo de log de transações.

Ajustar o desempenho de índices de texto completo

Para maximizar o desempenho de seus índices de texto completo, implemente as seguintes práticas recomendadas:

• Para usar todos os processadores ou núcleos de CPU ao máximo, defina sp_configure 'max full-text crawl range' para o número de CPUs no sistema. Para obter informações sobre esta opção de configuração, consulte max full-text crawl range Opção de Configuração do Servidor.

• Certifique-se de que a tabela base tem um índice clusterizado. Use um tipo de dados inteiro para a primeira coluna do índice clusterizado. Evite usar GUIDs na primeira coluna do índice clusterizado. Uma população multi-faixa num índice agrupado pode produzir a maior velocidade de população. Recomendamos que a coluna que serve como chave de texto completo seja um tipo de dados inteiro.

• Atualize as estatísticas da tabela base usando a instrução UPDATE STATISTICS. Mais importante, atualize as estatísticas sobre o índice agrupado ou a chave de texto completo para uma população completa. Isso ajuda uma população de múltiplas faixas a gerar boas partições na tabela.

• Antes de executar uma população completa num computador grande com várias CPUs, recomendamos que limite temporariamente o tamanho do pool de buffers, definindo o valor de de memória máxima do servidor para, para deixar memória suficiente para o processo de fdhost.exe e para o uso do sistema operativo. Para obter mais informações, consulte Estimativa dos Requisitos de Memória do Processo Host do Daemon de Filtro (fdhost.exe), mais adiante neste tópico.

• Se utilizar uma população incremental com base numa coluna de carimbo de data/hora, crie um índice secundário na coluna de carimbo de data/hora para melhorar o desempenho do processo de população incremental.

Solucionar problemas de desempenho de populações completas

Revise os logs de rastreamento de texto completo

Para ajudar a diagnosticar problemas de desempenho, examine os logs de rastreamento de texto completo.

Quando ocorre um erro durante um rastreamento, o recurso de registo de rastreamento da Pesquisa Full-Text cria e mantém um registo de rastreamento, que é um ficheiro de texto simples. Cada log de rastreamento corresponde a um catálogo de texto completo específico. Por padrão, os logs de rastreamento de uma determinada instância (neste exemplo, a instância padrão) estão localizados em %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG pasta.

O arquivo de log de rastreamento segue o seguinte esquema de nomenclatura:

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

As partes variáveis do nome do arquivo de log de rastreamento são as seguintes.

• < DatabaseID> - A ID de um banco de dados. < dbid> é um número de cinco dígitos com zeros à esquerda.
• < FullTextCatalogID> - ID de catálogo de texto completo. < catid> é um número de cinco dígitos com zeros à esquerda.
• < n> - É um número inteiro que indica que existem um ou mais logs de rastreamento do mesmo catálogo de texto completo.

Por exemplo, SQLFT0000500008.2 é o arquivo de log de rastreamento para um banco de dados com ID de banco de dados = 5 e ID de catálogo de texto completo = 8. O 2 no final do nome do arquivo indica que há dois arquivos de log de rastreamento para esse par banco de dados/catálogo.

Verificar o uso da memória física

Durante uma população de texto completo, é possível que fdhost.exe ou sqlservr.exe fiquem com pouca memória ou fiquem sem memória.

• Se o log de rastreamento de texto completo mostrar que fdhost.exe está sendo reiniciado com frequência ou que o código de erro 8007008 está sendo retornado, isso significa que um desses processos está ficando sem memória.
• Se fdhost.exe estiver produzindo dumps, especialmente em computadores grandes com várias CPUs, ele pode estar ficando sem memória.
• Para obter informações sobre buffers de memória usados por uma varredura de texto completo, consulte sys.dm_fts_memory_buffers (Transact-SQL).

As possíveis causas de problemas de memória insuficiente ou falta de memória são as seguintes:

• Memória insuficiente. Se a quantidade de memória física disponível durante uma população completa for zero, o pool de buffers do SQL Server pode estar consumindo a maior parte da memória física no sistema.

  O processo de sqlservr.exe tenta capturar toda a memória disponível para o pool de buffers, até a memória máxima do servidor configurada. Se a alocação de memória máxima do servidor para for demasiado grande, podem ocorrer condições de falta de memória e falha na alocação de memória partilhada para o processo de fdhost.exe.

  Você pode resolver esse problema definindo o max server memory valor do pool de buffers do SQL Server adequadamente. Para obter mais informações, consulte Estimativa dos Requisitos de Memória do Processo Host do Daemon de Filtro (fdhost.exe), mais adiante neste tópico. Reduzir o tamanho do lote usado para indexação de texto completo também pode ajudar.

• Contenção de memória. Durante uma população de texto completo em um computador com várias CPUs, a contenção para a memória do pool de buffers pode ocorrer entre fdhost.exe ou sqlservr.exe. A falta resultante de memória compartilhada causa repetições em lote, falhas de memória e despejos pelo processo fdhost.exe.

• Problemas de paginação. O tamanho insuficiente do arquivo de paginação, como em um sistema que tem um arquivo de paginação pequeno com crescimento restrito, também pode fazer com que o fdhost.exe ou sqlservr.exe fique sem memória. Se os registos de rastreamento não indicarem falhas relacionadas à memória, é provável que o desempenho seja lento devido a uma paginação excessiva.

Estimar os requisitos de memória do processo Filter Daemon Host (fdhost.exe)

A quantidade de memória exigida pelo processo de fdhost.exe para uma população depende principalmente do número de intervalos de rastreamento de texto completo que ele usa, do tamanho da memória compartilhada de entrada (ISM) e do número máximo de instâncias do ISM.

A quantidade de memória (em bytes) consumida pelo host do daemon de filtro pode ser estimada aproximadamente usando a seguinte fórmula:

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

Os valores padrão das variáveis na fórmula anterior são os seguintes:

A tabela a seguir apresenta diretrizes sobre como estimar os requisitos de memória do fdhost.exe. As fórmulas nesta tabela usam os seguintes valores:

• F, que é uma estimativa da memória necessária para fdhost.exe (em MB).

• T, que é a memória física total disponível no sistema (em MB).

• M, que é a configuração ideal de memória máxima do servidor.

Para obter informações essenciais sobre as fórmulas a seguir, consulte as notas que se seguem à tabela.

Notas sobre as fórmulas

1. Se várias populações completas estiverem em andamento, calcule os requisitos de memória fdhost.exe de cada uma separadamente, como F1, F2e assim por diante. Em seguida, calcule M como T- sigma**(_F_i)**.
2. 500 MB é uma estimativa da memória necessária por outros processos no sistema. Se o sistema estiver a fazer trabalho adicional, aumente este valor em conformidade.
3. .ism_size é assumido como sendo 8 MB para plataformas x64.

Exemplo: Estimar os requisitos de memória do fdhost.exe

Este exemplo é para um computador de 64 bits que tem 8 GB de RAM e 4 processadores dual core. As primeiras estimativas de cálculo de memória necessária para fdhost.exe -F. O número de intervalos de rastreamento é 8.

F = 8*10*8 = 640

O próximo cálculo obtém o valor ideal para memória máxima do servidor -M. A memória física total disponível neste sistema em MB-T-é 8192.

M = 8192-640-500 = 7052

Exemplo: Configuração de memória máxima do servidor

Este exemplo usa as instruções sp_configure e RECONFIGURE Transact-SQL para definir memória máxima do servidor para o valor calculado para M no exemplo anterior, 7052:

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

Para obter mais informações sobre as opções de memória do servidor, consulte Server Memory Server Configuration Options.

Verificar o uso da CPU

O desempenho de populações completas não é ideal quando o consumo médio de CPU é inferior a cerca de 30%. Aqui estão alguns fatores que afetam o consumo da CPU.

• Tempo de espera elevado para páginas

  Para descobrir se o tempo de espera de uma página é alto, execute a seguinte instrução Transact-SQL:

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

  A tabela a seguir descreve os tipos de interesse de espera aqui.

  Tipo de espera	Descrição	Resolução possível
  PAGEIO_LATCH_SH (_EX ou _UP)	Isso pode indicar um estrangulamento de E/S, caso em que normalmente ver-se-ia também um comprimento médio de fila de disco alto.	Mover o índice de texto completo para um grupo de ficheiros diferente em um disco diferente pode ajudar a reduzir o gargalo de E/S.
  PAGELATCH_EX (ou _UP)	Isso pode indicar muita contenção entre threads que estão tentando gravar no mesmo arquivo de banco de dados.	Adicionar arquivos ao grupo de arquivos no qual o índice de texto completo reside pode ajudar a aliviar essa contenção.

  Para mais informações, consulte sys.dm_os_wait_stats (Transact-SQL).

• Ineficiências no escaneamento da tabela base

  Uma população completa verifica a tabela base para produzir lotes. Essa verificação de tabela pode ser ineficiente nos seguintes cenários:

  • Se a tabela base tiver uma alta porcentagem de colunas fora de linha que estão sendo indexadas em texto completo, a verificação da tabela base para produzir lotes pode ser o gargalo. Nesse caso, mover os dados menores em linha usando varchar(max) ou nvarchar(max) pode ajudar.

  • Se a tabela base estiver muito fragmentada, a análise pode não ser eficiente. Para obter informações sobre o processamento de dados fora da linha e fragmentação de índices, consulte sys.dm_db_partition_stats (Transact-SQL) e sys.dm_db_index_physical_stats (Transact-SQL).

    Para reduzir a fragmentação, você pode reorganizar ou reconstruir o índice clusterizado. Para obter mais informações, consulte Reorganizar e reconstruir índices.

Solucionar problemas de indexação lenta de documentos

O Full-Text Engine usa dois tipos de filtros quando preenche um índice de texto completo: filtros multiencadeados e filtros de encadeamento único.

• Certos documentos, como os documentos do Microsoft Word, são filtrados utilizando um filtro multiencadeado.
• Outros documentos, como documentos Adobe Acrobat Portable Document Format (PDF), são filtrados usando um filtro de thread único.

Por motivos de segurança, os filtros são carregados pelos processos de host do daemon de filtro. Uma instância de servidor usa um processo multifios para todos os filtros multifios e um processo monofio para todos os filtros monofio. Quando um documento que usa um filtro multithreaded contém um documento incorporado que usa um filtro de thread único, o mecanismo de Full-Text inicia um processo de thread único para o documento incorporado. Por exemplo, ao encontrar um documento do Word que contém um documento PDF, o mecanismo de Full-Text utiliza o processo multiencadeado para o conteúdo do Word e inicia um processo de encadeamento único para o conteúdo PDF. No entanto, um filtro de thread único pode não funcionar bem nesse ambiente e pode desestabilizar o processo de filtragem. Em certas circunstâncias em que essa incorporação é comum, a desestabilização pode levar a falhas do processo. Quando isso ocorre, o mecanismo de Full-Text redireciona qualquer documento com falha - por exemplo, um documento do Word que contém conteúdo PDF incorporado - para o processo de filtragem de thread único. Se o reencaminhamento ocorrer com frequência, isso resultará na degradação do desempenho do processo de indexação de texto completo.

Para contornar este problema, marque o filtro para o documento contentor (o documento do Word, neste exemplo) como um filtro de encadeamento único. Para marcar um filtro como um filtro de thread único, defina o valor do Registro ThreadingModel para o filtro como Apartment Threaded. Para obter informações sobre apartamentos de thread único, consulte o documento técnico Understanding and Using COM Threading Models.

Ver também

Opções de configuração do servidor de memória do servidor
intervalo máximo de rastreamento de texto completo Opção de configuração do servidor
Preencher Full-Text índices
Criar e gerir Full-Text índices
sys.dm_fts_memory_buffers (Transact-SQL)
sys.dm_fts_memory_pools (Transact-SQL)
Solucionar problemas de indexação Full-Text
Full-Text Arquitetura de pesquisa