No artigo anterior, apresentei algumas considerações sobre identificação de índices pouco utilizados que muitas vezes não justificam a sobrecarga de trabalho que causam ao SGBD. Isso é um caso evidente de índices inadequados, mas eu considero que existe outro caso importante: índices que são muito grandes, ou seja, que ocupam muito espaço.

Já vi muitos casos em que os responsáveis por aplicações definiram índices que ocupavam mais de 50% do espaço das tabelas correspondentes. E é evidente que a maioria destes índices causava mais problema do que ajudava.

O tamanho do índice é uma questão relevante e identificar/tratar estas situações, além de ajudar a manter a saúde e a performance do seu banco de dados.

Entenda o problema

Não é novidade para ninguém que o SQL Server usa intensamente a memória do servidor como meio de aumentar performance. O objetivo, é claro, é minimizar operações de I/O. Por isso, é boa prática cuidar bem da memória do seu servidor.

Quando sua consulta é executada, são carregadas em memória (buffer pool) as páginas de dados da tabela, mas antes disso são carregadas também as páginas de dados dos índices utilizados.

As páginas do buffer pool são idênticas àquelas gravadas em disco, ou seja, cada página é uma alocação de 8 kbytes. Portanto, um índice muito grande exigirá a execução de mais paginações para ser pesquisado. E depois de identificadas as chaves do índice, serão carregadas as páginas de dados com as informações necessárias. Evidentemente é uma boa prática evitar este consumo de memória desnecessário.

Principais causadores

Acredito que a maioria dos casos de índices gigantes se enquadra em uma das seguintes situações:

• Índices compostos com uma combinação inadequada de campos
• Índices que usam a cláusula INCLUDE sem critério

O caso dos índices compostos acontece quando se tenta atender consultas que usam uma combinação muito grande de predicados (a serem usados em cláusulas WHERE/JOIN/GROUP BY/ORDER BY). Na verdade, na maioria das vezes, estes índices estão mal definidos.

É surpreendentemente comum encontrar índices compostos que incluem campos alfanuméricos grandes, que no fim das contas são usados como predicados com operador LIKE… e que, portanto, não usariam índice.

O correto nestas situações seria excluir o(s) campo(s) alfanumérico(s) do índice composto e, se necessário, criar separadamente índice(s) FULL-TEXT neste(s) campo(s). E naturalmente a consulta também terá que ser alterada para se beneficiar deste(s) novo(s) índice(s) J.

O caso dos índices gigantes que usam a cláusula INCLUDE é um pouco mais complicado. Esta cláusula é muito útil, porque ela acrescenta ao catálogo de um índice uma ou mais colunas que podem ser usadas na cláusula SELECT da consulta. Se desenhado de forma apropriada, um índice assim permitiria um ganho de performance muito importante nas consultas, porque as consultas cobertas por ele seriam resolvidas usando apenas as páginas de dados próprio índice, dispensando a pesquisa nas páginas de dados da tabela.

O problema é que muita gente resolve “aumentar o escopo” do índice incluindo uma lista enorme de campos, fazendo o índice crescer de forma exagerada. Portanto, o desenho de um índice com INCLUDE requer uma análise criteriosa de quais consultas serão atendidas por ele e, consequentemente, quais campos precisam ser incluídos.

Reconhecendo os casos críticos

Para fazer esta análise, precisamos identificar o espaço ocupado por cada índice e pela tabela e também a quantidade de registros desta tabela. Esses dados são obtidos através das visões de catálogo sys.indexes e sys.partitions.

A quantidade de registros da tabela e a proporção entre os espaços ocupados pelo índice e pela tabela são dois critérios importantes nesta análise. Por isso preparei um script incluindo duas variáveis (@decRatio e @intRowCount respectivamente).

Eu considero o número de registros relevante, porque as tabelas muito pequenas (menos de 10 mil registros) raramente precisam ter qualquer índice além do índice clusterizado, pois o ganho de performance obtido com índices extra geralmente é desprezível. Ou seja, salvo casos específicos, qualquer índice não-clusterizado em tabelas pequenas é um candidato natural à exclusão.

Sobre a proporção entre tamanho do índice e o tamanho da tabela, considero que, em ambiente de produção, raramente um índice precisa ter mais que 50% do tamanho da tabela. Lembre-se sempre que um dos fundamentos na criação de índices é que ele deve ser tão pequeno quanto possível. Sendo assim, esta proporção é bastante alta.

A seguir, apresento o script completo que utilizei. Neste exemplo, pesquisei a base em busca de tabelas com mais de 10 mil registros que apresentassem índices com tamanho maior ou igual a 60% do tamanho da própria tabela (escolhi estes valores para limitar o tamanho da listagem que apresento a seguir). O resultado identificou quatro índices, como se vê na tabela anexa.

Comandos:

USE AdventureWorks2012
GO
DECLARE @decRatio AS DECIMAL(7,2) ,
	@intRowCount AS INTEGER
SET @decRatio = 0.6    	-- informa proporção máxima
SET @intRowCount = 10000 	-- informa número de registros mínimo
;WITH cteData (object_id, TableName, Data_Kb, Row_Count) 
AS (
  SELECT 
	  i.object_id
	, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS TableName
	, SUM(s.[used_page_count]) * 8.0   AS Data_Kb
	, SUM(s.row_count) AS Row_Count
  FROM sys.dm_db_partition_stats s 
	INNER JOIN sys.indexes i 
		ON s.object_id = i.object_id and s.index_id = i.index_id
  WHERE OBJECT_SCHEMA_NAME(i.object_id) <> 'sys' -- exclui catalogos
	AND i.type IN (0,1)  -- HEAPS ou CLUSTERED
	AND s.Row_Count >= @intRowCount
  GROUP BY i.object_id
  )
, cteIndex (object_id, IndexName, Index_Kb)
AS (
  SELECT 
	  i.object_id
	, i.name AS IndexName
	, SUM(s.[used_page_count]) * 8.0   AS Index_Kb
  FROM sys.dm_db_partition_stats s 
	INNER JOIN sys.indexes i 
		ON s.object_id = i.object_id and s.index_id = i.index_id
  WHERE OBJECT_SCHEMA_NAME(i.object_id) <> 'sys' -- exclui catalogos
	AND i.type IN (2)  -- NONCLUSTERED
  GROUP BY i.object_id, i.name
  )
SELECT
	  d.TableName
	, i.IndexName
	, d.Data_Kb
	, i.Index_Kb
	, ROUND( (i.Index_Kb / d.Data_Kb * 100.0) , 1) AS Ratio
	, d.Row_Count
FROM cteData d INNER JOIN cteIndex i ON d.object_id = i.object_id
WHERE i.Index_Kb >= @decRatio * d.Data_Kb
ORDER BY d.Row_Count DESC, Ratio DESC

Output. (base demo AdventureWorks2012):

TableName	IndexName	Data_Kb	Index_Kb	Ratio	Row_Count
Person.BusinessEntity	AK_BusinessEntity_rowguid	784	552	70,4	20.777
Person.PersonPhone	IX_PersonPhone_PhoneNumber	1.200	976	81,3	19.972
Person.EmailAddress	IX_EmailAddress_EmailAddress	2.008	1.488	74,1	19.972
Person.BusinessEntityAddress	AK_BusinessEntityAddress_rowguid	896	680	75,9	19.614

Como corrigir a situação

Não existe uma solução geral deste problema, porque existem muitas variáveis que influenciam esta análise. Duas delas são a lista de campos que compõem os índices problemáticos e a lista de consultas que os utilizam.

Mas eu acredito que um ponto básico é identificar o tamanho de cada campo que faz parte do índice. Veja, a seguir, um exemplo onde foi identificado um índice muito grande que usava a cláusula INCLUDE.

CREATE INDEX ixExemplo ON minhaTabela (Coluna3) INCLUDE (Column2, Column5)

Estruturas da tabela “minhaTabela” e do índice “ixExemplo”:

Coluna	Tipo de Dados	Tamanho (bytes)	Participa Indice?
Coluna1	BIGINT	8	NÃO
Coluna2	VARCHAR(50)	50	SIM
Coluna3	DATETIME	8	SIM
Coluna4	INTEGER	4	NÃO
Coluna5	VARCHAR(100)	100	SIM
TOTAL TABELA “minhaTabela”		170 (máximo)
TOTAL ÍNDICE “ixExemplo”		158 (máximo)

Neste exemplo, os registros da tabela “minhaTabela” podem ter até 170 bytes, dependendo obviamente se os dois campos VARCHAR foram completamente usados. Porém, em razão de sua estrutura, cada registro do catálogo do índice “ixExemplo” pode ter até 158 bytes. Em outras palavras, o tamanho final deste índice pode chegar a 93% do tamanho da tabela inteira.

Muito provavelmente este índice funcionaria melhor sem a cláusula INCLUDE. Neste caso, as consultas fariam uma operação de INDEX SEEK no novo índice e um novo INDEX SEEK no índice clusterizado da tabela.

Porém, toda hipótese de alteração de índice deve ser testada. O ideal é trazer para o ambiente de testes uma cópia atualizada da base de produção e, então, avaliar a performance/plano de execução das principais consultas que usavam o(s) índice(s) antigo(s).

Leituras sugeridas

1. Find the size of Index in SQL Server, por Basit Aalishan
2. A Sysadmin’s Guide to Microsoft SQL Server Memory, por Brent Ozar
3. SQL Server Memory Buffer Pools: Understand the Basics, por Pinal Dave