sp_create_plan_guide (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Membuat panduan paket untuk mengaitkan petunjuk kueri atau rencana kueri aktual dengan kueri dalam database. Untuk informasi selengkapnya tentang panduan paket, lihat Panduan Paket.
Sintaks
sp_create_plan_guide [ @name = ] N'plan_guide_name'
, [ @stmt = ] N'statement_text'
, [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
, [ @module_or_batch = ]
{
N'[ schema_name. ] object_name'
| N'batch_text'
| NULL
}
, [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
, [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
| N'XML_showplan'
| 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.
[ @stmt = ] N'statement_text'
Adalah pernyataan T-SQL untuk membuat panduan rencana. Saat pengoptimal kueri SQL Server mengenali kueri yang cocok dengan statement_text, plan_guide_name berlaku. Agar pembuatan panduan rencana berhasil, statement_text harus muncul dalam konteks yang ditentukan oleh parameter @type, @module_or_batch, dan @params.
statement_text harus disediakan dengan cara yang memungkinkan pengoptimal kueri mencocokkannya dengan pernyataan yang sesuai yang disediakan dalam batch atau modul yang diidentifikasi oleh @module_or_batch dan @params. Untuk informasi selengkapnya, lihat bagian "Komentar". Ukuran statement_text hanya dibatasi oleh memori server yang tersedia.
[@type = ] N'{ OBJECT | SQL | TEMPLAT }'
Adalah jenis entitas tempat statement_text muncul. Ini menentukan konteks untuk mencocokkan statement_text dengan plan_guide_name.
OBJECT
Menunjukkan statement_text muncul dalam konteks prosedur tersimpan Transact-SQL, fungsi skalar, fungsi bernilai tabel multistatement, atau pemicu DML Transact-SQL dalam database saat ini.
SQL
Menunjukkan statement_text muncul dalam konteks pernyataan atau batch yang berdiri sendiri yang dapat dikirimkan ke SQL Server melalui mekanisme apa pun. Pernyataan Transact-SQL yang dikirimkan oleh objek runtime bahasa umum (CLR) atau prosedur tersimpan yang diperluas, atau dengan menggunakan EXEC N'sql_string', diproses sebagai batch di server dan, oleh karena itu, harus diidentifikasi sebagai @type = 'SQL'. Jika SQL ditentukan, PARAMETERISASI petunjuk kueri { FORCED | SIMPLE } tidak dapat ditentukan dalam parameter @hints.
TEMPLATE
Menunjukkan panduan paket berlaku untuk kueri apa pun yang berparameter ke formulir yang ditunjukkan dalam statement_text. Jika TEMPLAT ditentukan, hanya PARAMETERISASI { FORCED | Petunjuk kueri SIMPLE } dapat ditentukan dalam parameter @hints. Untuk informasi selengkapnya tentang panduan paket TEMPLAT, lihat Menentukan Perilaku Parameterisasi Kueri dengan Menggunakan Panduan Paket.
[@module_or_batch =] { N'[ schema_name. ] object_name' | N'batch_text' | NULL }
Menentukan nama objek tempat statement_text muncul, atau teks batch tempat statement_text muncul. Teks batch tidak dapat menyertakan pernyataan database USE.
Agar panduan paket sesuai dengan batch yang dikirimkan dari aplikasi, batch_text harus disediakan dalam format yang sama, karakter untuk karakter, seperti yang dikirimkan ke SQL Server. Tidak ada konversi internal yang dilakukan untuk memfasilitasi kecocokan ini. Untuk informasi lebih lanjut, lihat bagian Keterangan.
[schema_name.] object_name menentukan nama prosedur tersimpan Transact-SQL, fungsi skalar, fungsi bernilai tabel multistatement, atau pemicu DML Transact-SQL yang berisi statement_text. Jika schema_name tidak ditentukan, schema_name menggunakan skema pengguna saat ini. Jika NULL ditentukan dan @type = 'SQL', nilai @module_or_batch diatur ke nilai @stmt. Jika @type = 'TEMPLATE**'**, @module_or_batch harus NULL.
[ @params = ] { N'@parameter_name data_type [ , ... n ]' | NULL }
Menentukan definisi semua parameter yang disematkan dalam statement_text. @params hanya berlaku jika salah satu hal berikut ini benar:
@type = 'SQL' atau 'TEMPLATE'. Jika 'TEMPLATE', @params tidak boleh NULL.
statement_text dikirimkan dengan menggunakan
sp_executesqldan nilai untuk parameter @params ditentukan, atau SQL Server secara internal mengirimkan pernyataan setelah membuat parameter. Pengiriman kueri berparameter dari API database (termasuk ODBC, OLE DB, dan ADO.NET) muncul ke SQL Server sebagai panggilan kesp_executesqlatau ke rutinitas kursor server API; oleh karena itu, mereka juga dapat dicocokkan dengan panduan paket SQL atau TEMPLATE.
@parameter_name data_type harus disediakan dalam format yang sama persis seperti yang dikirimkan ke SQL Server baik dengan menggunakan sp_executesql atau dikirimkan secara internal setelah parameterisasi. Untuk informasi lebih lanjut, lihat bagian Keterangan. Jika batch tidak berisi parameter, NULL harus ditentukan. Ukuran @params hanya dibatasi oleh memori server yang tersedia.
[@hints = ] { N'OPTION (query_hint [ ,... n ] )' | N'XML_showplan' | NULL }
N'OPTION (query_hint [ ,... n ] )
Menentukan klausa OPTION untuk melampirkan ke kueri yang cocok dengan @stmt. @hints harus secara sintetis sama dengan klausul OPTION dalam pernyataan SELECT, dan dapat berisi urutan petunjuk kueri yang valid.
N'XML_showplan'
Apakah rencana kueri dalam format XML akan diterapkan sebagai petunjuk.
Sebaiknya tetapkan Xml Showplan ke variabel; jika tidak, Anda harus lolos dari tanda kutip tunggal di Showplan dengan mendahuluinya dengan tanda kutip tunggal lainnya. Lihat contoh E.
NULL
Menunjukkan bahwa petunjuk yang ada yang ditentukan dalam klausa OPTION kueri tidak diterapkan ke kueri. Untuk informasi selengkapnya, lihat Klausul OPTION (Transact-SQL).
Keterangan
Argumen untuk sp_create_plan_guide harus disediakan dalam urutan yang ditampilkan. Saat Anda menyediakan nilai untuk parameter sp_create_plan_guide, semua nama parameter harus ditentukan secara eksplisit, atau tidak sama sekali. Misalnya, jika @name = ditentukan, maka @stmt = , @type =, dan sebagainya, juga harus ditentukan. Demikian juga, jika @name = dihilangkan dan hanya nilai parameter yang disediakan, nama parameter yang tersisa juga harus dihilangkan, dan hanya nilainya yang disediakan. Nama argumen hanya untuk tujuan deskriptif, untuk membantu memahami sintaks. SQL Server tidak memverifikasi bahwa nama parameter yang ditentukan cocok dengan nama untuk parameter di posisi tempat nama digunakan.
Anda dapat membuat lebih dari satu panduan paket OBJECT atau SQL untuk kueri dan batch atau modul yang sama. Namun, hanya satu panduan paket yang dapat diaktifkan pada waktu tertentu.
Panduan paket jenis OBJECT tidak dapat dibuat untuk nilai @module_or_batch yang mereferensikan prosedur tersimpan, fungsi, atau pemicu DML yang menentukan klausa WITH ENCRYPTION atau yang bersifat sementara.
Mencoba menghilangkan atau mengubah fungsi, prosedur tersimpan, atau pemicu DML yang dirujuk oleh panduan paket, baik diaktifkan atau dinonaktifkan, menyebabkan kesalahan. Mencoba menghilangkan tabel yang memiliki pemicu yang ditentukan di atasnya yang dirujuk oleh panduan rencana juga menyebabkan kesalahan.
Catatan
Panduan paket tidak dapat digunakan di setiap edisi Microsoft SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Fitur yang Didukung oleh Edisi SQL Server 2016. Panduan paket terlihat dalam edisi apa pun. Anda juga dapat melampirkan database yang berisi panduan paket ke edisi apa pun. Panduan paket tetap utuh saat Anda memulihkan atau melampirkan database ke versi SQL Server yang ditingkatkan. Anda harus memverifikasi diinginkannya panduan paket di setiap database setelah melakukan peningkatan server.
Persyaratan pencocokan Panduan Rencana
Untuk panduan rencana yang menentukan @type = 'SQL' atau @type = 'TEMPLATE' agar berhasil mencocokkan kueri, nilai untuk batch_text dan @parameter_name data_type [,... n ] harus disediakan dalam format yang sama persis dengan rekan-rekan mereka yang dikirimkan oleh aplikasi. Ini berarti Anda harus menyediakan teks batch persis seperti pengkompilasi SQL Server menerimanya. Untuk mengambil teks batch dan parameter aktual, Anda dapat menggunakan SQL Server Profiler. Untuk informasi selengkapnya, lihat Menggunakan SQL Server Profiler untuk Membuat dan Menguji Panduan Rencana.
Saat @type = 'SQL' dan @module_or_batch diatur ke NULL, nilai @module_or_batch diatur ke nilai @stmt. Ini berarti bahwa nilai untuk statement_text harus disediakan dalam format yang sama persis, karakter-untuk-karakter, seperti yang dikirimkan ke SQL Server. Tidak ada konversi internal yang dilakukan untuk memfasilitasi kecocokan ini.
Ketika SQL Server cocok dengan nilai statement_text dengan batch_text dan @parameter_name data_type [,... n ], atau jika @type = **'**OBJECT', ke teks kueri terkait di dalam object_name, elemen string berikut tidak dipertimbangkan:
Karakter spasi kosong (tab, spasi, pengembalian gerak, atau umpan baris) di dalam string.
Komentar (-- atau /* */).
Titik koma berikutnya
Misalnya, SQL Server dapat mencocokkan string N'SELECT * FROM T WHERE a = 10' statement_text dengan batch_text berikut:
N'SELECT *
FROM T
WHERE a = 10'
Namun, string yang sama tidak akan dicocokkan dengan batch_text ini:
N'SELECT * FROM T WHERE b = 10'
SQL Server mengabaikan karakter pengembalian gerbong, umpan baris, dan spasi di dalam kueri pertama. Pada kueri kedua, urutan WHERE b = 10 ditafsirkan secara berbeda dari WHERE a = 10. Pencocokan peka huruf besar/kecil dan aksen (bahkan ketika kolatasi database tidak peka huruf besar/kecil), kecuali dalam kasus kata kunci, di mana kasus tidak sensitif. Pencocokan sensitif terhadap spasi kosong. Pencocokan tidak peka terhadap bentuk kata kunci yang dipersingkat. Misalnya, kata kunci EXECUTE, EXEC, dan execute dianggap setara.
Efek Panduan Rencana pada cache paket
Membuat panduan paket pada modul akan menghapus rencana kueri untuk modul tersebut dari cache paket. Membuat panduan paket jenis OBJECT atau SQL pada batch akan menghapus rencana kueri untuk batch yang memiliki nilai hash yang sama. Membuat panduan paket jenis TEMPLATE menghapus semua batch pernyataan tunggal dari cache paket dalam database tersebut.
Izin
Untuk membuat panduan paket jenis OBJECT, memerlukan ALTER izin pada objek yang dirujuk. Untuk membuat panduan paket jenis SQL atau TEMPLATE, memerlukan ALTER izin pada database saat ini.
Contoh
J. Membuat panduan paket tipe OBJECT untuk kueri dalam prosedur tersimpan
Contoh berikut membuat panduan rencana yang cocok dengan kueri yang dijalankan dalam konteks prosedur tersimpan berbasis aplikasi, dan menerapkan OPTIMIZE FOR petunjuk ke kueri.
Berikut adalah prosedur tersimpan:
IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry
(@Country_region nvarchar(60))
AS
BEGIN
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country_region;
END
GO
Berikut adalah panduan paket yang dibuat pada kueri dalam prosedur tersimpan:
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID
WHERE t.CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';
B. Membuat panduan paket jenis SQL untuk kueri mandiri
Contoh berikut membuat panduan rencana untuk mencocokkan kueri dalam batch yang dikirimkan oleh aplikasi yang menggunakan prosedur tersimpan sp_executesql sistem.
Berikut adalah batch:
SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;
Untuk mencegah rencana eksekusi paralel dihasilkan pada kueri ini, buat panduan paket berikut:
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT TOP 1 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';
C. Membuat panduan paket jenis TEMPLAT untuk formulir kueri berparameter
Contoh berikut membuat panduan rencana yang cocok dengan kueri apa pun yang diparameterkan ke formulir tertentu, dan mengarahkan SQL Server untuk memaksa parameterisasi kueri. Dua kueri berikut secara sinaptis setara, tetapi hanya berbeda dalam nilai harfiah konstan mereka.
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
Berikut adalah panduan rencana tentang bentuk kueri yang diparameterkan:
EXEC sp_create_plan_guide
@name = N'TemplateGuide1',
@stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = @0',
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = N'@0 int',
@hints = N'OPTION(PARAMETERIZATION FORCED)';
Dalam contoh sebelumnya, nilai untuk @stmt parameter adalah bentuk kueri berparameter. Satu-satunya cara yang dapat diandalkan untuk mendapatkan nilai ini untuk digunakan dalam sp_create_plan_guide adalah dengan menggunakan prosedur tersimpan sistem sp_get_query_template . Skrip berikut dapat digunakan keduanya untuk mendapatkan kueri berparameter lalu membuat panduan rencana di atasnya.
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;',
@stmt OUTPUT,
@params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';
Penting
Nilai literal konstanta dalam parameter yang @stmt diteruskan ke sp_get_query_template dapat memengaruhi jenis data yang dipilih untuk parameter yang menggantikan literal. Ini akan memengaruhi pencocokan panduan rencana. Anda mungkin harus membuat lebih dari satu panduan paket untuk menangani rentang nilai parameter yang berbeda.
D. Membuat panduan paket pada kueri yang dikirimkan dengan menggunakan permintaan kursor API
Panduan paket dapat mencocokkan kueri yang dikirimkan dari rutinitas kursor server API. Rutinitas ini termasuk sp_cursorprepare, sp_cursorprepexec, dan sp_cursoropen. Aplikasi yang menggunakan API ADO, OLE DB, dan ODBC sering berinteraksi dengan SQL Server dengan menggunakan kursor server API. Anda dapat melihat pemanggilan rutinitas kursor server API di jejak SQL Server Profiler dengan melihat peristiwa pelacakan RPC:Memulai profiler.
Misalkan data berikut muncul dalam peristiwa RPC:Memulai pelacakan profiler untuk kueri yang ingin Anda sesuaikan dengan panduan paket:
DECLARE @p1 int;
SET @p1=-1;
DECLARE @p2 int;
SET @p2=0;
DECLARE @p5 int;
SET @p5=4104;
DECLARE @p6 int;
SET @p6=8193;
DECLARE @p7 int;
SET @p7=0;
EXEC sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(255),@P2 varchar(255)',N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',@p5 OUTPUT,@p6 OUTPUT,@p7 OUTPUT,'20040101','20050101'
SELECT @p1, @p2, @p5, @p6, @p7;
Anda melihat bahwa paket kueri SELECT dalam panggilan sp_cursorprepexec menggunakan gabungan gabungan, tetapi Anda ingin menggunakan gabungan hash. Kueri yang dikirimkan dengan menggunakan sp_cursorprepexec diparameterkan, termasuk string kueri dan string parameter. Anda dapat membuat panduan paket berikut untuk mengubah pilihan paket dengan menggunakan string kueri dan parameter persis seperti yang muncul, karakter untuk karakter, dalam panggilan ke sp_cursorprepexec.
EXEC sp_create_plan_guide
@name = N'APICursorGuide',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
WHERE h.OrderDate BETWEEN @P1 AND @P2',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 varchar(255),@P2 varchar(255)',
@hints = N'OPTION(HASH JOIN)';
Eksekusi berikutnya dari kueri ini oleh aplikasi akan dipengaruhi oleh panduan rencana ini, dan gabungan hash akan digunakan untuk memproses kueri.
E. Membuat panduan paket dengan mendapatkan XML Showplan dari paket yang di-cache
Contoh berikut membuat panduan rencana untuk pernyataan SQL ad hoc sederhana. Rencana kueri yang diinginkan untuk pernyataan ini disediakan dalam panduan paket dengan menentukan Xml Showplan untuk kueri langsung dalam @hints parameter . Contoh pertama kali menjalankan pernyataan SQL untuk menghasilkan rencana dalam cache rencana. Untuk tujuan contoh ini, diasumsikan bahwa rencana yang dihasilkan adalah rencana yang diinginkan dan tidak diperlukan penyetelan kueri tambahan. Xml Showplan untuk kueri diperoleh dengan mengkueri sys.dm_exec_query_statstampilan manajemen dinamis , sys.dm_exec_sql_text, dan sys.dm_exec_text_query_plan dan ditetapkan ke @xml_showplan variabel . Variabel @xml_showplan kemudian diteruskan ke sp_create_plan_guide pernyataan dalam @hints parameter . Atau, Anda bisa membuat panduan paket dari rencana kueri di cache paket dengan menggunakan prosedur tersimpan sp_create_plan_guide_from_handle.
USE AdventureWorks2022;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');
EXEC sp_create_plan_guide
@name = N'Guide1_from_XML_showplan',
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints =@xml_showplan;
GO
Lihat Juga
Panduan Paket
sp_control_plan_guide (T-SQL)
sys.plan_guides (T-SQL)
Prosedur Tersimpan Mesin Database (Transact-SQL)
Prosedur Tersimpan Sistem (Transact-SQL)
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_exec_cached_plans (T-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sp_create_plan_guide_from_handle (T-SQL)
sys.fn_validate_plan_guide (T-SQL)
sp_get_query_template (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