sys.dm_db_index_operational_stats (Transact-SQL)
van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Retourneert huidige I/O op lager niveau, vergrendelen, vergrendelen, vergrendelen en toegangsmethodeactiviteit voor elke partitie van een tabel of index in de database.
Indexen die zijn geoptimaliseerd voor geheugen, worden niet weergegeven in deze DMV.
Notitie
sys.dm_db_index_operational_stats retourneert geen informatie over indexen die zijn geoptimaliseerd voor geheugen. Zie sys.dm_db_xtp_index_stats (Transact-SQL)voor informatie over het gebruik van indexen die zijn geoptimaliseerd voor geheugen.
Transact-SQL syntaxisconventies
Syntaxis
sys.dm_db_index_operational_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | 0 | NULL | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
)
Argumenten
database_id | NULL | 0 | VERSTEK
Id van de database. database_id is kleine. Geldige invoer is het id-nummer van een database, NULL, 0 of DEFAULT. De standaardwaarde is 0. NULL, 0 en DEFAULT zijn equivalente 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.
object_id | NULL | 0 | VERSTEK
Object-id van de tabel of weergave waarop de index is ingeschakeld. object_id is int.
Geldige invoer is het id-nummer van een tabel en weergave, NULL, 0 of DEFAULT. De standaardwaarde is 0. NULL, 0 en DEFAULT zijn equivalente waarden in deze context.
Geef NULL op om gegevens in de cache 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
Id van de index. index_id is int. Geldige invoer is het id-nummer van een index, 0 als object_id een heap, NULL, -1 of DEFAULT is. De standaardwaarde is -1, NULL, -1 en DEFAULT zijn equivalente waarden in deze context.
Geef NULL op om informatie in de cache op te geven 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
Partitienummer in het object. partition_number is int. Geldige invoer is de partition_number van een index of heap, NULL, 0 of DEFAULT. De standaardwaarde is 0. NULL, 0 en DEFAULT zijn equivalente waarden in deze context.
Geef NULL op om gegevens in de cache te retourneren voor alle partities van de index of heap.
partition_number is gebaseerd op 1. Een niet-gepartitioneerde index of heap heeft partition_number ingesteld op 1.
Tabel geretourneerd
| Kolomnaam | Gegevenstype | Beschrijving |
|---|---|---|
| database_id | kleine | Database-id. In Azure SQL Database zijn de waarden uniek binnen één database of een elastische pool, maar niet binnen een logische server. |
| object_id | Id van de tabel of weergave. | |
| index_id | Id van de index of heap. 0 = Heap |
|
| partition_number | Partitienummer op basis van 1 in de index of heap. | |
| hobt_id | grote |
Van toepassing op: SQL Server 2016 (13.x) en latere versies, Azure SQL Database. Id van de gegevens heap- of B-boomstructuurrijset waarmee interne gegevens voor een columnstore-index worden bijgehouden. NULL: dit is geen interne columnstore-rijenset. Zie sys.internal_partitions (Transact-SQL) voor meer informatie |
| leaf_insert_count | grote | Cumulatief aantal invoegingen op bladniveau. |
| leaf_delete_count | grote | Cumulatief aantal verwijderingen op bladniveau. leaf_delete_count wordt alleen verhoogd voor verwijderde records die niet als ghost zijn gemarkeerd. Voor verwijderde records die eerst worden ge ghosted, wordt leaf_ghost_count in plaats daarvan verhoogd. |
| leaf_update_count | grote | Cumulatief aantal updates op bladniveau. |
| leaf_ghost_count | grote | Cumulatief aantal rijen op bladniveau die zijn gemarkeerd als verwijderd, maar nog niet zijn verwijderd. Dit aantal bevat geen records die onmiddellijk worden verwijderd zonder gemarkeerd als ghost. Deze rijen worden verwijderd door een opschoningsthread met ingestelde intervallen. Deze waarde bevat geen rijen die worden bewaard vanwege een openstaande isolatietransactie voor momentopnamen. |
| nonleaf_insert_count | grote | Cumulatief aantal invoegingen boven het bladniveau. 0 = Heap of columnstore |
| nonleaf_delete_count | grote | Cumulatief aantal verwijderingen boven het bladniveau. 0 = Heap of columnstore |
| nonleaf_update_count | grote | Cumulatief aantal updates boven het bladniveau. 0 = Heap of columnstore |
| leaf_allocation_count | grote | Cumulatief aantal paginatoewijzingen op bladniveau in de index of heap. Voor een index komt een paginatoewijzing overeen met een paginasplitsing. |
| nonleaf_allocation_count | grote | Cumulatief aantal paginatoewijzingen dat wordt veroorzaakt door paginasplitsingen boven het bladniveau. 0 = Heap of columnstore |
| leaf_page_merge_count | grote | Cumulatief aantal paginasamenvoegingen op bladniveau. Altijd 0 voor columnstore-index. |
| nonleaf_page_merge_count | grote | Cumulatief aantal paginasamenvoegingen boven het bladniveau. 0 = Heap of columnstore |
| range_scan_count | grote | Het cumulatieve aantal bereik- en tabelscans is gestart op de index of heap. |
| singleton_lookup_count | grote | Cumulatief aantal opvragen van één rij uit de index of heap. |
| forwarded_fetch_count | grote | Het aantal rijen dat is opgehaald via een doorstuurrecord. 0 = Indexen |
| lob_fetch_in_pages | grote | Cumulatief aantal LOB-pagina's (large object) opgehaald uit de LOB_DATA toewijzingseenheid. Deze pagina's bevatten gegevens die zijn opgeslagen in kolommen van het type tekst, ntext, afbeelding, varchar(max), nvarchar(max), varbinary(max)en XML-. Zie gegevenstypen (Transact-SQL)voor meer informatie. |
| lob_fetch_in_bytes | grote | Cumulatief aantal opgehaalde LOB-gegevensbytes. |
| lob_orphan_create_count | grote | Cumulatief aantal zwevende LOB-waarden die zijn gemaakt voor bulkbewerkingen. 0 = Niet-geclusterde index |
| lob_orphan_insert_count | grote | Cumulatief aantal zwevende LOB-waarden ingevoegd tijdens bulkbewerkingen. 0 = Niet-geclusterde index |
| row_overflow_fetch_in_pages | grote | Cumulatief aantal rij-overloopgegevenspagina's die zijn opgehaald uit de ROW_OVERFLOW_DATA toewijzingseenheid. Deze pagina's bevatten gegevens die zijn opgeslagen in kolommen van het type varchar(n), nvarchar(n), varbinary(n)en sql_variant die buiten rij zijn gepusht. |
| row_overflow_fetch_in_bytes | grote | Cumulatief aantal opgehaalde gegevensbytes voor rij-overloop. |
| column_value_push_off_row_count | grote | Cumulatief aantal kolomwaarden voor LOB-gegevens en rij-overloopgegevens die buiten de rij worden gepusht om een ingevoegde of bijgewerkte rij binnen een pagina te plaatsen. |
| column_value_pull_in_row_count | grote | Cumulatief aantal kolomwaarden voor LOB-gegevens en rij-overloopgegevens die in rij worden opgehaald. Dit gebeurt wanneer een updatebewerking ruimte vrij maakt in een record en een mogelijkheid biedt om een of meer waarden uit de rij op te halen uit de LOB_DATA of ROW_OVERFLOW_DATA toewijzingseenheden naar de IN_ROW_DATA toewijzingseenheid. |
| row_lock_count | grote | Cumulatief aantal aangevraagde rijvergrendelingen. |
| row_lock_wait_count | grote | Cumulatief aantal keren dat de database-engine op een rijvergrendeling heeft gewacht. |
| row_lock_wait_in_ms | grote | Het totale aantal milliseconden dat de database-engine wachtte op een rijvergrendeling. |
| page_lock_count | grote | Cumulatief aantal aangevraagde paginavergrendelingen. |
| page_lock_wait_count | grote | Cumulatief aantal keren dat de database-engine op een paginavergrendeling heeft gewacht. |
| page_lock_wait_in_ms | grote | Het totale aantal milliseconden dat de database-engine op een paginavergrendeling heeft gewacht. |
| index_lock_promotion_attempt_count | grote | Cumulatief aantal keren dat de database-engine heeft geprobeerd vergrendelingen te escaleren. |
| index_lock_promotion_count | grote | Cumulatief aantal keren dat de database-engine vergrendelingen escaleerde. |
| page_latch_wait_count | grote | Cumulatief aantal keren dat de database-engine wachtte vanwege vergrendelingsconflicten. |
| page_latch_wait_in_ms | grote | Cumulatief aantal milliseconden dat de database-engine wachtte vanwege vergrendelingsconflicten. |
| page_io_latch_wait_count | grote | Cumulatief aantal keren dat de database-engine op een I/O-paginavergrendeling heeft gewacht. |
| page_io_latch_wait_in_ms | grote | Cumulatief aantal milliseconden dat de database-engine op een pagina-I/O-vergrendeling heeft gewacht. |
| tree_page_latch_wait_count | grote | Subset van page_latch_wait_count die alleen de pagina's van de B-structuur op het hoogste niveau bevat. Altijd 0 voor een heap- of columnstore-index. |
| tree_page_latch_wait_in_ms | grote | Subset van page_latch_wait_in_ms die alleen de pagina's van de B-structuur op het hoogste niveau bevat. Altijd 0 voor een heap- of columnstore-index. |
| tree_page_io_latch_wait_count | grote | Subset van page_io_latch_wait_count die alleen de pagina's van de B-structuur op het hoogste niveau bevat. Altijd 0 voor een heap- of columnstore-index. |
| tree_page_io_latch_wait_in_ms | grote | Subset van page_io_latch_wait_in_ms die alleen de pagina's van de B-structuur op het hoogste niveau bevat. Altijd 0 voor een heap- of columnstore-index. |
| page_compression_attempt_count | grote | Aantal pagina's dat is geëvalueerd voor compressie op PAGINAniveau voor specifieke partities van een tabel, index of geïndexeerde weergave. Bevat pagina's die niet zijn gecomprimeerd omdat aanzienlijke besparingen niet kunnen worden bereikt. Altijd 0 voor columnstore-index. |
| page_compression_success_count | grote | Het aantal gegevenspagina's dat is gecomprimeerd met behulp van PAGE-compressie voor specifieke partities van een tabel, index of geïndexeerde weergave. Altijd 0 voor columnstore-index. |
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
Dit dynamische beheerobject accepteert geen gecorreleerde parameters van CROSS APPLY en OUTER APPLY.
U kunt sys.dm_db_index_operational_stats gebruiken om de tijd bij te houden die gebruikers moeten wachten om te wachten op lezen of schrijven naar een tabel, index of partitie, en om de tabellen of indexen te identificeren die aanzienlijke I/O-activiteit of hotspots ondervinden.
Gebruik de volgende kolommen om conflictengebieden te identificeren.
Als u een gemeenschappelijk toegangspatroon wilt analyseren voor de tabel of indexpartitie, gebruikt u deze kolommen:
leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count
Gebruik de volgende kolommen om conflicten tussen loskoppelen en vergrendelen te identificeren:
page_latch_wait_count en page_latch_wait_in_ms
Deze kolommen geven aan of er sprake is van vergrendelingsconflicten op de index of heap, en de betekenis van het conflict.
row_lock_count en page_lock_count
Deze kolommen geven aan hoe vaak de database-engine heeft geprobeerd rij- en paginavergrendelingen te verkrijgen.
row_lock_wait_in_ms en page_lock_wait_in_ms
Deze kolommen geven aan of er vergrendelingsconflicten zijn op de index of heap en de betekenis van het conflict.
Statistieken van fysieke I/Os op een index of heap-partitie analyseren
page_io_latch_wait_count en page_io_latch_wait_in_ms
Deze kolommen geven aan of er fysieke I/Os zijn uitgegeven om de index of heap-pagina's in het geheugen te brengen en hoeveel I/Os zijn uitgegeven.
Kolom opmerkingen
De waarden in lob_orphan_create_count en lob_orphan_insert_count moeten altijd gelijk zijn.
De waarde in de kolommen lob_fetch_in_pages en lob_fetch_in_bytes kan groter zijn dan nul voor niet-geclusterde indexen die een of meer LOB-kolommen bevatten als opgenomen kolommen. Zie Indexen maken met opgenomen kolommenvoor meer informatie. Op dezelfde manier kan de waarde in de kolommen row_overflow_fetch_in_pages en row_overflow_fetch_in_bytes groter zijn dan 0 voor niet-geclusterde indexen als de index kolommen bevat die buiten de rij kunnen worden gepusht.
Hoe de tellers in de metagegevenscache opnieuw worden ingesteld
De gegevens die door sys.dm_db_index_operational_stats worden geretourneerd, bestaan alleen zolang het metagegevenscacheobject dat de heap of index vertegenwoordigt, beschikbaar is. Deze gegevens zijn niet permanent of transactioneel consistent. Dit betekent dat u deze tellers niet kunt gebruiken om te bepalen of een index al dan niet is gebruikt, of wanneer de index voor het laatst is gebruikt. Zie sys.dm_db_index_usage_stats (Transact-SQL)voor meer informatie hierover.
De waarden voor elke kolom worden ingesteld op nul wanneer de metagegevens voor de heap of index worden overgebracht naar de metagegevenscache en statistieken worden verzameld totdat het cacheobject wordt verwijderd uit de metagegevenscache. Daarom heeft een actieve heap of index waarschijnlijk altijd de metagegevens in de cache en kunnen de cumulatieve aantallen de activiteit weerspiegelen sinds het exemplaar van SQL Server voor het laatst is gestart. De metagegevens voor een minder actieve heap of index worden verplaatst naar en uit de cache terwijl deze wordt gebruikt. Als gevolg hiervan zijn er al dan niet waarden beschikbaar. Als u een index verwijdert, worden de bijbehorende statistieken uit het geheugen verwijderd en worden deze niet meer gerapporteerd door de functie. Andere DDL-bewerkingen op basis van de index kunnen ertoe leiden dat de waarde van de statistieken opnieuw wordt ingesteld op nul.
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. Zorg er altijd voor dat een geldige id wordt geretourneerd wanneer u DB_ID of OBJECT_ID gebruikt. Zie de sectie Opmerkingen in sys.dm_db_index_physical_stats (Transact-SQL)voor meer informatie.
Machtigingen
Hiervoor zijn de volgende machtigingen vereist:
CONTROLmachtiging voor het opgegeven object in de databaseVIEW DATABASE STATE- ofVIEW 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 = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE-machtiging (SQL Server 2022) voor het retourneren van gegevens over alle databases met behulp van het jokerteken @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, worden alle objecten in de database 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 dynamische beheerweergaven en -functies (Transact-SQL)voor meer informatie.
Voorbeelden
Een. Retourneert informatie voor een opgegeven tabel
In het volgende voorbeeld wordt informatie geretourneerd voor alle indexen en partities van de tabel Person.Address in de Database AdventureWorks2022. Voor het uitvoeren van deze query is minimaal CONTROL-machtiging voor Person.Address tabel vereist.
Belangrijk
Wanneer u de Transact-SQL functies gebruikt DB_ID en OBJECT_ID om een parameterwaarde te retourneren, moet u er altijd voor zorgen dat een geldige id wordt geretourneerd. Als de database- of objectnaam niet kan worden gevonden, bijvoorbeeld wanneer ze niet bestaan of onjuist zijn gespeld, retourneren beide functies NULL. De functie sys.dm_db_index_operational_stats interpreteert NULL als een jokertekenwaarde waarmee alle databases of alle objecten worden opgegeven. Omdat dit een onbedoelde bewerking kan zijn, laten de voorbeelden in deze sectie de veilige manier zien om database- en object-id's te bepalen.
DECLARE @db_id int;
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_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO
B. Informatie retourneren voor alle tabellen en indexen
In het volgende voorbeeld worden gegevens geretourneerd voor alle tabellen en indexen binnen het exemplaar van SQL Server. Voor het uitvoeren van deze query is de machtiging VIEW SERVER STATE vereist.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Zie ook
dynamische beheerweergaven en -functies (Transact-SQL)
indexgerelateerde dynamische beheerweergaven en -functies (Transact-SQL)
Prestaties bewaken en afstemmen op
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_os_latch_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)