sys.dm_exec_text_query_plan (T-SQL)
Berlaku untuk: SQL Server
Azure SQL Database
Azure SQL Managed Instance
Mengembalikan Showplan dalam format teks untuk batch Transact-SQL atau untuk pernyataan tertentu dalam batch. Rencana kueri yang ditentukan oleh handel paket dapat di-cache atau sedang dijalankan. Fungsi bernilai tabel ini mirip dengan sys.dm_exec_query_plan (Transact-SQL), tetapi memiliki perbedaan berikut:
- Output paket kueri dikembalikan dalam format teks.
- Output rencana kueri tidak dibatasi ukurannya.
- Pernyataan individual dalam batch dapat ditentukan.
Berlaku untuk: SQL Server (SQL Server 2008 (10.0.x) dan yang lebih baru), Azure SQL Database.
Sintaks
sys.dm_exec_text_query_plan
(
plan_handle
, { statement_start_offset | 0 | DEFAULT }
, { statement_end_offset | -1 | DEFAULT }
)
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:
statement_start_offset | 0 | DEFAULT
Menunjukkan, dalam byte, posisi awal kueri yang dijelaskan baris dalam teks objek batch atau persistennya. statement_start_offset int. Nilai 0 menunjukkan awal batch. Nilai default adalah 0.
Pernyataan memulai offset dapat diperoleh dari objek manajemen dinamis berikut:
statement_end_offset | -1 | DEFAULT
Menunjukkan, dalam byte, posisi akhir kueri yang dijelaskan baris dalam teks objek batch atau persistennya.
statement_start_offset int.
Nilai -1 menunjukkan akhir batch. Nilai defaultnya adalah -1.
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 | nvarchar(maks) | Berisi representasi Showplan waktu kompilasi dari rencana eksekusi kueri yang ditentukan dengan plan_handle. Showplan dalam format teks. 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 paket tabel yang dikembalikan untuk sys.dm_exec_text_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_text_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. Showplans untuk pernyataan tersebut tidak dapat diambil dengan menggunakan sys.dm_exec_text_query_plan 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_text_query_plan untuk batch atau prosedur tersimpan. Sebagai gantinya, Anda harus melakukan panggilan terpisah untuk sys.dm_exec_text_query_plan plan_handle 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_text_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 .
Izin
Untuk menjalankan sys.dm_exec_text_query_plan, pengguna harus menjadi anggota peran server tetap sysadmin atau memiliki izin TAMPILKAN STATUS SERVER di server.
Izin untuk SQL Server 2022 dan yang lebih baru
Memerlukan izin TAMPILKAN STATUS PERFORMA SERVER pada server.
Contoh
J. Mengambil rencana kueri yang di-cache untuk kueri atau batch Transact-SQL yang berjalan lambat
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 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
. Contoh berikut mengembalikan rencana kueri untuk handel paket yang ditentukan dan menggunakan nilai default 0 dan -1 untuk mengembalikan semua pernyataan dalam kueri atau batch.
USE master;
GO
SELECT query_plan
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
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_text_query_plan
paket sebagai berikut. Output Showplan untuk setiap paket yang saat ini ada di 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_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
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_text_query_plan
paket sebagai berikut. Output Showplan untuk setiap paket ada di query_plan
kolom tabel yang dikembalikan.
USE master;
GO
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
GO
D. Mengambil informasi tentang lima kueri teratas menurut waktu CPU rata-rata
Contoh berikut mengembalikan paket kueri dan waktu CPU rata-rata untuk lima kueri teratas. Fungsi sys.dm_exec_text_query_plan menentukan nilai default 0 dan -1 untuk mengembalikan semua pernyataan dalam batch dalam rencana kueri.
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_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO