sys.dm_db_index_operational_stats (T-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

Mengembalikan statistik akses, penguncian, dan kait data tingkat bawah untuk setiap partisi tabel atau indeks dalam database.

Konvensi sintaks Transact-SQL

Syntax

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 | NOL | 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 merupakan 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 | NOL | 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 merupakan nilai yang setara dalam konteks ini.

Tentukan NULL untuk mengembalikan informasi 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 | NOL | -1 | DEFAULT }

ID indeks. index_idint. Input yang valid adalah nomor ID indeks, 0 jika object_id adalah tumpukan, NULL, -1, atau DEFAULT. Defaultnya adalah -1. NULL, -1, dan DEFAULT merupakan nilai yang setara dalam konteks ini.

Tentukan NULL untuk mengembalikan informasi 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 | NOL | 0 | DEFAULT }

Nomor partisi dalam objek. partition_numberint . Input yang valid adalah partition_number indeks atau timbunan, , , atau . Defaultnya adalah 0. NULL, 0, dan DEFAULT merupakan nilai yang setara dalam konteks ini.

Tentukan NULL untuk mengembalikan informasi untuk semua partisi indeks atau tumpukan.

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. Untuk informasi selengkapnya, lihat sys.objects.
index_id int ID indeks atau timbunan. Untuk informasi selengkapnya, lihat sys.indexes.
partition_number int Nomor partisi berbasis 1 dalam indeks atau timbunan. Untuk informasi selengkapnya, lihat sys.partitions.
hobt_id bigint 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 informasi selengkapnya, lihat sys.internal_partitions.
leaf_insert_count bigint Jumlah kumulatif sisipan tingkat daun. Untuk informasi selengkapnya tentang tingkat indeks, lihat Arsitektur indeks dan panduan desain.
leaf_delete_count bigint Jumlah kumulatif penghapusan tingkat daun. leaf_delete_count hanya ditambahkan untuk catatan 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. Penghitungan ini tidak termasuk rekaman yang segera dihapus tanpa ditandai sebagai hantu. Utas pembersihan menghapus baris hantu pada interval yang ditetapkan. Nilai ini tidak termasuk baris hantu yang dipertahankan karena transaksi rekam jepret yang belum diselesaikan.
nonleaf_insert_count bigint Jumlah kumulatif sisipan di atas tingkat daun. Hanya berlaku untuk indeks pohon B. 0 untuk timbunan atau indeks penyimpan kolom.
nonleaf_delete_count bigint Jumlah penghapusan kumulatif di atas tingkat daun. Hanya berlaku untuk indeks pohon B. 0 untuk timbunan atau indeks penyimpan kolom.
nonleaf_update_count bigint Jumlah pembaruan kumulatif di atas tingkat daun. Hanya berlaku untuk indeks pohon B. 0 untuk timbunan atau indeks 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. Hanya berlaku untuk indeks pohon B. 0 untuk timbunan atau indeks 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. Hanya berlaku untuk indeks pohon B. 0 untuk timbunan atau indeks 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. Hanya berlaku untuk tumpukan, 0 untuk indeks pohon B.
lob_fetch_in_pages bigint Jumlah kumulatif halaman objek besar (LOB) yang diambil dari unit alokasi LOB_DATA . Halaman-halaman ini berisi data yang disimpan dalam kolom jenis text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, dan json. Untuk informasi selengkapnya, lihat Jenis data.
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. Berlaku untuk tumpukan dan indeks berkluster B-tree saja, 0 untuk indeks nonclustered dan columnstore.
lob_orphan_insert_count bigint Jumlah kumulatif nilai LOB yatim piatu yang dimasukkan selama operasi massal. Berlaku untuk tumpukan dan indeks berkluster B-tree saja, 0 untuk indeks nonclustered dan columnstore.
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 untuk baris besar.
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 LOB_DATA unit atau ROW_OVERFLOW_DATA alokasi ke IN_ROW_DATA unit alokasi.
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 untuk memperoleh kait.
page_latch_wait_in_ms bigint Jumlah kumulatif milidetik Mesin Database menunggu untuk memperoleh kait.
page_io_latch_wait_count bigint Frekuensi kumulatif Mesin Database menunggu pada halaman kait 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 yang page_latch_wait_count hanya mencakup halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom.
tree_page_latch_wait_in_ms bigint Subset yang page_latch_wait_in_ms hanya mencakup halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom.
tree_page_io_latch_wait_count bigint Subset yang page_io_latch_wait_count hanya mencakup halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom.
tree_page_io_latch_wait_in_ms bigint Subset yang page_io_latch_wait_in_ms hanya mencakup halaman pohon B tingkat atas. Selalu 0 untuk indeks timbunan atau penyimpan kolom.
page_compression_attempt_count bigint Jumlah halaman yang dievaluasi untuk PAGE kompresi tingkat untuk partisi tertentu dari tabel, indeks, atau tampilan yang diindeks. Termasuk halaman yang tidak dikompresi karena penghematan yang signifikan tidak dapat dicapai. Selalu 0 untuk indeks penyimpan kolom.
page_compression_success_count bigint Jumlah halaman data yang dikompresi dengan menggunakan PAGE kompresi untuk partisi tertentu dari tabel, indeks, atau tampilan yang diindeks. Selalu 0 untuk indeks penyimpan kolom.
version_generated_inrow bigint Jumlah kumulatif versi dalam baris dengan payload yang dihasilkan dalam tumpukan atau pohon B untuk operasi pembaruan, penggabungan, atau insert-over-ghost. Versi dalam baris menyimpan gambar baris lama (atau berbeda) langsung di baris, menghindari perjalanan ke penyimpanan versi. Jumlah ini adalah superset yang mencakup versi yang dihitung oleh insert_over_ghost_version_inrow. Untuk informasi selengkapnya tentang versi dalam baris dan di luar baris, lihat Ruang yang digunakan oleh penyimpanan versi persisten (PVS).
version_generated_offrow bigint Jumlah kumulatif versi yang didorong ke penyimpanan off-row untuk operasi heap, B-tree, atau LOB delete, update, merge, atau insert-over-ghost. Versi di luar baris dihasilkan ketika gambar baris lama tidak dapat disimpan dalam baris. Jumlah ini adalah superset yang mencakup versi yang dihitung oleh ghost_version_offrow dan insert_over_ghost_version_offrow.
ghost_version_inrow bigint Jumlah kumulatif kali penghapusan atau pembaruan (dilakukan sebagai penghapusan diikuti dengan sisipan) menandai baris yang ada sebagai hantu dengan informasi penerapan versi dalam baris. Versi dalam baris hanya menyimpan tanda waktu transaksi dan payload panjang nol, sehingga membatalkan penghapusan hanya memerlukan unghosting baris.
ghost_version_offrow bigint Jumlah kumulatif kali penghapusan atau pembaruan (dilakukan sebagai penghapusan diikuti dengan penyisipan) mendorong data kolom baris atau LOB yang ada ke penyimpanan di luar baris, meninggalkan stub di baris untuk informasi penerapan versi. Penghitung ini dinaikkan bersama-sama selama version_generated_offrow operasi hantu.
insert_over_ghost_version_inrow bigint Jumlah kumulatif versi dalam baris dengan payload yang dihasilkan untuk operasi insert-over-ghost B-tree. Insert-over-ghost terjadi ketika baris baru dimasukkan ke dalam slot rekaman yang sebelumnya dihantui, baik dari penghapusan eksplisit diikuti oleh sisipan, atau dari pembaruan atau penggabungan yang diimplementasikan sebagai penghapusan diikuti oleh sisipan. Penghitung ini adalah subset dari version_generated_inrow.
insert_over_ghost_version_offrow bigint Jumlah kumulatif kali baris hantu yang ada didorong ke penyimpanan off-row selama operasi insert-over-ghost B-tree, meninggalkan rintangan di baris yang baru dimasukkan untuk informasi penerapan versi. Penghitung ini adalah subset dari version_generated_offrow.
compaction_attempt_count bigint Jumlah kumulatif upaya pemadatan otomatis indeks. Untuk informasi selengkapnya, lihat Pemadatan indeks otomatis (pratinjau).
compaction_complete_count bigint Jumlah kumulatif pemadatan otomatis indeks yang telah selesai.
compaction_skip_count bigint Jumlah kumulatif pemadatan otomatis indeks yang dilewati. Untuk informasi selengkapnya tentang alasan lewati, lihat Menggunakan peristiwa yang diperpanjang untuk memantau statistik pemadatan.
compaction_ineligible_count bigint Jumlah kumulatif upaya pemadatan dilewati karena halaman tidak memenuhi syarat untuk pemadatan otomatis.
compaction_failure_count bigint Jumlah kumulatif upaya pemadatan yang gagal.
compaction_row_move_count bigint Jumlah kumulatif baris yang dipindahkan dari satu halaman ke halaman lain sebagai bagian dari pemadatan otomatis.
compaction_page_deallocation_count bigint Jumlah kumulatif halaman yang dibatalkan alokasi setelah memindahkan semua baris ke halaman lain.

