Bagikan melalui

Panduan Paket

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Panduan rencana memungkinkan Anda mengoptimalkan performa kueri saat Anda tidak dapat atau tidak ingin langsung mengubah teks kueri aktual di SQL Server. Panduan rencana memengaruhi pengoptimalan kueri dengan melampirkan petunjuk kueri atau rencana kueri tetap kepada mereka. Panduan paket dapat berguna ketika subset kecil kueri dalam aplikasi database yang disediakan oleh vendor pihak ketiga tidak berkinerja seperti yang diharapkan. Dalam panduan paket, Anda menentukan pernyataan Transact-SQL yang ingin Anda optimalkan dan klausa OPTION yang berisi petunjuk kueri yang ingin Anda gunakan atau rencana kueri tertentu yang ingin Anda gunakan untuk mengoptimalkan kueri. Saat kueri dijalankan, SQL Server mencocokkan pernyataan Transact-SQL dengan panduan paket dan melampirkan klausul OPTION ke kueri pada waktu proses atau menggunakan rencana kueri yang ditentukan. Karena Pengoptimal Kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri, sebaiknya hanya menggunakan panduan paket sebagai upaya terakhir untuk pengembang dan administrator database yang berpengalaman.

Catatan

Petunjuk Penyimpanan Kueri menyediakan metode yang lebih mudah digunakan untuk membentuk rencana kueri tanpa mengubah kode aplikasi. Petunjuk Penyimpanan Kueri lebih sederhana daripada panduan paket. Petunjuk Penyimpanan Kueri tersedia di Azure SQL Database dan Azure SQL Managed Instance, dan di SQL Server 2022 (16.x) dan yang lebih baru.

Jumlah total panduan paket yang dapat Anda buat hanya dibatasi oleh sumber daya sistem yang tersedia. Namun demikian, panduan rencana harus terbatas pada kueri misi-kritis yang ditargetkan untuk meningkatkan atau menstabilkan performa. Panduan rencana tidak boleh digunakan untuk memengaruhi sebagian besar beban kueri aplikasi yang disebarkan.

Rencana eksekusi yang dihasilkan yang dipaksa oleh fitur ini akan sama atau mirip dengan rencana yang dipaksakan. Karena rencana yang dihasilkan mungkin tidak identik dengan rencana yang ditentukan oleh panduan rencana, performa rencana dapat bervariasi. Dalam kasus yang jarang terjadi, perbedaan performa mungkin signifikan dan negatif; dalam hal ini, administrator harus menghapus rencana paksa.

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.

Jenis Panduan Paket

Jenis panduan paket berikut dapat dibuat.

Panduan paket OBJECT

Panduan paket OBJECT cocok dengan kueri yang dijalankan dalam konteks prosedur tersimpan Transact-SQL, fungsi skalar yang ditentukan pengguna, fungsi bernilai tabel multi-pernyataan yang ditentukan pengguna, dan pemicu DML.

Misalkan prosedur tersimpan berikut, yang mengambil @Country_region parameter, berada dalam aplikasi database yang disebarkan terhadap AdventureWorks2022 database:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;

Asumsikan bahwa prosedur tersimpan ini telah dikompilasi dan dioptimalkan untuk @Country_region = N'AU' (Australia). Namun, karena relatif sedikit pesanan penjualan yang berasal dari Australia, performa menurun saat kueri dijalankan menggunakan nilai parameter negara/wilayah dengan lebih banyak pesanan penjualan. Karena pesanan penjualan terbanyak berasal dari Amerika Serikat, rencana kueri yang dihasilkan @Country_region = N'US' kemungkinan akan berkinerja lebih baik untuk semua nilai parameter yang @Country_region mungkin.

Anda dapat mengatasi masalah ini dengan mengubah prosedur tersimpan untuk menambahkan OPTIMIZE FOR petunjuk kueri ke kueri. Namun, karena prosedur tersimpan berada dalam aplikasi yang disebarkan, Anda tidak dapat langsung mengubah kode aplikasi. Sebagai gantinya, Anda dapat membuat panduan paket berikut dalam AdventureWorks2022 database.

sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,  
        Sales.Customer AS c,  
        Sales.SalesTerritory AS t  
        WHERE h.CustomerID = c.CustomerID   
            AND c.TerritoryID = t.TerritoryID  
            AND CountryRegionCode = @Country_region',  
@type = N'OBJECT',  
@module_or_batch = N'Sales.GetSalesOrderByCountry',  
@params = NULL,  
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

Saat kueri yang ditentukan dalam sp_create_plan_guide pernyataan dijalankan, kueri dimodifikasi sebelum pengoptimalan untuk menyertakan OPTIMIZE FOR (@Country = N''US'') klausa.

Panduan paket SQL

Panduan paket SQL cocok dengan kueri yang dijalankan dalam konteks pernyataan dan batch Transact-SQL yang berdiri sendiri yang bukan bagian dari objek database. Panduan paket berbasis SQL juga dapat digunakan untuk mencocokkan kueri yang membuat parameter ke formulir tertentu. Panduan paket SQL berlaku untuk pernyataan dan batch Transact-SQL yang berdiri sendiri. Sering kali, pernyataan ini dikirimkan oleh aplikasi dengan menggunakan prosedur tersimpan sistem sp_executesql . Misalnya, pertimbangkan batch yang berdiri sendiri berikut:

SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC;

Untuk mencegah rencana eksekusi paralel dihasilkan pada kueri ini, buat panduan paket berikut dan atur MAXDOP petunjuk kueri ke 1 dalam @hints parameter .

sp_create_plan_guide   
@name = N'Guide2',   
@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)';

Sebagai contoh lain, pertimbangkan pernyataan SQL berikut yang dikirimkan menggunakan sp_executesql.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;

Untuk membuat rencana unik untuk setiap eksekusi kueri ini, buat panduan paket berikut dan gunakan OPTION (RECOMPILE) petunjuk kueri dalam @hints parameter .

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

Penting

Nilai yang disediakan untuk @module_or_batch argumen sp_create_plan guide dan @params pernyataan harus cocok dengan teks terkait yang dikirimkan dalam kueri aktual. Untuk informasi selengkapnya, lihat sp_create_plan_guide (Transact-SQL) dan Menggunakan SQL Server Profiler untuk Membuat dan Menguji Panduan Rencana.

Panduan paket SQL juga dapat dibuat pada kueri yang berparameter ke formulir yang sama ketika opsi database PARAMETERISASI DIATUR ke FORCED, atau ketika panduan paket TEMPLAT dibuat menentukan bahwa kelas kueri yang diparameterkan.

Panduan paket TEMPLAT

Panduan paket TEMPLAT cocok dengan kueri mandiri yang berparameter ke formulir tertentu. Panduan paket ini digunakan untuk mengambil alih opsi KUMPULAN database PARAMETERISASI saat ini dari database untuk kelas kueri.

Anda dapat membuat panduan paket TEMPLAT dalam salah satu situasi berikut:

  • Opsi database PARAMETERISASI DIATUR ke FORCED, tetapi ada kueri yang ingin Anda kompilasi sesuai dengan aturan Parameterisasi Sederhana.

  • Opsi database PARAMETERISASI DIATUR ke SIMPLE (pengaturan default), tetapi Anda ingin Parameterisasi Paksa dicoba pada kelas kueri.

Persyaratan Pencocokan Panduan Rencana

Panduan paket dicakup ke database tempat panduan dibuat. Oleh karena itu, hanya panduan rencana yang ada di database yang saat ini saat kueri dijalankan yang dapat dicocokkan dengan kueri. Misalnya, jika AdventureWorks2022 adalah database saat ini dan kueri berikut dijalankan:

SELECT FirstName, LastName FROM Person.Person;

Hanya panduan paket dalam AdventureWorks2022 database yang memenuhi syarat untuk dicocokkan dengan kueri ini. Namun, jika AdventureWorks2022 adalah database saat ini dan pernyataan berikut dijalankan:

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

Hanya panduan paket di DB1 yang memenuhi syarat untuk dicocokkan dengan kueri karena kueri dijalankan dalam konteks DB1.

Untuk panduan paket berbasis SQL atau TEMPLATE, SQL Server cocok dengan nilai untuk @module_or_batch argumen dan @params dengan kueri dengan membandingkan dua nilai karakter menurut karakter. Ini berarti Anda harus memberikan teks persis seperti SQL Server menerimanya dalam batch aktual.

Ketika @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 identik, karakter-untuk-karakter, seperti yang dikirimkan ke SQL Server. Tidak ada konversi internal yang dilakukan untuk memfasilitasi kecocokan ini.

Ketika panduan paket reguler (SQL atau OBJECT) dan panduan paket TEMPLAT dapat berlaku untuk pernyataan, hanya panduan paket reguler yang akan digunakan.

Catatan

Batch yang berisi pernyataan tempat Anda ingin membuat panduan paket tidak boleh berisi pernyataan database USE.

Efek Panduan Rencana pada Singgahan 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.

Tugas Topik
Menjelaskan cara membuat panduan paket. Buat Panduan Paket Baru
Menjelaskan cara membuat panduan paket untuk kueri berparameter. Membuat Panduan Paket untuk Kueri Berparameter
Menjelaskan cara mengontrol perilaku parameterisasi kueri dengan menggunakan panduan rencana. Tentukan Perilaku Parameterisasi Kueri dengan Menggunakan Panduan Paket
Menjelaskan cara menyertakan rencana kueri tetap dalam panduan paket. Menerapkan Rencana Kueri Tetap ke Panduan Paket
Menjelaskan cara menentukan petunjuk kueri dalam panduan paket. Lampirkan Petunjuk Kueri ke Panduan Paket
Menjelaskan cara melihat properti panduan rencana. Lihat Properti Panduan Paket
Menjelaskan cara menggunakan SQL Server Profiler untuk membuat dan menguji panduan rencana. Menggunakan SQL Server Profiler untuk Membuat dan Menguji Panduan Rencana
Menjelaskan cara memvalidasi panduan paket. Memvalidasi Panduan Paket Setelah Peningkatan

Lihat Juga

sp_create_plan_guide (T-SQL)
sp_create_plan_guide_from_handle (T-SQL)
sp_control_plan_guide (T-SQL)
sys.plan_guides (T-SQL)
sys.fn_validate_plan_guide (T-SQL)