Como Reorganizar e Reconstruir Índices no MS SQL?

Os índices são os objetos dos arquivos de banco de dados MS SQL. Esses objetos de esquema atuam como um índice do banco de dados. Eles são usados para melhorar as operações de recuperação de dados em uma tabela de banco de dados MS SQL.

No entanto, com o tempo, a tabela pode ficar fragmentada devido a operações contínuas de INSERT e UPDATE. Além disso, assim como outros objetos, os índices também são suscetíveis a corrupção. Se os índices ficarem corrompidos, você pode receber erros como:

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

​

Msg 8955, Level 16, State 1, Line 1

​

Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

​

Msg 8951, Level 16, State 1, Line 1

Para resolver os erros associados à corrupção nos índices ou quando o nível de fragmentação dos índices aumenta, você pode reconstruir ou reorganizar os índices. Neste artigo, vamos discutir como reorganizar e reconstruir os índices no MS SQL Server. Também mencionaremos uma ferramenta avançada de reparo de banco de dados MS SQL que pode restaurar índices e outros objetos de arquivos de banco de dados SQL corrompidos rapidamente e com total precisão.

Verifique a Percentagem de Fragmentação dos Índices

Antes de reorganizar os índices, você precisa saber a percentagem de fragmentação. Você pode usar o comando abaixo para verificar a percentagem da fragmentação do índice:

SELECT 

    OBJECT_NAME(object_id) tableName12,

    index_id,

    index_type_desc,

    avg_fragmentation_in_percent,

    page_count

FROM 

    sys.dm_db_index_physical_stats (

        DB_ID('Adventureworks2019'), 

        OBJECT_ID('[Person].[Person]'), 

        NULL, NULL, 'DETAILED'

    );

​

Reorganizando Índices no SQL Server

Se o nível de fragmentação do índice estiver entre 10 e 30, é recomendado reorganizar o índice. Você pode usar o seguinte código em T-SQL para reorganizar o índice:

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]

REORGANIZE;

​

Alternativamente, você pode usar o SQL Server Management Studio (SSMS) para reorganizar os índices. Siga os passos abaixo:

• No SSMS, conecte-se à instância do seu SQL Server.
• No Explorador de Objetos, expanda o banco de dados.
• Expanda a pasta Tabelas e em seguida a pasta Índices.
• Clique com o botão direito no índice que você precisa reorganizar e depois clique em Reorganizar.

Reconstruindo Índices no MS SQL

Se a fragmentação for alta, então você pode reconstruir os índices. Você pode usar o comando DBCC DBREINDEX para reconstruir um índice. Veja como executar este comando:

DBCC DBREINDEX

(

    table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

​

Este comando não suporta índices espaciais e índices de colunas armazenadas em memória otimizada. 

Alternativamente, você pode usar o comando ALTER INDEX:

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]

REBUILD;

​

Você também pode reconstruir os índices usando a interface gráfica no SSMS. Aqui estão os passos:

• No SSMS, no Explorador de Objetos, expanda o banco de dados que contém a tabela na qual você precisa reconstruir um índice.
• Expanda a pasta Tabelas e em seguida a tabela na qual você precisa reconstruir um índice.
• Expanda a pasta Índices, clique com o botão direito no índice que você precisa reconstruir e selecione Reconstruir.

Reparar o Banco de Dados do SQL Server

Se reorganizar ou reconstruir os índices não funcionar, então você pode tentar o comando DBCC CHECKDB com a opção REPAIR_ALLOW_DATA_LOSS para reparar o banco de dados. Aqui está como usar esse comando:

Primeiro, altere o modo do banco de dados para SINGLE_USER usando o comando abaixo:

ALTER DATABASE BusinessEntity SET SINGLE_USER

Em seguida, execute o comando DBCC CHECKDB conforme mostrado abaixo para reparar o banco de dados:

DBCC CHECKDB (N ’BusinessEntity’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 

GO

​

Após o reparo, defina o modo do banco de dados para MULTI_USER executando o comando abaixo:

ALTER DATABASE BusinessEntity SET MULTI_USER

O comando DBCC CHECKDB acima pode ajudar a resolver todos os erros relacionados à corrupção nos índices. Ele pode reparar índices agrupados e não agrupados em bancos de dados SQL. No entanto, não garante recuperação completa de dados e pode resultar em perda de dados. Para evitar a perda de dados, você pode usar ferramentas profissionais de reparo do MS SQL. Elas são projetadas com algoritmos avançados para recuperar todos os objetos, incluindo índices agrupados e não agrupados, procedimentos armazenados, gatilhos, etc., do banco de dados corrompido. 

Conclusão

Você pode reduzir a fragmentação de índices reorganizando ou reconstruindo o índice. Neste artigo, explicamos como verificar a porcentagem de fragmentação nos índices e como reconstruir os índices.