Melhorar o desempenho dos índices de texto completo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Este artigo aborda as causas comuns do fraco desempenho em índices e consultas em texto completo, e como mitigá-las.

Causas comuns de problemas de desempenho

Esta secção descreve as causas de problemas comuns de desempenho quando se utilizam índices em texto completo.

Problemas de recursos de hardware

Recursos de hardware como memória, velocidade do disco, velocidade do CPU e arquitetura da máquina afetam o desempenho da indexação e das consultas em texto completo.

Os limites de recursos do hardware causam uma redução do desempenho da indexação de texto completo.

• CPU. Se o uso da CPU pelo processo anfitrião do daemon do filtro (fdhost.exe) ou pelo processo do SQL Server (sqlservr.exe) estiver próximo de 100 por cento, a CPU é o gargalo.

• Memória. A falta de memória física pode causar um estrangulamento de desempenho.

• Disk. Se o comprimento médio da fila de espera do disco for superior ao dobro do número de cabeças de disco, existe um gargalo 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.

Problemas de processamento em lote de texto completo

Se o sistema não tiver gargalos de hardware, o desempenho de indexação da pesquisa em texto completo depende principalmente dos seguintes fatores:

• Quanto tempo demora o Database Engine a criar lotes em texto completo.

• A rapidez com que o daemon do filtro consome esses lotes.

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

• Tipo de população. Ao contrário da população completa, a população de monitorização incremental, manual e automática não é concebida para maximizar os recursos de hardware e alcançar uma velocidade mais rápida. Portanto, as sugestões de afinação neste artigo podem não melhorar o desempenho da indexação de texto completo quando utiliza população de monitorização incremental, manual ou automática.

• Mescla principal. Quando uma população termina, um processo final de fusão funde os fragmentos do índice num único índice mestre em texto completo. Este processo resulta numa melhoria do desempenho das consultas, uma vez que apenas o índice mestre precisa de ser consultado e não um número de fragmentos de índice. Melhores estatísticas de pontuação podem ser usadas para classificar a relevância. No entanto, a fusão mestre pode ser intensiva em I/O porque grandes quantidades de dados devem ser escritas e lidas quando fragmentos de índice são fundidos. No entanto, não bloqueia as consultas recebidas.

  A fusão master de uma grande quantidade de dados pode criar uma transação de longa duração, atrasando o truncamento do registo de transações durante o checkpoint. 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 mais informações, consulte Gerir o tamanho do ficheiro de registo 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 núcleos da CPU ao máximo, altere max full-text crawl range o número de núcleos no sistema. Para mais informações, consulte configuração do servidor: alcance máximo de rastreamento de texto completo.

• 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. Use um tipo de dado inteiro para a coluna que serve como chave de texto completo.

• Atualize as estatísticas da tabela base usando a UPDATE STATISTICS instrução. Mais importante, atualize as estatísticas sobre o índice agrupado ou a chave de texto completo para uma população completa. Esta ação ajuda uma população com vários intervalos a gerar boas partições na tabela.

• Antes de realizar uma população completa num grande computador multi-core, limite temporariamente o tamanho do pool de buffers definindo o max server memory valor para deixar memória suficiente para o fdhost.exe processo e o uso do sistema operativo. Para mais informações, consulte Estimar os requisitos de memória do processo host do daemon do filtro (fdhost.exe), mais adiante neste artigo.

• 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

Consulte a secção seguinte para resolver problemas de desempenho com populações completas.

Revise os logs de rastreamento de texto completo

Para ajudar a diagnosticar problemas de desempenho, consulte os registos de rastreamento em 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>.[<n>]

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

• <DatabaseID>: O identificador de uma base de dados, sob a forma de um número de cinco dígitos com zeros à esquerda.

• <FullTextCatalogID>: ID de catálogo em texto completo, como um número de cinco dígitos com zeros à esquerda.

• <n>: Um número inteiro que indica que existem um ou mais registos de pesquisa relativos ao 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 um preenchimento de texto completo, o processo fdhost.exe ou sqlservr.exe pode ficar com pouca memória disponível, ou até ficar sem memória.

• Se o registo de rastreamento em texto completo mostrar que fdhost.exe reinicia frequentemente ou devolve código de erro 8007008, significa que um destes processos está a ficar sem memória.

• Se fdhost.exe gerar dumps, especialmente em sistemas de grande dimensão e multicore, pode estar a ficar sem memória.

• Para obter informações sobre os buffers de memória utilizados por um rastreio de texto integral, veja sys.dm_fts_memory_buffers.

As possíveis causas de baixa memória ou problemas de falta de memória incluem os seguintes elementos:

• 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 Database Engine pode estar a consumir a maior parte da memória física do 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 max server memory for demasiado grande, podem ocorrer condições de falta de memória e falha na alocação de memória partilhada para o fdhost.exe processo.

  Defina o max server memory valor do buffer pool do Database Engine de forma adequada para resolver este problema. Para mais informações, consulte Estimar os requisitos de memória do processo host do daemon do filtro (fdhost.exe), mais adiante neste artigo. 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 num sistema multi-core, fdhost.exe pode sqlservr.exe competir por memória de pool de buffer. A consequente ausência de memória partilhada provoca novas tentativas em lote, thrashing da memória e dumps do processo fdhost.exe.

