Megosztás a következőn keresztül:

Indexkarbantartás optimalizálása a lekérdezési teljesítmény javítása és az erőforrás-felhasználás csökkentése érdekében

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)SQL database in Microsoft Fabric

Ez a cikk segít eldönteni, hogy mikor és hogyan végezze el az indexkarbantartást. Olyan fogalmakat tárgyal, mint az index töredezettsége és az oldalsűrűség, valamint azok a lekérdezési teljesítményre és az erőforrás-felhasználásra gyakorolt hatásuk. Két indexkarbantartási módszert ír le: egy index átrendezését és egy index újraépítését. A cikk egy indexkarbantartási stratégiát is javasol, amely kiegyensúlyozza a karbantartáshoz szükséges erőforrás-felhasználás lehetséges teljesítménybeli javulásait.

Jegyzet

Ez a cikk nem vonatkozik az Azure Synapse Analytics dedikált SQL-készletére. Az Azure Synapse Analytics dedikált SQL-készleteinek indexkarbantartására vonatkozó információkért lásd az Azure Synapse Analytics dedikált SQL-készlettábláinak indexeléseoldalt.

Fogalmak: indextöredezettség és oldalsűrűség

Mi az indextöredezettség, és hogyan befolyásolja a teljesítményt?

  • A B-fa (sortár) indexekben töredezettség akkor áll fenn, ha az indexek olyan lapokkal rendelkeznek, amelyekben az indexen belüli logikai sorrend az index fő értékei alapján nem egyezik meg az indexlapok fizikai sorrendjével.

    Jegyzet

    A dokumentáció általában a B-fa kifejezést használja az indexekre hivatkozva. A sorkataszterekben az adatbázismotor egy B+ fát implementál. Ez nem vonatkozik az oszlopcentrikus indexekre vagy a memóriaoptimalizált táblák indexére. További információ: SQL Server és Azure SQL index architektúrája és tervezési útmutatója.

  • Az adatbázismotor automatikusan módosítja az indexeket, amikor beszúrási, frissítési vagy törlési műveleteket hajt végre a mögöttes adatokon. Egy táblázat sorainak hozzáadása például azt eredményezheti, hogy sortárindexek meglévő lapjai feloszthatók, így helyet ad az új sorok beszúrásának. Ezek a módosítások idővel az indexben lévő adatok szétszóródását okozhatják az adatbázisban (töredezettek).

  • Az olyan lekérdezések esetében, amelyek több oldalt olvasnak teljes vagy tartományindex-vizsgálatokkal, a erősen töredezett indexek csökkenthetik a lekérdezési teljesítményt, ha további I/O-ra van szükség az adatok olvasásához. Néhány nagy I/O-kérés helyett a lekérdezéshez sok kis I/O-kérés szükséges ugyanennyi adat beolvasásához.

  • Ha a tárolási alrendszer jobb szekvenciális I/O-teljesítményt biztosít, mint a véletlenszerű I/O-teljesítmény, az indextöredezettség csökkentheti a teljesítményt, mivel több véletlenszerű I/O szükséges a töredezett indexek olvasásához.

Mi az oldalsűrűség (más néven az oldal teljessége), és hogyan befolyásolja a teljesítményt:

  • Az adatbázis minden oldala változó számú sort tartalmazhat. Ha a sorok minden helyet foglalnak el egy lapon, az oldalsűrűség 100%. Ha egy oldal üres, az oldal sűrűsége 0%. Ha egy 100% sűrűségű lap két oldalra van felosztva egy új sor elhelyezésére, a két új oldal sűrűsége körülbelül 50%.
  • Ha az oldalsűrűség alacsony, több lapra van szükség ugyanennyi adat tárolásához. Ez azt jelenti, hogy több I/O szükséges az adatok olvasásához és írásához, és több memória szükséges az adatok gyorsítótárazásához. Ha a memória korlátozott, kevesebb oldal, amit a lekérdezés igényel, kerül gyorsítótárba, ami még több lemez I/O-t eredményez. Ennek következtében az alacsony oldalsűrűség negatívan befolyásolja a teljesítményt.
  • Ha az adatbázismotor sorokat ad hozzá egy laphoz az index létrehozása, újraépítése vagy átrendezése során, az nem tölti ki teljesen az oldalt, ha az index kitöltési tényezője nem 100 (vagy 0, ami ebben a kontextusban egyenértékű). Ez alacsonyabb lapsűrűséget okoz, és hasonlóképpen növeli az I/O-terhelést, és negatívan befolyásolja a teljesítményt.
  • Az alacsony oldal-sűrűség növelheti a köztes B-faszintek számát. Ez mérsékelten növeli a processzor- és I/O-költségeket, ha levélszintű oldalakat keresnek az indexvizsgálatokban és -keresésekben.
  • Amikor a Lekérdezésoptimalizáló összeállít egy lekérdezési tervet, figyelembe veszi a lekérdezés által igényelt adatok olvasásához szükséges I/O-költségeket. Alacsony oldalsűrűség esetén több oldalt kell olvasni, ezért az I/O költsége magasabb. Ez hatással lehet a lekérdezésterv választására. Ha például az oldaleloszlások miatt az oldalsűrűség idővel csökken, az optimalizáló más tervet állíthat össze ugyanahhoz a lekérdezéshez, eltérő teljesítmény- és erőforrás-felhasználási profillal.

Borravaló

Sok számítási feladat esetén az oldalsűrűség növelése nagyobb pozitív teljesítményt eredményez, mint a töredezettség csökkentése.

