Free Press

Az SQL-indexek adatbázis-táblákon történő létrehozásának célja, hogy az adatlekérdezési folyamatok felgyorsításával növelje az alaptáblából adatokat olvasó lekérdezések teljesítményét. Ezek a tranzakciós táblák azonban nem statikusak, és idővel nagyon gyakran változnak. Ezek az adatbázis-táblán végrehajtott változások magukban foglalják új rekordok hozzáadását, a meglévő rekordok módosítását vagy törlését. Mivel ezeket a változásokat a kapcsolódó táblaindexekre is replikálni kell, a táblaindex idővel töredezetté válik.

SQL Server Index Fragmentation Overview

Az SQL index töredezettségének két fő típusa van; a belső és a külső töredezettség. A belső töredezettséget egy új rekord beszúrása vagy egy meglévő rekord frissítése okozza olyan értékekkel, amelyek nem férnek bele az adatlap aktuális szabad helyére, és az oldal két oldalra osztását eredményezi, hogy az új érték elférjen. Ebben az esetben az SQL Server Engine az adatok körülbelül 50%-át áthelyezi az aktuális oldalról a felosztási műveletből keletkező új adatoldalra, hogy az SQL indexfa kiegyensúlyozott maradjon. Belső töredezettséget okozhatnak a véletlenszerű törlési műveletek is, amelyek üres helyet eredményeznek az adatoldalakon. Az ilyen típusú műveletek az adatlapokat kitöltetlenül hagyják, és nagyobb SQL Server-indexméretet és lassabb teljesítményt eredményeznek, mivel több adatlapot kell beolvasni a kért adatok lekérdezéséhez.

A külső töredezettséget az okozza, amikor az SQL Server-index adatlapjai szétszóródnak az adatbázis fizikai fájljában, mivel az SQL Server-index logikai sorrendje nem felel meg a fizikai sorrendnek a mögöttes lemezmeghajtón. A külső töredezettséget olyan véletlenszerű törlési műveletek okozhatják, amelyek a kiterjedési oldalak egy részét üresen hagyják, miközben maga a kiterjedés foglalt. Az ilyen típusú töredezettség teljesítményromlást eredményez, mivel több időt vesz igénybe és több erőforrást fogyaszt a szétszórt adatoldalak közötti ugrálás a kért adatoknak a mögöttes lemezmeghajtóról való lekérdezése érdekében.

Azért foglalkozunk sokat az SQL-index töredezettségével, mert a rendszer teljesítményének növelése érdekében létrehozott index a nagymértékű töredezettség után ronthatja a lekérdezések teljesítményét. Képzeljük el az idő és a lemezolvasások száma közötti különbséget aközött, hogy egy adott számú sort egy egészséges SQL-indexből olvasunk, amelyben az adatok az adatbázisfájlon belül összefüggő adatoldalakban vannak tárolva, és ugyanennyi sort egy erősen töredezett SQL Server-indexből olvasunk, amelyben az adatok az adatbázisfájlon belül nem összefüggő adatoldalakban vannak szétszórva.

Ha azt találjuk, hogy a tábla SQL-indexe töredezett, akkor az SQL-index töredezettségi százalékának alapján az SQL Server-index újrarendezésével vagy az index újraépítésével kell megoldani a töredezettségi problémát. Például egy 30%-nál kisebb töredezettségi százalékkal rendelkező SQL Server-index átszervezhető, míg a 30%-nál nagyobb töredezettségi százalékkal rendelkező SQL-indexet újra kell építeni.

• Megjegyzés: További információkért tekintse meg az SQL Server-indexek karbantartása című részt.

Az SQL Server index töredezettségével és az oldalak felosztásával kapcsolatos problémákat proaktívan kiküszöbölheti az index megfelelő megtervezésével, valamint a Fill Factor és a pad_index index létrehozási beállítások propervalues beállításával.

• Megjegyzés: További információkért tekintse meg az SQL Server Index műveletek című részt.

SQL Server Index Fragmentation Report

Nem tekinthető a legjobb gyakorlatnak, ha csak azért hoz létre egy SQL-indexet az adatbázis táblájában, hogy kihasználja az adatlekérdezési művelet javításának előnyeit, és örökre meghagyja ezt az SQL Server-indexet anélkül, hogy folyamatosan figyelemmel kísérné a töredezettségét és karbantartaná azt.

