sys.dm_exec_query_optimizer_info (T-SQL)

Artikel
07/06/2024

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'
);

Bagikan melalui

sys.dm_exec_query_optimizer_info (T-SQL)

Izin

Keterangan

Contoh

J. Melihat statistik pada eksekusi pengoptimal

B. Melihat jumlah total pengoptimalan

C. Waktu rata-rata yang berlalu per pengoptimalan

D. Pecahan pengoptimalan yang melibatkan subkueri

E. Lihat jumlah total petunjuk selama pengoptimalan

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

sys.dm_exec_query_optimizer_info (T-SQL)

Izin

Keterangan

Contoh

J. Melihat statistik pada eksekusi pengoptimal

B. Melihat jumlah total pengoptimalan

C. Waktu rata-rata yang berlalu per pengoptimalan

D. Pecahan pengoptimalan yang melibatkan subkueri

E. Lihat jumlah total petunjuk selama pengoptimalan

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: