sys.dm_exec_query_plan (T-SQL)

Berlaku untuk: SQL ServerAzure SQL Database Azure 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.

Konvensi sintaks transact-SQL

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:

• sys.dm_exec_cached_plans (T-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (T-SQL)

• sys.dm_exec_trigger_stats (T-SQL)

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 melihat 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)