Ebben a cikkben megtanuljuk, hogyan lehet azonosítani és feloldani az index töredezettségét az SQL Serverben. Az indextöredezettség azonosítása és az indexek karbantartása az adatbázis-karbantartási feladatok fontos része. A Microsoft SQL Server a táblán végzett Insert, Update vagy Delete tevékenységgel folyamatosan frissíti az indexstatisztikákat. Az index töredezettség az index teljesítményének százalékban kifejezett értéke, amelyet az SQL Server DMV segítségével lehet lekérni. Az index teljesítményértékének megfelelően a felhasználók az indexeket karbantartásba vehetik a töredezettségi százalék felülvizsgálatával az Újjáépítés vagy Újraszervezés művelet segítségével.
Miért változik az index töredezettségi százaléka?
Az index töredezettségi százaléka akkor változik, ha a logikai laprendek nem egyeznek meg a fizikai laprenddel az index lapkiosztásában. A táblázatban lévő adatmódosítással az információ átméretezhető az adatlapon. A táblán végzett frissítési művelet előtt az oldal tetején tele volt. A táblán végzett frissítési művelettel azonban szabad helyet lehetett találni az adatlapon. A felhasználók megfigyelhetik a zavaró oldalsorrendet a táblán végzett masszív törlési művelettel. A frissítési és törlési műveletekkel együtt az adatoldal nem lesz felülről teli vagy üres oldal. Ezért a ki nem használt szabad hely a töredezettség növekedésével növeli a logikai oldal és a fizikai oldal közötti sorrendeltérést, ami a legrosszabb lekérdezési teljesítményt okozhatja, és több szerver erőforrást is fogyaszt.
Még lényegesebben ki kell fejteni, hogy az index töredezettsége csak az oldalpásztázással befolyásolja a lekérdezési teljesítményt. Ilyen esetekben megnöveli a többi SQL-kérés rossz teljesítményének esélyét is, mivel a nagy töredezettségű indexszel rendelkező lekérdezés a táblázat felett több időt vesz igénybe a végrehajtás, és több erőforrást fogyaszt, mint például a gyorsítótár, a CPU és az IO. Ezért a többi SQL-kérés nehezen tudja befejezni a műveletet az inkonzisztens kiszolgálói erőforrásokkal. Még blokkolás is előfordulhat a frissítési és törlési műveletnél, mivel az optimalizáló nem fogja összegyűjteni az index töredezettségére vonatkozó információkat a lekérdezés végrehajtási tervének generálásakor.
Egyetlen táblán több indexet is létrehozhatunk különböző oszlopok kombinációjával, és minden indexnek eltérő töredezettségi százaléka lehet. Most, mielőtt megfelelővé tenné vagy karbantartásba venne egy indexet, a felhasználóknak meg kell találniuk ezt a küszöbértéket az adatbázisból. Az alábbi T-SQL utasítás egy hatékony módja annak, hogy megtalálja azt az objektum részleteivel.
Az index fragmentáltsági állapotának keresése a T-SQL utasítás
|
1
2
3
4
5
6
. 7
8
9
10
11
12
13
14
|
SELECT 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
|
Itt láthatjuk, hogy a maximális átlagos töredezettségi százalék 99 %-ban érzékelhető, amit a töredezettség csökkentésére irányuló művelettel kell foglalkozni a REBUILD vagy a REORGANIZE választási lehetőségekkel. A REBUILD vagy a REORGANIZE az ALTER INDEX utasítással végrehajtható indexkarbantartási parancs. A felhasználók ezt a parancsot az SSMS segítségével is elvégezhetik.
Rebuild and Reorganize Index using SQL Server Management Studio (SSMS)
Keresse meg és bontsa ki a táblát az Object Explorerben >> Open Indexes >> Jobb gombbal kattintson a célindexre >> Rebuild or Reorganize.
Ahogy a fenti képen látható, a REBUILD és az REORGANIZE a két választható lehetőség a lapon végzett trimmelési művelet lejátszásához. Ideális esetben ezt a műveletet csúcsidőn kívül kellene elvégezni, hogy elkerüljük a többi tranzakcióra és felhasználóra gyakorolt hatását. A Microsoft SQL Server Enterprise Edition támogatja az index online és offline funkcióit az index REBUILD segítségével.
REBUILD INDEX
INDEX REBUILD mindig eldobja az indexet, és új indexoldalakkal reprodukálja azt. Ez a tevékenység párhuzamosan futtatható online opcióval (Enterprise Edition) az ALTER INDEX paranccsal, ami nem befolyásolja a hasonló tábla futó kéréseit és feladatait.
REBUILD Az index online vagy offline beállítható az alábbi SQL parancsokkal:
|
1
2
3
4
5
|
–Basic Rebuild parancs
ALTER INDEX Index_Név ON Table_Name REBUILD
-.-REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Ha a felhasználó offline végzi a REBUILD INDEX-et, akkor az index objektum erőforrása (táblázat) nem lesz elérhető a REBUILD folyamat befejezéséig. Ez számos más tranzakciót is érint, amelyek ehhez az objektumhoz kapcsolódnak. Az index újraépítése művelet újra létrehozza az indexet. Ezért új statisztikákat hoz létre, és az index naplóbejegyzéseit is csatolja az adatbázis tranzakciós naplófájljához.
Vegyük például az index újjáépítése előtt az AdventureWorks adatbázis, a Sales.OrderTracking tábla és az IX_OrderTracking_CarrierTrackingNumber nevű index indexének aktuális kiosztott oldalszámát.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as 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
|
Here, 1961 oldal létezik az adatbázisfájlban ehhez az indexhez, és az első 5 oldal a 861, 862, 1627, 1628 és 1904 oldal az oldalszám sorrendjében. Most építsük újra az indexet az SSMS segítségével.
Az index REBUILD művelet sikeresen befejeződött, és ugyanannak az indexnek az oldalkiosztási referenciáit ugyanannak a T-SQL lekérdezésnek a segítségével ismét vegyük fel.
|
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_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
|
Az index újraépítése után a frissített oldalszám 1457, ami korábban 1961 volt. Ha megnézzük ugyanennek az indexnek az első 5 kiosztott oldalát, akkor az új oldalhivatkozásokkal változott. Feltételezi, hogy az indexet eldobták és újra elkészítették. Ellenőriznünk kell ugyanennek az indexnek a felfrissített töredezettségi százalékát, és mint alább látható, ez most 0,1%.
A tábla feletti REBUILD fürtözött index hatással van a tábla többi indexére is, mert a REBUILD fürtözött index újraépíti a tábla nem fürtözött indexét is. A tábla vagy az adatbázis összes indexén együttesen végezze el az újjáépítési műveletet; a felhasználó használhatja a DBCC DBREINDEX() parancsot.
|
1
|
DBCC DBREINDEX (‘AdatbázisNév’, ‘TáblázatNév’);
|
REORGANIZED INDEX
A REORGANIZE INDEX parancs átrendezi az indexlapot azáltal, hogy kiűzi a lapon lévő szabad vagy nem használt helyet. Ideális esetben az indexoldalakat fizikailag átrendezi az adatfájlban. A REORGANIZE parancs nem dobja ki és nem hozza létre az indexet, hanem egyszerűen átrendezi az oldalon lévő információkat. A REORGANIZE nem rendelkezik offline választási lehetőséggel, és a REORGANIZE nem befolyásolja a statisztikákat a REBUILD opcióhoz képest. A REORGANIZE mindig online végzi.
Vegyük például a REORGANIZE elvégzése előtt az index felett az “AdventureWorks” adatbázis, az “Sales.OrderTracking” tábla és az “IX_OrderTracking_SalesOrderID” nevű index töredezettségi olvasatát.
Itt az index töredezettségi aránya 98,39 a REORGANIZE előtt. A képen látható alábbi lista az indexhez tartozó allokációs oldalakat tartalmazza.
|
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_page_id
FROM sys.dm_db_datbázis_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
|
Itt a fenti képen összesen 459 oldal szerepel, az első öt oldal pedig 1065, 1068, 1069, 1944 és 1945. Most végezzük el a REORGANIZE parancsot az indexen az alábbi T-SQL utasítással, és nézzük meg újra az oldalkiosztást.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Itt a teljes oldalszám 331-re csökken, ami korábban 459 volt. Továbbá nem látunk új oldalakat az első öt oldal listájában, ami arra utal, hogy az adatokat csak átstrukturálták – nem töltötték fel újra. Még láthatnánk új oldalakat is, ez abban a helyzetben fordul elő, amikor a nagy index erősen töredezett, és az adatok átrendezése új oldalt használ.
A REORGANIZE index műveletnek a tábla vagy az adatbázis összes indexére együttesen történő elvégzéséhez a felhasználó a DBCC INDEXDEFRAG() parancsot használhatja:
|
1
|
DBCC INDEXDEFRAG(‘DatabaseName’, ‘TableName’);
|
Mint látható, az index REBUILD és REORGANIZE között lényeges különbség van. Itt a felhasználók az index töredezettségi százalékának megfelelően választhatnak az alternatívák közül. Érthető, hogy nincsenek dokumentált szabványok, azonban az adatbázis-adminisztrátor az index méretének és az információ típusának követelménye szerint követi a szabványos egyenletet.
Az egyenlet használatának szokásos meghatározása :
- Ha a töredezettségi százalék 15-30 között van: REORGANIZE
- Ha a töredezettség nagyobb, mint 30: REBUILD
A REBUILD opció hasznosabb az ONLINE opcióval együtt, ha az adatbázis nem áll rendelkezésre az index karbantartására csúcsidőn kívül.
Következtetés
Az index töredezettsége az adatfájl belső töredezettsége. Az adatbázis gyors teljesítményének alapvető paraméterei az adatbázis-architektúra, az adatbázis-tervezés és a lekérdezésírás. A jó indextervezés karbantartással mindig növeli a lekérdezési teljesítményt az adatbázis-motorban.
- Author
- Recent Posts
Jignesh Raiyani összes bejegyzése
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Hogyan lehet automatizálni a táblák particionálását az SQL Serverben – 2020. július 7.
- Az SQL Server Always On rendelkezésre állási csoportjainak konfigurálása az AWS EC2-n – 2020. július 6.