Tentukan Perilaku Parameterisasi Kueri dengan Menggunakan Panduan Paket

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Saat opsi database PARAMETERISASI diatur ke SIMPLE, pengoptimal kueri SQL Server dapat memilih untuk membuat parameter kueri. Ini berarti bahwa setiap nilai harfiah yang terkandung dalam kueri diganti dengan parameter. Proses ini disebut sebagai parameterisasi sederhana. Ketika parameterisasi SIMPLE 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 opsi database PARAMETERISASI ke FORCED. Proses ini disebut sebagai parameterisasi paksa.

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

• Saat opsi database PARAMETERISASI diatur ke SIMPLE, Anda dapat menentukan bahwa parameterisasi paksa dicoba pada kelas kueri tertentu. Anda melakukan ini dengan membuat panduan paket TEMPLAT pada bentuk kueri yang diparameterkan, dan menentukan petunjuk kueri PARAMETERISASI PAKSA 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 tentang parameterisasi sederhana, lihat Panduan Arsitektur Pemrosesan Kueri.

• Saat opsi database PARAMETERISASI diatur ke FORCED, Anda dapat menentukan bahwa untuk kelas kueri tertentu, hanya parameterisasi sederhana yang dicoba, bukan parameterisasi paksa. Anda melakukan ini dengan membuat panduan paket TEMPLAT pada bentuk kueri yang diparameterkan paksa, dan menentukan petunjuk kueri PARAMETERISASI SIMPLE di sp_create_plan_guide. Untuk informasi selengkapnya tentang parameterisasi paksa, lihat Panduan Arsitektur Pemrosesan Kueri.

Pertimbangkan kueri berikut pada AdventureWorks2022 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 telah 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 dalam sp_create_plan_guide adalah dengan menggunakan prosedur tersimpan sistem sp_get_query_template .

2. Buat panduan rencana pada formulir kueri berparameter, menentukan petunjuk kueri PARAMETERISASI 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 terjadi pada nilai literal konstanta yang diteruskan ke parameter output @stmt sp_get_query_template. Karena tipe data yang ditentukan dalam argumen @params sp_create_plan_guide harus cocok dengan kueri seperti yang diparameterkan oleh SQL Server, Anda mungkin harus membuat lebih dari satu panduan paket untuk mencakup rentang lengkap nilai parameter yang mungkin untuk kueri.

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 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;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';  

Demikian pula, dalam database di mana parameterisasi paksa sudah diaktifkan, Anda dapat memastikan bahwa kueri sampel, dan lainnya yang secara sintis setara, kecuali untuk nilai harfiah konstannya, diparameterkan sesuai dengan aturan parameterisasi sederhana. Untuk melakukan ini, tentukan PARAMETERISASI SIMPLE alih-alih PARAMETERISASI YANG DIPAKSAKAN dalam klausa OPTION.

Catatan

Panduan paket TEMPLAT mencocokkan pernyataan ke kueri yang dikirimkan dalam batch yang hanya terdiri dari satu pernyataan. Pernyataan di dalam batch multistatement tidak memenuhi syarat untuk dicocokkan oleh panduan paket TEMPLATE.