sys.dm_exec_query_optimizer_info (T-SQL)

Berlaku untuk:SQL ServerAzure SQL DatabaseInstans Terkelola Azure SQLAzure Synapse AnalyticsSistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

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

Note

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)

Permissions

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

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.

Counter Kemunculan Nilai
optimizations Jumlah total pengoptimalan. Tidak teraplikasi
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 Hanya internal Hanya internal
tasks Hanya internal Hanya internal
no plan Hanya internal Hanya internal
search 0 Hanya internal Hanya internal
search 0 time Hanya internal Hanya internal
search 0 tasks Hanya internal Hanya internal
search 1 Hanya internal Hanya internal
search 1 time Hanya internal Hanya internal
search 1 tasks Hanya internal Hanya internal
search 2 Hanya internal Hanya internal
search 2 time Hanya internal Hanya internal
search 2 tasks Hanya internal Hanya internal
gain stage 0 to stage 1 Hanya internal Hanya internal
gain stage 1 to stage 2 Hanya internal Hanya internal
timeout Hanya internal Hanya internal
memory limit exceeded Hanya internal Hanya internal
insert stmt Jumlah pengoptimalan yang untuk INSERT pernyataan. Tidak teraplikasi
delete stmt Jumlah pengoptimalan yang untuk DELETE pernyataan. Tidak teraplikasi
update stmt Jumlah pengoptimalan yang untuk UPDATE pernyataan. Tidak teraplikasi
merge stmt Jumlah pengoptimalan yang untuk MERGE pernyataan. Tidak teraplikasi
contains subquery Jumlah pengoptimalan untuk kueri yang berisi setidaknya satu subkueri. Tidak teraplikasi
unnest failed Hanya internal Hanya 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 teraplikasi
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 teraplikasi
join hint Berapa kali algoritma gabungan dipaksa oleh petunjuk gabungan. FORCE ORDER Petunjuk kueri tidak menaikkan penghitung ini. Tidak teraplikasi
view reference Berapa kali tampilan dirujuk dalam kueri. Tidak teraplikasi
remote query Jumlah pengoptimalan di mana kueri mereferensikan setidaknya satu sumber data jarak jauh, seperti tabel dengan nama empat bagian atau OPENROWSET hasil. Tidak teraplikasi
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 Hanya internal Hanya 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 teraplikasi
fast forward cursor request Jumlah pengoptimalan di mana permintaan kursor maju cepat ditentukan. Tidak teraplikasi

Examples

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

  • Last updated on