Az oldalsűrűség szükségtelen csökkentése érdekében a Microsoft nem javasolja a kitöltési tényező beállítását a 100-nál vagy 0-nál eltérő értékekre, kivéve bizonyos esetekben, ha az indexek nagy számú oldaleloszlást tapasztalnak. Ez történhet például olyan gyakran módosított indexekben, amelyekben a vezető oszlop nem ismétlődő GUID-értékeket tartalmaz.

Index töredezettségének és oldalsűrűségének mérése

Mind a töredezettség, mind az oldalsűrűség azon tényezők közé tartozik, amelyeket figyelembe kell venni az indexkarbantartás és a használni kívánt karbantartási módszer kiválasztásakor.

A töredezettség eltérően van definiálva a rowstore és a columnstore indexek esetében. Sorcentrikus indexek esetén a sys.dm_db_index_physical_stats() lehetővé teszi a töredezettség és az oldalsűrűség meghatározását egy adott indexben vagy több indexben. Particionált indexek esetén sys.dm_db_index_physical_stats() minden partícióhoz megadja ezeket az információkat.

A sys.dm_db_index_physical_stats által visszaadott eredményhalmaz a következő oszlopokat tartalmazza:

Oszlop Leírás
avg_fragmentation_in_percent Logikai töredezettség (rendkívül sorrendben lévő oldalak az indexben).
avg_page_space_used_in_percent Átlagos oldalsűrűség.

Az oszlopcentrikus indexekben lévő tömörített sorcsoportok esetében a töredezettség a törölt sorok és az összes sor arányát határozza meg százalékban kifejezve. sys.dm_db_column_store_row_group_physical_stats segítségével meghatározhatja egy adott index sorcsoportonkénti teljes és törölt sorainak számát, a tábla összes indexét vagy az adatbázis összes indexét.

A sys.dm_db_column_store_row_group_physical_stats által visszaadott eredményhalmaz a következő oszlopokat tartalmazza:

Oszlop Leírás
total_rows A sorcsoportban fizikailag tárolt sorok száma. Tömörített sorcsoportok esetén ez magában foglalja a töröltként megjelölt sorokat is.
deleted_rows A törlésre megjelölt tömörített sorcsoportban fizikailag tárolt sorok száma. 0 a deltatárolóban lévő sorcsoportok esetében.

Az oszlopcentrikus indexek tömörített sorcsoportjának töredezettsége a következő képlet használatával számítható ki:

100.0 * (ISNULL(total_stored_deleted_rows, 0)) / NULLIF(total_rows, 0)

Ha meg szeretné határozni egy nem klaszteres oszlopközpontú indexhez a fizikailag tárolt törölt sorok teljes számát, adja hozzá az értékeket az deleted_rows oszlopban sys.dm_db_column_store_row_group_physical_stats, illetve a rows oszlopban a sys.internal_partitions táblázatban azonos objektumhoz, indexhez és partícióhoz tartozó belső objektumtípus COLUMN_STORE_DELETE_BUFFER esetén. Példaértékűen lásd: Oszlopalapú index töredezettségének ellenőrzése.

Borravaló

hu-HU: A soros és oszlopos indexek esetében tekintse át az indexek és halmazok töredezettségét és oldalsűrűségét nagy számú sor törlése vagy frissítése után. Halom esetén, ha gyakori frissítések vannak, rendszeresen tekintse át a töredezettséget, hogy elkerülje a továbbító rekordok elszaporodását. További információk a halmokról (fürtözött index nélküli táblák) a jelzés alatt találhatók:.

A töredezettség és az oldalsűrűség meghatározásához tekintse meg a minta lekérdezéseket a példák között.

Indexkarbantartási módszerek: átrendezés és újraépítés

Az alábbi módszerek egyikével csökkentheti az index töredezettségét, és növelheti az oldalsűrűséget:

  • Index átrendezése
  • Index újraépítése

Jegyzet

A particionált indexek esetében az alábbi módszerek bármelyikét használhatja az összes partíción vagy egy index egyetlen partícióján.

Index átrendezése

Az indexek átrendezése kevésbé erőforrásigényes, mint egy index újraépítése. Ezért legyen ez az elsődleges indexkarbantartási módszer, hacsak nincs konkrét oka az index újraépítésére. Az átrendezés mindig online művelet. Ez azt jelenti, hogy a hosszú távú objektumszintű zárolások nincsenek tárolva, és a mögöttes tábla lekérdezései vagy frissítései ALTER INDEX ... REORGANIZE a művelet során is folytatódhatnak.

  • Soros tárolású indexek esetén az adatbázismotor csak a táblákon és nézeteken klaszterezett és nem klaszterezett indexek levélszintjét töredezettségmentesíti. Fizikailag átrendezi a levélszintű lapokat a levélcsomópontok logikai sorrendjének megfelelően, balról jobbra. Az átrendezés az indexlapokat is tömöríti, így az oldalsűrűség megegyezik az index kitöltési tényezőjének. A kitöltési tényező beállításának megtekintéséhez használja sys.indexes. A szintaxisra vonatkozó példákért lásd: Példák – Sortár átrendezése.
  • Az oszlopcentrikus indexekhasználatakor a deltatároló több kis sorcsoportot is tartalmazhat az adatok beszúrása, frissítése és törlése után. Az oszlopcentrikus index átrendezésével a deltatároló sorcsoportokat tömörített sorcsoportokká kell alakítani az oszloptárban, és a kisebb tömörített sorcsoportokat nagyobb sorcsoportokba egyesíti. Az átrendezési művelet fizikailag eltávolítja az oszloptárban töröltként megjelölt sorokat is. Az oszlopcentrikus indexek átrendezéséhez további CPU-erőforrásokra lehet szükség az adatok tömörítéséhez. Amíg a művelet fut, a teljesítmény lelassulhat. Az adatok tömörítése után azonban javul a lekérdezési teljesítmény. A szintaxisra vonatkozó példákért lásd a Példák – Oszloptár átrendezésecímű témakört.

Az SQL Server 2019-től (15.x), az Azure SQL Database-től és az Azure SQL-felügyelt példánytól kezdődően a tuple-mover munkát egy háttérben futó egyesítési feladat segíti. Ez a feladat automatikusan tömöríti azokat a kisebb nyitott delta sorcsoportokat, amelyek egy belső küszöbérték meghatározása szerint már egy ideje léteznek, vagy egyesíti azokat a tömörített sorcsoportokat, amelyekből jelentős számú sor lett törölve. Ez idővel javítja az oszlopcentrikus index minőségét. A legtöbb esetben ez nem igényli ALTER INDEX ... REORGANIZE parancsok kiadását.

Borravaló

Ha megszakít egy átrendezési műveletet, vagy ha egyébként megszakad, az addig elért folyamat megmarad az adatbázisban. A nagy indexek átrendezéséhez a művelet többször is elindítható és leállítható, amíg be nem fejeződik.

Index újraépítése

Az index újraépítése során az index törlődik és újra létrejön. Az index típusától és az adatbázismotor verziójától függően az újraépítési művelet offline vagy online is elvégezhető. Az offline indexek újraépítése általában kevesebb időt vesz igénybe, mint egy online újraépítés, de az újraépítési művelet időtartama alatt objektumszintű zárolásokat tartalmaz, megakadályozva a lekérdezések elérését a táblához vagy nézethez.

Az online indexek újraépítéséhez nem szükséges objektumszintű zárolás a művelet végéig, ha az újraépítés befejezéséhez rövid ideig zárolást kell tartani. Az adatbázismotor verziójától függően az online index újraépítése elindítható újrakezdhető műveletként. Az újrakezdhető indexek újraépítése szüneteltethető, így a folyamat az adott pontig tart. Az újrakezdhető újraépítési művelet a szüneteltetés vagy megszakítás után folytatható, vagy megszakítható, ha az újraépítés befejezése szükségtelenné válik.

Transact-SQL szintaxisért lásd ALTER INDEX REBUILD. További információ az online index újraépítéséről: Indexműveletek online végrehajtása.

Jegyzet

Az index online újraépítése közben az indexelt oszlopokban lévő adatok minden módosításának frissítenie kell az index egy további példányát. Ez az adatmódosítási utasítások kisebb teljesítménycsökkenéséhez vezethet az online újraépítés során.

Ha egy online folytatható indexművelet szüneteltetve van, ez a teljesítményhatás mindaddig fennáll, amíg a művelet be nem fejeződik vagy meg nem szakad. Ha nem kíván végrehajtani egy újrahasználható indexelési műveletet, a szüneteltetés helyett megszakítja azt.

Borravaló

A rendelkezésre álló erőforrásoktól és a számítási feladatok mintáitól függően a MAXDOP utasítás alapértelmezett értékénél magasabb érték megadása lerövidítheti az újraépítés időtartamát a magasabb processzorhasználat rovására.

  • sortárindexekesetén az újraépítés eltávolítja a töredezettséget az index összes szintjében, és tömöríti a lapokat a megadott vagy az aktuális kitöltési tényező alapján. Ha ALL van megadva, a rendszer a tábla összes indexét elveti, és egyetlen műveletben újraépül. A 128 vagy annál több kiterjedésű indexek esetén az adatbázismotor elhalasztja az oldallefoglalásokat és a kapcsolódó zárolások megszerzését az újraépítés befejezése utánra. Szintaxisbeli példákért lásd: Példák – Sortár újraépítése.

  • Oszlopcentrikus indexek esetén az újraépítés eltávolítja a töredezettségeket, áthelyezi a deltatároló sorait az oszloptárba, és fizikailag törli a törlésre megjelölt sorokat. Szintaxisbeli példákért lásd: Példák – Oszlopcentrikus újraépítés.

    Borravaló

    Az SQL Server 2016-tól kezdve (13.x) általában nincs szükség az oszlopcentrikus index újraépítésére, mivel REORGANIZE online műveletként elvégzi az újraépítés alapvető feladatait.

Használja az index újraépítését az adatsérülés helyreállításához.

Az SQL Server 2008 (10.0.x) előtt néha lehetett újraépíteni egy nem klaszterezett index sortárolót, hogy kijavítsa az index adatsérülése miatti inkonzisztenciákat.

Az ilyen inkonzisztenciákat a nem klaszteres indexben továbbra is kijavíthatja, ha offline újraépíti a nem klaszteres indexet. Az index online újraépítésével azonban nem lehet kijavítani a nemclustered index-inkonzisztenciákat, mert az online újraépítési mechanizmus a meglévő nemclustered indexet használja az újraépítés alapjául, és így továbbviszi az inkonzisztenciát. Az index offline újraépítése néha kényszeríthet a fürtözött index (vagy halom) vizsgálatára, és így a nemfürtözött index inkonzisztens adatait a fürtözött index vagy halom adataival helyettesítheti.

Annak érdekében, hogy a fürtözött index vagy halmaz legyen az adatok forrása, ahelyett hogy újraépítesz egy nem fürtözött indexet, inkább töröld és hozd létre újra. A korábbi verziókhoz hasonlóan az inkonzisztenciákból is helyreállíthatja az érintett adatokat egy biztonsági másolatból. Előfordulhat azonban, hogy offline újraépítéssel vagy újrakészítéssel meg tudja javítani a nem klaszteres indexek inkonzisztenciáit. További információ: DBCC CHECKDB (Transact-SQL).

Automatikus index- és statisztikakezelés

A adaptív indextöredezettség-mentesítési segítségével automatikusan kezelheti egy vagy több adatbázis indextöredezettségét és statisztikai frissítéseit. Ez az eljárás automatikusan kiválasztja, hogy újraépít vagy átrendez egy indexet a töredezettségi szintnek megfelelően, többek között más paraméterekkel, és lineáris küszöbértékkel frissíti a statisztikákat.

A soros indexek újraépítésével és átrendezésével kapcsolatos megfontolások

A következő forgatókönyvek miatt a tábla összes soralapú, nem klaszterezett indexe automatikusan újraépül:

  • Klaszterezett index létrehozása egy táblán, beleértve a klaszterezett index egy másik kulccsal történő újraépítését a CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • A fürtözött index elvetése, ami miatt a táblázat halmazként lesz tárolva

A következő forgatókönyvek nem építik újra automatikusan az összes sorkataszter nélküli indexet ugyanazon a táblán:

  • Klaszterezett index újraépítése
  • A fürtözött indextároló módosítása, például particionálási séma alkalmazása vagy a fürtözött index áthelyezése egy másik fájlcsoportba

Fontos

Egy indexet nem lehet átszervezni vagy újraépíteni, ha a fájlcsoport, amelyen található, offline vagy írásvédett. Ha az ALL kulcsszó meg van adva, és egy vagy több index offline vagy írásvédett fájlcsoporton található, az utasítás meghiúsul.

Az index újraépítése közben a fizikai adathordozónak elegendő helynek kell lennie az index két másolatának tárolásához. Ha az újraépítés befejeződött, az adatbázismotor törli az eredeti indexet.

Ha a ALL a ALTER INDEX ... REORGANIZE utasítással van megadva, a tábla fürtözött, nemclustered és XML-indexei újra lesznek rendezve.

A kis sortárindexek újraépítése vagy átrendezése általában nem csökkenti a töredezettség mértékét. Az SQL Server 2014 -ig (12.x) az SQL Server adatbázismotorja vegyes mértékben foglal le helyet. Ezért a kis indexek lapjait néha vegyes mértékben tárolják, ami implicit módon széttöredezetté teszi az ilyen indexeket. A vegyes kiterjedéseket legfeljebb nyolc objektum osztja meg, így előfordulhat, hogy egy kis index töredezettsége nem csökken az újraszervezés vagy újraépítés után.

Oszloparchitektúrás index újraépítésével kapcsolatos szempontok

Oszlopcentrikus index újraépítésekor az adatbázismotor beolvassa az összes adatot az eredeti oszlopcentrikus indexből, beleértve a deltatárolót is. Az adatokat új sorcsoportokba egyesíti, és az összes sorcsoportot oszloptárba tömöríti. Az adatbázismotor törli az oszloptárat úgy, hogy fizikailag törli a töröltként megjelölt sorokat.

Jegyzet

Az SQL Server 2019 (15.x) verziójától kezdődően a tömbmozgatót egy háttérben futó egyesítési feladat segíti, amely automatikusan tömöríti a belső küszöbérték által meghatározott, már egy ideje létező kisebb, nyitott deltatároló sorcsoportokat, vagy egyesíti a tömörített sorcsoportokat, amelyekben nagyszámú sor került törlésre. Ez idővel javítja az oszlopalapú index minőségét. Az oszloptár kifejezésekről és fogalmakról további információt talál Oszloptár indexek: Áttekintés.

Partíció újraépítése a teljes tábla helyett

A teljes tábla újraépítése hosszú időt vesz igénybe, ha az index nagy, és elegendő lemezterületre van szükség a teljes index másolatának tárolásához az újraépítés során.

Particionált táblák esetében nem kell újraépítenie a teljes oszlopcentrikus indexet, ha a töredezettség csak bizonyos partíciókban van jelen, például olyan partíciókban, ahol UPDATEa , DELETEvagy MERGE utasítások nagy számú sort módosítottak.

A partíciók adatok betöltése vagy módosítása utáni újraépítése biztosítja, hogy az összes adat tömörített sorcsoportokban legyen tárolva az oszloptárban. Amikor az adatbetöltési folyamat 102 400 sornál kisebb kötegekkel szúr be adatokat egy partícióba, a partíció több nyitott sorcsoporttal is végződhet a Delta Store-ban. Az újraépítés az oszloptár összes deltatároló sorát tömörített sorcsoportokba helyezi át.

Oszloptár index átrendezésével kapcsolatos szempontok

Oszlopstore index átrendezésekor az adatbázismotor a deltatároló összes zárt sorcsoportját oszlopstore-ba tömörített sorcsoportként tömöríti. Az SQL Server 2016 -tól kezdve (13.x) és az Azure SQL Database-ben a REORGANIZE parancs a következő további, online töredezettségmentesítési optimalizálást hajtja végre:

  • Fizikailag eltávolítja a sorokat egy sorcsoportból, ha 10% vagy több sor logikailag törlődik. Ha például egy 1 millió sorból álló tömörített sorcsoport 100 000 sort töröl, az adatbázismotor eltávolítja a törölt sorokat, és 900 000 sornyi sorra tömöríti újra a sorcsoportot, csökkentve a tárterület-terhelést.
  • Egy vagy több tömörített sorcsoport összefűzésével sorcsoportonként a sorok száma legfeljebb 1 048 576 sor lehet. Ha például 102 400 sorból álló köteget szúr be tömegesen, öt tömörített sorcsoportot kap. Ha a REORGANIZE parancsot futtatja, ezek a sorcsoportok egy 512 000 sort tartalmazó tömörített sorcsoportba egyesülnek. Ez azt feltételezi, hogy nem voltak szótárméret- vagy memóriakorlátozások.
  • Az adatbázismotor olyan sorcsoportokat próbál egyesíteni, amelyekben 10% vagy több sor törölve van más sorcsoportokkal. Az 1. sorcsoport például tömörítve van, és 500 000 sorból áll, míg a 21. sorcsoport tömörítése 1 048 576 sorból áll. A 21. sorcsoportból 60% sora töröltként van megjelölve, így 409 830 sor marad. Az adatbázismotor a két sorcsoport kombinálását részesíti előnyben egy 909 830 sorból álló új sorcsoport tömörítéséhez.

