sys.dm_db_index_operational_stats (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL Database Azure SQL Managed Instance
Mengembalikan I/O tingkat bawah saat ini, penguncian, latching, dan aktivitas metode akses untuk setiap partisi tabel atau indeks dalam database.
Indeks yang dioptimalkan memori tidak muncul di DMV ini.
Catatan
sys.dm_db_index_operational_stats tidak mengembalikan informasi tentang indeks yang dioptimalkan memori. Untuk informasi tentang penggunaan indeks yang dioptimalkan memori, lihat sys.dm_db_xtp_index_stats (Transact-SQL).
Sintaks
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 }
)
Argumen
database_id | NULL | 0 | DEFAULT
ID database. database_id kecil. Input yang valid adalah nomor ID database, NULL, 0, atau DEFAULT. Defaultnya adalah 0. NULL, 0, dan DEFAULT adalah nilai yang setara dalam konteks ini.
Tentukan NULL untuk mengembalikan informasi untuk semua database dalam instans SQL Server. Jika Anda menentukan NULL untuk database_id, Anda juga harus menentukan NULL untuk object_id, index_id, dan partition_number.
Fungsi bawaan DB_ID dapat ditentukan.
object_id | NULL | 0 | DEFAULT
ID objek tabel atau lihat indeks aktif. object_id adalah int.
Input yang valid adalah nomor ID tabel dan tampilan, NULL, 0, atau DEFAULT. Defaultnya adalah 0. NULL, 0, dan DEFAULT adalah nilai yang setara dalam konteks ini.
Tentukan NULL untuk mengembalikan informasi cache untuk semua tabel dan tampilan dalam database yang ditentukan. Jika Anda menentukan NULL untuk object_id, Anda juga harus menentukan NULL untuk index_id dan partition_number.
index_id | 0 | NULL | -1 | DEFAULT
ID indeks. index_id int. Input yang valid adalah nomor ID indeks, 0 jika object_id adalah heap, NULL, -1, atau DEFAULT. Defaultnya adalah -1, NULL, -1, dan DEFAULT adalah nilai yang setara dalam konteks ini.
Tentukan NULL untuk mengembalikan informasi cache untuk semua indeks untuk tabel atau tampilan dasar. Jika Anda menentukan NULL untuk index_id, Anda juga harus menentukan NULL untuk partition_number.
partition_number | NULL | 0 | DEFAULT
Nomor partisi dalam objek. partition_number int. Input yang valid adalah partion_number indeks atau timbunan, NULL, 0, atau DEFAULT. Defaultnya adalah 0. NULL, 0, dan DEFAULT adalah nilai yang setara dalam konteks ini.
Tentukan NULL untuk mengembalikan informasi cache untuk semua partisi indeks atau timbunan.
partition_number berbasis 1. Indeks atau tumpukan yang tidak dipartisi telah partition_number diatur ke 1.
Tabel Dikembalikan
| Nama kolom | Jenis data | Deskripsi |
|---|---|---|
| database_id | smallint | ID Database. Di Azure SQL Database, nilainya unik dalam satu database atau kumpulan elastis, tetapi tidak dalam server logis. |
| object_id | int | ID tabel atau tampilan. |
| index_id | int | ID indeks atau timbunan. 0 = Timbunan |
| partition_number | int | Nomor partisi berbasis 1 dalam indeks atau timbunan. |
| hobt_id | bigint | Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru, Azure SQL Database. ID timbunan data atau set baris pohon B yang melacak data internal untuk indeks penyimpan kolom. NULL - ini bukan kumpulan baris penyimpan kolom internal. Untuk detail selengkapnya, lihat sys.internal_partitions (Transact-SQL) |
| leaf_insert_count | bigint | Jumlah kumulatif sisipan tingkat daun. |
| leaf_delete_count | bigint | Jumlah kumulatif penghapusan tingkat daun. leaf_delete_count hanya dinaikkan untuk rekaman yang dihapus yang tidak ditandai sebagai hantu terlebih dahulu. Untuk rekaman yang dihapus yang dihantui terlebih dahulu, leaf_ghost_count dinaikkan sebagai gantinya. |
| leaf_update_count | bigint | Jumlah kumulatif pembaruan tingkat daun. |
| leaf_ghost_count | bigint | Jumlah kumulatif baris tingkat daun yang ditandai sebagai dihapus, tetapi belum dihapus. Jumlah ini tidak termasuk rekaman yang segera dihapus tanpa ditandai sebagai hantu. Baris ini dihapus oleh utas pembersihan pada interval yang ditetapkan. Nilai ini tidak termasuk baris yang dipertahankan, karena transaksi isolasi rekam jepret yang luar biasa. |
| nonleaf_insert_count | bigint | Jumlah kumulatif sisipan di atas tingkat daun. 0 = Timbunan atau penyimpan kolom |
| nonleaf_delete_count | bigint | Jumlah penghapusan kumulatif di atas tingkat daun. 0 = Timbunan atau penyimpan kolom |
| nonleaf_update_count | bigint | Jumlah pembaruan kumulatif di atas tingkat daun. 0 = Timbunan atau penyimpan kolom |
| leaf_allocation_count | bigint | Jumlah kumulatif alokasi halaman tingkat daun dalam indeks atau timbunan. Untuk indeks, alokasi halaman sesuai dengan pemisahan halaman. |
| nonleaf_allocation_count | bigint | Jumlah kumulatif alokasi halaman yang disebabkan oleh pemisahan halaman di atas tingkat daun. 0 = Timbunan atau penyimpan kolom |
| leaf_page_merge_count | bigint | Jumlah kumulatif penggabungan halaman pada tingkat daun. Selalu 0 untuk indeks penyimpan kolom. |
| nonleaf_page_merge_count | bigint | Jumlah kumulatif penggabungan halaman di atas tingkat daun. 0 = Timbunan atau penyimpan kolom |
| range_scan_count | bigint | Jumlah kumulatif pemindaian rentang dan tabel dimulai pada indeks atau timbunan. |
| singleton_lookup_count | bigint | Jumlah kumulatif pengambilan baris tunggal dari indeks atau timbunan. |
| forwarded_fetch_count | bigint | Jumlah baris yang diambil melalui rekaman penerusan. 0 = Indeks |
| lob_fetch_in_pages | bigint | Jumlah kumulatif halaman objek besar (LOB) yang diambil dari unit alokasi LOB_DATA. Halaman ini berisi data yang disimpan dalam kolom jenis teks, ntext, gambar, varchar(max), nvarchar(max), varbinary(max), dan xml. Untuk informasi selengkapnya, lihat Jenis Data (Transact-SQL). |
| lob_fetch_in_bytes | bigint | Jumlah kumulatif byte data LOB yang diambil. |
| lob_orphan_create_count | bigint | Jumlah kumulatif nilai LOB yatim piatu yang dibuat untuk operasi massal. 0 = Indeks non-klusster |
| lob_orphan_insert_count | bigint | Jumlah kumulatif nilai LOB yatim piatu yang dimasukkan selama operasi massal. 0 = Indeks non-klusster |
| row_overflow_fetch_in_pages | bigint | Jumlah kumulatif halaman data luapan baris yang diambil dari unit alokasi ROW_OVERFLOW_DATA. Halaman ini berisi data yang disimpan dalam kolom jenis varchar(n), nvarchar(n), varbinary(n), dan sql_variant yang telah didorong dari baris. |
| row_overflow_fetch_in_bytes | bigint | Jumlah kumulatif byte data luapan baris yang diambil. |
| column_value_push_off_row_count | bigint | Jumlah kumulatif nilai kolom untuk data LOB dan data luapan baris yang didorong dari baris untuk membuat baris yang disisipkan atau diperbarui pas dalam halaman. |
| column_value_pull_in_row_count | bigint | Jumlah kumulatif nilai kolom untuk data LOB dan data luapan baris yang ditarik berturut-turut. Ini terjadi ketika operasi pembaruan membebaskan ruang dalam rekaman dan memberikan kesempatan untuk menarik satu atau beberapa nilai off-row dari unit alokasi LOB_DATA atau ROW_OVERFLOW_DATA ke unit alokasi IN_ROW_DATA. |
| row_lock_count | bigint | Jumlah kumulatif kunci baris yang diminta. |
| row_lock_wait_count | bigint | Berapa kali Mesin Database menunggu pada kunci baris. |
| row_lock_wait_in_ms | bigint | Jumlah total milidetik Mesin Database menunggu pada kunci baris. |
| page_lock_count | bigint | Jumlah kumulatif kunci halaman yang diminta. |
| page_lock_wait_count | bigint | Berapa kali Mesin Database menunggu pada kunci halaman. |
| page_lock_wait_in_ms | bigint | Jumlah total milidetik Mesin Database yang menunggu pada kunci halaman. |
| index_lock_promotion_attempt_count | bigint | Berapa kali Mesin Database mencoba meningkatkan kunci. |
| index_lock_promotion_count | bigint | Jumlah kumulatif kali Mesin Database meningkatkan kunci. |
| page_latch_wait_count | bigint | Berapa kali Mesin Database menunggu, karena ketidakcocokan kait. |
| page_latch_wait_in_ms | bigint | Jumlah kumulatif milidetik mesin database menunggu, karena ketidakcocokan kait. |
| page_io_latch_wait_count | bigint | Berapa kali Mesin Database menunggu pada kait halaman I/O. |
| page_io_latch_wait_in_ms | bigint | Jumlah kumulatif milidetik Mesin Database menunggu pada kait I/O halaman. |
| tree_page_latch_wait_count | bigint | Subset page_latch_wait_count yang hanya menyertakan halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom. |
| tree_page_latch_wait_in_ms | bigint | Subset page_latch_wait_in_ms yang hanya menyertakan halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom. |
| tree_page_io_latch_wait_count | bigint | Subset page_io_latch_wait_count yang hanya menyertakan halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom. |
| tree_page_io_latch_wait_in_ms | bigint | Subset page_io_latch_wait_in_ms yang hanya menyertakan halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom. |
| page_compression_attempt_count | bigint | Jumlah halaman yang dievaluasi untuk pemadatan tingkat HALAMAN untuk partisi tertentu dari tabel, indeks, atau tampilan terindeks. Menyertakan halaman yang tidak dikompresi karena penghematan signifikan tidak dapat dicapai. Selalu 0 untuk indeks penyimpan kolom. |
| page_compression_success_count | bigint | Jumlah halaman data yang dikompresi dengan menggunakan pemadatan HALAMAN untuk partisi tertentu dari tabel, indeks, atau tampilan terindeks. Selalu 0 untuk indeks penyimpan kolom. |
Catatan
Dokumentasi menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.
Keterangan
Objek manajemen dinamis ini tidak menerima parameter yang berkorelasi dari CROSS APPLY dan OUTER APPLY.
Anda dapat menggunakan sys.dm_db_index_operational_stats untuk melacak lamanya waktu pengguna harus menunggu untuk membaca atau menulis ke tabel, indeks, atau partisi, dan mengidentifikasi tabel atau indeks yang mengalami aktivitas I/O atau hot spot yang signifikan.
Gunakan kolom berikut untuk mengidentifikasi area ketidakcocokan.
Untuk menganalisis pola akses umum ke tabel atau partisi indeks, gunakan kolom ini:
leaf_insert_count
leaf_delete_count
leaf_update_count
leaf_ghost_count
range_scan_count
singleton_lookup_count
Untuk mengidentifikasi ketidakcocokan kait dan penguncian, gunakan kolom ini:
page_latch_wait_count dan page_latch_wait_in_ms
Kolom ini menunjukkan apakah ada ketidakcocokan kait pada indeks atau timbunan, dan pentingnya pertikaian.
row_lock_count dan page_lock_count
Kolom ini menunjukkan berapa kali Mesin Database mencoba memperoleh kunci baris dan halaman.
row_lock_wait_in_ms dan page_lock_wait_in_ms
Kolom-kolom ini menunjukkan apakah ada ketidakcocokan kunci pada indeks atau timbunan, dan pentingnya pertikaian.
Untuk menganalisis statistik I/Os fisik pada indeks atau partisi timbunan
page_io_latch_wait_count dan page_io_latch_wait_in_ms
Kolom ini menunjukkan apakah I/Os fisik dikeluarkan untuk membawa indeks atau halaman timbunan ke dalam memori dan berapa banyak I/Os yang dikeluarkan.
Keterangan Kolom
Nilai dalam lob_orphan_create_count dan lob_orphan_insert_count harus selalu sama.
Nilai dalam kolom lob_fetch_in_pages dan lob_fetch_in_bytes bisa lebih besar dari nol untuk indeks nol yang berisi satu atau beberapa kolom LOB sebagai kolom yang disertakan. Untuk informasi selengkapnya, lihat Membuat Indeks dengan Kolom yang Disertakan. Demikian pula, nilai dalam kolom row_overflow_fetch_in_pages dan row_overflow_fetch_in_bytes bisa lebih besar dari 0 untuk indeks non-klusster jika indeks berisi kolom yang dapat didorong dari baris.
Cara penghitung dalam Metadata Cache diatur ulang
Data yang dikembalikan oleh sys.dm_db_index_operational_stats hanya ada selama objek cache metadata yang mewakili tumpukan atau indeks tersedia. Data ini tidak persisten atau konsisten secara transaksional. Ini berarti Anda tidak dapat menggunakan penghitung ini untuk menentukan apakah indeks telah digunakan atau tidak, atau kapan indeks terakhir digunakan. Untuk informasi tentang hal ini, lihat sys.dm_db_index_usage_stats (Transact-SQL).
Nilai untuk setiap kolom diatur ke nol setiap kali metadata untuk tumpukan atau indeks dibawa ke dalam cache metadata dan statistik diakumulasikan hingga objek cache dihapus dari cache metadata. Oleh karena itu, timbunan atau indeks aktif kemungkinan akan selalu memiliki metadatanya dalam cache, dan jumlah kumulatif dapat mencerminkan aktivitas sejak instans SQL Server terakhir kali dimulai. Metadata untuk timbunan atau indeks yang kurang aktif akan bergerak masuk dan keluar dari cache saat digunakan. Akibatnya, mungkin atau mungkin tidak memiliki nilai yang tersedia. Menghilangkan indeks akan menyebabkan statistik yang sesuai dihapus dari memori dan tidak lagi dilaporkan oleh fungsi. Operasi DDL lainnya terhadap indeks dapat menyebabkan nilai statistik diatur ulang ke nol.
Menggunakan fungsi sistem untuk menentukan nilai parameter
Anda dapat menggunakan fungsi Transact-SQL DB_ID dan OBJECT_ID untuk menentukan nilai untuk parameter database_id dan object_id . Namun, meneruskan nilai yang tidak valid untuk fungsi-fungsi ini dapat menyebabkan hasil yang tidak diinginkan. Selalu pastikan bahwa ID yang valid dikembalikan saat Anda menggunakan DB_ID atau OBJECT_ID. Untuk informasi selengkapnya, lihat bagian Keterangan di sys.dm_db_index_physical_stats (Transact-SQL).
Izin
Memerlukan izin berikut:
CONTROLizin pada objek yang ditentukan dalam databaseVIEW DATABASE STATEatauVIEW DATABASE PERFORMANCE STATEizin (SQL Server 2022) untuk mengembalikan informasi tentang semua objek dalam database yang ditentukan, dengan menggunakan kartubebas objek @object_id = NULLVIEW SERVER STATEVIEW SERVER PERFORMANCE STATE(SQL Server 2022) izin untuk mengembalikan informasi tentang semua database, dengan menggunakan kartubebas database @database_id = NULL
Pemberian VIEW DATABASE STATE izin memungkinkan semua objek dalam database dikembalikan, terlepas dari izin CONTROL apa pun yang ditolak pada objek tertentu.
Menolak VIEW DATABASE STATE melarang semua objek dalam database untuk dikembalikan, terlepas dari izin CONTROL apa pun yang diberikan pada objek tertentu. Selain itu, ketika kartubebas @database_id=NULL database ditentukan, database dihilangkan.
Untuk informasi selengkapnya, lihat Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL).
Contoh
J. Mengembalikan informasi untuk tabel tertentu
Contoh berikut mengembalikan informasi untuk semua indeks dan partisi Person.Address tabel dalam database AdventureWorks2022. Menjalankan kueri ini memerlukan, minimal, izin CONTROL pada Person.Address tabel.
Penting
Saat Anda menggunakan fungsi Transact-SQL DB_ID dan OBJECT_ID untuk mengembalikan nilai parameter, selalu pastikan bahwa ID yang valid dikembalikan. Jika database atau nama objek tidak dapat ditemukan, seperti ketika tidak ada atau salah dieja, kedua fungsi akan mengembalikan NULL. Fungsi sys.dm_db_index_operational_stats menafsirkan NULL sebagai nilai kartubebas yang menentukan semua database atau semua objek. Karena ini bisa menjadi operasi yang tidak disengaja, contoh di bagian ini menunjukkan cara aman untuk menentukan DATABASE dan ID objek.
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. Mengembalikan informasi untuk semua tabel dan indeks
Contoh berikut mengembalikan informasi untuk semua tabel dan indeks dalam instans SQL Server. Menjalankan kueri ini memerlukan izin TAMPILKAN STATUS SERVER.
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Lihat Juga
Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL)
Tampilan dan Fungsi Manajemen Dinamis Terkait Indeks (Transact-SQL)
Monitor dan Selaraskan Kinerja
sys.dm_db_index_physical_stats (T-SQL)
sys.dm_db_index_usage_stats (T-SQL)
sys.dm_os_latch_stats (T-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (T-SQL)
sys.indexes (Transact-SQL)