Come riorganizzare e ricostruire gli indici in MS SQL?

Gli indici sono gli oggetti dei file di database MS SQL. Questi oggetti di schema agiscono come un indice del database. Vengono utilizzati per migliorare le operazioni di recupero dati su una tabella del database MS SQL.

Tuttavia, nel tempo, la tabella può frammentarsi a causa di operazioni continue di INSERT e UPDATE. Inoltre, come altri oggetti, anche gli indici sono soggetti a corruzione. Se gli indici si corrompono, puoi ricevere errori come:

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

Per risolvere gli errori associati alla corruzione degli indici o quando il livello di frammentazione degli indici aumenta, puoi ricostruire o riorganizzare gli indici. In questo articolo, discuteremo come riorganizzare e ricostruire gli indici in MS SQL Server. Menzioneremo anche uno strumento avanzato di riparazione del database MS SQL che può ripristinare rapidamente gli indici e altri oggetti da file di database SQL corrotti con completa precisione.

Controlla la percentuale di frammentazione degli indici

Prima di riorganizzare gli indici, è necessario conoscere la percentuale di frammentazione. Puoi usare il comando seguente per controllare la percentuale di frammentazione dell’indice:

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'

    );

​

Riorganizzare gli indici in SQL Server

Se il livello di frammentazione dell’indice è compreso tra 10 e 30, è consigliato riorganizzare l’indice. Puoi usare il seguente codice in T-SQL per riorganizzare l’indice:

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

REORGANIZE;

​

In alternativa, è possibile utilizzare il SQL Server Management Studio (SSMS) per riorganizzare gli indici. Seguire i seguenti passaggi:

• In SSMS, connettersi all’istanza del server SQL.
• Nell’Esplora oggetti, espandere il database.
• Espandere la cartella Tabelle e poi la cartella Indici.
• Fare clic con il pulsante destro sull’indice che si desidera riorganizzare, quindi fare clic su Riorganizza.

Ricostruzione degli indici in MS SQL

Se la frammentazione è elevata, è possibile ricostruire gli indici. È possibile utilizzare il comando DBCC DBREINDEX per ricostruire un indice. Ecco come eseguire questo comando:

DBCC DBREINDEX

(

    table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

​

Questo comando non supporta gli indici spaziali e gli indici delle colonne con ottimizzazione per la memoria. 

In alternativa, è possibile utilizzare il comando ALTER INDEX:

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

REBUILD;

​

È anche possibile ricostruire gli indici utilizzando l’interfaccia utente grafica in SSMS. Ecco i passaggi da seguire:

• In SSMS, nell’Esplora oggetti, espandere il database contenente la tabella su cui è necessario ricostruire un indice.
• Espandere la cartella Tabelle e poi la tabella su cui è necessario ricostruire un indice.
• Espandere la cartella Indici, fare clic con il pulsante destro sull’indice che è necessario ricostruire e selezionare Ricostruisci.

Riparare il Database di SQL Server

Se la riorganizzazione o la ricostruzione degli indici non funziona, allora puoi provare il comando DBCC CHECKDB con l’opzione REPAIR_ALLOW_DATA_LOSS per riparare il database. Ecco come utilizzare questo comando:

Prima, cambia la modalità del database in SINGLE_USER utilizzando il comando seguente:

ALTER DATABASE BusinessEntity SET SINGLE_USER

Successivamente, esegui il comando DBCC CHECKDB come indicato di seguito per riparare il database:

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

GO

​

Dopo la riparazione, imposta la modalità del database su MULTI_USER eseguendo il comando seguente:

ALTER DATABASE BusinessEntity SET MULTI_USER

Il comando DBCC CHECKDB sopra può aiutarti a risolvere tutti gli errori correlati alla corruzione negli indici. Può riparare sia gli indici cluster che non cluster nei database SQL. Tuttavia, non garantisce un completo recupero dei dati e può causare perdita di dati. Per prevenire la perdita di dati, puoi utilizzare dei tool di riparazione MS SQL professionali. Sono progettati con algoritmi avanzati per recuperare tutti gli oggetti, inclusi gli indici cluster e non cluster, le stored procedure, i trigger, ecc., dal database corrotto. 

Conclusione

Puoi ridurre la frammentazione degli indici riorganizzando o ricostruendo l’indice. In questo articolo, abbiamo spiegato come verificare la percentuale di frammentazione negli indici e come ricostruire gli indici.