SQL Server – Otimizando o desempenho dos índices no SQL Server

Os índices são, inquestionavelmente, fundamentais para o desempenho do acesso às tabelas de um banco de dados. Porém, o que pouca gente leva em consideração são os custos inerentes aos índices.

Veja o exemplo real da tabela abaixo no SQL Server:

Em um determinado sistema financeiro, toda transação gera uma guia de pagamento e, na maioria absoluta das vezes em que ela é acessada para consulta o que interessa é somente as guias abertas (Situação ativa ou parcialmente paga).

A Query abaixo demonstra como os dados estão alocados na tabela.

with RegPorSituacao as (select a.SituacaoID
                             , b.nome SituacaoDS
                             , count(*) TotalID
                          from Guia a join Situacao b on A.SituacaoID = b.SituacaoID
                         group by a.SituaçãoID, b.nome)
   , RegTotal as (select Count(*) TotalGeral
                    from Guia a) 
select x.SituacaoID SitID
     , x.SituacaoDS SitDs
     , x.TotalID    Total
     , ROUND((CAST(x.TotalID as decimal)/
              CAST(y.TotalGeral as decimal))*100,2) QtdPerc
  from RegPorSituacao x
     , RegTotal y

Esta Query gera o seguinte resultado:

SitID SitDs             Total  QtdPerc
----- ----------------- ------ -------
15    BAIXADO         202624 88.6700
3     CANCELADO          14967  6.5500
1     ATIVO              10860  4.7500
25    PARCIALMENTE PAGO     58  0.0300

Espaço Ocupado

Índices ocupam bastante espaço e quanto mais complexo for o índice mais espaço ele ocupa.

Observe que, pela regra do sistema acima, mais de 94% dos registros não interessam ao sistema. Portanto, a criação de um índice único ocuparia espaço com registros que nunca seriam utilizado (ou pesquisados) no sistema

Além do desperdício de espaço, ele gera problema de I/O na busca, pois o SQL Server terá que buscar um grande volume de dados no índice.

Inserção e Updates

Toda vez que um registro é inserido ou atualizado, é necessário a atualização do índice.

Neste caso, quando a guia é cancelada ou paga, existe um desperdício de recurso atualizando índices que não serão atualizados.

Otimização do Índice no SQL Server

Uma forma muito inteligente e simples criada pelo SQL Server são os “Filtered Indexes” ou índices com filtros.

Nada mais é do que a possibilidade de adicionar um filtro ao índice, ditando desta forma a condição de existência do registro no arquivo de índices da tabela

Como o sistema não usa as situações de BAIXADO e CANCELADO para absolutamente nada, os índices possuem mais de 96% dos registros ocupando espaço desnecessariamente.

Observe o espaço utilizado pelo índice criado normalmente (sem a cláusula where):

Para testarmos o desempenho vamos rodar uma Query simples, conforme abaixo:

select count(*) from guia where situacaoid = 3

Veja o desempenho na tabela abaixo:

Agora vamos substituir o índice atual por um mais efetivo, conforme criado abaixo:

create index guia_indiceSituacaoAtivo on guia (situacaoid) where situacaoid in (1,25)

Observe o espaço utilizado pelo índice criado com a cláusula where:

Agora vamos testar o desempenho do novo índice, conforme tabela abaixo:

Resultados comparativos

Espaço Utilizado

Observe que com a cláusula where o índice representa 5,5% do índice geral, o que já demonstra uma significativa redução de espaço armazenado.

Desempenho do Acesso

Observe que no índice filtrado, o custo de I/O caiu de 0,027 para 0,004, ou seja, o índice filtrado gerou um custo de I/o 6,75 vezes menor

Outros indicadores a serem considerados são os indicadores “Operator Cost” e “Subtree Cost” que apresentaram redução de custo de 50%

Considerações

O índice filtrado é uma excelente opção para economia de espaço quando as tabelas apresentam situações que não seriam úteis para o sistema mas que o índice tradicional é obrigado a considerar, especialmente quando estas situações ocupam a maior parte dos registros.

Por outro lado, mesmo que todas as situações da tabela sejam úteis, ainda assim o índice filtrado se mostra útil, pois este reduz o custo de I/O do acesso ao índice quando acessado pelo campo do filtro.

Além da situação outras condições pode ser adotadas para auxiliar o desempenho geral do índice. Uma situação que está sendo aplicada é o particionamento das chaves de acesso.

Por exemplo, temos um cadastro de pessoas com 250 mil pessoas cadastradas que é constantemente acessado pelo campo PessoaID. Criamos o índice filtrado com a seguinte condição:

Create index Pessoa_01 on pessoa (PessoaID) where (PessoaID <= 50000);

Create index Pessoa_02 on pessoa (PessoaID) where (PessoaID  >  50000 and PessoaID <= 100000);

Create index Pessoa_03 on pessoa (PessoaID) where (PessoaID  > 100000 and PessoaID <= 150000);

Create index Pessoa_04 on pessoa (PessoaID) where (PessoaID  > 150000 and PessoaID <= 200000);

Create index Pessoa_05 on pessoa (PessoaID) where (PessoaID  > 200000 and PessoaID <= 250000);

Create index Pessoa_06 on pessoa (PessoaID) where (PessoaID  > 250000);

Com isso além de reduzir o I/O de acesso ao índice, reduz-se também o tempo de espera por acesso concorrente.

Essa mesma técnica pode ser aplicada no Oracle de duas maneiras diferentes: Filtered Index e particionamento. Posteriormente colocarei um artigo sobre este recurso aplicado ao Oracle