Bagikan melalui

sp_create_plan_guide (T-SQL)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure 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.

Konvensi sintaks transact-SQL

Sintaks

sp_create_plan_guide
    [ @name = ] N'name'
    [ , [ @stmt = ] N'stmt' ]
    , [ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
    [ , [ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' } ]
    [ , [ @params = ] N'@parameter_name data_type [ ,... n ]' ]
    [ , [ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' | N'XML_showplan' } ]
[ ; ]

Argumen

[ @name = ] N'name'

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

[ @stmt = ] N'stmt'

Pernyataan Transact-SQL untuk membuat panduan rencana. @stmt adalah nvarchar(max), dengan default NULL. Saat pengoptimal kueri SQL Server mengenali kueri yang cocok dengan @stmt, @name berlaku. Agar pembuatan panduan rencana berhasil, @stmt harus muncul dalam konteks yang ditentukan oleh parameter @type, @module_or_batch, dan @params .

@stmt 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 Keterangan. Ukuran @stmt hanya dibatasi oleh memori server yang tersedia.

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

Jenis entitas tempat @stmt muncul. Ini menentukan konteks untuk mencocokkan @stmt dengan @name. @type adalah nvarchar(60), dan bisa menjadi salah satu nilai berikut:

  • OBJECT

    Menunjukkan @stmt muncul dalam konteks prosedur tersimpan Transact-SQL, fungsi skalar, fungsi bernilai tabel multistatement, atau pemicu DML Transact-SQL dalam database saat ini.

  • SQL

    Menunjukkan @stmt 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, petunjuk PARAMETERIZATION { FORCED | SIMPLE } kueri tidak dapat ditentukan dalam parameter @hints .

  • TEMPLATE

    Menunjukkan panduan paket berlaku untuk kueri apa pun yang berparameter ke formulir yang ditunjukkan dalam @stmt. Jika TEMPLATE ditentukan, hanya petunjuk kueri yang PARAMETERIZATION { FORCED | SIMPLE } dapat ditentukan dalam parameter @hints . Untuk informasi selengkapnya tentang TEMPLATE panduan rencana, lihat Menentukan Perilaku Parameterisasi Kueri dengan Menggunakan Panduan Paket.

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

Menentukan nama objek tempat @stmt muncul, atau teks batch tempat @stmt muncul. @module_or_batch adalah nvarchar(max), dengan default NULL. Teks batch tidak dapat menyertakan USE <database> pernyataan.

Agar panduan paket sesuai dengan batch yang dikirimkan dari aplikasi, @module_or_batch 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 selengkapnya, 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 @stmt. Jika <schema_name> tidak ditentukan, <schema_name> menggunakan skema pengguna saat ini. Jika NULL ditentukan dan @type adalah SQL, nilai @module_or_batch diatur ke nilai @stmt. Jika @type adalah TEMPLATE, @module_or_batch harus NULL.

[ @params = ] N'@parameter_name data_type [ ,... n ]'

Menentukan definisi semua parameter yang disematkan dalam @stmt. @params adalah nvarchar(max), dengan default NULL. @params hanya berlaku jika salah satu opsi berikut ini benar:

  • @type adalah SQL atau TEMPLATE. Jika TEMPLATE, @params tidak boleh NULL.

  • @stmt dikirimkan dengan menggunakan sp_executesql dan 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 ke sp_executesql atau ke rutinitas kursor server API; oleh karena itu, mereka juga dapat dicocokkan dengan SQL atau TEMPLATE merencanakan panduan.

@params 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 selengkapnya, 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' }

@hints adalah nvarchar(max), dengan default NULL.

  • OPTION ( <query_hint> [ , ...n ] )

    Menentukan klausa OPTION untuk dilampirkan ke kueri yang cocok dengan @stmt. @hints harus secara sintetis sama OPTION dengan klausul dalam SELECT pernyataan, dan dapat berisi urutan petunjuk kueri yang valid.

  • <XML_showplan>'

    Rencana kueri dalam format XML yang akan diterapkan sebagai petunjuk.

    Sebaiknya tetapkan showplan XML 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 OPTION klausul kueri tidak diterapkan ke kueri. Untuk informasi selengkapnya, lihat klausa OPTION.

Keterangan

Argumen yang 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 OBJECT atau SQL panduan paket 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 merupakan pemicu yang ditentukan di atasnya yang dirujuk oleh panduan rencana juga menyebabkan kesalahan.

Panduan paket tidak dapat digunakan di setiap edisi SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022. 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.

Merencanakan persyaratan pencocokan panduan

Untuk panduan rencana yang menentukan @type SQL atau TEMPLATE agar berhasil mencocokkan kueri, nilai untuk @module_or_batch dan @params [, ... 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 paket.

Saat @type dan SQL @module_or_batch diatur ke NULL, nilai @module_or_batch diatur ke nilai @stmt. Ini berarti bahwa nilai untuk @stmt 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 @stmt dengan @module_or_batch dan @params [, ... n ], atau jika @type adalah OBJECT, ke teks kueri yang sesuai 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' @stmt dengan @module_or_batch berikut:

 N'SELECT *
 FROM T
 WHERE a = 10'

Namun, string yang sama tidak akan dicocokkan dengan @module_or_batch 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 sensitif terhadap aksen (bahkan ketika kolatasi database tidak peka huruf besar/kecil), kecuali jika ada kata kunci, di mana huruf besar/kecil tidak peka. 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 merupakan 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 batchnya:

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 bentuk 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 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 sp_create_plan_guide untuk mendapatkan nilai ini untuk digunakan adalah dengan menggunakan prosedur tersimpan sistem sp_get_query_template. Skrip berikut 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 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 RPC:Starting peristiwa pelacakan profiler.

Misalkan data berikut muncul dalam RPC:Starting peristiwa 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 dipengaruhi oleh panduan paket ini, dan gabungan hash digunakan untuk memproses kueri.

E. Buat panduan paket dengan mendapatkan showplan XML dari paket yang di-cache

Contoh berikut membuat panduan rencana untuk pernyataan ad hoc SQL sederhana. Rencana kueri yang diinginkan untuk pernyataan ini disediakan dalam panduan paket dengan menentukan showplan XML untuk kueri langsung dalam @hints parameter . Contoh pertama-tama SQL menjalankan pernyataan untuk menghasilkan rencana dalam cache paket. Untuk tujuan contoh ini, diasumsikan bahwa rencana yang dihasilkan adalah rencana yang diinginkan dan tidak diperlukan penyetelan kueri lebih lanjut. Showplan XML untuk kueri diperoleh dengan mengkueri sys.dm_exec_query_statstampilan manajemen dinamis , , sys.dm_exec_sql_textdan 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

Konten terkait