Az adatbetöltések elvégzése után több kis sorcsoport is lehet a delta tárolóban. A ALTER INDEX REORGANIZE használatával kényszerítheti ezeket a sorcsoportokat oszloptárba, majd egyesítheti a kisebb tömörített sorcsoportokat nagyobb tömörített sorcsoportokba. Az átrendezési művelet eltávolítja az oszloptárban töröltként megjelölt sorokat is.

Jegyzet

Oszlopalapú index átszervezése a Management Studio használatával egyesíti a tömörített sorcsoportokat, de nem kényszeríti az összes sorcsoport tömörítését az oszlopstore-ba. A zárt sorcsoportok tömörítettek, de a nyitott sorcsoportok nincsenek oszloparchívumba tömörítve. Az összes sorcsoport kényszerített tömörítéséhez használja a Transact-SQL példa, amely COMPRESS_ALL_ROW_GROUPS = ONtartalmaz.

Mit érdemes figyelembe venni az indexkarbantartás végrehajtása előtt?

Az indexkarbantartás erőforrás-igényes, amelyet egy index átrendezésével vagy újraépítésével hajtanak végre. Ez jelentősen megnöveli a processzorhasználatot, a felhasznált memóriát és a tárolási I/O-t. Az adatbázis számítási feladataitól és egyéb tényezőktől függően azonban az általa nyújtott előnyök a létfontosságútól a mínuszig terjednek.

A szükségtelen erőforrás-kihasználtság elkerülése érdekében kerülje az indexek válogatás nélküli karbantartását. Ehelyett az indexkarbantartás teljesítménybeli előnyeit empirikusan kell meghatározni minden számítási feladathoz az ajánlott stratégiahasználatával, és mérlegelni kell az ilyen előnyök eléréséhez szükséges erőforrásköltségeket és számítási feladatokat.

Az indexek átrendezésének vagy újraépítésének előnyei nagyobb valószínűséggel jelennek meg a teljesítmény szempontjából, ha az index erősen töredezett, vagy ha az oldalsűrűsége alacsony. Azonban nem csak ezeket érdemes figyelembe venni. Az olyan tényezők, mint a lekérdezési minták (tranzakciófeldolgozás és elemzés és jelentéskészítés), a tárolóalrendszer viselkedése, a rendelkezésre álló memória és az adatbázismotor fejlesztései mind szerepet játszanak.

Fontos

Az indexkarbantartási döntéseket azután kell meghozni, hogy az egyes számítási feladatok adott kontextusában több tényezőt is figyelembe kell venni, beleértve a karbantartás erőforrásköltségét is. Nem szabad, hogy csak rögzített töredezettségi vagy oldalsűrűségi küszöbértékeken alapuljanak.

Az index újraépítésének pozitív mellékhatása

Az ügyfelek az indexek újraépítése után gyakran tapasztalnak teljesítménybeli javulást. Ezek a fejlesztések azonban sok esetben nem kapcsolódnak a töredezettség csökkentéséhez vagy az oldalsűrűség növeléséhez.

Az index-újraépítésnek fontos előnye van: frissíti statisztikákat az index kulcsoszlopaihoz az index összes sorának vizsgálatával. Ez egyenértékű a UPDATE STATISTICS ... WITH FULLSCANvégrehajtásával, ami naprakészsé teszi a statisztikákat, és néha javítja a minőségüket az alapértelmezett mintavételezett statisztikai frissítéshez képest. A statisztikák frissítésekor a rájuk hivatkozó lekérdezéstervek újrafordításra kerülnek. Ha egy lekérdezés előző terve elavult statisztikák, nem megfelelő statisztikai mintavételezési arány vagy egyéb okok miatt nem volt optimális, az újrafordított terv gyakran jobban teljesít.

Az ügyfelek gyakran helytelenül tulajdonítják ezt a javulást az index újraépítésének, ami a kisebb töredezettség és a megnövekedett oldalsűrűség eredménye. A valóságban ugyanez az előny gyakran sokkal alacsonyabb erőforrásköltséggel érhető el, ha az indexek újraépítése helyett a statisztikákat frissíti .

Borravaló

A statisztikák frissítésének erőforrásköltsége kisebb az index újraépítéséhez képest, és a művelet gyakran percek alatt befejeződik. Az index újraépítése órákat is igénybe vehet.

Indexkarbantartási stratégia

