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=truehttp://msdn2.microsoft.com/en-us/library/ms915345.aspxAlém do books online!
