Index

Índice cluster

Os dados dentro da tabela são organizados (armazenados) pela coluna que está sendo indexada.
Toda PK de uma tabela é criado como índice cluster.
Uma tabela só pode ter um índice cluster.

Ex: CREATE CLUSTERED INDEX I_Func_Cod ON Funcionario (CodFunc)

Índice não cluster
Uma tabela pode ter vários índices não cluster.
É criado uma nova tabela contendo a chave do índice e o endereço de memória das páginas de dados que contém a coluna indexada. Veja figura anterior.

Ex: CREATE NONCLUSTERED INDEX I_Func_Cargo ON Funcionario (CodCargo)
Ex: CREATE INDEX I_Func_Salario ON Funcionario (Salario)
Ex: CREATE UNIQUE INDEX I_Func_CPF ON Funcionario (CPF).
EX: CREATE INDEX I_Func_Cargo_Salario ON Funcionario (CodCargo, Salario).

Projeto de Índicies

-Índices ocupam espaço de armazenamento.
-Índices são atualizados no caso de Insert, Deletes e Updates.
-Foreign Key devem ser indexadas.
-Atributos que aparecem na cláusula Where, order by ou group by para querys freqüentes são candidatos naturais.
-Não crie índices em atributos com poucos valores distintos, tipo sexo.

-Desabilite os índices antes de uma grande carga numa tabela.

Identificando Índices não usados desde o último restart da instância

-- Identify unused indexes (since last restart)
SELECT
sc.name + '.' + object_name(i.object_id) as objectName
, i.name as indexName
, i.type_desc as indexType
FROM sys.indexes i
INNER JOIN sys.objects o on o.object_id = i.object_id
INNER JOIN sys.schemas sc on o.schema_id = sc.schema_id
WHERE
objectproperty(i.object_id,'IsUserTable') = 1
AND i.index_id not in (
SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE
s.object_id = i.object_id
AND i.index_id = s.index_id
AND database_id = db_id()
)
ORDER BY objectName, indexName ASC

Identificando Índices Fragmentados
DBCC SHOWCONTIG

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;

-- View index fragmentation ordered by fragmentation level
SELECT stats.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(N'AdventureWorks'), NULL, NULL, NULL, NULL
) as stats
INNER JOIN sys.indexes AS b
ON stats.object_id = b.object_id AND stats.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC

Reorganize x Rebuild

REBUILD

ALTER INDEX ALL ON dbo.Tabe REBUILD
GO

REORGANIZE
ALTER INDEX ALL ON dbo.Table REORGANIZE
GO

Consultar os indicies de uma tabela use a procedure.

sp_helpindex [tabela]