A Microsoft azt javasolja, hogy az ügyfelek fontolja meg és fogadják el az alábbi indexkarbantartási stratégiát:

  • Ne feltételezze, hogy az indexkarbantartás mindig észrevehetően javítja a számítási feladatot.
  • Mérje meg az indexek átrendezésének vagy újraépítésének konkrét hatását a számítási feladat lekérdezési teljesítményére. A Lekérdezéstár jó módszer a "karbantartás előtti" és a "karbantartás utáni" teljesítmény mérésére az A/B tesztelési technikával.
  • Ha azt tapasztalja, hogy az indexek újraépítése javítja a teljesítményt, próbálja meg lecserélni a statisztikák frissítésére. Ez hasonló javulást eredményezhet. Ebben az esetben előfordulhat, hogy nem kell olyan gyakran vagy egyáltalán újraépítenie az indexeket, és ehelyett rendszeres statisztikai frissítéseket hajthat végre. Egyes statisztikák esetében előfordulhat, hogy növelnie kell a mintavételezési arányt a WITH SAMPLE ... PERCENT vagy WITH FULLSCAN záradékok használatával (ez nem gyakori).
  • Monitorozza az index töredezettségét és az oldalsűrűséget az idő függvényében, és ellenőrizze, hogy van-e korreláció a felfelé vagy lefelé mutató értékek és a lekérdezési teljesítmény között. Ha a nagyobb töredezettség vagy az alacsonyabb oldalsűrűség elfogadhatatlanul rontja a teljesítményt, átrendezheti vagy újraépítheti az indexeket. Gyakran elegendő csak a csökkentett teljesítményű lekérdezések által használt indexek átrendezése vagy újraépítése. Így elkerülhető, hogy az adatbázis minden indexe magasabb erőforrásköltséggel járjon.
  • A töredezettség/oldalsűrűség és a teljesítmény közötti korreláció megállapításával meghatározhatja az indexkarbantartás gyakoriságát is. ne feltételezze, hogy a karbantartást rögzített ütemezés szerint kell elvégezni. A jobb stratégia a töredezettség és az oldalsűrűség monitorozása, valamint az indexkarbantartás szükség szerinti futtatása, mielőtt a teljesítmény elfogadhatatlanul csökken.
  • Ha megállapította, hogy az indexkarbantartásra van szükség, és az erőforrás költsége elfogadható, akkor ha lehetséges, végezzen karbantartást az alacsony erőforrás-használati idő alatt.
  • Rendszeresen tesztelje, mivel az erőforrás-használati minták idővel változhatnak.

Indexkarbantartás az Azure SQL Database-ben és a felügyelt Azure SQL-példányban

A fenti szempontok és stratégia mellett az Azure SQL Database-ben és a felügyelt Azure SQL-példányban különösen fontos figyelembe venni az indexkarbantartás költségeit és előnyeit. Az ügyfelek csak akkor hajtják végre, ha bizonyított igény van rá, és figyelembe veszik az alábbi szempontokat.

  • Az Azure SQL Database és az Azure SQL Managed Instance erőforrás-szabályozási implementál, hogy a kiosztott tarifacsomagnak megfelelően állítsa be a cpu-, a memória- és az I/O-felhasználásra vonatkozó korlátokat. Ezek a korlátok az összes felhasználói számítási feladatra vonatkoznak, beleértve az indexkarbantartást is. Ha az összes számítási feladat összesített erőforrás-felhasználása megközelíti az erőforrás-korlátokat, az újraépítési vagy átrendezési művelet csökkentheti a többi számítási feladat teljesítményét az erőforrás-versengés miatt. A tömeges adatbetöltések például lassabbak lehetnek, mert a tranzakciónapló I/O-száma 100% az index egyidejű újraépítése miatt. A felügyelt Azure SQL-példányban ez a hatás csökkenthető, ha indexkarbantartást futtat egy külön erőforrás-vezérlő számítási feladatcsoportban, korlátozott erőforrás-kiosztással, az indexkarbantartás időtartamának meghosszabbítása rovására.
  • A költségmegtakarítás érdekében az ügyfelek gyakran minimális erőforrás-kezelőtérrel építenek ki adatbázisokat, rugalmas készleteket és felügyelt példányokat. A tarifacsomag úgy van kiválasztva, hogy elegendő legyen az alkalmazás számítási feladataihoz. Ahhoz, hogy az indexkarbantartás miatt jelentősen növekedjen az erőforrás-használat az alkalmazás teljesítményének csökkentése nélkül, előfordulhat, hogy az ügyfeleknek több erőforrást kell kiépíteniük, és növelniük kell a költségeket anélkül, hogy szükségszerűen javítanák az alkalmazás teljesítményét.
  • Rugalmas készletekben az erőforrások a készlet összes adatbázisában meg vannak osztva. Még ha egy adott adatbázis tétlen is, az adott adatbázis indexkarbantartása hatással lehet az ugyanazon készletben lévő más adatbázisokban egyidejűleg futó alkalmazásterhelésekre. További információ: Sűrű rugalmas készletek erőforrás-kezelése.
  • Az Azure SQL Database-ben és az Azure SQL Managed Instance-ben használt legtöbb tárolási típus esetében nincs különbség a szekvenciális I/O és a véletlenszerű I/O között. Ez csökkenti az index töredezettségének a lekérdezési teljesítményre gyakorolt hatását.
  • Olvasási méretezés horizontálisan vagy georeplikálás használatakor gyakran megnő a replikák adatkésése, miközben az index karbantartást az elsődleges replikán végzik. Ha egy georeplika nincs elegendő erőforrással kiépítve az indexkarbantartás által okozott tranzakciónapló-létrehozás növekedésének fenntartásához, az messze elmaradhat az elsődleges példánytól, ami miatt a rendszer újra eltárolhatja azt. Így a replika mindaddig elérhetetlenné válik, amíg az újratelepítés be nem fejeződik. Emellett a prémium és az üzleti szempontból kritikus szolgáltatási szinteken a magas rendelkezésre álláshoz használt replikák hasonlóképpen messze elmaradhatnak az indexkarbantartás során az elsődleges szint mögött. Ha az index karbantartása közben vagy nem sokkal utána szükség van a feladatátvételre, az a vártnál tovább tarthat.
  • Ha az index újraépítése az elsődleges replikán fut, és egy hosszú ideig futó lekérdezés egy olvasható replikán fut egyszerre, a lekérdezés automatikusan leállhat, hogy megakadályozza az újrakezdés szálának blokkolását a replikán.

