Bagikan melalui

sp_create_plan_guide_from_handle (T-SQL)

  • Artikel

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.

Konvensi sintaks transact-SQL

Sintaks

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

Argumen

[ @name = ] N'name'

Nama panduan rencana. @name adalah sysname, tanpa default. Nama panduan paket dicakup ke database saat ini. @name harus mematuhi aturan untuk pengidentifikasi dan tidak dapat dimulai dengan tanda nomor (#). Panjang maksimum @name adalah 124 karakter.

[ @plan_handle = ] plan_handle

Mengidentifikasi batch dalam cache paket. @plan_handle adalah varbinary(64), tanpa default. @plan_handle dapat diperoleh dari tampilan manajemen dinamis sys.dm_exec_query_stats.

[ @statement_start_offset = ] statement_start_offset

Mengidentifikasi posisi awal pernyataan dalam batch @plan_handle yang ditentukan. @statement_start_offset int, dengan default NULL.

Offset pernyataan sesuai dengan statement_start_offset kolom 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 USE PLAN petunjuk kueri 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 sys.dm_exec_query_stats tampilan manajemen dinamis untuk membantu menghindari kemungkinan kesalahan ini.

Penting

sp_create_plan_guide_from_handle membuat panduan rencana berdasarkan rencana 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 dapat berisi informasi sensitif yang kemudian disimpan dalam metadata database. Pengguna dengan izin yang sesuai dapat melihat informasi ini dengan menggunakan sys.plan_guides tampilan katalog dan kotak dialog Properti Panduan Paket di SQL Server Management Studio. Untuk memastikan bahwa informasi sensitif tidak diungkapkan melalui panduan paket, sebaiknya tinjau panduan paket yang dibuat dari cache paket.

Membuat panduan paket 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. Membuat panduan paket jenis OBJECT memerlukan ALTER izin pada objek yang dirujuk. Membuat panduan paket jenis SQL atau TEMPLATE 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
INNER 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 Ad hoc 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 SELECT pernyataan 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
INNER 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. 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

Konten terkait