sys.dm_exec_query_plan (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Mengembalikan Showplan dalam format XML untuk batch yang ditentukan oleh handel paket. Rencana yang ditentukan oleh handel paket dapat di-cache atau sedang dijalankan.
Skema XML untuk Showplan diterbitkan dan tersedia di situs Web Microsoft ini. Ini juga tersedia di direktori tempat SQL Server diinstal.
Sintaks
sys.dm_exec_query_plan(plan_handle)
Argumen
plan_handle
Adalah token yang secara unik mengidentifikasi rencana eksekusi kueri untuk batch yang telah dijalankan dan rencananya berada di cache rencana, atau saat ini sedang dijalankan. plan_handle adalah varbinary(64).
plan_handle dapat diperoleh dari objek manajemen dinamis berikut:
Tabel Dikembalikan
| Nama kolom | Jenis data | Deskripsi |
|---|---|---|
| dbid | smallint | ID database konteks yang berlaku ketika pernyataan Transact-SQL yang sesuai dengan rencana ini dikompilasi. Untuk pernyataan ad hoc dan SQL yang disiapkan, ID database tempat pernyataan dikompilasi. Kolom dapat diubah ke null. |
| objectid | int | ID objek (misalnya, prosedur tersimpan atau fungsi yang ditentukan pengguna) untuk rencana kueri ini. Untuk ad hoc dan batch yang disiapkan, kolom ini null. Kolom dapat diubah ke null. |
| number | smallint | Bilangan bulat prosedur tersimpan bernomor. Misalnya, sekelompok prosedur untuk aplikasi pesanan dapat diberi nama orderproc;1, orderproc;2, dan sebagainya. Untuk ad hoc dan batch yang disiapkan, kolom ini null. Kolom dapat diubah ke null. |
| Dienkripsi | bit | Menunjukkan apakah prosedur tersimpan yang sesuai dienkripsi. 0 = tidak dienkripsi 1 = terenkripsi Kolom tidak dapat diubah ke null. |
| query_plan | xml | Berisi representasi Showplan waktu kompilasi dari rencana eksekusi kueri yang ditentukan dengan plan_handle. Showplan dalam format XML. Satu paket dihasilkan untuk setiap batch yang berisi, misalnya pernyataan Transact-SQL ad hoc, panggilan prosedur tersimpan, dan panggilan fungsi yang ditentukan pengguna. Kolom dapat diubah ke null. |
Keterangan
Dalam kondisi berikut, tidak ada output Showplan yang dikembalikan di kolom query_plan tabel yang dikembalikan untuk sys.dm_exec_query_plan:
Jika rencana kueri yang ditentukan dengan menggunakan plan_handle telah dikeluarkan dari cache paket, kolom query_plan tabel yang dikembalikan null. Misalnya, kondisi ini dapat terjadi jika ada penundaan waktu antara ketika handel rencana diambil dan ketika digunakan dengan sys.dm_exec_query_plan.
Beberapa pernyataan Transact-SQL tidak di-cache, seperti pernyataan operasi massal atau pernyataan yang berisi literal string yang berukuran lebih besar dari 8 KB. Xml Showplans untuk pernyataan tersebut tidak dapat diambil dengan menggunakan sys.dm_exec_query_plan kecuali batch sedang dijalankan karena tidak ada di cache.
Jika batch Transact-SQL atau prosedur tersimpan berisi panggilan ke fungsi yang ditentukan pengguna atau panggilan ke SQL dinamis, misalnya menggunakan EXEC (string), Xml Showplan yang dikompilasi untuk fungsi yang ditentukan pengguna tidak disertakan dalam tabel yang dikembalikan oleh sys.dm_exec_query_plan untuk prosedur batch atau tersimpan. Sebagai gantinya, Anda harus melakukan panggilan terpisah ke sys.dm_exec_query_plan untuk handel paket yang sesuai dengan fungsi yang ditentukan pengguna.
Saat kueri ad hoc menggunakan parameterisasi sederhana atau paksa, kolom query_plan hanya akan berisi teks pernyataan dan bukan rencana kueri aktual. Untuk mengembalikan paket kueri, panggil sys.dm_exec_query_plan untuk handel paket kueri berparameter yang disiapkan. Anda dapat menentukan apakah kueri diparameterkan dengan merujuk kolom sql tampilan sys.syscacheobjects atau kolom teks tampilan manajemen dinamis sys.dm_exec_sql_text .
Catatan
Karena keterbatasan jumlah tingkat berlapis yang diizinkan dalam jenis data xml , sys.dm_exec_query_plan tidak dapat mengembalikan rencana kueri yang memenuhi atau melebihi 128 tingkat elemen berlapis. Dalam versi SQL Server sebelumnya, kondisi ini mencegah rencana kueri kembali dan menghasilkan kesalahan 6335. Di Paket Layanan SQL Server 2005 (9.x) 2 dan versi yang lebih baru, kolom query_plan mengembalikan NULL.
Anda dapat menggunakan fungsi manajemen dinamis sys.dm_exec_text_query_plan (Transact-SQL) untuk mengembalikan output rencana kueri dalam format teks.
Izin
Untuk menjalankan sys.dm_exec_query_plan, pengguna harus menjadi anggota peran server tetap sysadmin atau memiliki VIEW SERVER STATE izin di server.
Izin untuk SQL Server 2022 dan yang lebih baru
Memerlukan izin TAMPILKAN STATUS PERFORMA SERVER pada server.
Contoh
Contoh berikut menunjukkan cara menggunakan tampilan manajemen dinamis sys.dm_exec_query_plan .
Untuk menampilkan Xml Showplans, jalankan kueri berikut di Editor Kueri SQL Server Management Studio, lalu klik ShowPlanXML di kolom query_plan tabel yang dikembalikan oleh sys.dm_exec_query_plan. Xml Showplan ditampilkan di panel ringkasan Management Studio. Untuk menyimpan Xml Showplan ke file, klik kanan ShowPlanXML di kolom query_plan , klik Simpan Hasil Sebagai, beri nama file dalam format <file_name.sqlplan>; misalnya, MyXMLShowplan.sqlplan.
J. Mengambil rencana kueri yang di-cache untuk kueri atau batch Transact-SQL yang berjalan lambat
Rencana kueri untuk berbagai jenis batch Transact-SQL, seperti batch ad hoc, prosedur tersimpan, dan fungsi yang ditentukan pengguna, di-cache di area memori yang disebut cache rencana. Setiap rencana kueri yang di-cache diidentifikasi oleh pengidentifikasi unik yang disebut handel rencana. Anda dapat menentukan handel paket ini dengan tampilan manajemen dinamis sys.dm_exec_query_plan untuk mengambil rencana eksekusi untuk kueri atau batch Transact-SQL tertentu.
Jika kueri atau batch Transact-SQL berjalan lama pada koneksi tertentu ke SQL Server, ambil rencana eksekusi untuk kueri atau batch tersebut untuk menemukan apa yang menyebabkan penundaan. Contoh berikut menunjukkan cara mengambil Xml Showplan untuk kueri atau batch yang berjalan lambat.
Catatan
Untuk menjalankan contoh ini, ganti nilai untuk session_id dan plan_handle dengan nilai khusus untuk server Anda.
Pertama, ambil ID proses server (SPID) untuk proses yang menjalankan kueri atau batch dengan menggunakan prosedur tersimpan sp_who :
USE master;
GO
exec sp_who;
GO
Tataan hasil yang dikembalikan dengan sp_who menunjukkan bahwa SPID adalah 54. Anda bisa menggunakan SPID dengan sys.dm_exec_requests tampilan manajemen dinamis untuk mengambil handel paket dengan menggunakan kueri berikut:
USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO
Tabel yang dikembalikan oleh sys.dm_exec_requests menunjukkan bahwa handel rencana untuk kueri atau batch yang berjalan lambat adalah 0x06000100A27E7C1FA821B10600, yang dapat Anda tentukan sebagai argumen plan_handle dengan sys.dm_exec_query_plan untuk mengambil rencana eksekusi dalam format XML sebagai berikut. Rencana eksekusi dalam format XML untuk kueri atau batch yang berjalan lambat terkandung dalam kolom query_plan tabel yang dikembalikan oleh sys.dm_exec_query_plan.
USE master;
GO
SELECT *
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);
GO
B. Mengambil setiap rencana kueri dari cache paket
Untuk mengambil rekam jepret semua rencana kueri yang berada di cache paket, ambil handel rencana semua rencana kueri di cache dengan mengkueri sys.dm_exec_cached_plans tampilan manajemen dinamis. Handel paket disimpan di plan_handle kolom sys.dm_exec_cached_plans. Kemudian gunakan operator CROSS APPLY untuk meneruskan handel sys.dm_exec_query_plan paket sebagai berikut. Output Xml Showplan untuk setiap paket yang saat ini berada dalam cache paket berada di query_plan kolom tabel yang dikembalikan.
USE master;
GO
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO
C. Mengambil setiap rencana kueri tempat server telah mengumpulkan statistik kueri dari cache paket
Untuk mengambil rekam jepret semua rencana kueri di mana server telah mengumpulkan statistik yang saat ini berada di cache rencana, ambil handel rencana dari rencana ini di cache dengan mengkueri sys.dm_exec_query_stats tampilan manajemen dinamis. Handel paket disimpan di plan_handle kolom sys.dm_exec_query_stats. Kemudian gunakan operator CROSS APPLY untuk meneruskan handel sys.dm_exec_query_plan paket sebagai berikut. Output Xml Showplan untuk setiap paket tempat server mengumpulkan statistik yang saat ini ada di cache paket berada di query_plan kolom tabel yang dikembalikan.
USE master;
GO
SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO
D. Mengambil informasi tentang lima kueri teratas menurut waktu CPU rata-rata
Contoh berikut mengembalikan paket dan waktu CPU rata-rata untuk lima kueri teratas.
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO
Lihat Juga
Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL)
sys.dm_exec_cached_plans (T-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (T-SQL)
Referensi Operator Logis dan Fisik Showplan
sys.dm_exec_text_query_plan (T-SQL)
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk