Compartilhar via

  • Artigo

Ajuste de desempenho e otimização de índices de texto completo

O desempenho da indexação de texto completo e das consultas de texto completo é influenciado por recursos de hardware, como memória, velocidade de disco, velocidade da CPU, e pela arquitetura do computador. A principal causa da diminuição do desempenho da indexação de texto completo são os limites em termos de recursos de hardware:

  • Se o uso da CPU pelo processo de host do daemon de filtro (fdhost.exe) ou o processo SQL Server (sqlservr.exe) está próximo de 100%, o gargalo é a CPU.

  • Se a média de tamanho da lista de pendências de disco é duas vezes maior do que o número de cabeçotes de disco, há um gargalo no disco. A principal solução alternativa é criar catálogos de texto completo separados dos arquivos e logs de banco de dados do SQL Server. Coloque os logs, arquivos de banco de dados e catálogos de texto completo em discos separados. Comprar discos mais rápidos e usar RAID também pode ajudar a melhorar desempenho de indexação.

  • Se houver uma escassez de memória física (limite de 3 GB), memória poderá ser o gargalo. Limitações de memória física são possíveis em todos os sistemas e, em sistemas de 32 bits, a pressão por memória virtual pode tornar a indexação de texto completo lenta.

    ObservaçãoObservação

    A partir do SQL Server 2008, o Mecanismo de Texto Completo pode usar memória AWE, pois o mecanismo faz parte de sqlservr.exe.

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

  • O tempo necessário para o SQL Server criar lotes de texto completo.

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

ObservaçãoObservação

Ao contrário da população completa, a população de controle de alterações incremental, manual e automática não foi desenvolvida para maximizar os recursos de hardware a fim de obter maior velocidade. Portanto, essas sugestões de ajuste podem não melhorar o desempenho da indexação de texto completo.

Quando a população for concluída, um processo de mesclagem final será disparado, mesclando os fragmentos de índice em um índice de texto completo mestre. Isso resulta em desempenho aprimorado de consultas, uma vez que apenas o índice precisa ser consultado, em vez de uma série de fragmentos de índice, e melhores estatísticas de pontuação podem ser usadas para classificação de relevância. Observe que a mesclagem mestra pode ser de E/S intensiva, porque grandes quantidades de dados precisam ser gravados e lidos quando os fragmentos de índice são mesclados, embora isso não bloqueie as consultas de entrada.

Observação importanteImportante

A mesclagem mestra de grande quantidade de dados pode criar uma transação demorada, atrasando o truncamento do log de transações durante o ponto de verificação. Nesse caso, no 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 demorada. Para obter mais informações, consulte Gerenciando o tamanho do arquivo de logs de transações.

Ajustando o desempenho de índices de texto completo

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

  • Para obter o máximo de todos os processadores ou núcleos, defina sp_configure ‘max full-text crawl ranges’ como o número de CPUs do sistema. Para obter informações sobre essa opção de configuração, consulte Opção max full-text crawl range.

  • Verifique se a tabela base tem um índice clusterizado. Use um tipo de dados integer para a primeira coluna do índice clusterizado. Evite usar GUIDs na primeira coluna do índice clusterizado. Uma população de vários intervalos em um índice clusterizado pode gerar a maior velocidade de população. É recomendável que a coluna que funciona como chave de texto completo tenha um tipo de dados integer.

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

  • Crie um índice secundário em uma coluna timestamp para melhorar o desempenho da população incremental.

  • Antes de executar uma população completa em um computador com várias CPUs, é recomendável limitar o tamanho do pool de buffers temporariamente definindo o valor de max server memory para deixar memória suficiente para o processo do fdhost.exe e para uso do sistema operacional. Para obter mais informações, consulte "Estimando os requisitos de memória do processo do host do daemon de filtro (fdhost.exe)", posteriormente neste tópico.

Solucionando problemas de desempenho de população completa

Para diagnosticar problemas de desempenho, examine os logs de rastreamento de texto completo. Para obter mais informações sobre logs de rastreamento, consulte Solucionando problemas de erros em uma população de texto completo (rastreamento).

É recomendável seguir a ordem de solução de problemas especificada abaixo se o desempenho das populações completas não for satisfatório.

Uso de memória física

Durante uma população de texto completo, é possível que o fdhost.exe ou o sqlservr.exe fique com pouca memória não tenha memória suficiente. 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 indica que um desses processos está sendo executado sem memória. Se fdhost.exe estiver gerando despejos, principalmente em computadores grandes com várias CPUs, talvez ele esteja ficando com memória insuficiente.

ObservaçãoObservação

