Bagikan melalui


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

Konvensi sintaks 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:

  • CONTROL izin pada objek yang ditentukan dalam database

  • VIEW DATABASE STATE atau VIEW DATABASE PERFORMANCE STATE izin (SQL Server 2022) untuk mengembalikan informasi tentang semua objek dalam database yang ditentukan, dengan menggunakan kartubebas objek @object_id = NULL

  • VIEW 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)