sys.dm_db_index_physical_stats (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példánySQL-adatbázis a Microsoft Fabricben

A megadott tábla vagy nézet adatainak és indexeinek méretét és töredezettségét adja vissza az SQL Server adatbázismotorjában. Egy index esetében a rendszer egy sort ad vissza az egyes partíciók B-fa szintjeihez. Halom esetén a rendszer egy sort ad vissza az egyes partíciók IN_ROW_DATA foglalási egységéhez. Nagyméretű objektumadatok (LOB) esetén a rendszer egy sort ad vissza az egyes partíciók LOB_DATA foglalási egységéhez. Ha sorátfolyási adatok találhatók a táblában, a rendszer minden partíció ROW_OVERFLOW_DATA foglalási egységéhez egy sort ad vissza.

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.

sys.dm_db_index_physical_stats nem ad vissza információt a memóriaoptimalizált indexekről. A memóriaoptimalizált indexhasználatról további információt sys.dm_db_xtp_index_stats.

Ha olyan kiszolgálópéldányon kérdez le sys.dm_db_index_physical_stats, amely egy rendelkezésre állási csoportot üzemeltet, olvasható másodlagos replika, REDO blokkolási problémát tapasztalhat. Ennek az az oka, hogy ez a dinamikus felügyeleti nézet egy Intent-Shared (IS) zárolást szerez be a megadott felhasználói táblára vagy nézetre, amely letilthatja az adott felhasználói tábla vagy nézet kizárólagos (X) zárolásának REDO szál általi kéréseit.

Transact-SQL szintaxis konvenciói

Szintaxis

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Érvek

database_id | NULL | 0 | ALAPÉRTELMEZETT

Az adatbázis azonosítója. database_id . Az érvényes bemenetek egy adatbázis, NULL, 0vagy DEFAULTazonosítója. Az alapértelmezett érték a 0. NULL, 0és DEFAULT egyenértékű értékek ebben a kontextusban.

Adja meg a NULL az SQL Server-példányban lévő összes adatbázis adatainak visszaadásához. Ha NULL ad meg, NULL, index_idés partition_number is meg kell adnia.

Megadhatja a beépített DB_ID függvényt. Ha DB_ID használ adatbázisnév megadása nélkül, az aktuális adatbázis kompatibilitási szintjének 90 vagy annál nagyobbnak kell lennie.

object_id | NULL | 0 | ALAPÉRTELMEZETT

A tábla objektumazonosítója vagy az index megtekintése be van kapcsolva. object_idint. Az érvényes bemenetek egy tábla és nézet, NULL, 0vagy DEFAULTazonosítója. Az alapértelmezett érték a 0. NULL, 0és DEFAULT egyenértékű értékek ebben a kontextusban.

Az SQL Server 2016 (13.x) és újabb verzióiban az érvényes bemenetek közé tartozik a szolgáltatásközvetítő üzenetsor neve vagy az üzenetsor belső táblaneve is. Ha az alapértelmezett paramétereket alkalmazza (vagyis az összes objektumot, az összes indexet stb.), az összes üzenetsor töredezettségadatai bekerülnek az eredményhalmazba.

Adja meg a NULL a megadott adatbázis összes táblájának és nézetének információinak visszaadásához. Ha NULL ad meg, NULL és partition_number is meg kell adnia.

index_id | 0 | NULL | -1 | ALAPÉRTELMEZETT

Az index azonosítója. index_idint. Az érvényes bemenetek az index azonosítója, 0, ha object_id halom, NULL, -1vagy DEFAULT. Az alapértelmezett érték a -1. NULL, -1és DEFAULT egyenértékű értékek ebben a kontextusban.

Adja meg a NULL egy alaptábla vagy nézet összes indexének adatait. Ha NULL ad meg, NULL is meg kell adnia partition_number.

partition_number | NULL | 0 | ALAPÉRTELMEZETT

Az objektum partíciószáma. partition_numberint. Az érvényes bemenetek az index vagy halom, , NULLvagy 0DEFAULT. Az alapértelmezett érték a 0. NULL, 0és DEFAULT egyenértékű értékek ebben a kontextusban.

Adja meg a NULL a tulajdonos objektum összes partíciójára vonatkozó információkat.

partition_number 1-alapú. A nem particionált index vagy halom partition_number1értékre van állítva.

mód | NULL | ALAPÉRTELMEZETT

A mód neve. mód a statisztikák lekéréséhez használt vizsgálati szintet adja meg. módsysname. Az érvényes bemenetek a következők: DEFAULT, NULL, LIMITED, SAMPLEDvagy DETAILED. Az alapértelmezett (NULL) a LIMITED.

Visszaadott tábla

Oszlop neve	Adattípus	Leírás
database_id	kis	A tábla vagy nézet adatbázis-azonosítója. Az Azure SQL Database-ben az értékek egyediek egyetlen adatbázisban vagy rugalmas készletben, de nem logikai kiszolgálón belül.
object_id		Annak a táblának vagy nézetnek az objektumazonosítója, amelyen az index be van kapcsolva.
index_id		Index indexazonosítója. 0 = Halom.
partition_number		1-alapú partíciószám a tulajdonos objektumon belül; táblázatot, nézetet vagy indexet. 1 = Nem particionált index vagy halom.
index_type_desc	nvarchar(60)	Az indextípus leírása: - HEAP - CLUSTERED INDEX - NONCLUSTERED INDEX - PRIMARY XML INDEX - EXTENDED INDEX - XML INDEX - COLUMNSTORE MAPPING INDEX (belső) - COLUMNSTORE DELETEBUFFER INDEX (belső) - COLUMNSTORE DELETEBITMAP INDEX (belső)
alloc_unit_type_desc	nvarchar(60)	A foglalási egység típusának leírása: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA A LOB_DATA foglalási egység tartalmazza a típusú, ntext, kép, varchar(max), nvarchar(max), varbinary(max)és xml. További információ: Adattípusok. A ROW_OVERFLOW_DATA foglalási egység a varchar(n), nvarchar(n), varbinary(n)és sql_variant oszlopban tárolt adatokat tartalmazza.
index_depth	apró	Indexszintek száma. 1 = Halom, LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egység.
index_level	apró	Az index aktuális szintje. 0 indexlevélszintekhez, halmokhoz és LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységekhez. Nagyobb, mint 0 a nem bolha indexszintek esetében. index_level az index gyökérszintjén a legmagasabb. Az indexek nem bolhaszintjei csak akkor lesznek feldolgozva, ha módDETAILED.
avg_fragmentation_in_percent	lebegőpontos	Az indexek logikai töredezettsége, vagy a halmok mértéktöredezettsége a IN_ROW_DATA foglalási egységben. Az érték százalékos értékként van megadva, és több fájlt is figyelembe vesz. A logikai és a mértéktöredezettség definícióiért lásd Megjegyzések. 0 LOB_DATA és ROW_OVERFLOW_DATA foglalási egységekhez. NULL halomra, ha módSAMPLED.
fragment_count	bigint	Egy IN_ROW_DATA foglalási egység levélszintjében lévő töredékek száma. További információ a töredékekről: Megjegyzések. NULL index nemleaf szintjeihez, valamint LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységekhez. NULL halomra, ha módSAMPLED.
avg_fragment_size_in_pages	lebegőpontos	Egy töredék lapjainak átlagos száma egy IN_ROW_DATA foglalási egység levélszintjében. NULL index nemleaf szintjeihez, valamint LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységekhez. NULL halomra, ha módSAMPLED.
page_count	bigint	Indexek vagy adatlapok teljes száma. Index esetén az indexlapok teljes száma a B fa aktuális szintjén a IN_ROW_DATA foglalási egységben. Halom esetén a IN_ROW_DATA foglalási egység adatoldalainak teljes száma. LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységek esetén a foglalási egység lapjainak teljes száma.
avg_page_space_used_in_percent	lebegőpontos	A rendelkezésre álló adattárolási terület átlagos százalékos aránya az összes lapon. Index esetén az átlag a B fa aktuális szintjére vonatkozik a IN_ROW_DATA foglalási egységben. Halom esetén a IN_ROW_DATA foglalási egység összes adatlapjának átlaga. LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységek esetében a foglalási egység összes lapjának átlaga. NULL módLIMITED.
record_count	bigint	Rekordok teljes száma. Index esetén a rekordok teljes száma az IN_ROW_DATA foglalási egység B-fa aktuális szintjére vonatkozik. Halom esetén a IN_ROW_DATA foglalási egység rekordjainak teljes száma. Megjegyzés: Halom esetén előfordulhat, hogy a függvényből visszaadott rekordok száma nem egyezik meg a SELECT COUNT(*) a halomhoz való futtatásával visszaadott sorok számával. Ennek az az oka, hogy egy sor több rekordot is tartalmazhat. Bizonyos frissítési helyzetekben például előfordulhat, hogy egy halomsor egy továbbítási rekorddal és egy továbbított rekorddal rendelkezik a frissítési művelet eredményeként. Emellett a legtöbb nagy LOB-sor több rekordra van felosztva LOB_DATA tárolóban. LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységek esetében a teljes foglalási egység rekordjainak teljes száma. NULL módLIMITED.
ghost_record_count	bigint	A foglalási egységben lévő szellemkarbantartási feladat által eltávolításra kész szellemrekordok száma. 0 a IN_ROW_DATA foglalási egységben lévő index nemleaf szintjeihez. NULL módLIMITED.
version_ghost_record_count	bigint	A kiugró pillanatkép-elkülönítési tranzakció által fenntartott szellemrekordok száma egy foglalási egységben. 0 a IN_ROW_DATA foglalási egységben lévő index nemleaf szintjeihez. NULL módLIMITED.
min_record_size_in_bytes		Minimális rekordméret bájtban. Index esetén a minimális rekordméret a B fa aktuális szintjére vonatkozik a IN_ROW_DATA foglalási egységben. Halom esetén a IN_ROW_DATA foglalási egység minimális rekordmérete. A LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységek esetében a teljes foglalási egység minimális rekordmérete. NULL módLIMITED.
max_record_size_in_bytes		Maximális rekordméret bájtban. Index esetén a maximális rekordméret a B fa aktuális szintjére vonatkozik a IN_ROW_DATA foglalási egységben. Halom esetén a maximális rekordméret a IN_ROW_DATA foglalási egységben. LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységek esetében a teljes foglalási egység maximális rekordmérete. NULL módLIMITED.
avg_record_size_in_bytes	lebegőpontos	Átlagos rekordméret bájtban. Index esetén az átlagos rekordméret a B fa aktuális szintjére vonatkozik a IN_ROW_DATA foglalási egységben. Halom esetén az IN_ROW_DATA foglalási egység átlagos rekordmérete. LOB_DATA vagy ROW_OVERFLOW_DATA foglalási egységek esetében a teljes foglalási egység átlagos rekordmérete. NULL módLIMITED.
forwarded_record_count	bigint	Egy halom azon rekordjainak száma, amelyek mutatóit egy másik adathelyre továbbítja. (Ez az állapot egy frissítés során fordul elő, ha nincs elegendő hely az új sor eredeti helyen való tárolásához.) NULL a halom IN_ROW_DATA foglalási egységétől eltérő összes foglalási egységhez. NULL halomra, ha módLIMITED.
compressed_page_count	bigint	A tömörített lapok száma. Halom esetén az újonnan lefoglalt lapok nem PAGE tömörítve. A halom két különleges feltétel mellett PAGE tömörítve: az adatok tömeges importálása vagy egy halom újraépítése esetén. A laplefoglalásokat okozó tipikus DML-műveletek nem PAGE tömörítve. Halom újraépítése, ha a compressed_page_count érték nagyobb, mint a kívánt küszöbérték. Fürtözött indexet tartalmazó táblák esetében a compressed_page_count érték a PAGE tömörítés hatékonyságát jelzi.
hobt_id	bigint	Az index vagy partíció halom- vagy B-faazonosítója. Oszlopcentrikus indexek esetén ez egy olyan sorhalmaz azonosítója, amely egy partíció belső oszlopcentrikus adatait követi nyomon. A sorok adat halomként vagy B-fákként vannak tárolva. Ugyanazzal az indexazonosítóval rendelkeznek, mint a szülő oszlopcentrikus index. További információ: sys.internal_partitions.
columnstore_delete_buffer_state	apró	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID – a szülőindex nem oszlopcentrikus index. OPEN – ezt a törlők és a képolvasók használják. DRAINING – a törlők kiürítik, de a képolvasók továbbra is használják. FLUSHING – a puffer bezárul, és a puffer sorai a törlési bitképre lesznek írva. RETIRING – A lezárt törlési puffer sorai a törlési bitképre lettek megírva, de a puffert nem csonkították, mert a képolvasók továbbra is használják. Az új szkennereknek nem kell a nyugdíjba vonuló puffert használniuk, mert a nyitott puffer elegendő. READY – Ez a törlési puffer használatra kész. A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
version_record_count	bigint	Ez az indexben karbantartott sorverziórekordok száma. Ezeket a sorverziókat a gyorsított adatbázis-helyreállítás funkció tartja fenn. A következőkre vonatkozik: SQL Server 2019 (15.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
inrow_version_record_count	bigint	A gyors lekéréshez az adatsorban tárolt ADR-verziórekordok száma. A következőkre vonatkozik: SQL Server 2019 (15.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
inrow_diff_version_record_count	bigint	Az ADR-verziórekordok száma, amely az alapverziótól való eltérések formájában van tárolva. A következőkre vonatkozik: SQL Server 2019 (15.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
total_inrow_version_payload_size_in_bytes	bigint	Az index soron belüli verziórekordjainak bájtban megadott teljes mérete. A következőkre vonatkozik: SQL Server 2019 (15.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
offrow_regular_version_record_count	bigint	Az eredeti adatsoron kívül tárolt verziórekordok száma. A következőkre vonatkozik: SQL Server 2019 (15.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány
offrow_long_term_version_record_count	bigint	Az online index verziótárolójában lévő verziórekordok száma. A következőkre vonatkozik: SQL Server 2019 (15.x) és újabb verziók, az Azure SQL Database és a felügyelt Azure SQL-példány

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.

Megjegyzések

A sys.dm_db_index_physical_stats dinamikus felügyeleti függvény felváltja a DBCC SHOWCONTIG utasítást.

Vizsgálati módok

A függvény végrehajtásának módja határozza meg a függvény által használt statisztikai adatok lekéréséhez végzett vizsgálat szintjét. módLIMITED, SAMPLEDvagy DETAILED. A függvény bejárja a táblázat vagy index megadott partícióit alkotó foglalási egységek oldalláncait. sys.dm_db_index_physical_stats csak Intent-Shared (IS) táblazárolást igényel, függetlenül attól, hogy milyen üzemmódban fut.

A LIMITED mód a leggyorsabb mód, és a legkisebb számú oldalt vizsgálja. Index esetén a rendszer csak a B-fa szülőszintű lapjait (azaz a levélszint feletti lapokat) vizsgálja. Halom esetén a rendszer megvizsgálja a kapcsolódó PFS- és IAM-lapokat, és a halom adatlapjait LIMITED módban ellenőrzi.

LIMITED mód esetén compressed_page_countNULL, mert az adatbázismotor csak a B-fa nem bolhaoldalait, valamint a halom IAM- és PFS-lapjait vizsgálja. A SAMPLED mód használatával lekérheti a compressed_page_countbecsült értékét, és DETAILED mód használatával lekérheti compressed_page_counttényleges értékét. A SAMPLED mód az indexben vagy halomban lévő összes oldal 1 százalékos mintája alapján ad vissza statisztikákat. A SAMPLED módban elért eredményeket hozzávetőlegesnek kell tekinteni. Ha az index vagy a halom 10 000-nél kevesebb oldalból áll, DETAILED módot használja a rendszer SAMPLEDhelyett.

A DETAILED mód az összes oldalt megvizsgálja, és visszaadja az összes statisztikát.

A módok fokozatosan lassabbak LIMITED és DETAILEDközött, mivel minden módban több munka történik. A táblázat vagy index méretének vagy töredezettségének gyors felméréséhez használja a LIMITED módot. Ez a leggyorsabb, és nem ad vissza egy sort az index IN_ROW_DATA foglalási egységében lévő összes nem bolhaszinthez.

Paraméterértékek megadása rendszerfüggvényekkel

A Transact-SQL függvények DB_ID és OBJECT_ID használatával megadhatja a database_id és object_id paraméterek értékét. Ha azonban nem érvényes értékeket ad át ezeknek a függvényeknek, az nem kívánt eredményeket eredményezhet. Ha például az adatbázis vagy az objektum neve nem található, mert nem létezik, vagy helytelenül van beírva, mindkét függvény NULLad vissza. A sys.dm_db_index_physical_stats függvény az NULL helyettesítő karakterként értelmezi, amely az összes adatbázist vagy objektumot megadja.

Emellett a OBJECT_ID függvény feldolgozása a sys.dm_db_index_physical_stats függvény meghívása előtt történik, ezért az aktuális adatbázis kontextusában lesz kiértékelve, nem pedig a database_id. Ez a viselkedés azt eredményezheti, hogy a OBJECT_ID függvény NULL értéket ad vissza; vagy ha az objektumnév az aktuális adatbázis-környezetben és a megadott adatbázisban is létezik, hibaüzenet jelenik meg. Az alábbi példák ezeket a nem kívánt eredményeket mutatják be.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Ajánlott eljárás

Mindig győződjön meg arról, hogy a DB_ID vagy OBJECT_IDhasználatakor érvényes azonosítót ad vissza. Ha például OBJECT_IDhasznál, adjon meg egy háromrészes nevet, például OBJECT_ID(N'AdventureWorks2022.Person.Address'), vagy tesztelje a függvények által visszaadott értéket, mielőtt azokat a sys.dm_db_index_physical_stats függvényben használja. Az alábbi A és B példák az adatbázis- és objektumazonosítók biztonságos megadásának módját mutatják be.

Töredezettség észlelése

A töredezettség a táblán végrehajtott adatmódosítások (INSERT, UPDATEés DELETE utasítások) és így a táblában meghatározott indexek folyamatán keresztül történik. Mivel ezek a módosítások általában nem egyenlően vannak elosztva a táblázat és az indexek sorai között, az egyes lapok teljessége idővel változhat. Az olyan lekérdezések esetében, amelyek egy tábla egy részét vagy összes indexét átvizsgálják, ez a fajta töredezettség több oldalolvasást okozhat, ami akadályozza az adatok párhuzamos vizsgálatát.

Az index vagy halom töredezettségi szintje megjelenik a avg_fragmentation_in_percent oszlopban. A halmok esetében az érték a halom terjedelmének töredezettségét jelöli. Indexek esetén az érték az index logikai töredezettségét jelöli. A DBCC SHOWCONTIGellentétben a töredezettség számítási algoritmusai mindkét esetben a több fájlra kiterjedő tárolást veszik figyelembe, ezért pontosak.

Logikai töredezettség

Ez az index levéloldalain a sorrenden kívüli lapok százalékos aránya. A rendelésen kívüli lapok olyan lapok, amelyekhez az indexhez rendelt következő fizikai lap nem az a lap, amelyre az aktuális levéloldal következő oldal mutatója mutat.

Mértékek töredezettsége

Ez a halom levéloldalain a sorrenden kívüli mértékek százalékos aránya. A rendezésen kívüli mértékek olyan mértékek, amelyeknél a halom aktuális lapját tartalmazó mérték fizikailag nem a következő mérték az előző oldalt tartalmazó mérték után.

Az avg_fragmentation_in_percent értékének a lehető legközelebb kell lennie a nullához a maximális teljesítményhez. A 0 és 10 százalék közötti értékek azonban elfogadhatók lehetnek. A töredezettség csökkentésének minden módszere, például az újraépítés, az átszervezés vagy az újraépítés, használható az értékek csökkentésére. Az index töredezettségének mértékéről további információt az Indexkarbantartás optimalizálása a lekérdezési teljesítmény javítása és az erőforrás-felhasználáscímű témakörben talál.

Az index töredezettségének csökkentése

Ha egy index úgy töredezett, hogy a töredezettség befolyásolja a lekérdezés teljesítményét, három lehetőség van a töredezettség csökkentésére:

• A fürtözött index elvetése és ismételt létrehozása.

  A fürtözött index újbóli létrehozása újra elosztja az adatokat, és teljes adatoldalakat eredményez. A teljességi szint a FILLFACTORCREATE INDEX lehetőségével konfigurálható. Ennek a módszernek az a hátránya, hogy az index offline állapotban van az esés és az újbóli ciklus során, és hogy a művelet atomi. Ha az index létrehozása megszakad, az index nem jön létre újra. További információ: CREATE INDEX.

• A ALTER INDEX REORGANIZE, a DBCC INDEXDEFRAGhelyettesítésével logikai sorrendbe rendezheti az index levélszintű lapjait. Mivel ez egy online művelet, az index elérhető az utasítás futtatása közben. A művelet a már elvégzett munka elvesztése nélkül is megszakítható. Ennek a módszernek az a hátránya, hogy nem olyan jó feladat az adatok index-újraépítési műveletként való átrendezése, és nem frissíti a statisztikákat.

• Az index online vagy offline újraépítéséhez használja a ALTER INDEX REBUILD, a DBCC DBREINDEXhelyettesítését. További információ: ALTER INDEX (Transact-SQL).

A töredezettség önmagában nem elegendő ok egy index átrendezésére vagy újraépítésére. A töredezettség fő hatása az, hogy az indexvizsgálatok során lassítja az oldalak olvasási sebességét. Ez lassabb válaszidőt okoz. Ha a töredezett táblák vagy indexek lekérdezési számítási feladatai nem járnak vizsgálatokkal, mivel a számítási feladat elsősorban egytáblás keresés, a töredezettség eltávolításának nincs hatása.

Jegyzet

A DBCC SHRINKFILE vagy DBCC SHRINKDATABASE futtatása töredezettséghez vezethet, ha egy index részben vagy teljesen áthelyezve van a zsugorítási művelet során. Ezért ha zsugorítási műveletet kell végrehajtani, a töredezettség eltávolítása előtt végezze el.

Halom töredezettségének csökkentése

A halom terjedelmének csökkentése érdekében hozzon létre egy fürtözött indexet a táblában, majd vesse el az indexet. Ez újraosztja az adatokat a fürtözött index létrehozásakor. Ez a lehető legoptimálisabb, figyelembe véve az adatbázisban rendelkezésre álló szabad hely eloszlását. Amikor a fürtözött indexet elveti a halom újbóli létrehozásához, az adatok nem lesznek áthelyezve, és optimális helyzetben maradnak. A műveletek végrehajtásáról további információt CREATE INDEX és DROP INDEXcímű cikkben talál.

Figyelmeztet

Fürtözött index létrehozása és elvetése a táblán kétszer újraépíti a táblán lévő összes nemclustered indexet.

Nagyméretű objektumadatok tömörítése

Alapértelmezés szerint a ALTER INDEX REORGANIZE utasítás tömöríti a nagyméretű objektumadatokat (LOB) tartalmazó lapokat. Mivel a LOB-lapok üresen nem lesznek felszabadítva, az adatok tömörítése javíthatja a lemezterület használatát, ha sok LOB-adatot törölnek, vagy egy LOB-oszlopot elvetnek.

Egy adott fürtözött index átrendezése tömöríti a fürtözött indexben található összes LOB-oszlopot. A nemclustered index újraszervezése tömöríti az összes olyan LOB-oszlopot, amely nem kulcsalapú (belefoglalt) oszlop az indexben. Ha ALL van megadva az utasításban, a megadott táblához vagy nézethez társított összes index átrendeződik. Emellett a fürtözött indexhez, a mögöttes táblához vagy a nemclustered indexhez társított összes LOB-oszlop tömörítve van.

Lemezterület használatának kiértékelése

A avg_page_space_used_in_percent oszlop az oldal teljességét jelzi. Az optimális lemezterület-használat érdekében ennek az értéknek közel 100 százaléknak kell lennie egy olyan indexhez, amely nem rendelkezik sok véletlenszerű beszúrási értékkel. A sok véletlenszerű beszúrást tartalmazó és nagyon teljes lapokat tartalmazó indexek azonban megnövekedett számú oldaleloszlását eredményezik. Ez nagyobb töredezettséget okoz. Ezért az oldaleloszlások csökkentése érdekében az értéknek 100 százaléknál kisebbnek kell lennie. Ha újraépít egy indexet a megadott FILLFACTOR beállítással, a lap teljessége módosítható az index lekérdezési mintájának megfelelően. A kitöltési tényezővel kapcsolatos további információkért lásd Indexkitöltési tényezőinek megadása című témakört. Emellett ALTER INDEX REORGANIZE az indexet úgy tömöríti, hogy a legutóbb megadott FILLFACTOR próbál oldalakat kitölteni. Ez növeli a avg_space_used_in_percent értékét. ALTER INDEX REORGANIZE nem csökkentheti az oldal teljességét. Ehelyett index-újraépítést kell végrehajtani.

Indextöredékek kiértékelése

A töredék fizikailag egymást követő levéloldalakból áll ugyanabban a fájlban egy foglalási egységhez. Az index legalább egy töredékből áll. Az index maximális töredékei megegyezhetnek az index levélszintjének oldalainak számával. A nagyobb töredékek azt jelentik, hogy kevesebb lemez I/O-ra van szükség az azonos számú oldal olvasásához. Ezért minél nagyobb a avg_fragment_size_in_pages érték, annál jobb a tartományvizsgálati teljesítmény. A avg_fragment_size_in_pages és avg_fragmentation_in_percent értékek fordítottan arányosak egymással. Ezért az index újraépítésének vagy átrendezésének csökkentenie kell a töredezettség mennyiségét, és növelnie kell a töredezettség méretét.

Korlátozások

Nem ad vissza adatokat a fürtözött oszlopcentrikus indexekhez.

Engedélyek

A következő engedélyek szükségesek:

• CONTROL engedélyt a megadott objektumra az adatbázisban.

• VIEW DATABASE STATE vagy VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) engedély a megadott adatbázis összes objektumára vonatkozó adatok visszaadására az objektum helyettesítő karakterének @object_id = NULLhasználatával.

• VIEW SERVER STATE vagy VIEW SERVER PERFORMANCE STATE (SQL Server 2022) engedélyt az összes adatbázis adatainak visszaadására az adatbázis helyettesítő karakterének @database_id = NULLhasználatával.

A VIEW DATABASE STATE megadása lehetővé teszi az adatbázis összes objektumának visszaadását, függetlenül attól, hogy CONTROL adott objektumokra vonatkozó engedélyek megtagadva vannak-e.

A VIEW DATABASE STATE megtagadása nem engedélyezi az adatbázis összes objektumának visszaadását, függetlenül attól, hogy CONTROL adott objektumokra vonatkozó engedélyek vannak-e megadva. Ha az adatbázis helyettesítő karakterét @database_id = NULL adja meg, az adatbázis nem lesz megadva.

További információ: Rendszer dinamikus felügyeleti nézetei.

Példák

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

Egy. Megadott táblával kapcsolatos információk visszaadása

Az alábbi példa a Person.Address tábla összes indexére és partíciójára vonatkozó méret- és töredezettségstatisztikát adja vissza. A vizsgálati mód úgy van beállítva, hogy LIMITED a legjobb teljesítmény érdekében, és korlátozza a visszaadott statisztikákat. A lekérdezés végrehajtásához legalább CONTROL engedélyre van szükség a Person.Address táblában.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Egy halom adatainak visszaadása

Az alábbi példa az dbo.DatabaseLog adatbázisban lévő halom AdventureWorks2025 összes statisztikáit adja vissza. Mivel a táblázat LOB-adatokat tartalmaz, a LOB_DATA foglalási egység egy sorát adja vissza a halom adatlapjait tartalmazó IN_ROW_ALLOCATION_UNIT visszaadott sor mellett. A lekérdezés végrehajtásához legalább CONTROL engedélyre van szükség a dbo.DatabaseLog táblában.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. Az összes adatbázis adatainak visszaadása

Az alábbi példa az SQL Server-példány összes táblájának és indexének összes statisztikáját adja vissza az összes paraméter helyettesítő NULL megadásával. A lekérdezés végrehajtásához VIEW SERVER STATE engedély szükséges.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

D. Indexek újraépítése vagy átrendezése sys.dm_db_index_physical_stats szkriptben

Az alábbi példa automatikusan átrendezi vagy újraépíti az adatbázis összes partícióit, amelyek átlagos töredezettsége meghaladja a 10%-ot. A lekérdezés végrehajtásához VIEW DATABASE STATE engedély szükséges. Ez a példa a DB_ID adja meg első paraméterként adatbázisnév megadása nélkül.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. A sys.dm_db_index_physical_stats használata az oldalak által tömörített lapok számának megjelenítéséhez

Az alábbi példa bemutatja, hogyan jelenítheti meg és hasonlíthatja össze az oldalak teljes számát a sor- és oldalsűrített oldalakhoz. Ezek az információk felhasználhatók annak meghatározására, hogy a tömörítés milyen előnyt biztosít egy index vagy tábla számára.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Sys.dm_db_index_physical_stats használata SAMPLED módban

Az alábbi példa bemutatja, hogy SAMPLED mód a DETAILED mód eredményétől eltérő közelítő értéket ad vissza.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Lekérdezési szolgáltatásközvetítői üzenetsorok indextöredezettséghez

A következőkre vonatkozik: SQL Server 2016 (13.x) és újabb verziók

Az alábbi példa bemutatja, hogyan kérdezheti le a kiszolgáló-közvetítői üzenetsorokat a töredezettség érdekében.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

Kapcsolódó tartalom

• rendszer dinamikus felügyeleti nézetei
• indexelt dinamikus felügyeleti nézetek és függvények (Transact-SQL)
• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.allocation_units (Transact-SQL)
• Transact-SQL referencia (adatbázismotor)