Note

Dokumentasi biasanya menggunakan istilah pohon B ketika merujuk pada 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

Fungsi ini tidak mengembalikan informasi tentang indeks pada tabel yang dioptimalkan memori. Untuk informasi tentang indeks pada tabel yang dioptimalkan memori, lihat sys.dm_db_xtp_index_stats.

Fungsi ini tidak menerima parameter yang berkorelasi dari CROSS APPLY dan OUTER APPLY.

Anda dapat menggunakan sys.dm_db_index_operational_stats untuk melacak statistik operasi baca dan tulis data, dan kunci, latch halaman, dan statistik latch I/O halaman untuk tabel, indeks, atau partisi. Anda dapat mengidentifikasi tabel, indeks, dan partisi yang mengalami aktivitas atau ketidakcocokan yang signifikan.

Statistik disediakan pada tingkat partisi dan bersifat aditif. Ini berarti Anda bisa mendapatkan statistik tingkat indeks atau tingkat tabel dengan menulis kueri agregasi di T-SQL. Untuk informasi selengkapnya, lihat Contoh pemindaian indeks dan pencarian untuk semua tabel .

Untuk menganalisis statistik operasi baca dan tulis untuk tabel, indeks, atau partisi, 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, gunakan kolom ini:

  • page_latch_wait_count
  • page_latch_wait_in_ms

