Tentukan perilaku parameterisasi kueri dengan menggunakan panduan rencana

Berlaku untuk: SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Saat Anda mengatur PARAMETERIZATION opsi database ke SIMPLE, pengoptimal kueri SQL Server mungkin memilih untuk membuat parameter kueri. Parameterisasi ini menggantikan nilai harfiah apa pun dalam kueri dengan parameter. Proses ini disebut sebagai parameterisasi sederhana. Saat SIMPLE parameterisasi berlaku, Anda tidak dapat mengontrol kueri mana yang diparameterkan dan kueri mana yang tidak. Namun, Anda dapat menentukan bahwa semua kueri dalam database diparameterkan dengan mengatur PARAMETERIZATION opsi database ke FORCED. Proses ini disebut sebagai parameterisasi paksa.

Anda dapat mengambil alih perilaku parameterisasi database dengan menggunakan panduan paket dengan cara berikut:

Option Deskripsi
SIMPLE Anda dapat menentukan bahwa parameterisasi paksa dicoba pada kelas kueri tertentu. Anda melakukannya dengan membuat panduan rencana TEMPLATE pada bentuk kueri yang diberi parameter, dan menentukan petunjuk kueri PARAMETERIZATION FORCED dalam prosedur tersimpan sp_create_plan_guide. Anda dapat mempertimbangkan panduan paket semacam ini sebagai cara untuk mengaktifkan parameterisasi paksa hanya pada kelas kueri tertentu, bukan semua kueri. Untuk informasi selengkapnya, lihat Parameterisasi sederhana.
FORCED Anda dapat menentukan bahwa untuk kelas kueri tertentu, hanya parameterisasi sederhana yang dicoba, bukan parameterisasi paksa. Anda melakukannya dengan membuat panduan rencana TEMPLATE pada bentuk kueri yang diparametrisasi secara paksa, dan menentukan petunjuk kueri PARAMETERIZATION SIMPLE di sp_create_plan_guide. Untuk informasi selengkapnya, lihat Parameterisasi paksa.

Pertimbangkan kueri berikut pada AdventureWorks2025 database:

SELECT pi.ProductID,
       SUM(pi.Quantity) AS Total
FROM Production.ProductModel AS pm
     INNER JOIN Production.ProductInventory AS pi
         ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING SUM(pi.Quantity) > 50;

Sebagai administrator database, Anda menentukan bahwa Anda tidak ingin mengaktifkan parameterisasi paksa pada semua kueri dalam database. Namun, Anda ingin menghindari biaya kompilasi pada semua kueri yang secara sintetis setara dengan kueri sebelumnya, tetapi hanya berbeda dalam nilai literal konstan mereka. Dengan kata lain, Anda ingin kueri diparameterkan sehingga rencana kueri untuk kueri semacam ini digunakan kembali. Dalam hal ini, selesaikan langkah-langkah berikut:

  1. Ambil formulir kueri yang diparameterkan. Satu-satunya cara aman untuk mendapatkan nilai ini untuk digunakan sp_create_plan_guide adalah dengan menggunakan prosedur tersimpan sistem sp_get_query_template .

  2. Buat panduan rencana pada bentuk kueri yang diparameterkan, dengan menentukan petunjuk kueri PARAMETERIZATION FORCED.

    Penting

    Sebagai bagian dari parameterisasi kueri, SQL Server menetapkan jenis data ke parameter yang menggantikan nilai harfiah, tergantung pada nilai dan ukuran literal. Proses yang sama berlaku untuk nilai literal konstanta yang diteruskan ke parameter output @stmt dari sp_get_query_template. Karena tipe data yang ditentukan untuk argumen @params pada sp_create_plan_guide harus cocok dengan tipe data kueri saat kueri tersebut diparameterkan oleh SQL Server, Anda mungkin perlu membuat lebih dari satu panduan rencana untuk mencakup seluruh rentang kemungkinan nilai parameter untuk kueri tersebut.

Skrip berikut dapat digunakan keduanya untuk mendapatkan kueri berparameter lalu membuat panduan rencana di atasnya:

DECLARE @stmt AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE sp_get_query_template
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total
      FROM Production.ProductModel AS pm
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID
      WHERE pi.ProductID = 101
      GROUP BY pi.ProductID, pi.Quantity
      HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, @params OUTPUT;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
@stmt, N'TEMPLATE', NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)';

Jika parameterisasi paksa sudah diaktifkan pada database, Anda dapat mengambil alihnya untuk kueri tertentu. Untuk membuat parameter kueri sampel dan kueri yang setara secara sinonis sesuai dengan aturan parameterisasi sederhana, tentukan PARAMETERIZATION SIMPLE alih-alih PARAMETERIZATION FORCED dalam OPTION klausa.

Catatan

Petunjuk rencana TEMPLAT mencocokkan pernyataan dengan kueri yang dikirimkan dalam kelompok yang terdiri hanya dari satu pernyataan. Pernyataan di dalam batch multistatement tidak memenuhi syarat untuk dicocokkan dengan panduan rencana TEMPLATE.

Konten terkait

  • Last updated on