sys.dm_exec_query_optimizer_info (T-SQL)

Artikel
08/21/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System (PDW)

Mengembalikan statistik terperinci tentang operasi pengoptimal kueri SQL Server. Anda dapat menggunakan tampilan ini saat menyetel beban kerja untuk mengidentifikasi masalah atau penyempurnaan pengoptimalan kueri. Misalnya, Anda dapat menggunakan jumlah total pengoptimalan, nilai waktu yang berlalu, dan nilai biaya akhir untuk membandingkan pengoptimalan kueri beban kerja saat ini dan perubahan apa pun yang diamati selama proses penyetelan. Beberapa penghitung menyediakan data yang hanya relevan untuk penggunaan diagnostik internal SQL Server. Penghitung ini ditandai sebagai "Hanya internal."

Catatan

Untuk memanggil ini dari Azure Synapse Analytics atau Analytics Platform System (PDW), gunakan nama sys.dm_pdw_nodes_exec_query_optimizer_info. Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.

Nama	Jenis data	Deskripsi
Counter	nvarchar(4000)	Nama peristiwa statistik pengoptimal.
kemunculan	bigint	Jumlah kemunculan peristiwa pengoptimalan untuk penghitung ini.
value	float	Nilai properti rata-rata per kejadian.
pdw_node_id	int	Berlaku untuk: Azure Synapse Analytics, Analytics Platform System (PDW) Pengidentifikasi untuk simpul tempat distribusi ini aktif.

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.

Keterangan

sys.dm_exec_query_optimizer_info berisi properti berikut (penghitung). Semua nilai kemunculan bersifat kumulatif dan diatur ke 0 saat sistem dimulai ulang. Semua nilai untuk bidang nilai diatur ke NULL saat menghidupkan ulang sistem. Semua nilai kolom nilai yang menentukan rata-rata menggunakan nilai kemunculan dari baris yang sama dengan denominator dalam perhitungan rata-rata. Semua pengoptimalan kueri diukur ketika SQL Server menentukan perubahan pada dm_exec_query_optimizer_info, termasuk kueri yang dihasilkan pengguna dan sistem. Eksekusi paket yang sudah di-cache tidak mengubah nilai dalam dm_exec_query_optimizer_info, hanya pengoptimalan yang signifikan.

