Az indexek jók, szeretjük az indexeket. A lekérdezéseket egy-egy jól megválasztott index nagyon felgyorsíthatja.
De sajnos ez sem olyan egyszerű, hogy rádobjuk a tábláinkra, aztán minden rendben van velük, elfelejthetjük.
Az indexek is természetesen az adatbázisban tárolódnak. Van egy táblánk, rajta egy index. Beleírunk valamit a táblába, felveszünk egy új bejegyzést az indexbe. Aztán még egyet, még egyet, még egyet, hiszen a táblára azért tettünk indexet, hogy egy vagy több mező alapján gyorsabb legyen a lekérdezés. A táblába bármilyen sorrendbe írhatjuk az adatokat, az index majd szépen olyan sorrendben tárolja, amit megadtunk, ezért gyorsabb lesz.
A tábla fizikailag össze-vissza tartalmazza a rekordokat, az index megszépen „rendbe teszi” azokat. De ha egy indexbe beszúrunk egy új tételt, az nem az index végén lesz, hanem ahova logikailag tartozna. Ezt meg valóban, fizikailag oda beszúrni, ahova tartozna, elég munkás lenne általában.
Van pár millió rekordunk egy indexben, majd be szeretnénk írni plusz egyet a második helyre. Ha fizikailag oda szeretnénk beszúrni, akkor pár millió index bejegyzést el kellene tolni, ami erőforrásigényes.
Emiatt nem fizikailag oda szúrjuk be, hanem (általában) a végére természetesen. Így viszont felmerül a kérdés: tegyünk esetleg indexet az indexre? (Általában: majd egyszer írok a fillfactorról is.)
Természetesen ez nem lehetséges, meg aztán azzal is mit nyernénk, ha az is előbb-utóbb szénakazalnak nézne ki, és azon gondolkoznánk, hogy az index indexét is indexelni kellene.
Mit tehetünk?
Ehelyett időnként az indexeket jó újraépíteni, rebuildelni. Ennek egy sokkal szolidabb változata az újraszervezés, vagy reorganize. Hogy mikor melyikre lehet szükségünk, és miből tudjuk, hogy már ideje lenne valamelyiknek?
Rebuild esetén az indexet eldobjuk, majd újra létrehozzuk. Friss, ropogós lesz, a tartalma pedig gyors. A hátulütője, hogy nagy indexek esetén ez sokáig tarthat és ha nem tudjuk online megoldani (a táblája teljesen elérhető és használható marad az újraépítés közben), csak offline (kvázi lockolja a tábláját, nem lehet közben változtatni), akkor az nem szép. Plusz vannak még egyéb ronda dolgok is, amik egyrészt függenek az SQL verziódtól és editiontől, másrészt például a partitoningtől.
Reorganize esetén nem fogja az egész indexet újraépíteni, hanem csak az indexeket tartozó lapokat rendezi újra, esetleg kicsit megbolygatja a fillfactor miatt.
Tehát röviden: a rebuild lassabb és alapos, a reorganize gyors és egyszerű.
Miből tudjuk, hogy tenni kellene valamit?
A fenti leírás alapján már érhetjük, hogy az indexek töredezettek lehetnek az idő előrehaladtával. Ezt meg tudjuk nézni a felületen is az index tulajdonságai között, de itt egy script is, ami kiírja az adott adatbázisra az index töredezettség mértékét:
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
Ha nagy egy index töredezettsége, akkor jöhetnek a fentiek (mondjuk 50% felett már kötelezően).
Ha tudjuk a rendszerről, hogy egy adott időpontban nincs rajta terhelés, vagy csak minimális, akkor a rebuildet ajánlom, online módban.
Ha a rendszer folyamatosan terhelés alatt van, akkor is megpróbálkozhatunk a rebuild online móddal, de valószínűleg nem fog eredményre vezetni. Ilyenkor használjuk a rebuildet, sokkal kíméletesebben fog bánni az adatbázissal.
A fenti scriptben a page_count azért van benne, mert ha egy index kevés lapon helyezkedik el, akkor nem nagyon lehet vele töredezettség szemponjából mit kezdeni. Az index alapban gyors, mert kevés helyet foglal, az SQL szerver nem is hajlandó foglalkozni vele.
Ez egy szép nagy téma, visszatérek még rá a részletekkel.
