Hoe indexen opnieuw te organiseren en te herbouwen in MS SQL?

Indexen zijn de objecten van MS SQL-databasebestanden. Deze schema-objecten fungeren als een inhoudsopgave van de database. Ze worden gebruikt om de gegevensopvragingsbewerkingen op een MS SQL-databasetabel te verbeteren.

Met de tijd kan de tabel echter fragmenteren als gevolg van voortdurende INSERT en UPDATE-bewerkingen. Net als andere objecten zijn indexen ook vatbaar voor beschadiging. Als de indexen beschadigd raken, kunt u fouten ontvangen zoals:

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

Om de fouten die verband houden met beschadiging in indexen op te lossen of wanneer het fragmentatieniveau van de indexen toeneemt, kunt u de indexen opnieuw opbouwen of herorganiseren. In dit artikel zullen we bespreken hoe u de indexen in MS SQL Server kunt herorganiseren en herbouwen. We zullen ook een geavanceerd MS SQL-databasereparatiehulpprogramma noemen dat indexen en andere objecten snel en met volledige precisie kan herstellen uit beschadigde SQL-databasebestanden.

Controleer het Fragmentatiepercentage van Indexen

Voordat u de indexen herorganiseert, moet u het percentage fragmentatie weten. U kunt de onderstaande opdracht gebruiken om het percentage fragmentatie van de index te controleren:

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'

    );

​

Indexen Herorganiseren in SQL Server

Als het fragmentatieniveau van de index tussen 10 en 30 ligt, wordt aanbevolen om de index te herorganiseren. U kunt de volgende code in T-SQL gebruiken om de index te herorganiseren:

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

REORGANIZE;

​

Je kunt ook SQL Server Management Studio (SSMS) gebruiken om de indexen te reorganiseren. Volg de onderstaande stappen:

• Verbind in SSMS met je SQL Server-instantie.
• Open in Object Explorer de database.
• Open de map Tabellen en vervolgens de map Indexen.
• Klik met de rechtermuisknop op de index die je wilt reorganiseren en klik vervolgens op Reorganiseren.

Indexen herbouwen in MS SQL

Als de fragmentatie hoger is, kun je de indexen herbouwen. Je kunt het DBCC DBREINDEX-commando gebruiken om een index te herbouwen. Zo voer je dit commando uit:

DBCC DBREINDEX

(

    table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

​

Dit commando ondersteunt geen ruimtelijke indexen en geheugen-geoptimaliseerde kolomopslag. 

Je kunt ook het ALTER INDEX-commando gebruiken:

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

REBUILD;

​

Je kunt de indexen ook herbouwen met de grafische gebruikersinterface in SSMS. Volg deze stappen:

• In SSMS, in Object Explorer, open de database met de tabel waarop je een index wilt herbouwen.
• Open de map Tabellen en vervolgens de tabel waarop je een index wilt herbouwen.
• Open de map Indexen, klik met de rechtermuisknop op de index die je wilt herbouwen en selecteer Herbouwen.

Herstel de SQL Server Database

Als het reorganiseren of opnieuw opbouwen van de indexen niet werkt, dan kun je de DBCC CHECKDB opdracht proberen met de REPAIR_ALLOW_DATA_LOSS optie om de database te repareren. Hier is hoe je deze opdracht gebruikt:

Eerst verander je de modus van de database naar SINGLE_USER met de onderstaande opdracht:

ALTER DATABASE BusinessEntity SET SINGLE_USER

Vervolgens voer je de DBCC CHECKDB opdracht uit zoals hieronder gegeven om de database te repareren:

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

GO

​

Na de reparatie stel je de database modus in op MULTI_USER door de onderstaande opdracht uit te voeren:

ALTER DATABASE BusinessEntity SET MULTI_USER

De bovenstaande DBCC CHECKDB opdracht kan je helpen om alle fouten die verband houden met corruptie in indexen op te lossen. Het kan zowel geclusterde als niet-geclusterde indexen in SQL-databases repareren. Het garandeert echter geen volledige gegevensherstel en kan leiden tot gegevensverlies. Om gegevensverlies te voorkomen, kun je professionele MS SQL-repairtools gebruiken. Deze zijn ontworpen met geavanceerde algoritmen om alle objecten, inclusief geclusterde en niet-geclusterde indexen, opgeslagen procedures, triggers, enz., uit de corrupte database te herstellen.

Conclusie

Je kunt indexfragmentatie verminderen door de index te reorganiseren of opnieuw op te bouwen. In dit artikel hebben we uitgelegd hoe je het fragmentatiepercentage in indexen kunt controleren en hoe je de indexen opnieuw kunt opbouwen.