• Problemas de paginação. Um tamanho insuficiente do ficheiro de paginação, como num sistema com um ficheiro de paginação pequeno com crescimento restrito, pode também fazer com que o processo fdhost.exe ou sqlservr.exe fique sem memória. Se os registos de rastreio não indicarem quaisquer falhas relacionadas com a memória, a paginação excessiva está provavelmente a causar lentidão no desempenho.

Calcular os requisitos de memória do processo anfitrião do daemon de filtragem (fdhost.exe)

A quantidade de memória que o fdhost.exe processo necessita para preencher depende principalmente do número de intervalos de rastreamento de texto completo que utiliza, do tamanho da memória partilhada de entrada (ISM) e do número máximo de instâncias ISM.

Pode-se estimar aproximadamente o consumo de memória do host do daemon filtro 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 seguinte apresenta orientações para estimar os requisitos de memória de fdhost.exe. As fórmulas nesta tabela usam os seguintes valores:

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

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

• M, que é a definição ideal max server memory .

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

1. Se houver várias populações completas em progresso, calcule os fdhost.exe requisitos de memória de cada um separadamente, como F1, F2, e assim por diante. Em seguida, calcule M como T - Σ(Fi).

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 assume-se que seja de 8 MB para plataformas x64.

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

Este exemplo é para um computador de 64 bits que tem 8 GB de RAM e 4 processadores dual-core. O primeiro cálculo estima a memória necessária para fdhost.exeF. O número de intervalos de rastreio é 8.

F = 8 * 10 * 8 = 640

O cálculo seguinte obtém o valor ótimo para max server memory (M). A memória física total disponível neste sistema em MB, (T), é 8192.

M = 8192 - 640 - 500 = 7052

Exemplo: Set max server memory

Este exemplo usa as instruções sp_configure e RECONFIGURE Transact-SQL para definir max server memory para o valor calculado para M no exemplo anterior, 7052:

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

Para mais informações sobre as opções de memória do servidor, consulte opções de configuração da memória do servidor.

Verificar o uso da CPU

O desempenho de populações completas não é ótimo quando o consumo médio de CPU é inferior a cerca de 30 por cento. 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 seguinte descreve os tipos de espera relevantes.

  Tipo de espera	Descrição	Resolução possível
  PAGEIO_LATCH_SH (_EX ou _UP)	Este tipo de espera pode indicar um estrangulamento de E/S, sendo esse o caso, normalmente também se observa um comprimento médio elevado da fila de disco.	Mover o índice de texto completo para outro grupo de ficheiros num disco diferente pode ajudar a reduzir o gargalo de I/O.
  PAGELATCH_EX (ou _UP)	Este tipo de espera pode indicar muita contenda entre threads que tentam escrever no mesmo ficheiro de base de dados.	Adicionar ficheiros ao grupo onde se encontra o índice de texto completo pode ajudar a aliviar essa contenção.

  Para obter mais informações, consulte sys.dm_os_wait_stats.

• Ineficiências no escaneamento da tabela base

  Uma população completa verifica a tabela base para produzir lotes. Esta digitalização de tabelas 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. Neste caso, mover os dados mais pequenos para a 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 informações sobre cálculo de dados fora da linha e fragmentação de índices, consulte sys.dm_db_partition_stats e sys.dm_db_index_physical_stats.

    Para reduzir a fragmentação, você pode reorganizar ou reconstruir o índice clusterizado. Para obter mais informações, consulte Otimizar a manutenção do índice para melhorar o desempenho da consulta e reduzir o consumo de recursos.

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.

• Alguns documentos, como documentos Word, utilizam filtros multithread.
• Outros documentos, como os documentos PDF (Adobe Acrobat Portable Document Format), utilizam filtros de execução monothread.

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 multithreaded para todos os filtros multithreaded e um processo single-threaded para todos os filtros single-threaded. 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 rosca única pode não funcionar bem neste 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 esta condição ocorre, o motor de texto completo redireciona qualquer documento cujo processamento falhou (por exemplo, um documento Word que contém conteúdo PDF incorporado) para o processo de filtragem monothread. 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 do documento contentor (o documento do Word, neste exemplo) como um filtro monothread. Para marcar um filtro como filtro de execução monofio, defina o valor de registo ThreadingModel do filtro como Apartment Threaded. Para obter informações sobre apartments de thread única, consulte Compreender e Utilizar os Modelos de Threads do COM.

Conteúdo relacionado

• Opções de configuração de memória do Server
• Configuração do servidor: alcance máximo de rastreamento de texto completo
• Preencher Full-Text índices
• Criar e gerenciar índices de texto completo
• sys.dm_fts_memory_buffers (Transact-SQL)
• sys.dm_fts_memory_pools (Transact-SQL)
• Resolução de problemas na indexação de texto completo
• Full-Text Arquitetura de pesquisa