I denne artikel lærer vi, hvordan du identificerer og løser indeksfragmentering i SQL Server. Identifikation af indeksfragmentering og vedligeholdelse af indeks er vigtige dele af databasevedligeholdelsesopgaven. Microsoft SQL Server opdaterer hele tiden indeksstatistikken med Insert-, Update- eller Delete-aktiviteten over tabellen. Indeksfragmenteringen er værdien for indeksets ydeevne i procent, som kan hentes af SQL Server DMV. I henhold til indekspræstationsværdien kan brugerne tage indeksene i vedligeholdelse ved at revidere fragmenteringsprocenten ved hjælp af Rebuild- eller Reorganize-operationen.
Hvorfor varierer indeksfragmenteringsprocenten?
Indeksfragmenteringsprocenten varierer, når de logiske sideordrer ikke koordineres med den fysiske sideordre i sideallokeringen af et indeks. Med datamodificeringen i tabellen kan oplysninger ændres i størrelse på datasiden. Siden var topfyldt før opdateringsoperationen på tabellen. Der kunne dog findes ledig plads på datasiden med en opdateringsoperation på tabellen. Brugerne kan observere den forstyrrende sideordning med den massive sletningsoperation i tabellen. Sammen med opdaterings- og sletningsoperationerne vil datasiden ikke være en topfyldt eller tom side. Derfor øger den uudnyttede frie plads ordensmisforholdet mellem logisk side og fysisk side med stigende fragmentering, og det kan forårsage den dårligste forespørgselspræstation og forbruger også flere serverressourcer.
Det er vigtigere at understrege, at indeksfragmenteringen kun påvirker forespørgselspræstationen i forbindelse med sidescanning. I sådanne tilfælde øger det chancerne for den dårlige ydelse af andre SQL-forespørgsler også, fordi forespørgsel med det højt fragmenterede indeks over tabellen tager længere tid at udføre og bruger flere ressourcer såsom Cache, CPU og IO. Derfor har resten af SQL-forespørgslerne svært ved at afslutte operationen med de inkonsekvente serverressourcer. Selv blokering kan forekomme ved opdaterings- og sletningsoperationen, fordi optimeringsenheden ikke indsamler oplysninger om indeksfragmenteringen, mens den genererer udførelsesplanen for forespørgslen.
Der kan oprettes en række indekser på et enkelt bord med en kombination af forskellige kolonner, og hvert indeks kan have en anden fragmenteringsprocent. Nu skal brugerne, før de gør det hensigtsmæssigt eller tager et indeks i vedligeholdelse, finde denne tærskelværdi fra databasen, før de gør det hensigtsmæssigt eller tager et indeks i vedligeholdelse. Nedenstående T-SQL-anvisning er en effektiv måde at finde den på med objektdetaljer.
Find indeksfragmenteringsstatus ved hjælp af T-SQL-anvisning
|
1
2
3
4
5
6
6
7
8
9
10
11
12
13
14
|
VÆLG S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
Her kan vi se, at den maksimale gennemsnitlige fragmenteringsprocent er mærkbar som 99 %, hvilket skal inddrages med en handling for at reducere fragmenteringen med valgmulighederne enten REBUILD eller REORGANIZE. REBUILD eller REORGANIZE er den indeksvedligeholdelseskommando, der kan udføres med ALTER INDEX-anvisningen. Brugere kan også udføre denne kommando ved hjælp af SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Find og udvid tabellen i Object Explorer >> Open Indexes >> Højreklik på målindekset >> Rebuild or Reorganize.
Som det fremgår af ovenstående billede, er REBUILD og REORGANIZE de to tilgængelige valgmuligheder til at spille trimningsoperationen ud over siden. Ideelt set bør denne operation udføres i perioder uden for spidsbelastning for at undgå, at den påvirker andre transaktioner og brugere. Microsoft SQL Server Enterprise Edition understøtter indeks online- og offline-funktioner med indeks REBUILD.
REBUILD INDEX
INDEX REBUILD dropper altid indekset og reproducerer det med nye indekssider. Denne aktivitet kan køres parallelt ved hjælp af en onlineindstilling (Enterprise Edition) med kommandoen ALTER INDEX, hvilket ikke påvirker de kørende anmodninger og opgaver for en lignende tabel.
REBUILD Indekset kan indstilles online eller offline ved hjælp af nedenstående SQL-kommandoer:
|
1
2
3
4
5
|
–Grundlæggende Rebuild Kommando
ALTER INDEX Indeks_Navn PÅ Tabel_Navn REBUILD
–REBUILD Indeks med ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Hvis en bruger udfører REBUILD INDEX offline, så vil objektressourcen (tabel) for indekset ikke være tilgængelig før afslutningen af REBUILD-processen. Det påvirker også mange andre transaktioner, som er knyttet til dette objekt. Operationen “Rebuild index” genskaber indekset. Derfor genererer den nye statistikker og vedhæfter også logposterne for indekset i databasens transaktionslogfil.
Lad os f.eks. før genopbygningen af indekset tage den aktuelle tildeling af sider til indekset i AdventureWorks-databasen, tabellen Sales.OrderTracking og indekset IX_OrderTracking_CarrierTrackingNumber med navnet IX_OrderTracking_CarrierTrackingNumber.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_page_id, previous_page_page_page_id, next_page_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Her, Der findes 1961 sider i databasefilen for dette indeks, og de første 5 sider er 861, 862, 1627, 1628 og 1904 i rækkefølge efter sidetal. Lad os nu genopbygge indekset ved hjælp af SSMS.
Index REBUILD-operationen er gennemført med succes, og tag sidetildelingsreferencer for det samme indeks ved hjælp af den samme T-SQL-forespørgsel igen.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_page_id,
previous_page_page_page_id, next_page_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Efter genopbygning af indekset er det genopfriskede sidetal 1457, som før var 1961. Hvis du kontrollerer de første 5 tildelte sider i det samme indeks, er det blevet ændret med de nye sidehenvisninger. Det antager, at indekset er droppet og lavet en gang til. Vi bør kontrollere den genopfriskede fragmenteringsprocent for det samme indeks, og som det kan ses nedenfor, er den 0,1 % nu.
REBUILD clustered index over tabellen påvirker også andre indekser i tabellen, fordi REBUILD clustered index også genopbygger det ikke-clustered index for tabellen. Udfør en genopbygningsoperation på alle indekser i tabellen eller databasen sammen; en bruger kan bruge kommandoen DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‘DatabaseName’, ‘TableName’);
|
REORGANISERET INDEX
Kommandoen REORGANIZE INDEX omordner indekssiden ved at udvise den frie eller ubrugte plads på siden. Ideelt set omarrangeres indekssiderne fysisk i datafilen. REORGANIZE udelader og opretter ikke indekset, men omstrukturerer blot oplysningerne på siden. REORGANIZE har ikke noget offline-valg, og REORGANIZE påvirker ikke statistikkerne sammenlignet med REBUILD-muligheden. REORGANIZE udfører altid online.
Lad os f.eks., før vi udfører REORGANIZE over indekset, tage fragmenteringsaflæsningen for databasen “AdventureWorks”, tabellen “Sales.OrderTracking” og indekset ved navn “IX_OrderTracking_SalesOrderID”.
Her er indeksfragmenteringsprocenten 98,39 før REORGANIZE. Nedenstående liste i billedet er allokeringssiderne til indekset.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_page_id,
previous_page_page_page_id, next_page_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Her er i alt 459 sider opført i ovenstående billede, og de første fem sider er 1065, 1068, 1069, 1944 og 1945. Lad os nu udføre REORGANIZE-kommandoen på indekset ved hjælp af nedenstående T-SQL-anvisning og se på sideallokeringen igen.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Her er det samlede antal sider faldet til 331, som før var 459. Desuden ser vi ikke nye sider i listen over de første fem sider, hvilket antyder, at dataene blot er omstruktureret – ikke genopfyldt igen. Selv om man også kunne se nye sider, sker det i den situation, hvor det store indeks er stærkt fragmenteret, og omgruppering over data bruger en ny side.
For at udføre REORGANIZE index-operationen på alle indekser i tabellen eller databasen samlet, kan brugeren bruge kommandoen DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
Som det fremgår, er der en væsentlig forskel mellem indekset REBUILD og REORGANIZE. Her har brugerne mulighed for at vælge et af alternativerne i forhold til procentdelen af indeksfragmenteringen. Vi kan forstå, at der ikke findes nogen dokumenterede standarder; men databaseadministratoren følger standardligningen i overensstemmelse med kravet til indeksets størrelse og typen af oplysninger.
Almindelig bestemmelse af brugen af ligningen :
- Når fragmenteringsprocenten er mellem 15-30: REORGANIZE
- Når Fragmenteringen er større end 30: REBUILD
REBUILD-indstillingen er mere nyttig sammen med ONLINE-indstillingen, når databasen ikke er tilgængelig til at foretage indeksvedligeholdelse uden for spidsbelastningstimerne.
Konklusion
Indeksfragmentering er en intern fragmentering i datafilen. Kerneparametre for hurtig ydeevne af din database er databasearkitekturen, databasedesignet og forespørgselsskrivning. Et godt indeksdesign med vedligeholdelse øger altid forespørgselsydelsen i databasemaskinen.
- Author
- Recent Posts
Se alle indlæg af Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Sådan automatiseres Table Partitioning i SQL Server – juli 7, 2020
- Konfigurering af SQL Server Always On Availability Groups på AWS EC2 – juli 6, 2020