Melhorando o desempenho de consultas (SELECT) em tabelas grandes no SQL SERVER

"Tenho uma tabela com mais de um milhão de registros. Faço uma consulta... um simples SELECT... mas está demorando muito para retornar. O que fazer?"
Essa é a pergunta que você se faz nessa situação. Todo programador sabe trabalhar com banco, mas nem sempre sabemos utilizar da melhor forma. Um DBA já é craque na situação e sabe a melhor forma de melhorar o desempenho de uma consulta. A dica do dia é: índices! Se você não sabe o que são índices, veja essa matéria aqui de Thiago Pastorello que explica de forma bem compreensiva. Também recomendo que leia também esse artigo da Compute-rs onde explica as vantagens e desvantagens de usar índices.

É só isso? Talvez. Se você entedeu o significado de índices lendo os dois artigos (referência) e fez o que irei demonstrar abaixo pode ser que resolva. Na net você irá encontrar outros artigos semelhantes que irão mostrar outros caminhos que podem ajudar melhor. Aqui será de forma simplificada... Vamos?

Abra o Microsoft SQL Server Management Studio, conecte-se a base de dados e clique em New Query. Escreva a consulta que deseja avaliar e corrigir para ficar mais rápida. No meu caso irei executar uma Stored Procedure que contêm várias sub-consultas com manipulação de agrupamento tais como COUNT, SUM, etc. Na barra de ferramentas marque a opção Include Actual Execution Plan (Incluir Plano de Execução Atual).


Agora clique em Execute ou pressione F5 para executar a query e avaliá-la. Aguarde até que finalize completamente. Vá na última aba abaixo de Plano de Execução e veja os resultados.


Passando o mouse por cima de cada item irá ser exibidos detalhes acerca do custo de processamento. Depois procurem ai no Google mais explicações sobre cada item. Mas vamos nos atrelar a essa mensagem verde. Note que o próprio SQL Server informa que há indices ausentes! Vamos criá-los?

Clique com o direito sobre a mensagem e escolha Detalhes de Índices Ausentes.


Ao clicar, irá aparecer outra tela com um query de criação do índice, exemplo:

/*
Detalhes de Índice Ausentes de SQLQuery1.sql - SERVIDOR.Mailing (thiago (53))
O Processador de Consultas estima que a implementação do índice a seguir pode melhorar o custo da consulta em 99.7047%.
*/
/*
USE [Mailing]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[News_Envio] ([id_campanha])
GO
*/
 
A consulta de criação está praticamente pronta bastanto apenas dar um nome para o índice e remover o comentário, deixando da seguinte forma:
 
USE [Mailing]
GO
CREATE NONCLUSTERED INDEX PI_Index_Envio_Campanha
ON [dbo].[News_Envio] ([id_campanha])
GO

Pressione F5 ou Execute esse bloco de instruções para criar o índice. Antes de testarmos, vamos atualizar os índices e suas estatísticas.

Vá na tabela desejada, na opção de Índices e escolha Reorganizar Índices. Veja o nível de fragmentação e dê OK. Em seguida execute:

UPDATE STATISTICS News_Envio

No caso substitua News_Envio pelo nome de sua tabela. Agora execute novamente o primeiro processo e verá um ganho significativo de velocidade de consulta. O que levava 1 minuto para executar, em menos de 1 segundo obtêm-se o resultado. Bom né?

Aprecie com moderação o uso de índices e leiam as referências para saber quais situações onde é vantagem ou não o uso deles.

3 comentários:

Reginaldo Rodrigues de Oliveira disse...

Extremamente útil este post. Estava com dúvida se deveria ou não utilizar este recurso de criação de índice nonclustered. Porém, fiz exatamente como você falou e ficou uma maravilha. A performance de minha Query que levava 20 minutos em média caiu para absurdos 11 segundos.
Obrigado.

Anônimo disse...

Realmente um post muito interessante, principalmente quem tem dúvidas no assunto, claro que vale lembrar que não é sair criando índices, mas fiz uns testes em algumas tabelas de teste aqui, e deu certo. Muito Bom. Parabéns !

Beto

Edson Alves disse...

Muito bom!

Postar um comentário