Penghitung	Kemunculan	Value
Optimasi	Jumlah total pengoptimalan.	Tidak berlaku
waktu yang berlalu	Jumlah total pengoptimalan.	Waktu rata-rata yang berlalu per pengoptimalan pernyataan individu (kueri), dalam hitungan detik.
biaya akhir	Jumlah total pengoptimalan.	Perkiraan biaya rata-rata untuk rencana yang dioptimalkan dalam unit biaya internal.
rencana sepele	khusus internal	khusus internal
tugas	khusus internal	khusus internal
tidak ada rencana	khusus internal	khusus internal
cari 0	khusus internal	khusus internal
cari 0 kali	khusus internal	khusus internal
cari 0 tugas	khusus internal	khusus internal
pencarian 1	khusus internal	khusus internal
pencarian 1 kali	khusus internal	khusus internal
cari 1 tugas	khusus internal	khusus internal
pencarian 2	khusus internal	khusus internal
pencarian 2 kali	khusus internal	khusus internal
cari 2 tugas	khusus internal	khusus internal
mendapatkan tahap 0 hingga tahap 1	khusus internal	khusus internal
mendapatkan tahap 1 hingga tahap 2	khusus internal	khusus internal
waktu habis	khusus internal	khusus internal
batas memori terlampaui	khusus internal	khusus internal
sisipkan stmt	Jumlah pengoptimalan yang untuk pernyataan INSERT.	Tidak berlaku
hapus stmt	Jumlah pengoptimalan yang untuk pernyataan DELETE.	Tidak berlaku
perbarui stmt	Jumlah pengoptimalan yang untuk pernyataan UPDATE.	Tidak berlaku
berisi subkueri	Jumlah pengoptimalan untuk kueri yang berisi setidaknya satu subkueri.	Tidak berlaku
unnest gagal	khusus internal	khusus internal
tabel	Jumlah total pengoptimalan.	Jumlah rata-rata tabel yang dirujuk per kueri yang dioptimalkan.
petunjuk	Berapa kali beberapa petunjuk ditentukan. Petunjuk yang dihitung meliputi: petunjuk kueri JOIN, GROUP, UNION, dan FORCE ORDER, opsi set FORCE PLAN, dan petunjuk gabungan.	Tidak berlaku
petunjuk pesanan	Berapa kali petunjuk urutan paksa ditentukan.	Tidak berlaku
petunjuk gabungan	Berapa kali algoritma gabungan dipaksa oleh petunjuk gabungan.	Tidak berlaku
lihat referensi	Berapa kali tampilan telah dirujuk dalam kueri.	Tidak berlaku
kueri jarak jauh	Jumlah pengoptimalan di mana kueri mereferensikan setidaknya satu sumber data jarak jauh, seperti tabel dengan nama empat bagian atau hasil OPENROWSET.	Tidak berlaku
DOP maksimum	Jumlah total pengoptimalan.	Nilai MAXDOP efektif rata-rata untuk paket yang dioptimalkan. Secara default, MAXDOP yang efektif ditentukan oleh tingkat maksimum opsi konfigurasi server paralelisme , dan dapat ditimpa untuk kueri tertentu dengan nilai petunjuk kueri MAXDOP.
tingkat rekursi maksimum	Jumlah pengoptimalan di mana tingkat MAXRECURSION yang lebih besar dari 0 telah ditentukan dengan petunjuk kueri.	Tingkat MAXRECURSION rata-rata dalam pengoptimalan di mana tingkat rekursi maksimum ditentukan dengan petunjuk kueri.
tampilan terindeks dimuat	khusus internal	khusus internal
tampilan terindeks cocok	Jumlah pengoptimalan di mana satu atau beberapa tampilan terindeks telah dicocokkan.	Jumlah rata-rata tampilan yang cocok.
tampilan terindeks yang digunakan	Jumlah pengoptimalan di mana satu atau beberapa tampilan terindeks digunakan dalam rencana output setelah dicocokkan.	Jumlah rata-rata tampilan yang digunakan.
tampilan terindeks diperbarui	Jumlah pengoptimalan pernyataan DML yang menghasilkan rencana yang mempertahankan satu atau beberapa tampilan terindeks.	Jumlah rata-rata tampilan dipertahankan.
permintaan kursor dinamis	Jumlah pengoptimalan di mana permintaan kursor dinamis telah ditentukan.	Tidak berlaku
permintaan kursor maju cepat	Jumlah pengoptimalan di mana permintaan kursor maju cepat telah ditentukan.	Tidak berlaku
gabungkan stmt	Jumlah pengoptimalan yang untuk pernyataan MERGE.	Tidak berlaku

Contoh

J. Melihat statistik pada eksekusi pengoptimal

Apa statistik eksekusi pengoptimal saat ini untuk instans SQL Server ini?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Melihat jumlah total pengoptimalan

Berapa banyak pengoptimalan yang dilakukan?

SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info  
WHERE counter = 'optimizations';

C. Waktu rata-rata yang berlalu per pengoptimalan

Berapa rata-rata waktu yang berlalu per pengoptimalan?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization  
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';

D. Pecahan pengoptimalan yang melibatkan subkueri

Pecahan kueri yang dioptimalkan apa yang berisi subkueri?

SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /  
       (SELECT CAST (occurrence AS float)   
        FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')  
        AS ContainsSubqueryFraction;

Lihat Juga

Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL)
Tampilan dan Fungsi Manajemen Dinamis Terkait Eksekusi (Transact-SQL)