sys.dm_db_index_operational_stats (Transact-SQL)
Dotyczy:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Zwraca bieżące operacje we/wy niższego poziomu, blokowanie, zatrzasanie i działanie metody dostępu dla każdej partycji tabeli lub indeksu w bazie danych.
Indeksy zoptymalizowane pod kątem pamięci nie są wyświetlane w tym widoku DMV.
Nuta
sys.dm_db_index_operational_stats nie zwraca informacji o indeksach zoptymalizowanych pod kątem pamięci. Aby uzyskać informacje o użyciu indeksu zoptymalizowanego pod kątem pamięci, zobacz sys.dm_db_xtp_index_stats (Transact-SQL).
Transact-SQL konwencje składni
Składnia
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 }
)
Argumenty
database_id | NULL | 0 | DOMYŚLNY
Identyfikator bazy danych. database_id jest smallint. Prawidłowe dane wejściowe to identyfikator bazy danych, wartości NULL, 0 lub DEFAULT. Wartość domyślna to 0. Wartości NULL, 0 i DEFAULT są wartościami równoważnymi w tym kontekście.
Określ wartość NULL, aby zwrócić informacje dla wszystkich baz danych w wystąpieniu programu SQL Server. W przypadku określenia wartości NULL dla database_idnależy również określić wartość NULL dla object_id, index_idi partition_number.
Można określić wbudowaną funkcję DB_ID.
object_id | NULL | 0 | DOMYŚLNY
Identyfikator obiektu tabeli lub widoku indeks jest włączony. object_id jest int.
Prawidłowe dane wejściowe to identyfikator tabeli i widoku, wartości NULL, 0 lub DEFAULT. Wartość domyślna to 0. Wartości NULL, 0 i DEFAULT są wartościami równoważnymi w tym kontekście.
Określ wartość NULL, aby zwrócić buforowane informacje dla wszystkich tabel i widoków w określonej bazie danych. W przypadku określenia wartości NULL dla object_idnależy również określić wartość NULL dla index_id i partition_number.
index_id | 0 | NULL | -1 | DOMYŚLNY
Identyfikator indeksu. index_id jest int. Prawidłowe dane wejściowe to identyfikator indeksu, 0, jeśli object_id jest stertą, wartością NULL, -1 lub WARTOŚCIĄ DOMYŚLNĄ. Wartość domyślna to -1, NULL, -1 i DEFAULT są równoważnymi wartościami w tym kontekście.
Określ wartość NULL, aby zwrócić buforowane informacje dla wszystkich indeksów dla tabeli podstawowej lub widoku. W przypadku określenia wartości NULL dla index_idnależy również określić wartość NULL dla partition_number.
partition_number | NULL | 0 | DOMYŚLNY
Numer partycji w obiekcie. partition_number jest int. Prawidłowe dane wejściowe to partition_number indeksu lub sterta, WARTOŚCI NULL, 0 lub DEFAULT. Wartość domyślna to 0. Wartości NULL, 0 i DEFAULT są wartościami równoważnymi w tym kontekście.
Określ wartość NULL, aby zwrócić buforowane informacje dla wszystkich partycji indeksu lub sterta.
partition_number jest oparty na 1. Indeks niepartycyjny lub sterta ma partition_number ustawioną na 1.
Zwracana tabela
| Nazwa kolumny | Typ danych | Opis |
|---|---|---|
| database_id | smallint | Identyfikator bazy danych. W usłudze Azure SQL Database wartości są unikatowe w ramach pojedynczej bazy danych lub elastycznej puli, ale nie w obrębie serwera logicznego. |
| object_id | Identyfikator tabeli lub widoku. | |
| index_id | Identyfikator indeksu lub sterta. 0 = Sterta |
|
| partition_number | 1 numer partycji w indeksie lub stercie. | |
| hobt_id | bigint |
Dotyczy: SQL Server 2016 (13.x) i nowsze wersje usługi Azure SQL Database. Identyfikator sterta danych lub zestawu wierszy drzewa B, który śledzi dane wewnętrzne dla indeksu magazynu kolumn. NULL — nie jest to wewnętrzny zestaw wierszy magazynu kolumn. Aby uzyskać więcej informacji, zobacz sys.internal_partitions (Transact-SQL) |
| leaf_insert_count | bigint | Skumulowana liczba wstawiania na poziomie liścia. |
| leaf_delete_count | bigint | Skumulowana liczba usuniętych na poziomie liścia. leaf_delete_count jest zwiększana tylko dla usuniętych rekordów, które nie są oznaczone jako duch pierwszy. W przypadku usuniętych rekordów, które są najpierw upiorne, leaf_ghost_count jest zwiększana. |
| leaf_update_count | bigint | Skumulowana liczba aktualizacji na poziomie liścia. |
| leaf_ghost_count | bigint | Skumulowana liczba wierszy na poziomie liścia, które są oznaczone jako usunięte, ale nie zostały jeszcze usunięte. Ta liczba nie obejmuje rekordów, które są natychmiast usuwane bez oznaczania jako duch. Te wiersze są usuwane przez wątek oczyszczania w ustalonych odstępach czasu. Ta wartość nie zawiera wierszy, które są zachowywane z powodu zaległej transakcji izolacji migawki. |
| nonleaf_insert_count | bigint | Skumulowana liczba wstawek powyżej poziomu liścia. 0 = Sterta lub magazyn kolumn |
| nonleaf_delete_count | bigint | Skumulowana liczba usuwań powyżej poziomu liścia. 0 = Sterta lub magazyn kolumn |
| nonleaf_update_count | bigint | Skumulowana liczba aktualizacji powyżej poziomu liścia. 0 = Sterta lub magazyn kolumn |
| leaf_allocation_count | bigint | Skumulowana liczba alokacji stron na poziomie liścia w indeksie lub stercie. W przypadku indeksu alokacja strony odpowiada podziałowi strony. |
| nonleaf_allocation_count | bigint | Skumulowana liczba alokacji stron spowodowanych podziałami stron powyżej poziomu liścia. 0 = Sterta lub magazyn kolumn |
| leaf_page_merge_count | bigint | Skumulowana liczba scalanych stron na poziomie liścia. Zawsze 0 dla indeksu magazynu kolumn. |
| nonleaf_page_merge_count | bigint | Skumulowana liczba stron scala się powyżej poziomu liścia. 0 = Sterta lub magazyn kolumn |
| range_scan_count | bigint | Skumulowana liczba skanowań zakresów i tabel rozpoczętych na indeksie lub stercie. |
| singleton_lookup_count | bigint | Skumulowana liczba pobierania pojedynczych wierszy z indeksu lub sterta. |
| forwarded_fetch_count | bigint | Liczba wierszy pobranych za pośrednictwem rekordu przekazującego. 0 = indeksy |
| lob_fetch_in_pages | bigint | Skumulowana liczba stron dużych obiektów (LOB) pobranych z jednostki alokacji LOB_DATA. Te strony zawierają dane przechowywane w kolumnach typu tekst, ntext, obrazu, varchar(max), nvarchar(max), varbinary(max)i xml. Aby uzyskać więcej informacji, zobacz Typy danych (Transact-SQL). |
| lob_fetch_in_bytes | bigint | Skumulowana liczba pobranych bajtów danych BIZNESOWYCH. |
| lob_orphan_create_count | bigint | Skumulowana liczba oddzielonych wartości LOB utworzonych dla operacji zbiorczych. 0 = indeks nieklastrowany |
| lob_orphan_insert_count | bigint | Skumulowana liczba oddzielonych wartości LOB wstawionych podczas operacji zbiorczych. 0 = indeks nieklastrowany |
| row_overflow_fetch_in_pages | bigint | Skumulowana liczba stron danych przepełnienia wiersza pobranych z jednostki alokacji ROW_OVERFLOW_DATA. Te strony zawierają dane przechowywane w kolumnach typu varchar(n), nvarchar(n), varbinary(n)i sql_variant, które zostały wypchnięte poza wiersz. |
| row_overflow_fetch_in_bytes | bigint | Skumulowana liczba pobranych bajtów danych przepełnienia wiersza. |
| column_value_push_off_row_count | bigint | Skumulowana liczba wartości kolumn dla danych BIZNESOWYCH i danych przepełnienia wierszy, które są wypychane poza wierszem, aby wstawić lub zaktualizować wiersz pasujący do strony. |
| column_value_pull_in_row_count | bigint | Skumulowana liczba wartości kolumn dla danych BIZNESOWYCH i danych przepełnienia wierszy, które są pobierane w wierszu. Dzieje się tak, gdy operacja aktualizacji zwalnia miejsce w rekordzie i umożliwia ściąganie co najmniej jednej wartości poza wierszem z LOB_DATA lub ROW_OVERFLOW_DATA jednostek alokacji do jednostki alokacji IN_ROW_DATA. |
| row_lock_count | bigint | Żądana skumulowana liczba żądań blokad wierszy. |
| row_lock_wait_count | bigint | Skumulowana liczba przypadków oczekiwania aparatu bazy danych na blokadę wiersza. |
| row_lock_wait_in_ms | bigint | Łączna liczba milisekund oczekiwania aparatu bazy danych na blokadę wiersza. |
| page_lock_count | bigint | Żądana skumulowana liczba żądań blokad stron. |
| page_lock_wait_count | bigint | Skumulowana liczba przypadków oczekiwania aparatu bazy danych na blokadę strony. |
| page_lock_wait_in_ms | bigint | Łączna liczba milisekund oczekiwania aparatu bazy danych na blokadę strony. |
| index_lock_promotion_attempt_count | bigint | Skumulowana liczba prób eskalacji blokad przez aparat bazy danych. |
| index_lock_promotion_count | bigint | Skumulowana liczba przypadków eskalacji blokad przez aparat bazy danych. |
| page_latch_wait_count | bigint | Skumulowana liczba przypadków oczekiwania aparatu bazy danych z powodu rywalizacji o zatrzasanie. |
| page_latch_wait_in_ms | bigint | Skumulowana liczba milisekund oczekiwania aparatu bazy danych z powodu rywalizacji o zatrzasanie. |
| page_io_latch_wait_count | bigint | Skumulowana liczba zatrzaśnięć aparatu bazy danych na stronie we/wy. |
| page_io_latch_wait_in_ms | bigint | Skumulowana liczba milisekund oczekiwania aparatu bazy danych na zatrzask we/wy strony. |
| tree_page_latch_wait_count | bigint | Podzbiór page_latch_wait_count, który zawiera tylko strony drzewa B najwyższego poziomu. Zawsze 0 dla stosu lub indeksu magazynu kolumn. |
| tree_page_latch_wait_in_ms | bigint | Podzbiór page_latch_wait_in_ms, który zawiera tylko strony drzewa B najwyższego poziomu. Zawsze 0 dla stosu lub indeksu magazynu kolumn. |
| tree_page_io_latch_wait_count | bigint | Podzbiór page_io_latch_wait_count, który zawiera tylko strony drzewa B najwyższego poziomu. Zawsze 0 dla stosu lub indeksu magazynu kolumn. |
| tree_page_io_latch_wait_in_ms | bigint | Podzbiór page_io_latch_wait_in_ms, który zawiera tylko strony drzewa B najwyższego poziomu. Zawsze 0 dla stosu lub indeksu magazynu kolumn. |
| page_compression_attempt_count | bigint | Liczba stron, które zostały ocenione pod kątem kompresji na poziomie STRONY dla określonych partycji tabeli, indeksu lub widoku indeksowanego. Zawiera strony, które nie zostały skompresowane, ponieważ nie można osiągnąć znaczących oszczędności. Zawsze 0 dla indeksu magazynu kolumn. |
| page_compression_success_count | bigint | Liczba stron danych skompresowanych przy użyciu kompresji PAGE dla określonych partycji tabeli, indeksu lub widoku indeksowanego. Zawsze 0 dla indeksu magazynu kolumn. |
Nuta
W dokumentacji jest zwykle używany termin B-tree w odniesieniu do indeksów. W indeksach magazynu wierszy aparat bazy danych implementuje drzewo B+ . Nie dotyczy to indeksów magazynu kolumn ani indeksów w tabelach zoptymalizowanych pod kątem pamięci. Aby uzyskać więcej informacji, zobacz architektura usługi SQL Server i architektura indeksu usługi Azure SQL oraz przewodnik projektowania.
Uwagi
Ten obiekt dynamicznego zarządzania nie akceptuje skorelowanych parametrów z CROSS APPLY i OUTER APPLY.
Możesz użyć sys.dm_db_index_operational_stats do śledzenia czasu, przez jaki użytkownicy muszą czekać na odczyt lub zapis w tabeli, indeksie lub partycji oraz zidentyfikować tabele lub indeksy, które napotykają znaczące działania we/wy lub punkty aktywne.
Użyj poniższych kolumn, aby zidentyfikować obszary rywalizacji.
Aby przeanalizować wspólny wzorzec dostępu do tabeli lub partycji indeksu, użyj następujących kolumn:
leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count
Aby zidentyfikować zatrzaśanie i blokowanie rywalizacji, użyj następujących kolumn:
page_latch_wait_count i page_latch_wait_in_ms
Te kolumny wskazują, czy istnieje rywalizacja o zatrzasanie indeksu lub sterta oraz znaczenie rywalizacji.
row_lock_count i page_lock_count
Te kolumny wskazują, ile razy aparat bazy danych próbował uzyskać blokady wierszy i stron.
row_lock_wait_in_ms i page_lock_wait_in_ms
Te kolumny wskazują, czy istnieje rywalizacja o blokadę indeksu, czy sterta oraz znaczenie rywalizacji.
Aby przeanalizować statystyki fizycznych operacji we/wy na partycji indeksu lub sterty
page_io_latch_wait_count i page_io_latch_wait_in_ms
Te kolumny wskazują, czy fizyczne we/wy zostały wystawione w celu przeniesienia indeksu lub stron sterta do pamięci oraz liczby operacji we/wy.
Uwagi w kolumnie
Wartości w lob_orphan_create_count i lob_orphan_insert_count powinny być zawsze równe.
Wartość w kolumnach lob_fetch_in_pages i lob_fetch_in_bytes może być większa niż zero dla indeksów nieklastrowanych, które zawierają co najmniej jedną kolumnę LOB jako dołączone kolumny. Aby uzyskać więcej informacji, zobacz Tworzenie indeksów z dołączonymi kolumnami. Podobnie wartość w kolumnach row_overflow_fetch_in_pages i row_overflow_fetch_in_bytes może być większa niż 0 dla indeksów nieklastrowanych, jeśli indeks zawiera kolumny, które można wypchnąć poza wiersz.
Jak są resetowane liczniki w pamięci podręcznej metadanych
Dane zwrócone przez sys.dm_db_index_operational_stats istnieją tylko tak długo, jak obiekt pamięci podręcznej metadanych reprezentujący stertę lub indeks jest dostępny. Te dane nie są trwałe ani transakcyjnie spójne. Oznacza to, że nie można użyć tych liczników, aby określić, czy indeks został użyty, czy nie, czy też kiedy indeks był ostatnio używany. Aby uzyskać informacje o tym, zobacz sys.dm_db_index_usage_stats (Transact-SQL).
Wartości dla każdej kolumny są ustawiane na zero za każdym razem, gdy metadane sterty lub indeksu zostaną przeniesione do pamięci podręcznej metadanych, a statystyki zostaną zebrane do momentu usunięcia obiektu pamięci podręcznej z pamięci podręcznej metadanych. W związku z tym aktywna sterta lub indeks prawdopodobnie zawsze będzie zawierać metadane w pamięci podręcznej, a skumulowane liczby mogą odzwierciedlać aktywność od czasu ostatniego uruchomienia wystąpienia programu SQL Server. Metadane dla mniej aktywnego sterta lub indeksu zostaną przeniesione do pamięci podręcznej i z niej w miarę jej użycia. W związku z tym może lub nie ma dostępnych wartości. Usunięcie indeksu spowoduje usunięcie odpowiednich statystyk z pamięci i nie będzie już raportowane przez funkcję. Inne operacje DDL względem indeksu mogą spowodować zresetowanie wartości statystyk do zera.
Używanie funkcji systemowych do określania wartości parametrów
Funkcji Transact-SQL można użyć DB_ID i OBJECT_ID, aby określić wartość parametrów database_id i object_id. Jednak przekazywanie wartości, które nie są prawidłowe dla tych funkcji, może spowodować niezamierzone wyniki. Zawsze upewnij się, że podczas używania DB_ID lub OBJECT_ID jest zwracany prawidłowy identyfikator. Aby uzyskać więcej informacji, zobacz sekcję Uwagi w sys.dm_db_index_physical_stats (Transact-SQL).
Uprawnienia
Wymaga następujących uprawnień:
CONTROLuprawnienia do określonego obiektu w bazie danychVIEW DATABASE STATElubVIEW DATABASE PERFORMANCE STATE(SQL Server 2022) uprawnienie do zwracania informacji o wszystkich obiektach w określonej bazie danych przy użyciu symbolu wieloznakowego obiektu @object_id = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) uprawnienie do zwracania informacji o wszystkich bazach danych przy użyciu symbolu wieloznacznych bazy danych @database_id = NULL
Udzielenie VIEW DATABASE STATE umożliwia zwracanie wszystkich obiektów w bazie danych, niezależnie od wszelkich uprawnień KONTROLI odmowy dla określonych obiektów.
Odmowa VIEW DATABASE STATE nie zezwala na zwracanie wszystkich obiektów w bazie danych, niezależnie od wszelkich uprawnień KONTROLI przyznanych dla określonych obiektów. Ponadto po określeniu symbolu wieloznacznych bazy danych @database_id=NULL baza danych zostanie pominięta.
Aby uzyskać więcej informacji, zobacz dynamiczne widoki zarządzania i funkcje (Transact-SQL).
Przykłady
A. Zwracanie informacji dla określonej tabeli
Poniższy przykład zwraca informacje dotyczące wszystkich indeksów i partycji tabeli Person.Address w bazie danych AdventureWorks2022. Wykonanie tego zapytania wymaga co najmniej uprawnienia CONTROL w Person.Address tabeli.
Ważny
Jeśli używasz funkcji Transact-SQL DB_ID i OBJECT_ID, aby zwrócić wartość parametru, zawsze upewnij się, że zwracany jest prawidłowy identyfikator. Jeśli nie można odnaleźć nazwy bazy danych lub obiektu, na przykład gdy nie istnieją lub są niepoprawnie napisane, obie funkcje zwracają wartość NULL. Funkcja sys.dm_db_index_operational_stats interpretuje wartość NULL jako wartość wieloznaczny określającą wszystkie bazy danych lub wszystkie obiekty. Ponieważ może to być operacja niezamierzona, przykłady w tej sekcji przedstawiają bezpieczny sposób określania identyfikatorów baz danych i obiektów.
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. Zwracanie informacji dla wszystkich tabel i indeksów
Poniższy przykład zwraca informacje dotyczące wszystkich tabel i indeksów w wystąpieniu programu SQL Server. Wykonanie tego zapytania wymaga uprawnienia WYŚWIETL STAN SERWERA.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Zobacz też
dynamiczne widoki zarządzania i funkcje (Transact-SQL)
dynamiczne widoki zarządzania i funkcje związane z indeksem (Transact-SQL)
Monitorowanie i dostrajanie pod kątem wydajności
sys.dm_db_index_physical_stats (Transact-SQL)
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)