Az SQL Server több módszert is biztosít számunkra, amelyekkel ellenőrizhetjük az SQL index töredezettségi százalékát.

SQL Server Index Properties

Az első módszer az SQL Server Index Properties ablak Fragmentation lapja. Kattintson a jobb gombbal az ellenőrizni kívánt indexre, és válassza a Tulajdonságok lehetőséget. Az SQL Server Index Properties (SQL Server index tulajdonságai) oldalról a Fragmentation (Töredezettség) oldalra lépve a Fragmentation (Töredezettség) rész alatt láthatja az index töredezettségi százalékát és azt, hogy mennyire vannak tele az SQL Server index lapjai, ahogy az alábbiakban látható:

Néhány lépésben könnyen ellenőrizheti a kiválasztott index SQL Server index töredezettségi százalékát az index tulajdonságai ablakból. De mi van akkor, ha az összes tábla indexének vagy az összes adatbázis-tábla indexének töredezettségi százalékáról szeretne áttekintést kapni? Ebben az esetben egyesével kell mennie és ellenőriznie. Ennek eredményeként egyetlen munkahét nem lesz elegendő egyetlen adatbázis-jelentés elkészítéséhez!

sys.dm_dm_db_index_physical_stats

Egy másik módszer, amellyel ellenőrizhetjük az adatbázis-indexek töredezettségi százalékát, a sys.dm_db_index_physical_stats dinamikus kezelési funkció lekérdezése, amelyet az SQL Server 2005-ben vezettek be először, a már elavult DBCC SHOWCONTIG parancs helyett. Az adatbázis indexeinek méretéről és töredezettségi százalékáról nyújt információt.

Ahhoz, hogy a sys.dm_dm_db_index_physical_stats DMF-ből értelmes információt kapjunk a megadott adatbázis alatti összes index töredezettségi százalékáról, a sys.indexes DMV-vel, mint az alábbi T-SQL szkriptben:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

SQL-fejlesztési ismeretei alapján elvégezheti azt a szűrést, amelyre szüksége van az SQL-indexek töredezettségének lekérdezéséhez a kívánt hatókörben, és nincs lehetőség az összes példányadatbázisra vonatkozó összesítés lekérdezésére, figyelembe véve, hogy a megjelenített adatok az SQL Server szolgáltatás utolsó újraindítása óta vannak. Az eredmény a mi esetünkben a következő lesz:

SQL Server Index Physical Statistics Standard Report

Az SQL Server számos szabványos jelentést biztosít számunkra, amelyek hasznos statisztikai információkat tartalmaznak a kiválasztott adatbázisról. Az Index fizikai statisztikák jelentés, az SQL Server egyik szabványos jelentése, amely statisztikai információkat szolgáltat az SQL Server indexpartícióiról, a töredezettségi százalékról és az egyes SQL indexpartíciókon lévő oldalak számáról adatbázis-szinten, az SQL Server szolgáltatás utolsó újraindítása óta, ajánlást adva arra vonatkozóan, hogy az SQL Server index töredezettségi százaléka alapján hogyan javítsuk az index töredezettségi problémáját, ha van ilyen.

Az SQL Server index fizikai statisztikák jelentés megtekintéséhez kattintson az adatbázisra a jobb gombbal, válassza a Jelentések -> Standard jelentések menüpontot, majd válassza az Index fizikai statisztikák jelentést, az alábbiak szerint:

És a generált adatbázis szintű jelentés, amely nem szűrhető és az egyes táblák indexeinek eredményét kézzel kell kibontani, az alábbiak szerint alakul:

ApexSQL Defrag

Az ApexSQL Defrag eszköz egy 3rd party SQL index defragmentáló eszköz, amely könnyen használható az SQL Server index töredezettségének és felhasználási információinak ellenőrzésére és az index töredezettségi probléma megfelelő javításának elvégzésére vagy ütemezésére.

Egy központi helyről elemezheti és áttekintheti a töredezettségi és használati információkat különböző szinteken, beleértve az SQL index töredezettségi és használati információkat az SQL Server index szintjén, a táblaszinten, az adatbázis szintjén és végül a teljes SQL Server példány szintjén. Változatos típusú szűrőket biztosít számunkra, amelyek segítségével csak a célindexeket jeleníthetjük meg és tarthatjuk fenn.

