sp_create_plan_guide_from_handle (T-SQL)
Berlaku untuk:
SQL Server
Membuat satu atau beberapa panduan paket dari rencana kueri di cache paket. Anda bisa menggunakan prosedur tersimpan ini untuk memastikan pengoptimal kueri selalu menggunakan rencana kueri tertentu untuk kueri tertentu. Untuk informasi selengkapnya tentang panduan paket, lihat Panduan Paket.
Sintaks
sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'
, [ @plan_handle = ] plan_handle
, [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]
Argumen
[ @name = ] N'plan_guide_name'
Adalah nama panduan rencana. Nama panduan paket dicakup ke database saat ini. plan_guide_name harus mematuhi aturan untuk pengidentifikasi dan tidak dapat dimulai dengan tanda nomor (#). Panjang maksimum plan_guide_name adalah 124 karakter.
[ @plan_handle = ] plan_handle
Mengidentifikasi batch dalam cache paket. plan_handle adalah varbinary(64). plan_handle dapat diperoleh dari tampilan manajemen dinamis sys.dm_exec_query_stats.
[ @statement_start_offset = ] { statement_start_offset | NULL } ]
Mengidentifikasi posisi awal pernyataan dalam batch plan_handle yang ditentukan. statement_start_offset int, dengan default NULL.
Offset pernyataan sesuai dengan kolom statement_start_offset dalam tampilan manajemen dinamis sys.dm_exec_query_stats .
Ketika NULL ditentukan atau offset pernyataan tidak ditentukan, panduan paket dibuat untuk setiap pernyataan dalam batch menggunakan rencana kueri untuk handel paket yang ditentukan. Panduan rencana yang dihasilkan setara dengan panduan rencana yang menggunakan petunjuk kueri USE PLAN untuk memaksa penggunaan paket tertentu.
Keterangan
Panduan paket tidak dapat dibuat untuk semua jenis pernyataan. Jika panduan paket tidak dapat dibuat untuk pernyataan dalam batch, prosedur tersimpan mengabaikan pernyataan dan melanjutkan ke pernyataan berikutnya dalam batch. Jika pernyataan terjadi beberapa kali dalam batch yang sama, rencana untuk kemunculan terakhir diaktifkan dan paket sebelumnya untuk pernyataan dinonaktifkan. Jika tidak ada pernyataan dalam batch yang dapat digunakan dalam panduan paket, kesalahan 10532 muncul dan pernyataan gagal. Kami menyarankan agar Anda selalu mendapatkan handel rencana dari tampilan manajemen dinamis sys.dm_exec_query_stats untuk membantu menghindari kemungkinan kesalahan ini.
Penting
sp_create_plan_guide_from_handle membuat panduan rencana berdasarkan paket saat muncul di cache paket. Ini berarti bahwa teks batch, pernyataan Transact-SQL, dan XML Showplan diambil karakter demi karakter (termasuk nilai harfiah apa pun yang diteruskan ke kueri) dari cache paket ke dalam panduan rencana yang dihasilkan. String teks ini mungkin berisi informasi sensitif yang kemudian disimpan dalam metadata database. Pengguna dengan izin yang sesuai dapat melihat informasi ini dengan menggunakan tampilan katalog sys.plan_guides dan kotak dialog Properti Panduan Paket di SQL Server Management Studio. Untuk memastikan bahwa informasi sensitif tidak diungkapkan melalui panduan rencana, sebaiknya tinjau panduan paket yang dibuat dari cache paket.
Membuat Panduan Rencana untuk Beberapa Pernyataan Dalam Rencana Kueri
Seperti sp_create_plan_guide, sp_create_plan_guide_from_handle menghapus rencana kueri untuk batch atau modul yang ditargetkan dari cache paket. Ini dilakukan untuk memastikan bahwa semua pengguna mulai menggunakan panduan paket baru. Saat membuat panduan paket untuk beberapa pernyataan dalam satu rencana kueri, Anda dapat menunda penghapusan paket dari cache dengan membuat semua panduan paket dalam transaksi eksplisit. Metode ini memungkinkan rencana untuk tetap berada di cache hingga transaksi selesai dan panduan rencana untuk setiap pernyataan yang ditentukan dibuat. Lihat Contoh B.
Izin
Memerlukan VIEW SERVER STATE izin. Selain itu, izin individual diperlukan untuk setiap panduan paket yang dibuat dengan menggunakan sp_create_plan_guide_from_handle. Untuk membuat panduan paket tipe OBJECT memerlukan ALTER izin pada objek yang dirujuk. Untuk membuat panduan paket jenis SQL atau TEMPLAT memerlukan ALTER izin pada database saat ini. Untuk menentukan jenis panduan paket yang akan dibuat, jalankan kueri berikut:
SELECT cp.plan_handle, sql_handle, st.text, objtype
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;
Di baris yang berisi pernyataan yang Anda buat panduan paketnya, periksa objtype kolom dalam tataan hasil. Nilai Proc menunjukkan panduan paket berjenis OBJECT. Nilai lain seperti AdHoc atau Prepared menunjukkan panduan paket berjenis SQL.
Contoh
J. Membuat panduan paket dari rencana kueri di cache paket
Contoh berikut membuat panduan paket untuk satu pernyataan SELECT dengan menentukan rencana kueri dari cache paket. Contoh dimulai dengan menjalankan pernyataan sederhana SELECT di mana panduan rencana akan dibuat. Rencana untuk kueri ini diperiksa dengan menggunakan sys.dm_exec_sql_text tampilan manajemen dinamis dan sys.dm_exec_text_query_plan . Panduan paket kemudian dibuat untuk kueri dengan menentukan rencana kueri dalam cache paket yang terkait dengan kueri. Pernyataan akhir dalam contoh memverifikasi bahwa panduan rencana ada.
USE AdventureWorks2022;
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM Production.WorkOrder AS w
JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE p.ProductSubcategoryID > 4
ORDER BY p.Name, DueDate;
GO
-- Inspect the query plan by using dynamic management views.
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO
-- Create a plan guide for the query by specifying the query plan in the plan cache.
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
EXECUTE sp_create_plan_guide_from_handle
@name = N'Guide1',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
GO
-- Verify that the plan guide is created.
SELECT * FROM sys.plan_guides
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO
B. Membuat beberapa panduan paket untuk batch multi-status
Contoh berikut membuat panduan rencana untuk dua pernyataan dalam batch multistatement. Panduan paket dibuat dalam transaksi eksplisit sehingga rencana kueri untuk batch tidak dihapus dari cache paket setelah panduan paket pertama dibuat. Contoh dimulai dengan menjalankan batch multistatement. Rencana untuk batch diperiksa dengan menggunakan tampilan manajemen dinamis. Perhatikan bahwa baris untuk setiap pernyataan dalam batch dikembalikan. Panduan paket kemudian dibuat untuk pernyataan pertama dan ketiga dalam batch dengan menentukan @statement_start_offset parameter . Pernyataan akhir dalam contoh memverifikasi bahwa panduan rencana ada.
USE AdventureWorks2022;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO
-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO
-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
EXECUTE sp_create_plan_guide_from_handle
@name = N'Guide_Statement1_only',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'
EXECUTE sp_create_plan_guide_from_handle
@name = N'Guide_Statement3_only',
@plan_handle = @plan_handle,
@statement_start_offset = @offset;
COMMIT TRANSACTION
GO
-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO
Lihat Juga
Prosedur Tersimpan Mesin Database (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
Panduan Paket
sp_create_plan_guide (T-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_text_query_plan (T-SQL)
sp_control_plan_guide (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