sys.dm_db_missing_index_details (T-SQL)

Artikel
08/21/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Mengembalikan informasi terperinci tentang indeks yang hilang.

Di Azure SQL Database, tampilan manajemen dinamis tidak dapat mengekspos informasi yang akan memengaruhi penahanan database atau mengekspos informasi tentang database lain yang dapat diakses pengguna. Untuk menghindari mengekspos informasi ini, setiap baris yang berisi data yang bukan milik penyewa yang tersambung difilter.

Nama kolom	Jenis data	Deskripsi
index_handle	int	Mengidentifikasi indeks tertentu yang hilang. Pengidentifikasi unik di seluruh server. `index_handle` adalah kunci tabel ini.
database_id	smallint	Mengidentifikasi database tempat tabel dengan indeks yang hilang berada. Di Azure SQL Database, nilainya unik dalam satu database atau kumpulan elastis, tetapi tidak dalam server logis.
object_id	int	Mengidentifikasi tabel tempat indeks hilang.
equality_columns	nvarchar(4000)	Daftar kolom yang dipisahkan koma yang berkontribusi pada predikat kesetaraan formulir: constant_value table.column =
inequality_columns	nvarchar(4000)	Daftar kolom yang dipisahkan koma yang berkontribusi pada predikat ketidaksamaan, misalnya, predikat formulir: constant_value table.column> Operator perbandingan apa pun selain "=" mengekspresikan ketidaksamaan.
included_columns	nvarchar(4000)	Daftar kolom yang dipisahkan koma yang diperlukan sebagai kolom yang mencakup kueri. Untuk informasi selengkapnya tentang mencakup atau menyertakan kolom, lihat Membuat Indeks dengan Kolom yang Disertakan. Untuk indeks yang dioptimalkan memori (hash dan nonclustered yang dioptimalkan memori), abaikan `included_columns`. Semua kolom tabel disertakan dalam setiap indeks yang dioptimalkan memori.
statement	nvarchar(4000)	Nama tabel tempat indeks hilang.

Keterangan

Informasi yang dikembalikan oleh sys.dm_db_missing_index_details diperbarui saat kueri dioptimalkan oleh pengoptimal kueri, dan tidak dipertahankan. Informasi indeks yang hilang disimpan hanya sampai mesin database dimulai ulang. Administrator database harus secara berkala membuat salinan cadangan informasi indeks yang hilang jika mereka ingin menyimpannya setelah daur ulang server. sqlserver_start_time Gunakan kolom di sys.dm_os_sys_info untuk menemukan waktu mulai mesin database terakhir.

Untuk menentukan grup indeks mana yang hilang dari indeks tertentu yang hilang, Anda dapat mengkueri sys.dm_db_missing_index_groups tampilan manajemen dinamis dengan menyekutukannya berdasarkan sys.dm_db_missing_index_detailsindex_handle kolom.

Catatan

Hasil yang ditetapkan untuk DMV ini dibatasi hingga 600 baris. Setiap baris berisi satu indeks yang hilang. Jika Anda memiliki lebih dari 600 indeks yang hilang, Anda harus mengatasi indeks yang hilang yang ada sehingga Anda kemudian dapat melihat yang lebih baru.

Menggunakan informasi indeks yang hilang dalam pernyataan CREATE INDEX

Untuk mengonversi informasi yang dikembalikan oleh sys.dm_db_missing_index_details menjadi pernyataan CREATE INDEX untuk indeks yang dioptimalkan memori dan berbasis disk, kolom kesetaraan harus dimasukkan sebelum kolom ketidaksetaraan, dan bersama-sama mereka harus membuat kunci indeks. Kolom yang disertakan harus ditambahkan ke pernyataan CREATE INDEX menggunakan klausa INCLUDE. Untuk menentukan urutan efektif untuk kolom kesetaraan, urutkan berdasarkan selektivitasnya: cantumkan kolom yang paling selektif terlebih dahulu (paling kiri dalam daftar kolom). Pelajari selengkapnya di Menyetel indeks tanpa kluster dengan saran indeks yang hilang, termasuk Batasan fitur indeks yang hilang.

Untuk informasi selengkapnya tentang indeks yang dioptimalkan memori, lihat Indeks untuk Tabel yang Dioptimalkan Memori.

Konsistensi transaksi

Jika transaksi membuat atau menghilangkan tabel, baris yang berisi informasi indeks yang hilang tentang objek yang dihilangkan dihapus dari objek manajemen dinamis ini, mempertahankan konsistensi transaksi. Pelajari selengkapnya tentang batasan fitur indeks yang hilang.

Izin

Pada SQL Server dan SQL Managed Instance, memerlukan VIEW SERVER STATE izin.

Pada tujuan layanan SQL Database Basic, S0, dan S1, dan untuk database di kumpulan elastis, akun admin server, akun admin Microsoft Entra, atau keanggotaan dalam##MS_ServerStateReader## peran server diperlukan. Pada semua tujuan layanan SQL Database lainnya, izin VIEW DATABASE STATE pada database, atau keanggotaan dalam ##MS_ServerStateReader## peran server diperlukan.

Izin untuk SQL Server 2022 dan yang lebih baru

Memerlukan izin TAMPILKAN STATUS PERFORMA SERVER pada server.

Contoh

Contoh berikut mengembalikan saran indeks yang hilang untuk database saat ini. Saran indeks yang hilang harus digabungkan jika memungkinkan satu sama lain, dan dengan indeks yang ada dalam database saat ini. Pelajari cara menerapkan saran ini dalam menyetel indeks nonclustered dengan saran indeks yang hilang.

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Catatan

Skrip Pembuatan Indeks di Tiger Toolbox Microsoft memeriksa DMV indeks yang hilang dan secara otomatis menghapus indeks yang disarankan yang berlebihan, mengurai indeks berdampak rendah, dan menghasilkan skrip pembuatan indeks untuk tinjauan Anda. Seperti pada kueri di atas, tidak menjalankan perintah pembuatan indeks. Skrip Pembuatan Indeks cocok untuk SQL Server dan Azure SQL Managed Instance. Untuk Azure SQL Database, pertimbangkan untuk menerapkan penyetelan indeks otomatis.

Langkah berikutnya

Pelajari selengkapnya tentang fitur indeks yang hilang di artikel berikut ini: