Blog – Fabiano Neves Amorim

Aqui estou, de volta a rotina, no ônibus a caminho de São Paulo… tempo para ler e escrever um pouco. Por falar em ler vou aproveitar para deixar uma dica ai pra vocês, caso gostem de leitura não deixem de aproveitar a oportunidade de comprar os livros da Série Deixados para Trás no Submarino, a série toda (13 livros) esta em promoção, não, não ganho nada com do Submarino, mas quem gosta de leitura tem que aproveitar… os livros são muito bons, eu li até o oitavo, agora comprei a série toda vou começar a ler tudo denovo… vale muito a pena.

Anyway, vamos logo ao assunto que interessa pra vocês SQL Server.

Entendendo o Problema

Quero compartilhar com vocês um problema bem interessante em uma consulta utilizando TOP e ORDER BY para tabelas com muitos registros.

O problema é o seguinte:  Tenho uma consulta que faz um TOP em uma tabela utilizando a clausula ORDER BY por uma coluna diferente da chave cluster. Quando uso TOP 100 o SQL utiliza 550mb de memória, 9875 de CPU e retorna a consulta em 40 segundos, quando uso TOP 101 o SQL utiliza 798mb de memória, 42745 de CPU e retorna a consulta em 1 minuto e 20 segundos.

A pergunta que não quer calar, porque a inclusão de apenas 1 registro degrada tanto o desempenho?

TOP <= 100 VS TOP > 100

Bom, vamos entender como eu cheguei à conclusão abaixo.

O Operador TOP aplica um SORT para limitar a quantidade de linhas de duas formas, uma utilizando um algoritmo otimizado para ordenar poucas linhas, e outro para ordenar muitas linhas que é o operador de sort normal que conhecemos.

Suponha uma consulta SELECT TOP <N> * FROM TABELA, caso o <N> for >= 100 então o QO utiliza o algoritmo otimizado se o TOP N for > que 100 então ele utiliza o SORT no tempdb. Li esta explicação neste link no connect.

Configuração Ambiente

Entendendo isso, fui realizar os testes para medir a performance, para fazer isso medi o uso CPU, Memória  e Tempo execução das consultas. Utilizei o Profiler para ler o uso de CPU e o Perfom para ler o contador “Total Server Memory”. A cada execução das consultas eu parava e iniciava o banco de dados para obter os valores de uso de memória.

Estou utilizando o SQL Server 2008 versão developer com Service Pack 1, e ele esta configurado para utilizar até 2 gb de memória. Segue a tela de configuração:

Como vocês podem observar nos prints abaixo, criei duas consultas. Ambas fazendo um select com TOP em uma tabela do meu banco de dados, a diferença entre as consultas é justamente o TOP 100 para o TOP 101.

Veja que para o primeiro plano de execução(TOP 100) o SQL Server reservou 1024 kb de memória, e para no segundo plano(TOP 101) ele reservou 224920 kbs de memória.

Veja o gráfico de memória utilizada pelo SQL Server ao rodar a primeira consulta. Repare que o SQL utilizou 553mb de memória e rodou a consulta em 44 segundos(veja no SSMS atrás do PerfMon).

 Agora veja a diferença para a segunda consulta com o TOP 101. Bem diferente não?

Bom galera fica ai a dica… Vale a pena ficar de olho nos TOPs que retornem mais de 100 registros…

Ok ok, eu sei que faltou um item aqui de Conclusão, masss estou cansado, vou tentar dormir um pouco, depois quem sabe não escrevo mais sobre isso?…

Abraços