In dit artikel zullen we leren hoe we Index Fragmentatie in SQL Server kunnen identificeren en oplossen. Identificatie van indexfragmentatie en indexonderhoud zijn belangrijke onderdelen van de onderhoudstaak van een database. Microsoft SQL Server blijft de index statistieken bijwerken met de Insert, Update of Delete activiteit over de tabel. De index fragmentatie is de index prestatie waarde in percentage, die kan worden opgehaald door SQL Server DMV. Volgens de index prestatie waarde, kunnen gebruikers de indexen in onderhoud nemen door het herzien van de fragmentatie percentage met behulp van Rebuild of Reorganize operatie.
Waarom de Index Fragmentatie percentage varieert?
Index Fragmentatie percentage varieert wanneer de logische pagina orders niet coördineren met de fysieke pagina order in de pagina toewijzing van een index. Met de gegevens wijziging in de tabel, kan informatie worden gewijzigd op de gegevens pagina. De pagina was topvol vóór de bijwerkingsoperatie op de tabel. Met een bijwerkingsoperatie op de tabel kon echter vrije ruimte op de gegevenspagina worden gevonden. Bij de massale verwijderoperatie op de tabel kan de volgorde van de pagina’s worden verstoord. Samen met de bijwerkings- en verwijderingsoperaties zal de gegevenspagina geen top-volledige of lege pagina zijn. Niet gebruikte vrije ruimte verhoogt dus het verschil in volgorde tussen logische en fysieke pagina’s met toenemende fragmentatie, en dat kan slechtere query prestaties veroorzaken en verbruikt ook meer server resources.
Het is nog belangrijker om te benadrukken dat de index fragmentatie alleen van invloed is op de query prestaties met de pagina scan. In dergelijke gevallen verhoogt het de kans op slechte prestaties van andere SQL-verzoeken ook, omdat query met de hoge gefragmenteerde index over de tabel meer tijd kost om uit te voeren en meer middelen verbruikt, zoals Cache, CPU en IO. Daarom is het voor de rest van de SQL verzoeken moeilijk om de bewerking te voltooien met de inconsistente server bronnen. Zelfs blokkeren kan optreden door de Update en Delete operaties, omdat de optimizer niet de informatie van de index fragmentatie verzamelt tijdens het genereren van het uitvoeringsplan voor de query.
Er kunnen een aantal indexen worden gemaakt op een enkele tabel met de combinatie van verschillende kolommen, en elke index kan een ander fragmentatie percentage hebben. Nu, alvorens het geschikt te maken of een index in onderhoud te nemen, moeten gebruikers die drempelwaarde uit de database vinden. Het onderstaande T-SQL statement is een efficiënte manier om het te vinden met object details.
Vind Index Fragmentatie status met behulp van het T-SQL-instructie
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT S.name as ‘Schema’,
T.name as ‘Tabel’,
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) 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
|
Hier kunnen we zien dat het maximale gemiddelde fragmentatiepercentage opvalt als 99%, waarvoor een actie moet worden ondernomen om de fragmentatie te verminderen met de keuze uit REBUILD of REORGANIZE. REBUILD of REORGANIZE is het indexonderhouds-commando dat kan worden uitgevoerd met het ALTER INDEX-commando. Gebruikers kunnen dit commando ook met SSMS uitvoeren.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Zoek en vouw de tabel uit in Object Explorer >> Open Indexes >> Klik met de rechtermuisknop op de doelindex >> Rebuild or Reorganize.
Zoals in de bovenstaande afbeelding te zien is, zijn REBUILD en REORGANIZE de twee beschikbare keuzes om de trimbewerking over de pagina uit te voeren. Idealiter zou deze bewerking in de daluren moeten worden uitgevoerd om de impact op andere transacties en gebruikers te vermijden. Microsoft SQL Server Enterprise Edition ondersteunt index online en offline functies met index REBUILD.
REBUILD INDEX
INDEX REBUILD laat altijd de index vallen en reproduceert deze met nieuwe index pagina’s. Deze activiteit kan parallel worden uitgevoerd met behulp van een online-optie (Enterprise Edition) met het ALTER INDEX-commando, dat geen invloed heeft op de lopende verzoeken en taken van een soortgelijke tabel.
REBUILD Index kan online of offline worden ingesteld met de onderstaande SQL-opdrachten:
|
1
2
3
4
5
|
–Basic Rebuild Command
ALTER INDEX Index_Name ON Table_Name REBUILD
-.-REBUILD Index met ONLINE OPTIE
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Als een gebruiker de REBUILD INDEX offline uitvoert, dan is de objectbron (tabel) van de index niet toegankelijk tot het REBUILD-proces is voltooid. Dit heeft ook gevolgen voor talrijke andere transacties die aan dit object gekoppeld zijn. Rebuild index operatie maakt de index opnieuw aan. Daarom genereert het nieuwe statistieken en voegt de log records van de index in de database transactie log file ook.
Laten we bijvoorbeeld, voordat we de index herbouwen, de huidige toewijzing van pagina’s nemen voor de index van de AdventureWorks database, Sales.OrderTracking tabel en IX_OrderTracking_CarrierTrackingNumber named index.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) als db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),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
|
Hier, bestaan er 1961 pagina’s in de database file voor deze index, en de eerste 5 pagina’s zijn de 861, 862, 1627, 1628, en 1904 in volgorde van het paginanummer. Laten we nu de index opnieuw opbouwen met behulp van SSMS.
Index REBUILD operatie is met succes voltooid en neem pagina toewijzing referenties voor dezelfde index met behulp van dezelfde T-SQL query opnieuw.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) als db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),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
|
Nadat de index opnieuw is opgebouwd, is het aantal ververste pagina’s 1457, wat voorheen 1961 was. Als u de eerste 5 toegewezen pagina’s van dezelfde index controleert, is deze gewijzigd met de nieuwe paginaverwijzingen. Het veronderstelt dat de index is gedropt en opnieuw is gemaakt. We moeten het opgefriste fragmentatiepercentage voor dezelfde index controleren, en zoals hieronder te zien is, is het nu 0,1%.
REBUILD clustered index over de tabel heeft ook invloed op andere indexen van de tabel, omdat de REBUILD clustered index ook de niet-geclusterde index van de tabel herbouwt. Voer een rebuild operatie uit op alle indexen van de tabel of database samen; een gebruiker kan DBCC DBREINDEX() commando gebruiken.
|
1
|
DBCC DBREINDEX (‘DatabaseNaam’, ‘TabelNaam’);
|
REORGANIZED INDEX
Met de opdracht REORGANIZE INDEX wordt de indexpagina opnieuw geordend door de vrije of ongebruikte ruimte op de pagina te verwijderen. Idealiter worden indexpagina’s fysiek opnieuw geordend in het gegevensbestand. REORGANIZE laat de index niet vallen en creëert deze niet, maar herstructureert alleen de informatie op de pagina. REORGANIZE heeft geen offline keuze, en REORGANIZE heeft geen invloed op de statistieken in vergelijking met de REBUILD optie. REORGANIZE voert altijd online uit.
Laten we bijvoorbeeld, voordat we de REORGANIZE over de index uitvoeren, de fragmentatie uitlezen voor de database ‘AdventureWorks’, tabel ‘Sales.OrderTracking’ en index met de naam ‘IX_OrderTracking_SalesOrderID’.
Hier is het fragmentatiepercentage van de index 98,39 voordat REORGANIZE werd uitgevoerd. De onderstaande lijst in de afbeelding is de toewijzingspagina’s aan de index.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) als db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),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
|
Hier worden in totaal 459 pagina’s weergegeven in de bovenstaande afbeelding, en de eerste vijf pagina’s zijn 1065, 1068, 1069, 1944, en 1945. Laten we nu het REORGANIZE commando op de index uitvoeren met het onderstaande T-SQL statement en opnieuw naar de paginatoewijzing kijken.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Hier is het totale aantal pagina’s gedaald tot 331, terwijl dit voorheen 459 was. Verder zien we geen nieuwe pagina’s in de eerste vijf pagina’s lijst, wat impliceert dat gegevens alleen worden geherstructureerd – niet opnieuw gevuld. Zelfs je zou kunnen zien nieuwe pagina’s ook, het gebeurt in de situatie wanneer de grote index is zwaar gefragmenteerd, en herschikking over de gegevens gebruik maken van een nieuwe pagina.
Om de REORGANIZE index operatie uit te voeren op alle indexen van de tabel of database samen, kan de gebruiker gebruik maken van de DBCC INDEXDEFRAG() commando:
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
Zoals te zien is, is er een wezenlijk verschil tussen de index REBUILD en REORGANIZE. Hier hebben de gebruikers de keuze om een van de alternatieven te kiezen, afhankelijk van het fragmentatiepercentage van de index. We kunnen begrijpen dat er geen gedocumenteerde normen zijn; maar de database beheerder volgt de standaard vergelijking volgens de eis van de Index grootte en het type van informatie.
Gebruikelijke bepaling van het gebruik van de vergelijking :
- Wanneer het Fragmentatie percentage tussen 15-30 ligt: REORGANIZE
- Wanneer de Fragmentatie groter is dan 30: REBUILD
REBUILD optie is nuttiger met de ONLINE optie wanneer de database niet beschikbaar is om index onderhoud te nemen in daluren.
Conclusie
Index Fragmentatie is een interne fragmentatie in het data bestand. Kernparameters van snelle prestaties van uw database zijn de Database Architectuur, Database Ontwerp, en Query schrijven. Een goed indexontwerp met onderhoud bevordert altijd de queryprestaties in de database-engine.
- Auteur
- Recent Posts
Bekijk alle berichten van Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – 17 juli, 2020
- Hoe tabelpartities automatiseren in SQL Server – 7 juli 2020
- SQL Server Always On Availability Groups configureren op AWS EC2 – 6 juli 2020