Az ApexSQL Defrag lehetővé teszi számunkra a kitöltési tényező beállításának konfigurálását is a táblaindexekhez, riasztások létrehozását az összes defragmentálási feladathoz és irányelvhez, valamint e-mail értesítést küldhetünk a sikeres vagy sikertelen feladatokról.

A megjelenített információk jó áttekintést nyújtanak az SQL index méretéről és a töredezettség százalékáról, amelyet nem befolyásol semmilyen SQL Server szolgáltatás újraindítása vagy a szerver újraindítása. Ez azért van így, mert ezek az információk az ApexSQL Defrag eszköz központi tároló adatbázisában lesznek tárolva.

ApexSQL Defrag telepítése

Az ApexSQL Defrag egyszerűen letölthető az ApexSQL Download Centerből és telepíthető a szerverre az alábbiakban bemutatott, egyszerű telepítési varázslót követve:

Először meg kell adnia azt a szolgáltatásfiókot, amelyet az ApexSQL Defrag ügynök végrehajtási környezeteként fog használni és parancsokat közvetít az SQL Server példánynak, az alábbiak szerint:

Az ApexSQL Defrag szolgáltatási fiók megadása után meg kell adnia, hogy hova telepítse az ApexSQL Defrag eszközt, és hogy hozzon létre egy parancsikont az eszköz számára az asztalon, az alábbiak szerint:

Ha ellenőrzi, hogy a megadott elérési út legalább 127 MB szabad helyet tartalmaz, az ApexSQL Defrag sikeresen települ a szerverre. És a következő értesítés jelenik meg, amely lehetőséget biztosít az ApexSQL Defrag közvetlen indítására:

Ha az ApexSQL Defragot először futtatja, a következő módon kéri a központi tároló adatbázis létrehozásának megerősítését, ahol ezek az adattörténeti és konfigurációs adatok tárolódnak:

Új kiszolgáló hozzáadása

A tároló adatbázis létrehozása után az ApexSQL Defrag SQL-példány nélkül indul. Az SQL index töredezettségi százalékának ellenőrzéséhez egy adott SQL Server példányon, hozzá kell adni az adott példányt az eszközhöz. Ehhez kattintson a Kezdőlap fül alatt található Hozzáadás gombra, az alábbiakban látható módon:

A Csatlakozás az SQL Serverhez ablakban meg kell adnia a használni kívánt SQL Server példány nevét és a szerverhez való csatlakozáshoz szükséges hitelesítő adatokat az alábbiak szerint:

SQL Server Index Analysis

A kiszolgáló hozzáadásakor az alábbi Fragmentáció lapon végezhet új ellenőrzést az összes SQL index töredezettségi információjára vonatkozóan:

Az ApexSQL Defrag lehetőséget biztosít az adott példány összes indexének elemzésére három mód alapján:

• DETAILED: amelyben az összes adat- és SQL-indexoldalt beolvassa a keresési folyamat során
• SAMPLED: amelyben az oldalaknak csak 1%-át olvassa be, figyelembe véve, hogy az SQL Server indexe több mint 10 000 oldalt tartalmaz
• LIMITED: amelyben csak a b-fa szülői szintjén található oldalak kerülnek beolvasásra

A csatlakoztatott SQL Server-példány SQL Server-indexinformációinak elemzéséhez kattintson a Töredezettség lapon az Elemzés gombra, és válassza a Gyors vagy Mély elemzés elvégzését. Mély elemzést fogunk végezni, hogy teljes körű információkat kapjunk, az alábbiak szerint:

Az eszköz alsó részén lévő előrehaladási sávot folyamatosan figyeljük az elemzés előrehaladásának ellenőrzéséhez, az alábbiak szerint:

És egy átfogó jelentés, amely teljes körű információkat tartalmaz az adott SQL Server-példány minden egyes adatbázisában létrehozott összes SQL indexről, az alábbiak szerint fog megjelenni:

Azzal a képességgel, hogy ugyanazon központi ablak alatt egyetlen kattintással elmerülhet a különböző szintek között; SQL Server-példány, adatbázis, tábla vagy index, és szűrheti a lekérdezett eredményt egy adott töredezettségi szint vagy küszöbérték alapján, ahogy az alább látható:

