Mengkombinasikan ulang Prosedur Tersimpan

Artikel
04/03/2023

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Analytics Platform System (PDW)

Artikel ini menjelaskan cara mengkompilasi ulang prosedur tersimpan di SQL Server dengan menggunakan Transact-SQL. Ada tiga cara untuk melakukan ini: WITH RECOMPILE opsi dalam definisi prosedur atau ketika prosedur dipanggil, petunjuk kueri RECOMPILE pada pernyataan individual, atau dengan menggunakan sp_recompile prosedur tersimpan sistem.

Sebelum Anda mulai

Rekomendasi

Saat prosedur dikompilasi untuk pertama kalinya atau dikompilasi ulang, rencana kueri prosedur dioptimalkan untuk status database saat ini dan objeknya. Jika database mengalami perubahan signifikan pada data atau strukturnya, mengolah ulang pembaruan prosedur dan mengoptimalkan rencana kueri prosedur untuk perubahan tersebut. Ini dapat meningkatkan performa pemrosesan prosedur.
Ada kalanya kompilasi ulang prosedur harus dipaksakan dan di lain waktu ketika terjadi secara otomatis. Kompilasi ulang otomatis terjadi setiap kali SQL Server dimulai ulang. Ini juga terjadi jika tabel yang mendasar yang dirujuk oleh prosedur telah mengalami perubahan desain fisik.
Alasan lain untuk memaksa prosedur kompilasi ulang adalah untuk melawan perilaku "parameter sniffing" kompilasi prosedur. Saat SQL Server menjalankan prosedur, nilai parameter apa pun yang digunakan oleh prosedur saat dikompilasi disertakan sebagai bagian dari pembuatan rencana kueri. Jika nilai-nilai ini mewakili nilai-nilai khas yang kemudian dipanggil prosedur, maka prosedur mendapat manfaat dari rencana kueri setiap kali mengkompilasi dan menjalankannya. Jika nilai parameter pada prosedur sering atipikal, memaksa kompilasi ulang prosedur dan rencana baru berdasarkan nilai parameter yang berbeda dapat meningkatkan performa.
SQL Server fitur kompilasi ulang prosedur tingkat pernyataan. Ketika SQL Server mengkompilasi ulang prosedur yang disimpan, hanya pernyataan yang menyebabkan kompilasi ulang dikompilasi, bukan prosedur lengkap.
Jika kueri tertentu dalam prosedur secara teratur menggunakan nilai atipikal atau sementara, performa prosedur dapat ditingkatkan dengan menggunakan petunjuk kueri RECOMPILE di dalam kueri tersebut. Karena hanya kueri yang menggunakan petunjuk kueri yang akan dikommpilasikan ulang alih-alih prosedur lengkap, perilaku kompilasi ulang tingkat pernyataan SQL Server dimigrasikan. Tetapi selain menggunakan nilai parameter prosedur saat ini, petunjuk kueri RECOMPILE juga menggunakan nilai variabel lokal apa pun di dalam prosedur tersimpan saat Anda mengkompilasi pernyataan. Untuk informasi selengkapnya, lihat Petunjuk Kueri (Transact-SQL).

Catatan

Di Azure Synapse Analytics kumpulan khusus dan tanpa server, prosedur yang disimpan bukan kode yang telah dikompilasi sebelumnya, sehingga tidak dapat dikompilasi ulang. Untuk informasi selengkapnya, lihat Menggunakan prosedur tersimpan untuk kumpulan SQL khusus di Azure Synapse Analytics.

Keamanan

Izin

Dengan opsi KOMPILASI ULANG

Jika opsi ini digunakan saat definisi prosedur dibuat, diperlukan izin CREATE PROCEDURE dalam database dan izin UBAH pada skema tempat prosedur sedang dibuat.

Jika opsi ini digunakan dalam pernyataan EXECUTE, opsi ini memerlukan izin EXECUTE pada prosedur. Izin tidak diperlukan pada pernyataan EXECUTE itu sendiri tetapi izin eksekusi diperlukan pada prosedur yang dirujuk dalam pernyataan EXECUTE. Untuk informasi selengkapnya, lihat EXECUTE (Transact-SQL).

Petunjuk kueri RECOMPILE

Fitur ini digunakan ketika prosedur dibuat dan petunjuk disertakan dalam pernyataan T-SQL dalam prosedur. Oleh karena itu, diperlukan izin CREATE PROCEDURE dalam database dan izin UBAH pada skema tempat prosedur sedang dibuat.

sp_recompile prosedur tersimpan sistem

Memerlukan izin UBAH pada prosedur yang ditentukan.

Menggunakan T-SQL

Sambungkan ke Mesin Database.
Dari bilah Standar , pilih Kueri Baru.

Salin dan tempel contoh berikut ke dalam jendela kueri dan pilih Jalankan. Contoh ini membuat definisi prosedur.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL   
    DROP PROCEDURE dbo.uspProductByVendor;  
GO  
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'  
WITH RECOMPILE  
AS  
    SET NOCOUNT ON;  
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'  
    FROM Purchasing.Vendor AS v   
    JOIN Purchasing.ProductVendor AS pv   
      ON v.BusinessEntityID = pv.BusinessEntityID   
    JOIN Production.Product AS p   
      ON pv.ProductID = p.ProductID  
    WHERE v.Name LIKE @Name;

Untuk mengkompilasi ulang prosedur tersimpan dengan menggunakan opsi WITH RECOMPILE

Pilih Kueri Baru, lalu salin dan tempel contoh kode berikut ke dalam jendela kueri dan pilih Jalankan. Ini menjalankan prosedur dan mengolah ulang rencana kueri prosedur.

USE AdventureWorks2022;  
GO  
EXECUTE HumanResources.uspProductByVendor WITH RECOMPILE;  
GO

Untuk mengkompilasi ulang prosedur tersimpan dengan menggunakan sp_recompile

Pilih Kueri Baru, lalu salin dan tempel contoh berikut ke dalam jendela kueri dan pilih Jalankan. Ini tidak menjalankan prosedur tetapi menandai prosedur yang akan dikommpilasikan ulang sehingga rencana kuerinya diperbarui saat berikutnya prosedur dijalankan.

USE AdventureWorks2022;  
GO  
EXEC sp_recompile N'dbo.uspProductByVendor';   
GO

Langkah berikutnya

Membuat Prosedur Tersimpan
Mengubah Prosedur Tersimpan
Mengganti nama Prosedur Tersimpan
Lihat Definisi Prosedur Tersimpan
Menampilkan Dependensi Prosedur Tersimpan
DROP PROCEDURE (Transact-SQL)