A maioria dos arquitetos de bancos de dados concorda que toda tabela deve ter uma chave primária. Em outras palavras, toda tabela precisa ter um identificador de registros que garanta a unicidade dos dados (quer dizer: que não existam dados duplicados).

Essa é uma característica tão importante no modelo relacional que, no momento em que se cria a chave primária da tabela (daqui por diante chamaremos apenas de PK, do inglês “primary key”), automaticamente o SQL Server cria também um índice clusterizado sobre este(s) mesmo(s) campo(s).

Isso traz ao menos dois benefícios:

• Garante que não existirão registros duplicados na tabela;
• O armazenamento dos dados será ordenado fisicamente conforme os valores da PK, agilizando muito as consultas baseadas neste(s) campo(s).

Infelizmente, muita gente cria tabelas novas sem uma PK, índice clusterizado ou mesmo um índice columnstore clusterizado (disponível a partir do SQL Server 2014). Uma tabela assim é, para quaisquer fins práticos, uma pilha de dados sem ordenação específica. Isso evidentemente pode transformar-se num problema sério de performance.

Nesse artigo, eu comento este assunto e apresento um script para verificar se existem tabelas nessa condição dentro do seu banco de dados.

O que são heaps

Chamamos de “heaps” as tabelas que não possuem um índice clusterizado de nenhum tipo, ou seja, tabelas em que os dados são gravados conforme a ordem cronológica das inserções. Basicamente, estas tabelas são pilhas de dados (daí o nome “heap”).

É possível criar índices em heaps e o mais comum é usar índices não-clusterizados. Em termos práticos, isso é como montar catálogos que associam os valores do campo indexado e as páginas de dados onde eles foram gravados em disco.

Isso pode funcionar muito bem dependendo do tamanho da heap. Mas a eficiência desta solução cai dramaticamente em tabelas com muitas páginas de dados.

Estimando o tamanho da heap

Em fase projeto, será necessário estimar o tamanho que uma heap possa alcançar. Para ilustrar este cálculo, usarei a tabela especificada a seguir (*).

USE WideWorldImporters
GO
CREATE TABLE Minhaheap 
  (
  codigoProd    INT              NOT NULL IDENTITY (1,1),
  descricao     CHAR(50)         NOT NULL,
  qtdEstoque    INT              NOT NULL,
  precoUnit     DECIMAL(9,2)     NOT NULL,
  codigoFornec  INT              NOT NULL,
  observacao    CHAR(100)        NOT NULL
  )
GO
--script para popular a tabela como dados de teste (50 mil registros)
DECLARE @intContador INTEGER = 1
DECLARE @intFim INTEGER = 50000
TRUNCATE TABLE Minhaheap
WHILE @intContador <= @intFim
	BEGIN
	INSERT INTO Minhaheap (descricao, qtdEstoque, precoUnit, codigoFornec, observacao) SELECT 'XXXXXXXXXXXXX' , 10, 20, 1, 'ADÇKJAJADÇKJADKJASDÇJKSDFJK'
	SET @intContador = @intContador + 1
	END
SELECT COUNT(*) Num_rows FROM Minhaheap
GO

(*): Observe que a tabela não possui PK e também não tem índice clusterizado, sendo portanto uma heap.

Executo, agora, o procedimento SP_HELP para verificar o tamanho de cada campo criado (coluna “Length”). A listagem a seguir mostra os resultados obtidos. Somando os valores desta coluna, verificamos que cada registro da tabela deve ocupar 167 bytes (observe que nenhum campo aceita valor nulo e todos têm largura fixa).

sp_help ‘Minhaheap‘ GO

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation codigoProd int no 4 10 0 no (n/a) (n/a) NULL descricao char no 50 no no no Latin1_General_CI_AS qtdEstoque int no 4 10 0 no (n/a) (n/a) NULL precoUnit decimal no 5 9 2 no (n/a) (n/a) NULL codigoFornec int no 4 10 0 no (n/a) (n/a) NULL observacao char no 100 no no no Latin1_General_CI_AS

Sabemos que no SQL Server os dados são sempre gravados em páginas de 8 kb. As páginas de dados gravam sempre um cabeçalho (de 96 bytes) e o mapeamento dos dados nela existentes, conhecido como “slot array”. Portanto, o espaço efetivamente disponível para dados será os 8192 bytes (8Kb), menos 96 bytes do cabeçalho, menos o espaço para o slot array, que pode variar conforme a fragmentação interna da página de dados.

Por simplificação, eu considero neste meu cálculo que sobram 8000 bytes livres para armazenamento de dados em cada página. Com isso, eu posso estimar quantas páginas de dados serão usadas nessa heap., como mostro a seguir.

A tabela Minhaheap terá no máximo 47 registros por página de dados, isto é, a divisão inteira de 8000 por 167. Por que tem que ser a divisão inteira? Simples: lembre-se que os registros deve ser gravados obrigatoriamente numa única página.

Assim, para armazenar 50 mil registros, serão necessárias aproximadamente 1064 páginas de dados (arredondamento para cima de 10000/47). Isso representaria aproximadamente 8512 Kb (1064 * 8 Kb). Com este tamanho, é bem possível que as consultas sobre com esta heap apresentem problemas de performance.

Numa tabela deste porte, um índice não-clusterizado pode resolver a questão de performance. Porém, conforme esta heap continuar a crescer, chegará o momento em que nem mesmo este índice seja suficiente.

Sendo assim, a menos que se tenha uma boa razão em contrário, a melhor abordagem ainda é criar uma PK nesta tabela (ou o índice clusterizado).

Tamanho de heaps já existentes

É bom saber estimar o tamanho de uma heap, mas quando esta tabela já existe, é mais fácil usar a procedure SP_SPACEUSED, que traz informações muita interessantes. Veja a listagem a seguir.

sp_spaceused ‘Minhaheap‘ GO

name Rows reserved data Index_size unused MinhaHeap 50000 9160 KB 9096 KB 8 KB 56 KB

Esta listagem mostra que o tamanho real da heap é de 9096 Kb, ou seja, a estimativa calculada anteriormente estava bastante razoável (erro de apenas 6,5%).

Como identificar heaps

Para avaliar quais heaps devem receber um índice clusterizado, são necessárias informações físicas e lógicas sobre essa tabela.

As informações físicas são fáceis de se coletar, pois basta consultar os catálogos do banco. Eu recomendo identificar ao menos cinco características:

• Se a heap tem índices não-clusterizados;
• Quantidade de registros na tabela;
• Se a heap possui algum índice;
• Se a heap possui PK;
• Se existem campos identidade;

Observe que uma heap que contenha uma PK tem, na verdade, apenas um identificador de registro não indexado. Ou seja, sabemos que ela tem um ou mais campos que apresentam uma combinação única entre todos os registros, mas é necessário definir manualmente um índice sobre este(s) campo(s).

Outra questão é que não me preocupei em identificar diferentes tipos de índices existentes na heap (se houver algum). Este tipo de informação é importante, mas faz parte de uma etapa mais avançada de análise.

O script a seguir traz as informações desejadas:

SELECT OBJECT_SCHEMA_NAME (STAT.object_id) + ‘.’ + OBJECT_NAME(STAT.object_id) AS Heap , MAX(STAT.row_count) AS Row_Count , MIN(COALESCE(X.IX_CNT, 0)) AS Index_Count , MIN(COALESCE(Y.PK, ‘NO’)) AS Have_PK , MIN(COALESCE(Z.ColumnName, ‘NONE’)) AS ID_Column FROM sys.dm_db_partition_stats STAT LEFT JOIN ( SELECT IX.object_id , COUNT(*) AS IX_CNT FROM sys.indexes IX WHERE IX.type NOT IN (0,1,5) GROUP BY IX.object_id ) X ON STAT.object_id = X.object_id LEFT JOIN ( SELECT O.parent_object_id AS object_id , ‘YES’ AS PK FROM sys.objects O WHERE O.type = ‘PK’ ) Y ON STAT.object_id = Y.object_ID LEFT JOIN ( SELECT C.object_id , MIN(C.name) AS ColumnName FROM sys.columns C WHERE C.is_identity = 1 GROUP BY C.object_id ) Z ON STAT.object_id = Z.object_id WHERE STAT.object_id in ( SELECT I.object_id FROM sys.indexes I WHERE I.type = 0 AND OBJECTPROPERTY (I.object_id, N’IsUserTable’) = 1 ) GROUP BY STAT.object_id ORDER BY 1

Heap Row_Count Index_Count Have_PK ID_Column dbo.Minhaheap 50000 0 NO codigoProd Warehouse.ColdRoomTemperatures 4 2 YES ColdRoomTemperatureID Warehouse.VehicleTemperatures 65998 1 YES VehicleTemperatureID

Analisando os resultados da listagem acima, vemos imediatamente que as heaps dbo.Minhaheap e Warehouse.VehicleTemperatures são candidatas a receberem um índice clusterizado, já que ambas possuem uma quantidade razoável de registros (mais que 50 mil registros nos dois casos).

No caso da tabela Warehouse.VehicleTemperatures, a solução mais simples é excluir a PK existente e recriá-la sobre os mesmos campos (sem incluir o parâmetro NON-CLUSTERED, que foi usado na primeira versão da tabela).

Para a tabela dbo.Minhaheap, podemos aproveitar o campo identidade ‘codigoProd’ e criarmos uma PK com índice clusterizado sobre esta coluna.

Quais heaps precisam mudar

A decisão sobre a criação dos índices nas heaps deve considerar ainda informações lógicas e operacionais. Eu considero que o fatores mais relevantes nessa categoria sejam:

• A durabilidade dos dados, isso é, se a tabela armazena dados duráveis ou se passa frequentemente por processos de exclusão em massa dos seus registros;
• A frequência com que são executadas consultas (SELECTs) sobre esses dados; lembre-se que índices ajudam na performance dos SELECTs, mas impactam na performance UPDATEs, DELETEs e INSERTs.
• A relevância das consultas executadas sobre essas heaps; quando se lida com tabelas muito grandes, muitas vezes é vantajoso criar um índice mesmo que estes dados sejam apagados depois de um pequeno período de processamento.

Conclusão

Identificar as heaps é muito mais fácil do que decidir se elas devem ser convertidas em tabelas com índices clusterizados ou tabelas com índices columnstore clusterizados.

Aqui, apresentei um script para trazer informações básicas sobre as heaps existentes, mas a análise dos resultados gerados por este script vai exigir outras informações, que envolverão o arquiteto da aplicação, o administrador do banco e o time de desenvolvedores.

No entanto, volto a repetir: toda tabela do SQL Server merece ter um índice clusterizado ou um índice columnstore clusterizado. Heaps podem existir no seu banco, mas devem ser tratadas como exceções, necessárias em situações bastante específicas.