Bagikan melalui

sys.dm_exec_query_optimizer_info (T-SQL)

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure 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.
occurrence bigint Jumlah kemunculan peristiwa pengoptimalan untuk penghitung ini.
value float Nilai properti rata-rata per kejadian.
pdw_node_id int Pengidentifikasi untuk simpul tempat distribusi ini aktif.

Berlaku untuk: Azure Synapse Analytics, Analytics Platform System (PDW)

Izin

SQL Server 2019 (15.x) dan versi yang lebih lama, dan Azure SQL Managed Instance, memerlukan VIEW SERVER STATE izin.

SQL Server 2022 (16.x) dan versi yang lebih baru, memerlukan VIEW SERVER PERFORMANCE STATE izin di server.

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

Keterangan

sys.dm_exec_query_optimizer_info berisi properti berikut (penghitung). Semua nilai kemunculan bersifat kumulatif dan diatur ke 0 pada mulai ulang sistem. Semua nilai untuk bidang nilai diatur ke NULL pada mulai 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 yang dihasilkan sistem. Eksekusi paket yang sudah di-cache tidak mengubah nilai dalam dm_exec_query_optimizer_info, hanya pengoptimalan yang signifikan.

Penghitung Kemunculan Nilai
optimizations Jumlah total pengoptimalan. Tidak berlaku
elapsed time Jumlah total pengoptimalan. Waktu rata-rata yang berlalu per pengoptimalan pernyataan individu (kueri), dalam hitungan detik.
final cost Jumlah total pengoptimalan. Perkiraan biaya rata-rata untuk rencana yang dioptimalkan dalam unit biaya internal.
trivial plan khusus internal khusus internal
tasks khusus internal khusus internal
no plan khusus internal khusus internal
search 0 khusus internal khusus internal
search 0 time khusus internal khusus internal
search 0 tasks khusus internal khusus internal
search 1 khusus internal khusus internal
search 1 time khusus internal khusus internal
search 1 tasks khusus internal khusus internal
search 2 khusus internal khusus internal
search 2 time khusus internal khusus internal
search 2 tasks khusus internal khusus internal
gain stage 0 to stage 1 khusus internal khusus internal
gain stage 1 to stage 2 khusus internal khusus internal
timeout khusus internal khusus internal
memory limit exceeded khusus internal khusus internal
insert stmt Jumlah pengoptimalan yang untuk INSERT pernyataan. Tidak berlaku
delete stmt Jumlah pengoptimalan yang untuk DELETE pernyataan. Tidak berlaku
update stmt Jumlah pengoptimalan yang untuk UPDATE pernyataan. Tidak berlaku
contains subquery Jumlah pengoptimalan untuk kueri yang berisi setidaknya satu subkueri. Tidak berlaku
unnest failed khusus internal khusus internal
tables Jumlah total pengoptimalan. Jumlah rata-rata tabel yang dirujuk per kueri yang dioptimalkan.
hints Berapa kali beberapa petunjuk ditentukan. Petunjuk yang dihitung meliputi: JOIN, , GROUPUNION dan FORCE ORDER petunjuk kueri, FORCE PLAN opsi atur, dan petunjuk gabungan. Tidak berlaku
order hint Berapa kali urutan gabungan dipaksa. Penghitung ini tidak dibatasi untuk FORCE ORDER petunjuk. Menentukan algoritma gabungan dalam kueri, seperti INNER HASH JOIN, juga memaksa urutan gabungan, yang meningkatkan penghitung. Tidak berlaku
join hint Berapa kali algoritma gabungan dipaksa oleh petunjuk gabungan. FORCE ORDER Petunjuk kueri tidak menaikkan penghitung ini. Tidak berlaku
view reference Berapa kali tampilan dirujuk dalam kueri. Tidak berlaku
remote query Jumlah pengoptimalan di mana kueri mereferensikan setidaknya satu sumber data jarak jauh, seperti tabel dengan nama empat bagian atau OPENROWSET hasil. Tidak berlaku
maximum DOP Jumlah total pengoptimalan. Nilai efektif MAXDOP rata-rata untuk paket yang dioptimalkan. Secara default, efektif MAXDOP ditentukan oleh tingkat maksimum opsi konfigurasi server paralelisme , dan mungkin ditimpa untuk kueri tertentu dengan nilai MAXDOP petunjuk kueri.
maximum recursion level Jumlah pengoptimalan di mana tingkat yang MAXRECURSION lebih besar dari 0 yang ditentukan dengan petunjuk kueri. Tingkat rata-rata MAXRECURSION dalam pengoptimalan di mana tingkat rekursi maksimum ditentukan dengan petunjuk kueri.
indexed views loaded khusus internal khusus internal
indexed views matched Jumlah pengoptimalan di mana satu atau beberapa tampilan terindeks cocok. Jumlah rata-rata tampilan yang cocok.
indexed views used Jumlah pengoptimalan di mana satu atau beberapa tampilan terindeks digunakan dalam rencana output setelah dicocokkan. Jumlah rata-rata tampilan yang digunakan.
indexed views updated Jumlah pengoptimalan pernyataan DML yang menghasilkan rencana yang mempertahankan satu atau beberapa tampilan terindeks. Jumlah rata-rata tampilan dipertahankan.
dynamic cursor request Jumlah pengoptimalan di mana permintaan kursor dinamis ditentukan. Tidak berlaku
fast forward cursor request Jumlah pengoptimalan di mana permintaan kursor maju cepat ditentukan. Tidak berlaku
merge stmt Jumlah pengoptimalan yang untuk MERGE pernyataan. 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;

E. Lihat jumlah total petunjuk selama pengoptimalan

Berapa banyak petunjuk yang dihitung saat FORCE ORDER disertakan sebagai petunjuk kueri?

-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

SELECT poh.PurchaseOrderID,
    poh.OrderDate,
    pod.ProductID,
    pod.DueDate,
    poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
    FORCE ORDER,
    RECOMPILE
);

-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

Konten terkait