Banco de Dados

Ξ Deixe um comentário

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

publicado por Paulo Planez

Figura - SQL Server - Otimizando o desempenho dos índices no SQL ServerOs í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):

Espaço Utilizado pelo índice simples

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

select count(*) from guia where situacaoid = 3

Veja o desempenho na tabela abaixo:

Dsempenho no acesso ao índice simples

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:

Espaço utilizado pelo índice com filtro

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

Desempenho no acesso ao índice com filtro

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

Autor

Graduado em administração com especialização em Finanças, atua desde 1990 com sistemas de informação, em sua maioria focado em sistemas de gestão financeira. ♪ Atuou com sistemas de informação proprietários dos mais variados como Sistemas de gestão de Grãos, Sistemas de controle financeiro para transações eletrônicas e, no segmento de governo, com Sistemas Financeiros e de Sanidade Animal e Vegetal e com sistemas para processamento de Malha Fiscal. ♪ Atua por mais de 15 anos com sistemas de gestão (ERP) cobrindo ciclos operacionais como "Quote to Cash" e "Purchase to Pay" utilizando software da Oracle Corporation (Oracle EBS) em empresas como Globo, Motorola, Alcoa e Dell. ♪ Possui como linha de estudo e pesquisa a economicidade dos sistemas de informação, de modo a extrair destes o máximo de benefícios com o mínimo de recursos.♪ Contato: paulo.planez@gmail.com

Paulo Planez

Comentários

You must be logged in to post a comment.

Busca

Patrocínio

Publicidade



Siga-nos!

Newsletter: Inscreva-se

Para se inscrever em nossa newsletter preencha o formulário.