sys.dm_db_index_physical_stats (Transact-SQL)

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Retourneert informatie over grootte en fragmentatie voor de gegevens en indexen van de opgegeven tabel of weergave in de SQL Server Database Engine. Voor een index wordt één rij geretourneerd voor elk niveau van de B-structuur in elke partitie. Voor een heap wordt één rij geretourneerd voor de IN_ROW_DATA toewijzingseenheid van elke partitie. Voor grote objectgegevens (LOB) wordt één rij geretourneerd voor de LOB_DATA toewijzingseenheid van elke partitie. Als er rij-overloopgegevens in de tabel aanwezig zijn, wordt één rij geretourneerd voor de ROW_OVERFLOW_DATA toewijzingseenheid in elke partitie.

Notitie

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rijstore-indexen implementeert de database-engine een B+-structuur. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

sys.dm_db_index_physical_stats retourneert geen informatie over indexen die zijn geoptimaliseerd voor geheugen. Zie sys.dm_db_xtp_index_statsvoor informatie over het gebruik van indexen die zijn geoptimaliseerd voor geheugen.

Als u een query uitvoert op sys.dm_db_index_physical_stats op een serverexemplaren die als host fungeert voor een beschikbaarheidsgroep leesbare secundaire replica, kan er een REDO blokkeringsprobleem optreden. Dit komt doordat deze dynamische beheerweergave een Intent-Shared -vergrendeling (IS) verkrijgt op de opgegeven gebruikerstabel of -weergave die aanvragen kan blokkeren door een REDO thread voor een exclusieve (X)-vergrendeling op die gebruikerstabel of -weergave.

Transact-SQL syntaxisconventies

Syntaxis

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 }
)

Argumenten

database_id | NULL | 0 | VERSTEK

De id van de database. database_id is kleine. Geldige invoer is de id van een database, NULL, 0of DEFAULT. De standaardwaarde is 0. NULL, 0en DEFAULT zijn gelijkwaardige waarden in deze context.

Geef NULL op om informatie te retourneren voor alle databases in het exemplaar van SQL Server. Als u NULL opgeeft voor database_id, moet u ook NULL opgeven voor object_id, index_iden partition_number.

De ingebouwde functie DB_ID kan worden opgegeven. Wanneer u DB_ID gebruikt zonder een databasenaam op te geven, moet het compatibiliteitsniveau van de huidige database 90 of hoger zijn.

object_id | NULL | 0 | VERSTEK

De object-id van de tabel of weergave waarop de index is ingeschakeld. object_id is int. Geldige invoer is de id van een tabel en weergave, NULL, 0of DEFAULT. De standaardwaarde is 0. NULL, 0en DEFAULT zijn gelijkwaardige waarden in deze context.

In SQL Server 2016 (13.x) en latere versies bevatten geldige invoer ook de servicebrokerwachtrijnaam of de interne tabelnaam van de wachtrij. Wanneer standaardparameters worden toegepast (dat wil gezegd, alle objecten, alle indexen, enzovoort), wordt fragmentatie-informatie voor alle wachtrijen opgenomen in de resultatenset.

Geef NULL op om informatie te retourneren voor alle tabellen en weergaven in de opgegeven database. Als u NULL opgeeft voor object_id, moet u ook NULL opgeven voor index_id en partition_number.

index_id | 0 | NULL | -1 | VERSTEK

De id van de index. index_id is int. Geldige invoer is de id van een index, 0 als object_id een heap, NULL, -1of DEFAULTis. De standaardwaarde is -1. NULL, -1en DEFAULT zijn gelijkwaardige waarden in deze context.

Geef NULL op om informatie te retourneren voor alle indexen voor een basistabel of -weergave. Als u NULL opgeeft voor index_id, moet u ook NULL opgeven voor partition_number.

partition_number | NULL | 0 | VERSTEK

Het partitienummer in het object. partition_number is int. Geldige invoer is de partition_number van een index of heap, NULL, 0of DEFAULT. De standaardwaarde is 0. NULL, 0en DEFAULT zijn gelijkwaardige waarden in deze context.

Geef NULL op om informatie te retourneren voor alle partities van het eigendomsobject.

partition_number is gebaseerd op 1. Een niet-gepartitioneerde index of heap heeft partition_number ingesteld op 1.

modus | NULL | VERSTEK

De naam van de modus. modus geeft het scanniveau op dat wordt gebruikt voor het verkrijgen van statistieken. modus is sysname. Geldige invoer is DEFAULT, NULL, LIMITED, SAMPLEDof DETAILED. De standaardwaarde (NULL) is LIMITED.

Tabel geretourneerd

Kolomnaam	Gegevenstype	Beschrijving
database_id	kleine	Database-id van de tabel of weergave. In Azure SQL Database zijn de waarden uniek binnen één database of een elastische pool, maar niet binnen een logische server.
object_id		Object-id van de tabel of weergave waarop de index zich bevindt.
index_id		Index-id van een index. 0 = Heap.
partition_number		Partitienummer op basis van 1 binnen het eigendomsobject; een tabel, weergave of index. 1 = niet-gepartitioneerde index of heap.
index_type_desc	nvarchar(60)	Beschrijving van het indextype: - HEAP - CLUSTERED INDEX - NONCLUSTERED INDEX - PRIMARY XML INDEX - EXTENDED INDEX - XML INDEX - COLUMNSTORE MAPPING INDEX (intern) - COLUMNSTORE DELETEBUFFER INDEX (intern) - COLUMNSTORE DELETEBITMAP INDEX (intern)
alloc_unit_type_desc	nvarchar(60)	Beschrijving van het type toewijzingseenheid: - IN_ROW_DATA - LOB_DATA - ROW_OVERFLOW_DATA De LOB_DATA toewijzingseenheid bevat de gegevens die zijn opgeslagen in kolommen van het type tekst, ntext, afbeelding, varchar(max), nvarchar(max), varbinary(max)en xml-. Zie Gegevenstypenvoor meer informatie. De ROW_OVERFLOW_DATA toewijzingseenheid bevat de gegevens die zijn opgeslagen in kolommen van het type varchar(n), nvarchar(n), varbinary(n)en sql_variant die buiten rij worden gepusht.
index_depth	kleine	Aantal indexniveaus. 1 = Heap, of LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheid.
index_level	kleine	Huidig niveau van de index. 0 voor indexbladniveaus, heaps en LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden. Groter dan 0 voor niet-leaf-indexniveaus. index_level is het hoogste op het hoofdniveau van een index. De niet-afbreekniveaus van indexen worden alleen verwerkt wanneer modus wordt DETAILED.
avg_fragmentation_in_percent	float-	Logische fragmentatie voor indexen of uitbreidingsfragmentatie voor heaps in de IN_ROW_DATA toewijzingseenheid. De waarde wordt gemeten als een percentage en houdt rekening met meerdere bestanden. Zie Opmerkingenvoor definities van logische en omvangfragmentatie. 0 voor LOB_DATA en ROW_OVERFLOW_DATA toewijzingseenheden. NULL voor heaps wanneer modus is SAMPLED.
fragment_count	grote	Aantal fragmenten in het bladniveau van een IN_ROW_DATA toewijzingseenheid. Zie Opmerkingenvoor meer informatie over fragmenten. NULL voor niet-afbreekniveaus van een index en LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden. NULL voor heaps wanneer modus is SAMPLED.
avg_fragment_size_in_pages	float-	Gemiddeld aantal pagina's in één fragment in het bladniveau van een IN_ROW_DATA toewijzingseenheid. NULL voor niet-afbreekniveaus van een index en LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden. NULL voor heaps wanneer modus is SAMPLED.
page_count	grote	Totaal aantal index- of gegevenspagina's. Voor een index is het totale aantal indexpagina's in het huidige niveau van de B-structuur in de IN_ROW_DATA toewijzingseenheid. Voor een heap is het totale aantal gegevenspagina's in de IN_ROW_DATA toewijzingseenheid. Voor LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden, het totale aantal pagina's in de toewijzingseenheid.
avg_page_space_used_in_percent	float-	Gemiddeld percentage van de beschikbare opslagruimte voor gegevens die op alle pagina's wordt gebruikt. Voor een index is het gemiddelde van toepassing op het huidige niveau van de B-structuur in de IN_ROW_DATA toewijzingseenheid. Voor een heap is het gemiddelde van alle gegevenspagina's in de IN_ROW_DATA toewijzingseenheid. Voor LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden, het gemiddelde van alle pagina's in de toewijzingseenheid. NULL wanneer modus is LIMITED.
record_count	grote	Totaal aantal records. Voor een index is het totale aantal records van toepassing op het huidige niveau van de B-boomstructuur in de IN_ROW_DATA toewijzingseenheid. Voor een heap is het totale aantal records in de IN_ROW_DATA toewijzingseenheid. Opmerking: Voor een heap komt het aantal records dat door deze functie wordt geretourneerd mogelijk niet overeen met het aantal rijen dat wordt geretourneerd door een SELECT COUNT(*) uit te voeren tegen de heap. Dit komt doordat een rij meerdere records kan bevatten. In sommige updatesituaties kan een enkele heaprij bijvoorbeeld een doorstuurrecord en een doorgestuurde record hebben als gevolg van de updatebewerking. Bovendien worden de meeste grote LOB-rijen gesplitst in meerdere records in LOB_DATA-opslag. Voor LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden, het totale aantal records in de volledige toewijzingseenheid. NULL wanneer modus is LIMITED.
ghost_record_count	grote	Aantal ghostrecords dat gereed is voor verwijdering door de ghost-opschoontaak in de toewijzingseenheid. 0 voor niet-afbreekniveaus van een index in de IN_ROW_DATA toewijzingseenheid. NULL wanneer modus is LIMITED.
version_ghost_record_count	grote	Aantal ghostrecords dat wordt bewaard door een openstaande transactie voor isolatie van momentopnamen in een toewijzingseenheid. 0 voor niet-afbreekniveaus van een index in de IN_ROW_DATA toewijzingseenheid. NULL wanneer modus is LIMITED.
min_record_size_in_bytes		Minimale recordgrootte in bytes. Voor een index is de minimale recordgrootte van toepassing op het huidige niveau van de B-structuur in de IN_ROW_DATA toewijzingseenheid. Voor een heap is de minimale recordgrootte in de IN_ROW_DATA toewijzingseenheid. Voor LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden, de minimale recordgrootte in de volledige toewijzingseenheid. NULL wanneer modus is LIMITED.
max_record_size_in_bytes		Maximale recordgrootte in bytes. Voor een index is de maximale recordgrootte van toepassing op het huidige niveau van de B-boomstructuur in de IN_ROW_DATA toewijzingseenheid. Voor een heap is de maximale recordgrootte in de IN_ROW_DATA toewijzingseenheid. Voor LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden is de maximale recordgrootte in de volledige toewijzingseenheid. NULL wanneer modus is LIMITED.
avg_record_size_in_bytes	float-	Gemiddelde recordgrootte in bytes. Voor een index is de gemiddelde recordgrootte van toepassing op het huidige niveau van de B-structuur in de IN_ROW_DATA toewijzingseenheid. Voor een heap is de gemiddelde recordgrootte in de IN_ROW_DATA toewijzingseenheid. Voor LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden, de gemiddelde recordgrootte in de volledige toewijzingseenheid. NULL wanneer modus is LIMITED.
forwarded_record_count	grote	Aantal records in een heap met doorstuurpunten naar een andere gegevenslocatie. (Deze status treedt op tijdens een update, wanneer er onvoldoende ruimte is om de nieuwe rij op de oorspronkelijke locatie op te slaan.) NULL voor andere toewijzingseenheden dan de IN_ROW_DATA-toewijzingseenheden voor een heap. NULL voor heaps wanneer modus is LIMITED.
compressed_page_count	grote	Het aantal gecomprimeerde pagina's. Voor heaps worden nieuw toegewezen pagina's niet PAGE gecomprimeerd. Een heap wordt PAGE gecomprimeerd onder twee speciale omstandigheden: wanneer gegevens bulksgewijs worden geïmporteerd of wanneer een heap opnieuw wordt opgebouwd. Typische DML-bewerkingen die ervoor zorgen dat paginatoewijzingen niet worden PAGE gecomprimeerd. Bouw een heap opnieuw wanneer de compressed_page_count waarde groter wordt dan de gewenste drempelwaarde. Voor tabellen met een geclusterde index geeft de compressed_page_count waarde de effectiviteit van PAGE compressie aan.
hobt_id	grote	Heap- of B-tree-id van de index of partitie. Voor columnstore-indexen is dit de id voor een rijenset waarmee interne columnstore-gegevens voor een partitie worden bijgehouden. De rijensets worden opgeslagen als gegevenshops of B-trees. Ze hebben dezelfde index-id als de bovenliggende columnstore-index. Zie sys.internal_partitionsvoor meer informatie.
columnstore_delete_buffer_state	kleine	0 = NOT_APPLICABLE 1 = OPEN 2 = DRAINING 3 = FLUSHING 4 = RETIRING 5 = READY van toepassing op: SQL Server 2016 (13.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
columnstore_delete_buffer_state_desc	nvarchar(60)	NOT VALID: de bovenliggende index is geen columnstore-index. OPEN: verwijderprogramma's en scanners gebruiken dit. DRAINING : verwijderprogramma's worden leeggemaakt, maar scanners gebruiken deze nog steeds. FLUSHING : buffer wordt gesloten en rijen in de buffer worden naar de verwijder bitmap geschreven. RETIRING : rijen in de gesloten verwijderbuffer zijn naar de verwijder bitmap geschreven, maar de buffer is niet afgekapt omdat scanners deze nog steeds gebruiken. Nieuwe scanners hoeven de buitengebruik stellende buffer niet te gebruiken omdat de open buffer voldoende is. READY: deze verwijderbuffer is gereed voor gebruik. van toepassing op: SQL Server 2016 (13.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
version_record_count	grote	Dit is het aantal rijversierecords dat in deze index wordt onderhouden. Deze rijversies worden onderhouden door de functie versneld databaseherstel. van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
inrow_version_record_count	grote	Het aantal ADR-versierecords dat in de gegevensrij wordt bewaard om snel op te halen. van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
inrow_diff_version_record_count	grote	Het aantal ADR-versierecords dat wordt bewaard in de vorm van verschillen van de basisversie. van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
total_inrow_version_payload_size_in_bytes	grote	Totale grootte in bytes van de records in de rijversie voor deze index. van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
offrow_regular_version_record_count	grote	Het aantal versierecords dat buiten de oorspronkelijke gegevensrij wordt bewaard. van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance
offrow_long_term_version_record_count	grote	Het aantal versierecords in het online indexversiearchief. van toepassing op: SQL Server 2019 (15.x) en latere versies, Azure SQL Database en Azure SQL Managed Instance

Notitie

Documentatie maakt gebruik van de term B-tree in het algemeen in verwijzing naar indexen. In rijstore-indexen implementeert de database-engine een B+-structuur. Dit geldt niet voor columnstore-indexen of indexen voor tabellen die zijn geoptimaliseerd voor geheugen. Zie de SQL Server- en Azure SQL-indexarchitectuur en ontwerphandleidingvoor meer informatie.

Opmerkingen

De sys.dm_db_index_physical_stats dynamische beheerfunctie vervangt de DBCC SHOWCONTIG-instructie.

Scanmodi

De modus waarin de functie wordt uitgevoerd, bepaalt het scanniveau dat wordt uitgevoerd om de statistische gegevens te verkrijgen die door de functie worden gebruikt. modus is opgegeven als LIMITED, SAMPLEDof DETAILED. De functie doorkruist de paginaketens voor de toewijzingseenheden waaruit de opgegeven partities van de tabel of index behoren. sys.dm_db_index_physical_stats vereist alleen een Intent-Shared tabelvergrendeling (IS), ongeacht de modus waarin deze wordt uitgevoerd.

De LIMITED-modus is de snelste modus en scant het kleinste aantal pagina's. Voor een index worden alleen de pagina's op het bovenliggende niveau van de B-boomstructuur (de pagina's boven het bladniveau) gescand. Voor een heap worden de bijbehorende PFS- en IAM-pagina's onderzocht en worden de gegevenspagina's van een heap gescand in LIMITED modus.

Met LIMITED-modus wordt compressed_page_countNULL omdat de database-engine alleen niet-leaf pagina's van de B-boomstructuur en de IAM- en PFS-pagina's van de heap scant. Gebruik SAMPLED modus om een geschatte waarde voor compressed_page_countop te halen en gebruik DETAILED modus om de werkelijke waarde voor compressed_page_countop te halen. De SAMPLED-modus retourneert statistieken op basis van een steekproef van 1 procent van alle pagina's in de index of heap. Resultaten in SAMPLED modus moeten als geschat worden beschouwd. Als de index of heap minder dan 10.000 pagina's bevat, wordt DETAILED modus gebruikt in plaats van SAMPLED.

De DETAILED modus scant alle pagina's en retourneert alle statistieken.

De modi zijn geleidelijk langzamer van LIMITED tot DETAILED, omdat er in elke modus meer werk wordt uitgevoerd. Gebruik de modus LIMITED om snel de grootte of fragmentatieniveau van een tabel of index te meten. Dit is het snelste en retourneert geen rij voor elk niet-afbreekniveau in de IN_ROW_DATA toewijzingseenheid van de index.

Systeemfuncties gebruiken om parameterwaarden op te geven

U kunt de Transact-SQL-functies DB_ID en OBJECT_ID gebruiken om een waarde op te geven voor de parameters database_id en object_id. Het doorgeven van waarden die niet geldig zijn voor deze functies, kan echter onbedoelde resultaten veroorzaken. Als de naam van de database of het object bijvoorbeeld niet kan worden gevonden omdat deze niet bestaan of onjuist zijn gespeld, retourneren beide functies NULL. De functie sys.dm_db_index_physical_stats interpreteert NULL als een jokerteken dat alle databases of alle objecten aangeeft.

Bovendien wordt de OBJECT_ID functie verwerkt voordat de sys.dm_db_index_physical_stats functie wordt aangeroepen en daarom wordt geëvalueerd in de context van de huidige database, niet de database die is opgegeven in database_id. Dit gedrag kan ertoe leiden dat de OBJECT_ID functie een NULL waarde retourneert; of, als de objectnaam bestaat in zowel de huidige databasecontext als de opgegeven database, wordt er een foutbericht geretourneerd. In de volgende voorbeelden ziet u deze onbedoelde resultaten.

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

Best practice

Zorg er altijd voor dat een geldige id wordt geretourneerd wanneer u DB_ID of OBJECT_IDgebruikt. Als u bijvoorbeeld OBJECT_IDgebruikt, geeft u een driedelige naam op, zoals OBJECT_ID(N'AdventureWorks2022.Person.Address'), of test u de waarde die wordt geretourneerd door de functies voordat u deze in de functie sys.dm_db_index_physical_stats gebruikt. Voorbeelden A en B die volgen, laten een veilige manier zien om database- en object-id's op te geven.

Fragmentatie detecteren

Fragmentatie vindt plaats via het proces van gegevenswijzigingen (INSERT, UPDATEen DELETE-instructies) die zijn aangebracht op basis van de tabel, en daarom aan de indexen die in de tabel zijn gedefinieerd. Omdat deze wijzigingen normaal gesproken niet gelijk worden verdeeld over de rijen van de tabel en indexen, kan de volledige inhoud van elke pagina in de loop van de tijd variëren. Voor query's die een deel of alle indexen van een tabel scannen, kan dit soort fragmentatie meer leesbewerkingen van pagina's veroorzaken, waardoor parallelle scan van gegevens wordt belemmerd.

Het fragmentatieniveau van een index of heap wordt weergegeven in de kolom avg_fragmentation_in_percent. Voor heaps geeft de waarde de mate van fragmentatie van de heap aan. Voor indexen vertegenwoordigt de waarde de logische fragmentatie van de index. In tegenstelling tot DBCC SHOWCONTIG, beschouwen de algoritmen voor fragmentatieberekeningen in beide gevallen opslag die meerdere bestanden omvat en daarom nauwkeurig zijn.

Logische fragmentatie

Dit is het percentage out-of-order pagina's in de bladpagina's van een index. Een verouderde pagina is een pagina waarvoor de volgende fysieke pagina die is toegewezen aan de index niet de pagina is waarnaar de volgende pagina aanwijzer op de huidige bladpagina wijst.

Fragmentatie van omvang

Dit is het percentage out-of-order-bereiken op de bladpagina's van een heap. Een out-of-order-extent is een van de gebieden die de huidige pagina voor een heap bevat, is niet fysiek de volgende mate na de mate waarin de vorige pagina is opgenomen.

De waarde voor avg_fragmentation_in_percent moet zo dicht mogelijk bij nul liggen voor maximale prestaties. Waarden van 0 tot en met 10 procent kunnen echter acceptabel zijn. Alle methoden voor het verminderen van fragmentatie, zoals herbouwen, herstructureren of opnieuw maken, kunnen worden gebruikt om deze waarden te verminderen. Zie Indexonderhoud optimaliseren om de queryprestaties te verbeteren en het resourceverbruikte verminderen voor meer informatie over het analyseren van de mate van fragmentatie in een index.

Fragmentatie in een index verminderen

Wanneer een index wordt gefragmenteerd op een manier die invloed heeft op de queryprestaties, zijn er drie opties voor het verminderen van fragmentatie:

• Verwijder de geclusterde index en maak deze opnieuw.

  Het opnieuw maken van een geclusterde index herdistribueert de gegevens en resulteert in volledige gegevenspagina's. Het niveau van volledigheid kan worden geconfigureerd met behulp van de optie FILLFACTOR in CREATE INDEX. De nadelen in deze methode zijn dat de index offline is tijdens de cyclus voor neerzetten en opnieuw maken en dat de bewerking atomisch is. Als het maken van de index wordt onderbroken, wordt de index niet opnieuw gemaakt. Zie CREATE INDEXvoor meer informatie.

• Gebruik ALTER INDEX REORGANIZE, de vervanging voor DBCC INDEXDEFRAG, om de volgorde van de pagina's op bladniveau van de index in een logische volgorde te wijzigen. Omdat dit een onlinebewerking is, is de index beschikbaar terwijl de instructie wordt uitgevoerd. De bewerking kan ook worden onderbroken zonder werk te verliezen dat al is voltooid. Het nadeel van deze methode is dat het niet zo goed werkt als het herstructureren van de gegevens als een herbouwbewerking voor indexen en het bijwerken van statistieken niet bijwerkt.

• Gebruik ALTER INDEX REBUILD, de vervanging voor DBCC DBREINDEX, om de index online of offline opnieuw te bouwen. Zie ALTER INDEX (Transact-SQL)voor meer informatie.

Fragmentatie alleen is niet voldoende reden om een index opnieuw te ordenen of opnieuw te bouwen. Het belangrijkste effect van fragmentatie is dat de leesdoorvoer van pagina's tijdens indexscans wordt vertraagd. Dit veroorzaakt tragere reactietijden. Als de queryworkload in een gefragmenteerde tabel of index geen scans omvat, omdat de workload voornamelijk singletonzoekacties is, kan het verwijderen van fragmentatie geen effect hebben.

Notitie

Het uitvoeren van DBCC SHRINKFILE of DBCC SHRINKDATABASE kan fragmentatie veroorzaken als een index gedeeltelijk of volledig wordt verplaatst tijdens de verkleiningsbewerking. Als een verkleiningsbewerking moet worden uitgevoerd, moet u dit doen voordat fragmentatie wordt verwijderd.

Fragmentatie in een heap verminderen

Als u de fragmentatie van een heap wilt verminderen, maakt u een geclusterde index in de tabel en zet u de index neer. Hiermee worden de gegevens opnieuw gedistribueerd terwijl de geclusterde index wordt gemaakt. Dit maakt het ook zo optimaal mogelijk, gezien de verdeling van vrije ruimte die beschikbaar is in de database. Wanneer de geclusterde index vervolgens wordt verwijderd om de heap opnieuw te maken, worden de gegevens niet verplaatst en blijven ze optimaal in positie. Zie CREATE INDEX en DROP INDEXvoor meer informatie over het uitvoeren van deze bewerkingen.

Voorzichtigheid

Als u een geclusterde index in een tabel maakt en neervalt, worden alle niet-geclusterde indexen tweemaal opnieuw opgebouwd op die tabel.

Grote objectgegevens comprimeren

Standaard worden pagina's met grote objectgegevens (LOB) gecomprimeerd met de ALTER INDEX REORGANIZE-instructie. Omdat de toewijzing van LOB-pagina's niet ongedaan wordt gemaakt wanneer deze leeg zijn, kan het comprimeren van deze gegevens het gebruik van schijfruimte verbeteren als er veel LOB-gegevens worden verwijderd of als een LOB-kolom wordt verwijderd.

Als u een opgegeven geclusterde index opnieuw indeelt, worden alle LOB-kolommen gecomprimeerd die zich in de geclusterde index bevinden. Als u een niet-geclusterde index opnieuw indeelt, worden alle LOB-kolommen gecomprimeerd die niet-sleutelkolommen (opgenomen) in de index zijn. Wanneer ALL is opgegeven in de instructie, worden alle indexen die zijn gekoppeld aan de opgegeven tabel of weergave opnieuw ingedeeld. Daarnaast worden alle LOB-kolommen die zijn gekoppeld aan de geclusterde index, onderliggende tabel of niet-geclusterde index met opgenomen kolommen gecomprimeerd.

Gebruik van schijfruimte evalueren

De kolom avg_page_space_used_in_percent geeft de volledige pagina aan. Om optimaal schijfruimtegebruik te bereiken, moet deze waarde dicht bij 100 procent liggen voor een index die niet veel willekeurige invoegingen heeft. Een index met veel willekeurige invoegingen en zeer volledige pagina's heeft echter een verhoogd aantal paginasplitsingen. Dit veroorzaakt meer fragmentatie. Om paginasplitsingen te verminderen, moet de waarde daarom kleiner zijn dan 100 procent. Als u een index opnieuw bouwt met de opgegeven optie FILLFACTOR, kan de pagina volheid worden gewijzigd zodat het querypatroon in de index past. Zie Vulfactor opgeven voor een index-voor meer informatie over vulfactor. ALTER INDEX REORGANIZE compacteert ook een index door pagina's te vullen met de FILLFACTOR die voor het laatst is opgegeven. Hierdoor wordt de waarde in avg_space_used_in_percent verhoogd. ALTER INDEX REORGANIZE kan de volledige pagina niet verminderen. In plaats daarvan moet een index opnieuw worden opgebouwd.

Indexfragmenten evalueren

Een fragment bestaat uit fysiek opeenvolgende bladpagina's in hetzelfde bestand voor een toewijzingseenheid. Een index heeft ten minste één fragment. De maximumfragmenten die een index kan hebben, zijn gelijk aan het aantal pagina's in het bladniveau van de index. Grotere fragmenten betekenen dat minder schijf-I/O vereist is om hetzelfde aantal pagina's te lezen. Hoe groter de avg_fragment_size_in_pages waarde, hoe beter de scanprestaties van het bereik. De avg_fragment_size_in_pages- en avg_fragmentation_in_percent-waarden zijn omgekeerd evenredig met elkaar. Daarom moet het herbouwen of herorganiseren van een index de hoeveelheid fragmentatie verminderen en de fragmentgrootte vergroten.

Beperkingen

Retourneert geen gegevens voor geclusterde columnstore-indexen.

Machtigingen

Hiervoor zijn de volgende machtigingen vereist:

• CONTROL machtiging voor het opgegeven object in de database.

• VIEW DATABASE STATE of VIEW DATABASE PERFORMANCE STATE -machtiging (SQL Server 2022) voor het retourneren van informatie over alle objecten in de opgegeven database met behulp van het object-jokerteken @object_id = NULL.

• VIEW SERVER STATE- of VIEW SERVER PERFORMANCE STATE -machtiging (SQL Server 2022) voor het retourneren van gegevens over alle databases met behulp van het jokerteken van de database @database_id = NULL.

Als u VIEW DATABASE STATE verleent, kunnen alle objecten in de database worden geretourneerd, ongeacht eventuele CONTROL machtigingen die zijn geweigerd voor specifieke objecten.

Als u VIEW DATABASE STATE weigert, kunnen alle objecten in de database worden geretourneerd, ongeacht eventuele CONTROL machtigingen die zijn verleend voor specifieke objecten. Wanneer het jokerteken van de database @database_id = NULL is opgegeven, wordt de database ook weggelaten.

Zie Systeem dynamische beheerweergavenvoor meer informatie.

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina.

Een. Retourneert informatie over een opgegeven tabel

In het volgende voorbeeld worden grootte- en fragmentatiestatistieken geretourneerd voor alle indexen en partities van de Person.Address tabel. De scanmodus is ingesteld op LIMITED voor de beste prestaties en om de statistieken te beperken die worden geretourneerd. Voor het uitvoeren van deze query is minimaal CONTROL machtiging voor de Person.Address tabel vereist.

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. Informatie over een heap retourneren

In het volgende voorbeeld worden alle statistieken voor de heap-dbo.DatabaseLog in de AdventureWorks2022-database geretourneerd. Omdat de tabel LOB-gegevens bevat, wordt er een rij geretourneerd voor de LOB_DATA-toewijzingseenheid, naast de rij die wordt geretourneerd voor de IN_ROW_ALLOCATION_UNIT die de gegevenspagina's van de heap opslaat. Voor het uitvoeren van deze query is minimaal CONTROL machtiging voor de dbo.DatabaseLog tabel vereist.

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. Informatie retourneren voor alle databases

In het volgende voorbeeld worden alle statistieken voor alle tabellen en indexen in het exemplaar van SQL Server geretourneerd door het jokerteken op te geven NULL voor alle parameters. Voor het uitvoeren van deze query is de machtiging VIEW SERVER STATE vereist.

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

D. Gebruik sys.dm_db_index_physical_stats in een script om indexen opnieuw te bouwen of opnieuw te ordenen

In het volgende voorbeeld worden alle partities in een database met een gemiddelde fragmentatie van meer dan 10 procent automatisch opnieuw georganiseerd of opnieuw opgebouwd. Voor het uitvoeren van deze query is de machtiging VIEW DATABASE STATE vereist. In dit voorbeeld wordt DB_ID opgegeven als de eerste parameter zonder een databasenaam op te geven.

-- 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. Gebruik sys.dm_db_index_physical_stats om het aantal pagina's weer te geven dat is gecomprimeerd

In het volgende voorbeeld ziet u hoe u het totale aantal pagina's kunt weergeven en vergelijken met de pagina's die zijn gecomprimeerd met rijen en pagina's. Deze informatie kan worden gebruikt om het voordeel te bepalen dat compressie biedt voor een index of tabel.

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. Gebruik sys.dm_db_index_physical_stats in de modus SAMPLED

In het volgende voorbeeld ziet u hoe SAMPLED modus een benadering retourneert die verschilt van de DETAILED-modusresultaten.

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. Query's uitvoeren op servicebrokerwachtrijen voor indexfragmentatie

Van toepassing op: SQL Server 2016 (13.x) en latere versies

In het volgende voorbeeld ziet u hoe u query's uitvoert op serverbrokerwachtrijen voor fragmentatie.

--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);

Verwante inhoud

• Systeem dynamische beheerweergaven
• indexgerelateerde dynamische beheerweergaven en -functies (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 verwijzing (Database Engine)