sys.dm_exec_text_query_plan (T-SQL)

Artikel
08/21/2023

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.

Konvensi sintaks transact-SQL

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

Lihat Juga

sys.dm_exec_query_plan (T-SQL)

Bagikan melalui