Índices – Porque usá-los

Você está trabalhando tranquilamente, de repente seu chefe se aproxima e diz: “preciso que você verifique por que as consultas ao banco de dados está lenta”. Ok, até aí não temos um problema “anormal’, afinal o grande mal que aflige a nós, DBA’s, é lentidão no banco de dados, porém fica a pergunta: o problema é de hardware ou software ?

Quando somos solicitados a verificar problemas de lentidão, na maioria das vezes pensamos tratar-se de falta de recursos (memória, cpu) para o SQL Server, e quase sempre é, porém fica a pergunta: realizar upgrade de hardware vai resolver ? será que as tabelas estão projetadas da melhor forma ?

Recentemente identifiquei em um cliente uma tabela com mais de 200.000 registros, com poucas colunas, porém sem índices. Ao consultar a tabela, a CPU gasta alguns milisegundos (ás vezes segundos) para executar consultas nessa tabela. Em uma primeira análise, poucos segundos para uma consulta não parecem ser um problema, mas imagine milhares de consultas simultâneas em sua instância, onde está hospedado seu banco de dados, e consequentemente sua tabela sem índices. Pois é, uma simples tabela pode causar um problema enorme e, sem uma análise adequada, fazer com que a empresa gaste a verba do setor adquirindo mais memória e cpu para o servidor, e depois os problemas voltarem (e você correr o risco de perder seu emprego).

Irei demonstrar como identificar tabelas sem índices e também como comparar o tempo gasto pela CPU, na consulta de uma tabela com e sem índices. Primeiramente vamos a alguns conceitos:

Tabela Heap -> tabelas que não possuem índices clusterizados, ou seja, não há ordenação na gravação dos dados nas data pages, fazendo com que consultas à esse tipo de tabela realizem um table scan, ou seja, toda a tabela é consultada.

Clustered indexes -> tabelas com esse tipo de índice possuem uma coluna indexada, armazenando os dados (registros) dessa coluna dentro do índice, ordenando os dados, dentro das data pages. Uma consulta nesse tipo de tabela irá fazer com que a consulta seja finalizada quando o registro for encontrado, pois uma condição para índices clustered é que os valores da coluna indexada sejam únicos.

Para exemplificar, criarei um banco (DBHeap) e 2 tabelas: TabelaHeap e TabelaIndex. Após, irei popular as tabelas com a mesma quantidade de registros e depois iremos verificar o nível de fragmentação da tabela e o tempo gasto pela CPU para consultar as 2 tabelas.

Criando o banco DBHeap:

CREATE DATABASE DBHeap
GO

Vamos agora criar as tabelas:

-- Cria tabela Heap
CREATE TABLE Dbo.TabelaHeap (
[ID] INT IDENTITY(1,1),
[NOME] VARCHAR (30),
[EMAIL] VARCHAR (30)
)
GO

-- Cria tabela Index
CREATE TABLE Dbo.TabelaIndex (
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[NOME] VARCHAR (30),
[EMAIL] VARCHAR (30)
)
GO

Populando ambas tabelas com a mesma quantidade de registros (200.000):

-- Insere registros na tabela Heap
INSERT INTO  Dbo.TabelaHeap VALUES
('Fulano', 'email@com')
GO 200000

-- Insere registros na tabela Index
INSERT INTO  Dbo.TabelaIndex VALUES
('Fulano', 'email@com')
GO 200000

Após alguns minutos, os registros foram gravados no banco DBHeap. Agora vamos realizar uma consulta para identificar o tempo gasto pela CPU, habilitando a opção STATISTICS TIME:

SET STATISTICS TIME ON
SELECT [ID], [NOME],[EMAIL]
FROM Dbo.TabelaHeap
WHERE [ID] = 1
GO

Após a execução do comando acima, é retornado apenas 1 registro, conforme imagem abaixo:

Consulta Tabela Heap

Analisando a mensagem retornada pelo comando STATISTICS TIME percebemos o valor abaixo:

Consulta Tabela Heap CPU

Agora vamos realizar a mesma consulta na tabela Index:

Consulta Tabela Index

Consulta Tabela Index CPU

Conforme pode ser verificado, há diferenças no tempo gasto pela CPU na tabela Heap (16 ms) e na tabela index (0 ms). Como citei anteriormente, o tempo em questão pode ser irrelevante se executado uma ou duas vezes, mas em um ambiente de produção, com milhares de queries sendo executadas, isso ira gerar um impacto significativo.

Para identificar as tabelas Heaps e a quantidade de registros que cada tabela contém, utilizei a DMV sys.dm_db_index_physical_stats, a view sys.tables e a stored procedure não documentada pela Microsoft sp_MSforeachtable:

-- Consulta as tabelas com maior percentual de fragmentação de indíces
select tb.name as 'Tabelas',
db.index_type_desc as 'Tipo de Indice', db.avg_fragmentation_in_percent as 'Percentual de fragmentacao'
from sys.dm_db_index_physical_stats(DB_ID('DBHeaP'), Null, Null, Null, Null) db
inner join sys.tables tb
on tb.[object_id] = db.[object_id]

-- filtra a consulta por tipo de tabela (opcional)
where db.index_type_desc = 'HEAP'
order by db.avg_fragmentation_in_percent desc

-- cria tabela temporária 
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
EXEC sp_spaceused
CREATE TABLE #t (
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),     
data VARCHAR(18),     
index_size VARCHAR(18),    
unused VARCHAR(18)
) 
INSERT INTO #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * FROM #t
DROP TABLE #t
GO

Resultado:

Registros Tabela Heap

Minha intenção não é ensinar o que são índices e os tipos, porém dar uma visão da importância da criação de índices em tabelas com um número grande de registros. Recomendo fortemente buscar materiais ou livros sobre SQL Server, que falem de índices pois trata-se de um assunto muito importante e vital para o bom funcionamento das bases de dados.

Espero ter ajudado.

Abraços e até a próxima.

Anúncios

3 comentários sobre “Índices – Porque usá-los

  1. Pingback: Índices – Fundamentos, Estruturas e Usabilidade | Alex Souza

  2. Muito bom e parabéns pelo assunto abordado.
    Apenas uma ressalva, da necessidade de definir bem a PK durante a modelagem, porque depois tudo fica mais caro. Forte Abraço.
    Ataíde Antônio – desenvolvedor SQL e VBA.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s