Consultoria DBA SQL Server

Conheça os 10 scripts de índices que todos os DBA’s precisam saber para trabalhar no dia a dia.

Todos ou quase todos os DBAs SQL Server sabem que os índices nos bancos de dados são muito semelhantes aos índices de livros. Um índice em um banco de dados é uma estrutura associada a uma tabela ou view que acelera na recuperação de linhas da tabela ou view.

Este artigo lista as script T-SQL relacionadas aos índices que são úteis para administradores de banco de dados do SQL Server. Os Scripts T-SQL abordadas neste artigo são classificadas em três categorias: Definição de índice ou Criação – informações relacionadas ao índice de consulta e manutenção.

Índice Clusterizado
Os índices clusterizados armazenam as linhas de dados em ordem ordenada na tabela com base em seus valores-chave. Apenas um índice cluster pode ser criado por tabela, porque as linhas de dados em si só podem ser classificadas em uma ordem.
Um índice cluster pode ser criado enquanto cria as restrições como chave primária em uma tabela existente. Vejamos a seguir:

ALTER TABLE [MyAddress] 
ADD  CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED 
(
	[AddressID] ASC
) ON [PRIMARY]
GO

Um índice cluster também pode ser criado em uma coluna sem cláusulas relacionadas a restrições. Exemplo:

CREATE CLUSTERED INDEX [MyAddress_id_CIX] ON [MyAddress1] 
(
	[ID] ASC
)ON [PRIMARY]
GO

Índice não clusterizado.

Geralmente, os índices não-clusters são criados para melhorar o desempenho de consultas freqüentemente usadas não cobertas pelo índice cluster. Em um índice não clusterizado, a ordem lógica do índice não corresponde à ordem física armazenada das linhas no disco.

Um índice não clusterizado pode ser criado em uma tabela existente cobrindo as colunas não cobertas pelo índice agrupado. Exemplo:

CREATE UNIQUE NONCLUSTERED INDEX 
[NIX_col5_col2_col3_col4_col6] 
ON [MyAddress] 
(
	[AddressLine1] ASC,
	[AddressLine2] ASC,
	[City] ASC,
	[StateProvinceID] ASC,
	[PostalCode] ASC
)ON [PRIMARY]
GO

Um índice não clusterizado também pode ser criado ao criar restrições na tabela existente. Exemplo:

ALTER TABLE [MyAddressType] 
ADD  CONSTRAINT [DEFF_MyAddressType_ModifiedDate]  
DEFAULT (getdate()) FOR [ModifiedDate]
GO

Índice XML:

Um índice XML pode ser criado em uma coluna XML e a tabela deve ter um índice cluster. O índice XML pode ser primário ou secundário.

Um índice XML primário pode ser criado como mostrado abaixo:

CREATE PRIMARY XML INDEX idx_xCol_MinhaTabela on MinhaTabela (xCol)

Um índice XML secundário pode ser criado como mostrado abaixo:

CREATE TABLE MinhaTabela (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Criando indice primario.
CREATE PRIMARY XML INDEX PIdx_MinhaTabela_XmlCol 
ON T(XmlCol)
GO
-- Criando indices secundarios (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_MinhaTabela_XmlCol_PATH ON MinhaTabela(XmlCol)
USING XML INDEX PIdx_MinhaTabela_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_MinhaTabela_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_MinhaTabela_XmlCol
FOR VALUE
GO

Spatial Index:

O SQL Server 2008 apresentou um novo tipo especial de coluna chamada coluna espacial, que é uma coluna de tabela que contém dados de um tipo de dados espaciais, como geometria ou geografia.

Um índice spatial index pode ser criado usando a seguinte sintaxe:

CREATE TABLE MinhaTabelaEspacial(id int primary key, geometry_col geometry);
CREATE SPATIAL INDEX SIndx_MinhaTabelaEspacial_geometry_col1 
   ON MinhaTabelaEspacial(geometry_col)
   WITH ( BOUNDING_BOX = ( 0, 0, 500, 200 ) );

Saber mais sobre os índices:
Encontrando todos os índices.

A consulta a seguir pode ser usada para consultar todas as tabelas, colunas e índices no banco de dados atual.

SELECT OBJECT_SCHEMA_NAME(BaseT.[object_id],DB_ID()) AS [Schema], 
 BaseT.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name], 
 I.[type_desc]
FROM sys.[tables] AS BaseT 
 INNER JOIN sys.[indexes] I ON BaseT.[object_id] = I.[object_id] 
 INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] 
 INNER JOIN sys.[all_columns] AC ON BaseT.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
WHERE BaseT.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' 
ORDER BY BaseT.[name], I.[index_id], IC.[key_ordinal]

Query para descobrir a fragmentação de todos os índices.
Índices fragmentado ocupa muito espaço em disco, desfragmentá-los você ganhará mais espaço em disco.

SELECT object_name(IPS.object_id) AS [TableName], 
   SI.name AS [IndexName], 
   IPS.Index_type_desc, 
   IPS.avg_fragmentation_in_percent, 
   IPS.avg_fragment_size_in_pages, 
   IPS.avg_page_space_used_in_percent, 
   IPS.record_count, 
   IPS.ghost_record_count,
   IPS.fragment_count, 
   IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
   JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
   JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
order by IPS.avg_fragment_size_in_pages desc

Descobrir todos os índices que estão faltando.
O SQL Server mantém um registo dos índices que acredita que você deve criar e que ajudarão a melhorar o desempenho das consultas. O Script a seguir lista todos os índices ausentes.

SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL 
                THEN ''  
    ELSE CASE WHEN mid.equality_columns IS NULL 
                    THEN ''  
        ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
                THEN ''  
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns 
    FROM sys.dm_db_missing_index_group_stats AS migs 
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
    WHERE     (migs.group_handle IN 
        ( 
        SELECT     TOP (500) group_handle 
            FROM          sys.dm_db_missing_index_group_stats WITH (nolock) 
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
        AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 
    ORDER BY 2 DESC , 3 DESC 

Índice não utilizado.
O Script a seguir lista todos os índices que não foram usados. Também gera a instrução de DROP que pode ser útil ao excluir os índices.

SELECT o.name, indexname=i.name, i.index_id 
, reads=user_seeks + user_scans + user_lookups 
, writes = user_updates 
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
 WHEN s.user_updates < 1 THEN 100
 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
 END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name) 
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id 
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID() 
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads 

Manutenção de índices:
Rebuild índice.
Quando um índice está fragmentado, ele requer a desfragmentação. A desfragmentação pode ser feita usando a cláusula de REBUILD ao alterar uma tabela. Esse comando é equivalente a DBCC DBREINDEX em versões do SQL Server anteriores a 2005. O comando que pode ser usado para reconstruir o índice é da seguinte maneira:

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

Reorganize índice.
Especifica que o nível de folha de índice será reorganizado. A declaração REORGANIZE é sempre realizada on-line. O que permite não locar (bloquear) a tabela ao organizar o índice. Esse comando é equivalente a DBCC INDEXDEFRAG em versões do SQL Server anteriores a 2005.

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO