Índices e o excesso de coisa boa – Parte 01: identificando casos críticos

setembro 6, 2016 1:00 pm Publicado por Deixe um comentário

“A dose certa diferencia um veneno de um remédio” – Paracelso

O índice é uma benção no universo dos bancos de dados. Ele é essencial para o bom funcionamento do seu banco de dados, pois pode melhorar sensivelmente a performance das principais consultas.

Porém, muita gente adota esta premissa sem nenhum critério e cria zilhões de índices no banco. Eu comentei esta questão num artigo de 2013, em que chamei isso de “comportamento pessimista” (por não confiar na qualidade de seus índices e na eficiência do otimizador de consultas, o programador/DBA cria inúmeros índices “para se garantir”).

O problema é que índices são muito bons para melhorar performance de consultas (os SELECTs), mas eles causam uma sobrecarga nas operações de inclusão, alteração ou exclusão de dados (INSERTs, UPDATEs e DELETEs). Por conta disso, o excesso de zelo pode ser um tiro no pé, chegando a comprometer a performance do banco em alguns casos.

Esta questão sempre me lembra da expressão gringa “too much of a good thing”, que numa tradução livre seria algo como excesso de coisa boa.

Explorando os detalhes da situação

Se você não visualizou por que o excesso de índices é um problema, vamos aos detalhes.

Para todos os efeitos práticos, índices são “catálogos de dados”, construídos com base no conteúdo da tabela (no caso, estou me referindo a índices não-clusterizados).

Para cada novo índice, teremos um novo catálogo relacionado àquela tabela. No SQL Server, nós podemos criar até 999 índices por tabela, mas é evidente que este número é grande demais para a enorme maioria das aplicações práticas.

Sempre que houver uma ação alterando os dados da tabela, esta ação deverá necessariamente ser refletida em todos os catálogos de índices associados a esta tabela. Isso quer dizer que toda operação de DELETE, INSERT e UPDATE não afeta só a tabela, mas inclui todos os índices relacionados a ela.

OBS: claro que no caso específico de operações de UPDATE, as alterações só afetarão os índices que incluírem os campos que estão sendo atualizados.

Em outras palavras, para inserir um registro em uma tabela com 10 índices, esta operação será executada 11 vezes. Como não existe banco de dados que não execute operações de inserção, alteração ou exclusão de dados, é fácil concluir que índices em excesso podem causar problemas sérios de performance.

Definindo os limites aceitáveis

Muitos índices podem causar problema. Ok, mas o quanto é “muito”? Essa é a pergunta de um milhão de dólares.

E a resposta depende de diversas variáveis. Depende do nível de atividade da tabela (quantidade de INSERTs, UPDATEs e DELETEs), da finalidade da tabela (se pertence a um sistema transacional ou a um sistema de análise de dados) etc.

Já vi casos em que o responsável pelo sistema criou mais de 70 índices numa tabela! Não tem SGBD ou configuração de hardware que salve uma aberração dessas.

Uma diretriz que eu considero essencial para o programador interessado em boas práticas é que cada tabela deve ter o mínimo possível de índices (desde que tenha ao menos um, que normalmente é o índice clusterizado).

O número máximo de índices de cada tabela precisa ser avaliado caso a caso. Eu concordo com Paul Randal, especialista em SQL Server reconhecido mundialmente, quando ele diz que “a maioria das tabelas deveria ter no máximo 15 índices”. Mas sempre que lido com tabelas transacionais com grande volume de dados, tento ser ainda mais conservador e deixar o mínimo possível de índices. Cabe a você, responsável pelo modelo de dados, identificar quais tabelas merecem atenção especial.

Identificando os casos críticos

Para identificar os casos de indexação inadequada, seja por excesso ou inexistência de ao menos um índice, basta consultar o objeto “sys.indexes”.

Existe certo consenso entre os especialistas em SQL Server de que nem toda tabela precisa de um índice não-clusterizado, mas toda tabela exige um índice clusterizado. Esta estratégia é indiretamente incentivada pela MICROSOFT, que cria um índice clusterizado automaticamente quando se define a chave primária de uma tabela. Portanto, a existência de “heaps” dentro do banco deve ser vista como uma exceção à regra e reservada para situações especiais, como por exemplo, em tabelas com poucos registros.

Em relação à situação de indexação excessiva, uma contagem simples dos índices de cada tabela nos ajudará a identificar os casos que merecem revisão.

No quadro a seguir, eu apresento um script que identifica estes dois casos. O script informa o nome e o tipo de tabela, a quantidade de índices e quantidade de registros que ela tem. Ele inclui um parâmetro (@intQtdLimite) para especificar qual número limite de índices a partir do qual precisaremos reavaliar a indexação da tabela. E este script também corrige a contagem dos índices no caso de “heaps” (já que a “heap” em si não é um índice e não pode ser computada na coluna “NumIndices”).

Comandos:

USE AdventureWorks2012
GO
DECLARE @intQtdLimite AS INTEGER
SET @intQtdLimite = 5  -- informa limite de 5 índices por tabela

;WITH cteObject (object_id, Tipo, NumRegistros) 
AS	(
SELECT DISTINCT i.object_id, i.type_desc as Tipo , SUM(p.rows) AS NumRegistros
FROM sys.indexes i
	INNER JOIN sys.partitions p 
	ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.type_desc IN ('clustered', 'heap') 
		AND OBJECT_SCHEMA_NAME(i.object_id) <> 'sys' -- exclui catalogos
	GROUP BY i.object_id, i.type_desc
	)

SELECT 
	 CASE WHEN c.Tipo = 'clustered' THEN 'EXCESSO' ELSE 'AUSENCIA' END AS Problema
	,  OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS Tabela 
	, i.object_id
	, c.Tipo
	, COUNT(*) - CASE WHEN c.Tipo = 'clustered' THEN 0 ELSE 1 END AS NumIndices
	, MAX(c.NumRegistros) AS NumRegistros
FROM sys.indexes i 
	INNER JOIN cteObject c ON i.object_id = c.object_id
GROUP BY i.object_id , c.Tipo
HAVING 	c.Tipo = 'heap'  OR COUNT(*) >= @intQtdLimite
ORDER BY  1, 2

Output. (base demo AdventureWorks2012)

Problema Tabela object_id Tipo NumIndices NumRegistros
AUSENCIA dbo.DatabaseLog 245575913 HEAP 1 1.597
AUSENCIA Production.ProductProductPhoto 562101043 HEAP 1 504
EXCESSO HumanResources.Employee 1237579447 CLUSTERED 6 290
EXCESSO Person.Person 1765581328 CLUSTERED 8 19.972
EXCESSO Production.Document 1077578877 CLUSTERED 5 13
EXCESSO Production.ProductModel 418100530 CLUSTERED 5 128
EXCESSO Sales.SalesOrderHeader 1266103551 CLUSTERED 5 31.465

A consulta identificou duas heaps, cada uma contendo um índice não clusterizado. Apesar de serem tabelas muito pequenas (ambas com menos de 2 mil registros), é recomendável avaliar a possibilidade de substituir os índices existentes por um índice clusterizado.

Encontramos também cinco casos em que existe excesso de índices. O que me chama mais a atenção é que todas as cinco tabelas identificadas são bem pequenas (a maior delas com pouco mais de 30 mil registros).

A menos que estas tabelas tenham um nível de atividade muito alto, dificilmente eu me preocuparia em criar tantos índices em tabelas deste porte. É provável que a maioria destes índices seja dispensável.

Conclusão

Até este momento, identificamos os casos críticos de indexação do banco estudado. Os critérios que sugeri aqui obviamente devem ser ajustados conforme a sua conveniência, porque a ideia deste script é exatamente chamar a atenção para os casos que possam causar algum tipo de problema no seu banco.

O próximo passo é definir quais índices podem ser melhorados ou descartados. Mas isso é assunto para o próximo artigo.

Até lá.

Leituras sugeridas

  1. Índices: Equilibrando os pratos da balança, por Wagner Crivelini
  2. Maximum Capacity Specifications for SQL, por MICROSOFT
  3. How Many Indexes Should I Create?, por Paul Randal
  4. Make Sure All Tables Have a Clustered Index Defined, por Ben Snaidero
Mensagem do anunciante:

Experimente a Umbler, startup de Cloud Hosting por demanda feita para agências e desenvolvedores e ganhe até R$ 100 em créditos!

Source: IMasters

Categorizados em:

Este artigo foi escrito pormajor

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *