Reconstruir e reorganizar todos os índices duma base de dados

Um dos problemas que muitas vezes encontro é nas bases de dados de desenvolvimento/qualidade tabelas com índices fragmentados, o que faz com que as pesquisas sejam muito lentas e algumas vezes surjam deadlocks.

Uma forma de resolver o problema é ir tabela a tabela, índice a índice e fazer rebuild pelo SQL Server Management Studio. Mas isto é uma tarefa demorada e "chata" o que faz com que muitas vezes na realidade ninguém o faça :)

Outra forma de fazer é ter um script que faz isto automaticamente, este script que mostro de seguida faz isso mesmo. Ele vai tabela a tabela avaliar a fragmentação de cada índice e baseado nas recomendações da Microsoft se a fragmentação for maior que 30% faz um REBUILD senão se for entre 5% e 30% faz REORGAZINE.

NOTA 1: O script pode demorar algum tempo a executar e pode bloquear tabelas.

NOTA 2: "There are no silver bullets" isto ajuda as queries a ficarem mais rápidas ... ou não, nada substitui o bom desenho\implementação da base de dados e se o problema for estrutural não será uma manutenção dos índices que o vai resolver.

-- http://technet.microsoft.com/en-us/library/ms189858.aspx
-- Microsoft Recommends
-- REBUILD IF > 30% fragmented
-- REORGANIZE IF > 5% and < 30% fragmented

DECLARE 
    @schemaName NVARCHAR(255),
    @tableName NVARCHAR(255),
    @indexName NVARCHAR(255),
    @sql NVARCHAR(2000),
    @fragmentation FLOAT;

DECLARE TableCursor CURSOR FOR
SELECT DISTINCT
    schemas.name AS SchemaName,
    tables.name AS TableName,
    indexes.name AS IndexName,
    dm_db_index_physical_stats.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tablename'), NULL, NULL, NULL)
INNER JOIN sys.indexes ON (dm_db_index_physical_stats.OBJECT_ID = indexes.OBJECT_ID AND dm_db_index_physical_stats.index_id = indexes.index_id)
INNER JOIN sys.index_columns ON (indexes.OBJECT_ID = index_columns.OBJECT_ID AND indexes.index_id = index_columns.index_id)
INNER JOIN sys.columns ON (index_columns.OBJECT_ID = columns.OBJECT_ID AND index_columns.column_id = columns.column_id)
INNER JOIN sys.tables ON (indexes.OBJECT_ID = tables.OBJECT_ID)
INNER JOIN sys.schemas ON (tables.schema_id = schemas.schema_id)

OPEN TableCursor
FETCH NEXT FROM TableCursor 
INTO @schemaName, @tableName, @indexName, @fragmentation

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (@fragmentation > 30)
    BEGIN
        SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REBUILD;'
        EXEC (@sql)
    END
    ELSE IF (@fragmentation BETWEEN 5 AND 30)
    BEGIN
        SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @schemaName + '.' + @tableName + ' REORGANIZE;'
        EXEC (@sql)
    END

    FETCH NEXT FROM TableCursor 
    INTO @schemaName, @tableName, @indexName, @fragmentation
END

CLOSE TableCursor
DEALLOCATE TableCursor

GO

Fonte: Reorganize and Rebuild Indexes