Ezenkívül a jelentés oszlopai közül bármelyik oszlopot áthúzva csoportosíthatjuk a teljes jelentést az adott oszlop értékei alapján, mint például az alábbi példában, amelyben a jelentés adatait a táblázat neve alapján csoportosítottuk:

ApexSQL Defrag Reports

Az ApexSQL Defrag számos jelentést biztosít számunkra, amelyek statisztikai információkat mutatnak az adatbázis indexeiről. Ezeket a jelentéseket az alábbi Jelentések fül alatt lehet beállítani és ellenőrizni:

Az első jelentés a Teljes jelentés, amely grafikusan mutatja az SQL indexek töredezettségét és a lemezterület-használatot a kiválasztott szinten. Ha például a példány nevére kattint, a jelentés kirajzolja a töredezettségi százalékos grafikont és a helyhasználati grafikont az SQL Server példány szintjén, az alábbiak szerint:

Ha a Server Explorer ablakban bármelyik adatbázisra kattint, a jelentés automatikusan megváltoztatja a nézetet, és az adatbázis szintjén mutatja a grafikont, az alábbiak szerint:

A második jelentés, amely szintén különböző szinteken tekinthető, a Statisztika jelentés. Ez a jelentés statisztikai információkat mutat az indexek teljes méretéről és töredezettségéről, valamint az index típusok eloszlásáról a kiválasztott szinten, az alábbiak szerint:

A harmadik jelentés a Top 10 jelentés, amely a 10 legnagyobb méretű és legnagyobb százalékos SQL index töredezettségű fürtözött és nem fürtözött indexet tekinti meg a megadott szinten. A következő jelentés például a méret és töredezettség alapján a 10 legdrágább indexet mutatja az SQL-kiszolgáló példány szintjén:

Egy adott adatbázisra lefelé haladva a jelentés automatikusan megjeleníti a méret és töredezettség alapján a 10 legdrágább indexet a kiválasztott adatbázis szintjén, ahogy az alábbiakban látható:

Mélyebbre merülve egy adott táblára az adott adatbázis alatt, a jelentés a méret és a töredezettség alapján automatikusan megjeleníti a 10 legdrágább indexet, az alábbiak szerint:

ApexSQL Defrag jelentés exportálása

Az ApexSQL Defrag lehetővé teszi számunkra, hogy a töredezettségi jelentéseket különböző fájlformátumokba exportáljuk, beleértve a PDF, IMG, HTML, CS és XML formátumokat, azzal a lehetőséggel, hogy a jelentés beállításait a generálás előtt testre szabhatjuk. A Jelentések lapon kattintsunk az Opciók gombra:

Az Exportálási beállítások szerkesztése ablakban beállíthatjuk a különböző beállításokat, kezdve a generált fájl elnevezési konvenciójától, a fájl mentési útvonalát, az oldal tájolását és végül az egyes jelentések tartalmát, ahogy az alábbiakban látható:

Például, ha a generált Összes jelentés meghatározott szintű exportálásához kattintson az Exportálás gombra a Jelentések lapon, és válassza ki azt a fájltípust, amelybe ez az Összes jelentés exportálásra kerül. Néhány másodperc múlva a jelentés elkészül, és megkérdezi, hogy meg kívánja-e nyitni a generált jelentést. Kattintson az Igen gombra a jelentés közvetlen megnyitásához, ahogy az alábbiakban látható:

Ezenkívül exportálhatja a Top 10 jelentést a megadott szinten, ha a Jelentések lapon az Exportálás gombra kattint, és megadja az exportált fájl típusát, és a jelentés gyorsan generálódik, megkérdezve, hogy közvetlenül meg kívánja-e tekinteni a generált jelentést. Kattintson az Igen gombra, hogy megnyissa a generált, az alábbiak szerint:

Az előző példákból világosan látszik, hogy az ApexSQL Defrag eszközzel könnyen áttekinthetjük és elemezhetjük az SQL index töredezettségi százalékos információit és merülhetünk a különböző szinteken az SQL Server példány szintjétől kezdve az SQL Server index szintjéig, és különböző exportálható formátumokban megjeleníthetjük, anélkül, hogy a megjelenített információkat bármilyen szerver vagy szolgáltatás újraindítása befolyásolná, mivel azok egy tároló adatbázisban lesznek tárolva.