Untuk mengidentifikasi pertikaian kunci, gunakan kolom ini:

  • row_lock_count
  • page_lock_count
  • row_lock_wait_in_ms
  • page_lock_wait_in_ms

Untuk menganalisis statistik I/O fisik, gunakan kolom ini:

  • page_io_latch_wait_count
  • page_io_latch_wait_in_ms

Keterangan kolom

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 dapat lebih besar dari 0 untuk indeks nonclustered jika indeks berisi baris besar.

Cara penghitung dalam cache metadata diatur ulang

Data yang dikembalikan oleh sys.dm_db_index_operational_stats hanya ada selama objek cache metadata yang mewakili tumpukan atau pohon B tersedia. Data ini tidak persisten. Ini berarti Anda tidak dapat menggunakan penghitung ini untuk menentukan secara meyakinkan apakah indeks digunakan atau tidak, atau kapan indeks terakhir digunakan. Sebagai gantinya, gunakan sys.dm_db_index_usage_stats.

Nilai untuk setiap kolom numerik diatur ke nol setiap kali metadata untuk tumpukan atau pohon B dibawa ke dalam cache metadata. Statistik diakumulasikan hingga objek cache dihapus dari cache metadata. Timbunan aktif atau pohon B umumnya memiliki metadatanya di cache, dan jumlah kumulatif mencerminkan aktivitas sejak instans Mesin Database terakhir dimulai. Metadata untuk tumpukan atau B-tree yang kurang aktif mungkin bergerak masuk dan keluar dari cache saat digunakan, terutama jika instans Database Engine berada di bawah tekanan memori. Akibatnya, statistik operasional indeks terkadang tidak tercermin dalam sys.dm_db_index_operational_stats. Hal ini tidak umum.

Statistik dihapus dari cache dan tidak lagi dilaporkan oleh fungsi ini jika tabel atau indeks dihilangkan, atau jika partisi terpotong. 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 ke 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 Mengembalikan informasi untuk tabel tertentu.

Permissions

Memerlukan izin berikut:

  • CONTROL izin pada objek yang ditentukan dalam database

  • VIEW DATABASE STATE atau VIEW DATABASE PERFORMANCE STATE izin untuk mengembalikan informasi tentang semua objek dalam database yang ditentukan, ketika nilai for @object_id tidak ditentukan.

  • VIEW SERVER STATE atau VIEW SERVER PERFORMANCE STATE izin untuk mengembalikan informasi tentang semua database, ketika nilai for @database_id tidak ditentukan.

VIEW DATABASE STATE Memberikan atau VIEW SERVER PERFORMANCE STATE mengizinkan semua objek dalam database dikembalikan, terlepas dari izin apa pun CONTROL yang ditolak pada objek tertentu.

VIEW DATABASE STATE Menolak atau VIEW SERVER PERFORMANCE STATE melarang semua objek dalam database untuk dikembalikan, terlepas dari izin apa pun CONTROL yang diberikan pada objek tertentu.

Untuk informasi selengkapnya, lihat Tampilan dan fungsi manajemen dinamis sistem.

Examples

Mengembalikan informasi untuk tabel tertentu

Contoh berikut mengembalikan informasi untuk semua indeks dan partisi Person.Address tabel dalam database AdventureWorks2025.

Important

Saat Anda menggunakan fungsi DB_ID Transact-SQL dan OBJECT_ID untuk mengembalikan nilai parameter, selalu pastikan bahwa ID yang valid ditampilkan. Jika database atau nama objek tidak dapat ditemukan, seperti saat tidak ada atau salah dieja, kedua fungsi mengembalikan NULL. Fungsi ini 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 AS INT = DB_ID(N'AdventureWorks2025');
DECLARE @object_id AS INT = OBJECT_ID(N'AdventureWorks2025.Person.Address');

SELECT *
FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL)
WHERE @db_id IS NOT NULL
      AND @object_id IS NOT NULL;

Mengembalikan informasi untuk semua tabel dan indeks

Contoh berikut mengembalikan informasi untuk semua tabel dan indeks pada instans Mesin Database.

SELECT *
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);

Pemindaian indeks dan pencarian untuk semua tabel

Contoh berikut mengagregasi data tingkat partisi untuk mengembalikan pencarian indeks dan memindai statistik untuk semua tabel dalam database saat ini.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS object_name,
       COUNT(DISTINCT(index_id)) AS index_count,
       COUNT(DISTINCT(partition_number)) AS partition_count,
       SUM(range_scan_count) AS index_scan_count,
       SUM(singleton_lookup_count) AS index_seek_count
FROM sys.dm_db_index_operational_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT)
GROUP BY OBJECT_SCHEMA_NAME(object_id),
         OBJECT_NAME(object_id)
ORDER BY schema_name, object_name;

Konten terkait

  • Last updated on