Como reorganizar e reconstruir índices no MS SQL?

Os índices são os objetos dos arquivos de banco de dados do 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 do banco de dados MS SQL.

No entanto, com o tempo, a tabela pode se fragmentar devido a operações contínuas de INSERT e UPDATE. Além disso, como outros objetos, os índices também são propensos à 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, discutiremos 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 com total precisão.

Verifique a Percentagem de Fragmentação dos Índices

Antes de reorganizar os índices, você precisa saber a porcentagem de fragmentação. Você pode usar o comando abaixo para verificar a porcentagem 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 servidor SQL.
• No Explorador de Objetos, expanda o banco de dados.
• Expanda a pasta Tabelas e depois a pasta Índices.
• Clique com o botão direito no índice que deseja reorganizar e selecione Reorganizar.

Reconstruindo Índices no MS SQL

Se a fragmentação estiver 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 coluna armazenados em memória. 

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 depois a tabela na qual você precisa reconstruir um índice.
• Expanda a pasta Índices, clique com o botão direito no índice que deseja reconstruir e selecione Reconstruir.

Reparar o Banco de Dados do SQL Server

Se reorganizar ou reconstruir os índices não funcionar, você pode tentar o comando DBCC CHECKDB com a opção REPAIR_ALLOW_DATA_LOSS para reparar o banco de dados. Veja como usar este 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 indicado abaixo para reparar o banco de dados:

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

GO

​

Após a reparação, 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 ajudá-lo a resolver todos os erros relacionados à corrupção nos índices. Ele pode reparar tanto índices agrupados quanto não agrupados em bancos de dados SQL. No entanto, não garante a recuperação completa dos dados e pode resultar em perda de dados. Para evitar a perda de dados, você pode usar qualquer ferramenta profissional 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 dos índices reorganizando ou reconstruindo o índice. Neste artigo, explicamos como verificar a porcentagem de fragmentação nos índices e como reconstruir os índices.