Vannak bizonyos, de nem gyakori esetek, amikor egyszeri vagy időszakos indexkarbantartásra lehet szükség az Azure SQL Database-ben és a felügyelt Azure SQL-példányban:

Borravaló

Ha úgy ítéli meg, hogy az indexkarbantartás szükséges az Azure SQL Database és az Azure SQL Managed Instance számítási feladataihoz, akkor vagy át kell szerveznie az indexeket, vagy online index-újraépítést kell használnia. Ez lehetővé teszi, hogy a lekérdezési számítási feladatok hozzáférjenek a táblákhoz az indexek újraépítése közben.

Emellett a művelet folytathatóvá tételével elkerülheti az újraindítást a kezdetektől, ha egy tervezett vagy nem tervezett adatbázis-átállás megszakítja. Az ismételhető indexműveletek használata különösen fontos, ha az indexek nagyok.

Borravaló

Az offline indexműveletek általában gyorsabban fejeződnek be, mint az online műveletek. Ezeket akkor érdemes használni, ha a lekérdezések nem férnek hozzá a táblákhoz a művelet során, például miután egy szekvenciális ETL-folyamat részeként adatokat tölt be az átmeneti táblákba.

Korlátozások és korlátozások

A több mint 128 kiterjedésű sortárindexek két külön fázisban vannak újra felépítve: logikai és fizikai. A logikai fázisban az index által használt meglévő foglalási egységek ki vannak jelölve a felszabadításhoz, az adatsorokat átmásolja és rendezi, majd áthelyezi az újraépített index tárolására létrehozott új foglalási egységekbe. A fizikai fázisban a korábban felszabadításhoz megjelölt foglalási egységek fizikailag eltávolítva lesznek rövid tranzakciók során, amelyek a háttérben zajlanak, és nem igényelnek sok zárolást. A foglalási egységekről további információt a Lapok és kiterjesztések architektúraútmutatójaalatt olvashat.

A ALTER INDEX REORGANIZE utasításhoz az indexet tartalmazó adatfájlnak szabad helyre van szüksége, mivel a művelet csak az ideiglenes munkalapokat tudja lefoglalni ugyanabban a fájlban, egy másik fájlban nem ugyanabban a fájlcsoportban. Annak ellenére, hogy a fájlcsoport szabad területtel rendelkezik, a felhasználó továbbra is 1105-ös hibát tapasztalhat: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup az átszervezési művelet során, ha egy adatfájl nincs szabad területen.

Az index nem szervezhető át, ha ALLOW_PAGE_LOCKS ki van kapcsolva.

Az SQL Server 2017-ig (14.x) a fürtözött oszloptár index újraépítése offline művelet. Az adatbázismotornak kizárólagos zárolást kell szereznia a táblán vagy partíción az újraépítés során. Az adatok offline állapotban állnak, és nem érhetők el az újraépítés során, még akkor is, ha NOLOCK, írásvédett pillanatkép-elkülönítést (RCSI) vagy pillanatkép-elkülönítést használnak. Az SQL Server 2019-től kezdve (15.x) a fürtözött oszlopcentrikus indexek újraépíthetők a ONLINE = ON lehetőséggel.

Figyelmeztetés

Az 1000-nél több partíciót tartalmazó táblákon nem igazított indexek létrehozása és újraépítése lehetséges, de nem támogatott. Ez teljesítménycsökkenést vagy túlzott memóriahasználatot okozhat ezekben a műveletekben. A Microsoft csak igazított indexek használatát javasolja, ha a partíciók száma meghaladja az 1000-et.

Statisztikai korlátozások

  • Ha egy indexet létrehoznak vagy újraépítenek, a statisztikák a tábla összes sorának vizsgálatával jönnek létre vagy frissülnek, ami egyenértékű a(z) FULLSCAN záradék használatával a(z) CREATE STATISTICS vagy UPDATE STATISTICS. Az SQL Server 2012 -től (11.x) kezdődően azonban a particionált indexek létrehozásakor vagy újraépítésekor a rendszer nem hoz létre vagy frissít statisztikákat a tábla összes sorának vizsgálatával. Ehelyett a rendszer az alapértelmezett mintavételezési arányt használja. A particionált indexek statisztikáinak a tábla összes sorának vizsgálatával történő létrehozásához vagy frissítéséhez használja CREATE STATISTICS vagy UPDATE STATISTICS a FULLSCAN záradékkal.
  • Hasonlóképpen, ha az indexlétrehozási vagy újraépítési művelet újrakezdhető, a statisztikák az alapértelmezett mintavételezési aránysal jönnek létre vagy frissülnek. Ha statisztikákat hoztak létre vagy frissítettek utoljára a PERSIST_SAMPLE_PERCENT záradék ONértékre van állítva, akkor a továbbra is elérhető indexelési műveletek a továbbra is megmaradó mintavételezési arányt használják a statisztikák létrehozásához vagy frissítéséhez.
  • Az indexek átrendezésekor a statisztikák nem frissülnek.

Példák

Sor alapú index töredezettségének és oldalsűrűségének ellenőrzése

Az alábbi példa az aktuális adatbázis összes sortárindexének átlagos töredezettségét és lapsűrűségét határozza meg. A SAMPLED módot használja a végrehajtható eredmények gyors visszaadásához. A pontosabb eredmények érdekében használja a DETAILED módot. Ehhez az összes indexoldal vizsgálata szükséges, és hosszú időt vehet igénybe.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Az előző utasítás az alábbihoz hasonló eredményt ad vissza:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

További információkért tekintse meg sys.dm_db_index_physical_stats.

Oszlopalapú index töredezettségének ellenőrzése

Az alábbi példa az aktuális adatbázisban tömörített sorcsoportokkal rendelkező összes oszlopcentrikus index átlagos töredezettségét határozza meg.

WITH columnstore_row_group_partition
AS (SELECT object_id,
           index_id,
           partition_number,
           SUM(deleted_rows) AS partition_deleted_rows,
           SUM(total_rows) AS partition_total_rows
    FROM sys.dm_db_column_store_row_group_physical_stats
    WHERE state_desc = 'COMPRESSED'
    GROUP BY object_id, index_id, partition_number),
/* For nonclustered columnstore, include rows in the delete buffer */
 columnstore_internal_partition
AS (SELECT object_id,
           index_id,
           partition_number,
           SUM(rows) AS delete_buffer_rows
    FROM sys.internal_partitions
    WHERE internal_object_type_desc = 'COLUMN_STORE_DELETE_BUFFER'
    GROUP BY object_id, index_id, partition_number)
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       crgp.partition_number,
       100.0 * (ISNULL(crgp.partition_deleted_rows + ISNULL(cip.delete_buffer_rows, 0), 0)) / NULLIF (crgp.partition_total_rows, 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
     INNER JOIN columnstore_row_group_partition AS crgp
         ON i.object_id = crgp.object_id
        AND i.index_id = crgp.index_id
     LEFT OUTER JOIN columnstore_internal_partition AS cip
         ON i.object_id = cip.object_id
        AND i.index_id = cip.index_id
        AND crgp.partition_number = cip.partition_number
ORDER BY schema_name, object_name, index_name, partition_number, index_type;

Az előző utasítás az alábbi kimenethez hasonló eredményhalmazt ad vissza:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Indexek karbantartása az SQL Server Management Studióval

Index átrendezése vagy újraépítése

  1. Az Object Explorerterületen bontsa ki azt az adatbázist, amely azt a táblát tartalmazza, amelyre át szeretné szervezni az indexet.
  2. Nyissa meg a Táblák mappát.
  3. Bontsa ki azt a táblát, amelyre át szeretné szervezni az indexet.
  4. Bontsa ki a Indexek mappát.
  5. Kattintson a jobb gombbal az átrendezni kívánt indexre, és válassza Átrendezéslehetőséget.
  6. Az Indexek átrendezése párbeszédpanelen ellenőrizze, hogy a megfelelő index szerepel-e az Indexek között, amelyet át szeretne szervezni rácson, és válassza OKlehetőséget.
  7. Jelölje be a Nagyméretű objektumoszlop adatainak tömörítése jelölőnégyzetet, és adja meg, hogy a nagyméretű objektumadatokat tartalmazó lapok is tömörítve legyenek.
  8. Válassza OK.

Táblázat összes indexének átrendezése

  1. Az Object Explorerterületen bontsa ki azt az adatbázist, amely azt a táblát tartalmazza, amelyre át szeretné szervezni az indexeket.
  2. Nyissa meg a Táblák mappát.
  3. Bontsa ki azt a táblát, amelyre át szeretné szervezni az indexeket.
  4. Kattintson a jobb gombbal az Indexek mappára, és válassza az Összes átrendezéseparancsot.
  5. Az Indexek átrendezése párbeszédpanelen ellenőrizze, hogy a megfelelő indexek szerepelnek-e a átrendezendő indexekben. Ha el szeretne távolítani egy indexet a Újrendezendő indexek rácsról, jelölje ki az indexet, majd nyomja le a Delete billentyűt.
  6. Jelölje be a Nagyméretű objektumoszlop adatainak tömörítése jelölőnégyzetet, és adja meg, hogy a nagyméretű objektumadatokat tartalmazó lapok is tömörítve legyenek.
  7. Válassza OK.

Indexek karbantartása Transact-SQL

Jegyzet

További példák az indexek újraépítésére vagy átrendezésére szolgáló Transact-SQL használatáról: ALTER INDEX példák – Sortárindexek és ALTER INDEX példák – Oszloptár indexek.

Index átrendezése

Az alábbi példa a IX_Employee_OrganizationalLevel_OrganizationalNode adatbázis HumanResources.Employee táblájának AdventureWorks2022 indexét rendezi át.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

Az alábbi példa a IndFactResellerSalesXL_CCI adatbázis dbo.FactResellerSalesXL_CCI táblájának AdventureWorksDW2022 oszlopcentrikus indexét rendezi át. Ez a parancs az összes zárt és nyitott sorcsoportot oszloptárba helyezi.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Táblázat összes indexének átrendezése

Az alábbi példa az HumanResources.Employee adatbázis AdventureWorks2022 táblájának összes indexét átrendezi.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Index újraépítése

Az alábbi példa egyetlen indexet épít újra a Employee adatbázis AdventureWorks2022 tábláján.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Egy tábla összes indexének újraépítése

Az alábbi példa újraépíti az AdventureWorks2022 adatbázis táblájával társított összes indexet a ALL kulcsszó használatával. Három lehetőség van megadva.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

További információ: ALTER INDEX.

Kapcsolódó tartalom