Para obter informações sobre buffers de memória usados por um rastreamento de texto completo, consulte sys.dm_fts_memory_buffers (Transact-SQL).

As causas possíveis são as seguintes:

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

    O processo do sqlservr.exe tenta obter toda a memória disponível para o pool de buffers, até a memória máxima configurada para o servidor. Se a alocação de memória máxima do servidor for muito grande, condições de memória insuficiente e falha para alocar memória compartilhada poderão ocorrer para o processo do fdhost.exe.

    ObservaçãoObservação

    Durante uma população de texto completo em um computador com várias CPUs, como um computador de IA64 de 64 bits, pode ocorrer contenção da memória do pool de buffers entre fdhost.exe ou sqlservr.exe. A memória compartilhada insuficiente resultante provoca tentativas em lote, sobrecarga de memória e despejos do processo do fdhost.exe.

    Para resolver esse problema, defina o valor max server memory do pool de buffers do SQL Server adequadamente. Para obter mais informações, consulte "Estimando os requisitos de memória do processo do host do daemon de filtro (fdhost.exe)", posteriormente neste tópico. A redução do tamanho do lote usado para indexação de texto completo pode ajudar.

  • Um problema 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 o sqlservr.exe fique com memória insuficiente.

    Se os logs de rastreamento não indicarem nenhuma falha relacionada à memória, é provável que o desempenho esteja lento devido a excesso de paginação.

Estimando os requisitos de memória da memória compartilhada de saída do processo do host do daemon de filtro (fdhost.exe)

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

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

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:

Variável

Valor padrão

number_of_crawl_ranges

O número de CPUs

ism_size

1 MB para computadores x86

4 MB, 8 MB ou 16MB para computadores x64, dependendo da memória física total

max_outstanding_isms

25 MB para computadores x86

5 MB para computadores x64

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

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

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

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

Observação importanteImportante

Para obter informações essenciais sobre as fórmulas, consulte 1, 2e 3, abaixo.

Plataforma

Estimando as necessidades de memória do fdhost.exe em MB — F1

Fórmula para calcular a memória máxima do servidor — M2

x86 com AWE desabilitado

F=Number of crawl ranges* 50

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

x86 com AWE habilitado

F=Number of crawl ranges* 50

M=T–F– 500

x64 ou IA643

F=Number of crawl ranges* 10 * 8

M=T–F– 500

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

2 500 MB é uma estimativa da memória exigida por outros processos no sistema. Se o sistema estiver executando trabalho adicional, aumente esse valor de maneira correspondente.

3 .ism_size é presumido como 8 MB para plataformas x64.

Exemplo: Estimando as necessidades de memória do fdhost.exe

Este exemplo é para um computador AMD64 com 8 GB de RAM e 4 processadores de núcleo dual. O primeiro cálculo estima a 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. T O total de memória física disponível neste sistema, em MB — T — é 8192.

M = 8192-640-500=7052

Exemplo: Definindo a configuração max server memory

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

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

Para definir a opção de configuração da memória máxima do servidor

Fatores que podem reduzir o consumo da CPU

Esperamos que o desempenho das populações completas não seja o ideal quando a média de consumo da CPU for inferior a cerca de 30%. Esta seção discute alguns fatores que afetam o consumo da CPU.

  • Alta espera por páginas

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

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

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

    Tipo de espera

    Descrição

    Solução possível

    PAGEIO_LATCH_SH (_EX ou _UP)

    Isso pode indicar um gargalo de E/S, caso em que normalmente você também observa um comprimento médio da fila de disco alto.

    Mover o índice de texto completo para outro grupo de arquivos em outro disco pode ajudar a reduzir o gargalo de E/S.

    PAGELATCH_EX (ou _UP)

    Isso pode indicar muita contenção entre os threads que estão tentando para gravar no mesmo arquivo de banco de dados.

    Adicionar arquivos ao grupo de arquivos em que reside o índice de texto completo pode ajudar a aliviar essa contenção.

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

  • Ineficiências ao examinar a tabela base

    Uma população completa examina a tabela base para gerar lotes. Esse exame da tabela pode ser ineficiente nos seguintes cenários:

    • Se a tabela base tem uma porcentagem alta de colunas fora de linha que estão sendo indexadas com texto completo, examinar a tabela base para gerar lotes pode ser o gargalo. Nesse caso, pode ser útil mover os dados menores em linha usando varchar(max) ou nvarchar(max).

    • Se a tabela base estiver muito fragmentada, o exame poderá ser ineficiente. Para obter informações sobre como calcular dados fora de 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 recriar o índice clusterizado. Para obter mais informações, consulte Reorganizando e recriando índices.