SQL Server 2005 Full-Text

Recentemente tive que fazer um ajuste no desempenho dos resultados de pesquisas utilizando o Full-Text no SQL Server 2005. Para quem não sabe, o SQL Server possui uma ferramenta que permite realizar pesquisas textuais no BD, chamado Full-Text Search.

Antes de começar é bom deixar claro que a performance fica realmente comprometida quando o resultado supera os 100.000 registros. Este post trata apenas de ajustes de software e não de hardware como separação física de bases de dados, etc, que podem trazer grandes melhorias do desempenho.

A base de dados ajustada contém pouco mais de 6 milhões de registros, ocupa 20Gb e possui um catálogo (utilizado pelo Full-Text Search para buscas) com 4 Gb.

Como otimizar as pesquisas (você precisa ter um conhecimento mínimo no FTS a partir de agora):

1) Utilizar CONTAINSTABLE no lugar de CONTAINS para grandes pesquisas

- Quando você utiliza o CONTAINS em uma condição WHERE, implicitamente é realizado um Join para combinar os IDs retornados pelo CONTAINS com os registros filtrados pelo WHERE, e isto degrada a performance quando utilizado em grandes bases de dados.

2) Incluir condições de pesquisa no texto indexado

- Ou seja, incluir no texto indexado pelo Full-Text Search todas as palavras utilizadas como restrição no WHERE, exemplo: suponha uma biblioteca com o conteúdo dos textos dos livros indexados, se você pesquisa por texto e restringe a pesquisa apenas para os livros de determinado assunto, o texto do assunto deve ser incluído no texto do livro, ou seja, após o texto do livro, inclú-a algo como: ASSUNTO:TECNOLOGIA, supondo que o livro seja do assunto Tecnologia, agora utilize a palavra acima com o CONTAINSTABLE.

3) Aumentar o time-out do full-text (SQL Server 2005 SP2)

EXEC sp_configure 'remote query timeout', 10000 -- o padrão é 600 segundos

combine com a seguir:

EXEC sp_fulltext_querytimeout @ftcat = 'nomedocatalogoFT', @timeout = 10000000 -- em milisegundos

4) Configure memória suficiente para o Full-Text Search

Deixe o ajuste de performance do sistema em "System cache", da seguinte forma: botão da direita em "Meu Computador", selecione "Propriedades", "Avançado", "Ajuste de performance", abra a caixa de diálogo "Avançado" na seção de uso de memória, selecione melhor performance para "Cache do sistema".

5) Outras configurações

Como regra geral, configure o serviço Full-Text para criar indices de acordo com SLA, para isto a opção full-text crawl deve ser definida como 2x o número de processadores físicos do servidor, no meu caso segue a configuração:

EXEC sp_configure 'max full-text crawl range', 2

(obs: se der erro, execute antes: EXEC sp_configure 'show advanced option', '1' para permitir visualização e ajustes das opções avançadas)

Em seguida, ajuste o uso de recursos para o full-text:

EXEC sp_fulltext_service 'resource_usage', 5 -- padrão 3

Neste caso, a máquina fica mais dedicada para o uso com pesquisas Full-text e irá utilizar até 100% do processador para isto.

Apenas com os ajustes acima, tive um ganho de 60% no retorno das pesquisas. Você pode também estudar e modificar suas cláusulas SQL. Para concluir, não existe uma 'receita de bolo' para otimizar as pesquisas com Full-Text, você terá que experimentar, ou seja, o processo é um pouco empírico.

Algumas fontes de referência:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx?pf=true
http://msdn2.microsoft.com/en-us/library/ms915345.aspx

Além do books online!