Performance SQL Server - Índices

Sobre o tema "Performance SQL Server" (Tuning SQL Server) quero fazer uma série de posts para demonstrar os métodos de melhoria de desempenho do banco de dados em relação aos front-end's que recuperam os resultados e exibem aos usuários finais. 

Neste primeiro post, tratarei sobre a criação de índices. Não apresentarei aqui sobre nenhum front-end, mas em futuros posts explicarei sobre o Profiler para obter as instruções utilizadas por qualquer front-end que esteja interagindo com o SQL Server.

O primeiro ponto a esclarecer é que a criação de índices para uma base de baixa performance é um estudo de caso de cada instância em particular. Isso quer dizer que não adianta encontrar um índice que melhora o desempenho de uma base e que podemos sair distribuindo a todos os clientes que estejam utilizando mesmo software. Algumas vezes sim, se reaproveita. 

Por isso, o primeiro passo é verificar o que o SQL Server desta instância que estamos analisando nos diz sobre algum SELECT. O exemplo que irei passar aqui estou usando um código bruto, recorrendo a um caso típico que forço o SQL Server reconhecer que há necessidade de um índice. Este caso é uma situação em que devemos distribuir a todos os clientes o índice criado, pois trata-se de uma melhoria relacional básica e genérica.

Quando fazemos uma modelagem de tabelas bem estruturada, a criação de índices fica difícil de reconhecer. Vou iniciar criando uma tabela de produtos Swd_Produtos no qual vou popular com produtos têxteis (fios, tecidos e malhas) gerando uma codificação sequencial mas com um prefixo em cada produto (F para Fio, TEC para Tecido e MAL para Malha):

-- Produtos gerais
If object_id('Swd_Produtos') IS NULL 
BEGIN

 -- DROP TABLE Swd_Produtos

 CREATE TABLE dbo.Swd_Produtos
 (
  Id_CodProd int        NOT NULL,
  Codigo varchar(10)    NULL,
  Descricao varchar(50)    NULL,

  CONSTRAINT PK_Swd_Produtos PRIMARY KEY (Id_CodProd)
 )

 -- Vamos popular com 1500 produtos têxteis: 500 fios, 500 tecidos e 500 malhas
 Declare @i int = 1

 While (@i <= 500)
 Begin
   insert into Swd_Produtos (Id_CodProd, Codigo, Descricao)
   Values (@i, 'F'+Right('00000'+Cast(@i as varchar),5), 'FIO 100% CO ORDEM '+Cast(@i as varchar))

   set @i = @i + 1
 End

 While (@i <= 1000)
 Begin
   insert into Swd_Produtos (Id_CodProd, Codigo, Descricao)
   Values (@i, 'TEC'+Right('00000'+Cast(@i as varchar),5), 'TECIDO 100% CO ORDEM '+Cast(@i as varchar))

   set @i = @i + 1
 End

 While (@i <= 1500)
 Begin
   insert into Swd_Produtos (Id_CodProd, Codigo, Descricao)
   Values (@i, 'MAL'+Right('00000'+Cast(@i as varchar),5), 'MALHA 100% CO ORDEM '+Cast(@i as varchar))

   set @i = @i + 1
 End

 Select * from Swd_Produtos

END

Agora crio uma tabela para controle de estoque somente dos produtos fios Swd_Est_Fios no qual vou inserir saldos dos fios com valores aleatórios. Lembrando que aqui estou forçando uma situação-problema de performance. No uso comum, o usuário irá entrar com estes saldos via front-end e (talvez) não tenha inicialmente o problema simulado aqui. Mas após algum tempo de input de dados, ficará evidente a necessidade de criação de índice.

-- Estoque de fios
If object_id('Swd_Est_Fios') IS NULL 
BEGIN

 -- DROP TABLE Swd_Est_Fios
 CREATE TABLE dbo.Swd_Est_Fios
 (
  Id_EstFios int NOT NULL,
  Codigo_Barra varchar(13) NULL,
  CodFio varchar(10) NULL,
  Peso Decimal(16,4) NULL,

  CONSTRAINT PK_Swd_Est_Fios PRIMARY KEY (Id_EstFios)
 )

 -- Vamos popular com 50000 fios no estoque com pesos randomicos até 100kg
 Declare @j int = 1, @Peso Decimal(16,4), @CodFio varchar(10)

 While (@j <= 50000)
 Begin
   select @Peso = Round(rand()*100, 3)

   select @CodFio = 'F'+Right('00000'+Cast(Round((rand()*1000)/2, 0) as varchar),5)  

   insert into Swd_Est_Fios(Id_EstFios, Codigo_Barra, CodFio, Peso)
   Values (@j, Right('0000000000'+Cast(@j as varchar),10), @CodFio, @Peso)

   set @j = @j + 1
 End

 Select * from Swd_Est_Fios

END

Por fim, vamos consultar o estoque relacionando o saldo dos fios com a tabela de cadastro para demonstrar a descrição fio também:

Select * 
From Swd_Est_Fios SEF
 inner join Swd_Produtos SP on (SEF.CodFio=SP.Codigo)

Como o campo CodFio da tabela Swd_Est_Fios não está indexado, o relacionamento com a tabela Swd_Produtos nesta consulta será tudo via chave primária, em ambas tabelas. Podemos ver o plano de execução graficamente pela opção Consulta >> Incluir Plano de Execução Atual. A figura 1 mostra como ativar o plano de execução (atenção para o meu Management Studio que está em inglês na imagem, mas quem está com a versão em português irá encontrar facilmente).

 

Figura 1: Como ativar o plano de execução para atual consulta

Após ativar o plano de execução, podemos processar a consulta novamente e o Management Studio irá abrir uma nova aba nos resultados (figura 2). Todo esquema gráfico da consulta é apresentado. Neste caso em específico, o SQL Server detectou uma falha relacional demonstrando que é sensato a criação do índice para uma performance melhor na execução. Uma mensagem é apresentada como "Índice faltando".

Figura 2: Plano de execução demonstrando que a consulta necessita de um índice

O próprio Management Studio dá a opção de criar o código SQL para criação do índice. Veja na figura 3 que basta clicar com o botão da direita em cima da mensagem de "índice faltando" (Missing Index) e selecionar "Detalhes de índice ausente..." (Missing Index Details...)

Figura 3: Opção para criar os detalhes do índice ausente

O Management Studio abre uma nova guia com todo o script de criação do índice na tabela. Na figura 4, eu copio o script de criação e colo na parte não comentada e dou um nome ao índice (neste caso Ind_Swd_Est_Fios_CodFio) que sugere bem sua função. Você pode usar qualquer padrão que queira. Nesta nomenclatura usei: Ind_ + <tabela_do_índice> + _ + <campo_indexado>.

Figura 4: Criação do índice sugerido pelo SQL Server

Agora voltamos à guia com nossa consulta e executamos novamente para ver o que o plano de execução apresenta.

Figura 5: Execução da consulta após a criação do índice

Observe na figura 5 que desta vez o SQL Server usou o índice criado Ind_Swd_Est_Fios_CodFio na execução da consulta, pois a estrutura do SGBD detecta que esta é a maneira mais rápida de executar esta consulta. Com poucos dados nas tabelas, o tempo não apresenta diferença significativa. Mas após anos de uso da base, a diferença será bem evidente.