“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