I den här artikeln lär vi oss hur man identifierar och löser indexfragmentering i SQL Server. Identifiering av indexfragmentering och indexunderhåll är viktiga delar av underhållsuppgiften för databasen. Microsoft SQL Server fortsätter att uppdatera indexstatistiken med Insert, Update eller Delete-aktiviteten över tabellen. Indexfragmenteringen är indexprestandavärdet i procent, som kan hämtas av SQL Server DMV. Enligt indexprestandavärdet kan användarna ta indexen i underhåll genom att revidera fragmenteringsprocenten med hjälp av åtgärden Rebuild eller Reorganize.
Varför varierar indexfragmenteringsprocenten?
Indexfragmenteringsprocenten varierar när de logiska sidordningarna inte samordnas med de fysiska sidordningarna i sidoallokeringen av ett index. Med datamodifieringen i tabellen kan informationen ändras i storlek på datasidan. Sidan var toppfull före uppdateringsoperationen i tabellen. Det finns dock ledigt utrymme på datasidan med en uppdatering av tabellen. Användarna kan observera den störande sidordningen med den massiva raderingsoperationen i tabellen. Tillsammans med uppdaterings- och raderingsoperationer kommer datasidan inte att vara en toppfylld eller tom sida. Icke utnyttjat ledigt utrymme ökar därför missförhållandet mellan den logiska sidan och den fysiska sidan när fragmenteringen ökar, vilket kan leda till sämre prestanda för sökningar och förbrukar mer serverresurser.
Det är viktigare att klargöra att indexfragmenteringen endast påverkar sökningsprestandan vid sidavläsning. I sådana fall ökar risken för dålig prestanda för andra SQL-förfrågningar också, eftersom en fråga med ett starkt fragmenterat index över tabellen tar längre tid att utföra och förbrukar mer resurser, t.ex. cacheminnet, CPU och IO. Därför har resten av SQL-förfrågningarna svårt att slutföra operationen med de inkonsekventa serverresurserna. Till och med blockering kan uppstå vid uppdatering och borttagning eftersom optimeraren inte samlar in information om indexfragmenteringen när den genererar exekveringsplanen för frågan.
Det kan skapas ett antal index på en enda tabell med en kombination av olika kolumner, och varje index kan ha en annan fragmenteringsprocent. Nu måste användarna, innan de gör det lämpligt eller tar ett index i underhåll, hitta detta tröskelvärde från databasen. Nedanstående T-SQL-anvisning är ett effektivt sätt att hitta det med objektsdetaljer.
Finn indexfragmenteringsstatus med hjälp av T-SQL-anvisning
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
VÄLJ 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) 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
|
Här kan vi se att den högsta genomsnittliga fragmenteringsprocenten är 99 %, vilket innebär att man måste vidta en åtgärd för att minska fragmenteringen genom att välja antingen REBUILD eller REORGANIZE. REBUILD eller REORGANIZE är det indexunderhållskommando som kan utföras med ALTER INDEX-angivelsen. Användare kan också utföra det här kommandot med hjälp av SSMS.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Hitta och expandera tabellen i Object Explorer >> Open Indexes >> Högerklicka på målindexet >> Rebuild or Reorganize.
Som framgår av bilden ovan är REBUILD (återuppbygga) och REORGANIZE (omorganisera) de två tillgängliga valen för att spela ut trimningsoperationen över sidan. Helst bör den här operationen utföras under lågsäsong för att undvika att den påverkar andra transaktioner och användare. Microsoft SQL Server Enterprise Edition stöder funktioner för index online och offline med index REBUILD.
REBUILD INDEX
INDEX REBUILD släpper alltid indexet och återskapar det med nya indexsidor. Denna aktivitet kan köras parallellt med hjälp av ett onlinealternativ (Enterprise Edition) med kommandot ALTER INDEX, vilket inte påverkar de pågående förfrågningarna och uppgifterna för en liknande tabell.
REBUILD Index kan ställas in online eller offline med hjälp av nedanstående SQL-kommandon:
|
1
2
3
4
5
|
–Grundläggande återuppbyggnadskommando
ALTER INDEX Index_Name ON Table_Name REBUILD
–REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Om en användare utför REBUILD INDEX offline, kommer indexets objektresurs (tabell) inte att vara tillgänglig förrän REBUILD-processen är avslutad. Detta påverkar också många andra transaktioner som är kopplade till detta objekt. Operationen Rebuild index återskapar indexet. Därför genererar den ny statistik och lägger till loggposterna för indexet även i loggfilen för databastransaktioner.
Innan indexet byggs om tar vi till exempel den nuvarande tilldelningen av sidor för indexet i AdventureWorks-databasen, tabellen Sales.OrderTracking och indexet IX_OrderTracking_CarrierTrackingNumber med namnet 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_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
|
Här, 1961 sidor finns i databasfilen för detta index, och de fem första sidorna är 861, 862, 1627, 1628 och 1904 i sidnummerordning. Låt oss nu bygga om indexet med hjälp av SSMS.
Index REBUILD operation har slutförts framgångsrikt och ta sidallokeringsreferenser för samma index med hjälp av samma T-SQL-fråga 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_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
|
Efter att ha återuppbyggt indexet är det uppdaterade sidantalet 1457, vilket tidigare var 1961. Om du kontrollerar de fem första tilldelade sidorna i samma index har de ändrats med de nya sidreferenserna. Det förutsätter att indexet har tagits bort och gjorts om en gång till. Vi bör kontrollera den uppdaterade fragmenteringsprocenten för samma index, och som framgår nedan är den 0,1 % nu.
REBUILD klusterindex över tabellen påverkar även andra index i tabellen, eftersom REBUILD klusterindexet också återskapar det icke klusterindexet i tabellen. Utför en återuppbyggnadsoperation på alla index för tabellen eller databasen tillsammans; en användare kan använda kommandot DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (’DatabaseName’, ’TableName’);
|
REORGANIZED INDEX
Kommandot REORGANIZE INDEX ordnar om indexsidan genom att utvisa fritt eller oanvänt utrymme på sidan. I idealfallet omordnas indexsidorna fysiskt i datafilen. REORGANIZE släpper och skapar inte indexet utan omstrukturerar helt enkelt informationen på sidan. REORGANIZE har inget offlineval och REORGANIZE påverkar inte statistiken jämfört med alternativet REBUILD. REORGANIZE utför alltid online.
Till exempel, innan vi utför REORGANIZE över indexet, låt oss ta fragmenteringsavläsningen för databasen ”AdventureWorks”, tabellen ”Sales.OrderTracking” och indexet som heter ”IX_OrderTracking_SalesOrderID”.
Här är indexfragmenteringsprocenten 98,39 före REORGANIZE. Listan nedan i bilden är allokeringssidorna till indexet.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_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
|
Här listas sammanlagt 459 sidor i bilden ovan, och de fem första sidorna är 1065, 1068, 1069, 1944 och 1945. Låt oss nu utföra kommandot REORGANIZE på indexet med hjälp av nedanstående T-SQL-anvisning och titta på sidallokeringen igen.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Här har det totala antalet sidor minskat till 331, vilket tidigare var 459. Dessutom ser vi inga nya sidor i listan över de fem första sidorna, vilket innebär att data bara omstruktureras – inte fylls på igen. Även om du skulle kunna se nya sidor också, händer det i situationen när det stora indexet är kraftigt fragmenterat, och omblandning av data använder en ny sida.
För att utföra REORGANIZE indexoperationen på alla index för tabellen eller databasen tillsammans, kan användaren använda kommandot DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(’DatabaseName’, ’TableName’);
|
Som framgår finns det en väsentlig skillnad mellan indexen REBUILD och REORGANIZE. Här har användarna möjlighet att välja ett av alternativen beroende på indexfragmenteringsprocenten. Vi kan förstå att det inte finns några dokumenterade standarder, men databasadministratören följer standardekvationen enligt kraven på indexets storlek och informationstyp.
Vanlig bestämning av användningen av ekvationen :
- När fragmenteringsprocenten är mellan 15-30: REORGANISERA
- När fragmenteringen är större än 30: REBUILD
REBUILD-alternativet är mer användbart tillsammans med ONLINE-alternativet när databasen inte är tillgänglig för indexunderhåll under lågtrafik.
Slutsats
Indexfragmentering är en intern fragmentering i datafilen. Kärnparametrar för snabb prestanda för din databas är databasarkitektur, databasdesign och frågeskrivning. En bra indexdesign med underhåll ökar alltid frågeprestandan i databasmotorn.
- Author
- Recent Posts
Se alla inlägg av Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Hur man automatiserar Table Partitioning i SQL Server – 7 juli 2020
- Konfigurera SQL Server Always On Availability Groups på AWS EC2 – 6 juli 2020