Bagikan melalui

Kompilasikan Ulang Prosedur Tersimpan

Topik ini menjelaskan cara mengkompilasi ulang prosedur tersimpan di SQL Server 2014 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 prosedur tersimpan sistem sp_recompile. Topik ini menjelaskan penggunaan opsi WITH RECOMPILE saat membuat definisi prosedur dan menjalankan prosedur yang ada. Ini juga menjelaskan cara menggunakan prosedur sistem tersimpan sp_recompile untuk mengkompilasi ulang prosedur yang ada.

Dalam Topik Ini

Sebelum Anda mulai

Rekomendasi

  • Ketika prosedur dikompilasi untuk pertama kalinya atau dikompilasi ulang, rencana kueri prosedur dioptimalkan untuk status database saat ini dan objeknya. Jika sebuah database mengalami perubahan signifikan pada data atau strukturnya, penyusunan ulang prosedur akan memperbarui dan mengoptimalkan rencana kueri prosedur tersebut untuk mengakomodasi perubahan. 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 untuk dikompilasi ulang adalah untuk melawan pengaruh dari perilaku "parameter sniffing" pada kompilasi prosedur. Ketika 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 merupakan nilai khas saat prosedur tersebut kemudian dipanggil, maka prosedur akan mendapat manfaat dari rencana kueri setiap kali dikompilasi dan dijalankan. Jika nilai parameter pada prosedur sering atipikal, memaksa kompilasi ulang prosedur dan rencana baru berdasarkan nilai parameter yang berbeda dapat meningkatkan performa.

  • SQL Server memungkinkan kompilasi ulang prosedur pada tingkat pernyataan. Ketika SQL Server mengkompilasi ulang prosedur tersimpan, hanya pernyataan yang menyebabkan kompilasi ulang dikompilasi, alih-alih 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 dikompilasi ulang alih-alih seluruh prosedur, perilaku kompilasi ulang tingkat pernyataan pada SQL Server ditiru. 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).

Keamanan

Hak akses

WITH RECOMPILE Pilihan
Jika opsi ini digunakan saat definisi prosedur dibuat, opsi ini memerlukan izin CREATE PROCEDURE dalam database dan izin ALTER 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).

RECOMPILE Petunjuk Kueri
Fitur ini digunakan ketika prosedur dibuat dan petunjuk disertakan dalam pernyataan Transact-SQL dalam prosedur. Oleh karena itu, diperlukan izin CREATE PROCEDURE dalam database dan izin UBAH pada skema di mana prosedur sedang dibuat.

sp_recompile Prosedur Tersimpan Sistem
Memerlukan izin ALTER pada prosedur yang ditentukan.

Menggunakan T-SQL

Untuk mengkompilasi ulang prosedur tersimpan dengan menggunakan opsi WITH RECOMPILE

  1. Sambungkan ke Mesin Database.

  2. Dari bilah Standar, klik Kueri Baru.

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

USE AdventureWorks2012;  
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

  1. Sambungkan ke Mesin Database.

  2. Dari bilah Standar, klik Kueri Baru.

  3. Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan. Contoh ini mengilustrasikan prosedur sederhana yang mengembalikan semua karyawan (nama depan dan belakang yang disediakan), posisi pekerjaan mereka, dan nama departemen mereka dari tampilan database.

    Lalu salin dan tempel contoh kode kedua ke jendela kueri dan klik Jalankan. Ini menjalankan prosedur dan mengkompilasi ulang rencana kueri prosedur.

USE AdventureWorks2012;  
GO  
EXECUTE HumanResources.uspGetAllEmployees WITH RECOMPILE;  
GO

Untuk mengkompilasi ulang prosedur tersimpan dengan menggunakan sp_recompile

  1. Sambungkan ke Mesin Database.

  2. Dari bilah Standar, klik Kueri Baru.

  3. Salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan. Contoh ini membuat prosedur sederhana yang mengembalikan data semua karyawan (nama depan dan nama belakang disediakan), jabatan mereka, dan nama departemen mereka dari suatu tampilan.

    Kemudian, salin dan tempel contoh berikut ke dalam jendela kueri dan klik Jalankan. Ini tidak menjalankan prosedur tetapi menandai prosedur untuk dikommpilasikan ulang sehingga rencana kuerinya diperbarui saat berikutnya prosedur dijalankan.

USE AdventureWorks2012;  
GO  
EXEC sp_recompile N'HumanResources.uspGetAllEmployees';  
GO

Lihat Juga

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

  • Last updated on