Pemecahan masalah disimpan prosedur recompilation

Terjemahan Artikel Terjemahan Artikel
ID Artikel: 243586 - Melihat produk di mana artikel ini berlaku.
Perbesar semua | Perkecil semua

Pada Halaman ini

RINGKASAN

Artikel ini berkaitan dengan satu jenis tertentu kinerja masalah yang aplikasi mungkin mengalami dengan Microsoft SQL Server: run-time recompilation disimpan prosedur. Jika Anda adalah pemecahan masalah kinerja masalah, tetapi Anda belum teridentifikasi bahwa ini adalah tepat sumber Anda masalah, lihat artikel berikut pada Basis Pengetahuan Microsoft sebelum melanjutkan:

224587 CARA: Memecahkan masalah kinerja aplikasi dengan SQL Server
Artikel ini mengasumsikan bahwa Anda telah menggunakan yang artikel untuk mempersempit cakupan masalahnya, dan bahwa Anda telah menangkap SQL Server Profiler jejak dengan khusus peristiwa dan data kolom rinci di dalamnya.

INFORMASI LEBIH LANJUT

Ketika pengguna menjalankan prosedur yang disimpan, jika tidak sudah tersedia dalam cache, SQL Server load prosedur dan mengkompilasi permintaan rencana. Rencana terkompilasi disimpan dalam cache dan digunakan oleh penelepon berikutnya prosedur disimpan sampai terjadi beberapa tindakan untuk membatalkan rencana dan memaksa recompilation. Tindakan berikut dapat menyebabkan recompilation dari disimpan prosedur rencana:
  • Penggunaan dengan mengkompilasi ulang klausul dalam membuat prosedur atau MENGEKSEKUSI pernyataan.
  • Skema perubahan ke salah satu objek direferensikan, termasuk menambahkan atau menjatuhkan kendala, default atau aturan.
  • Menjalankan sp_recompile untuk tabel direferensikan oleh prosedur.
  • Memulihkan database yang berisi prosedur atau salah benda-benda referensi prosedur (jika Anda melakukan lintas-database operasi).
  • Cukup aktivitas server menyebabkan rencana harus berusia cache.
Semua alasan ini untuk mengkompilasi stored procedure memang ada di versi sebelumnya, dan menyebabkan rencana untuk mengkompilasi ulang sebelum awal pelaksanaan prosedur. Dalam SQL Server 7.0, perilaku baru diperkenalkan yang dapat menyebabkan stored procedure untuk mengkompilasi ulang selama eksekusi. Baru ini perilaku memastikan bahwa Pengoptimal selalu memiliki rencana yang terbaik untuk masing-masing pernyataan tertentu dalam prosedur. Peristiwa berikut dapat menyebabkan run-time recompilation disimpan prosedur:
  • Persentase perubahan data pada tabel yang cukup direferensikan oleh prosedur yang tersimpan.
  • Prosedur interleaves Data definisi bahasa (DDL) dan operasi manipulasi Data bahasa (DML).
  • Prosedur melakukan operasi tertentu sementara tabel.
Masing-masing penyebab ini dibahas secara lebih terperinci dalam hal ini artikel.

Dalam beberapa kasus, biaya rekompilasi prosedur yang tersimpan lebih dari manfaat yang berasal dari melakukannya, terutama untuk besar prosedur. Sangat penting untuk dicatat bahwa ketika recompilation memicu, seluruh batch atau prosedur adalah recompiled. Ini berarti bahwa kinerja degradasi secara langsung proposional dengan ukuran prosedur atau batch. Untuk informasi lebih lanjut tentang topik ini, lihat topik "Transact-SQL Tips" di SQL Server buku secara Online.


Informasi berikut di artikel ini berfokus pada mengidentifikasi penyebab run-time disimpan prosedur recompilations dan membahas metode yang dapat Anda gunakan untuk mencegah mereka.

Praktek terbaik

Cara terbaik untuk pemilik lolos disimpan prosedur nama ketika Anda melaksanakan prosedur. Hal ini memungkinkan untuk lebih baik kejelasan dan penggunaan kembali lebih mudah ada rencana pelaksanaan oleh pengguna saat ini. Misalnya, jika pengguna yang tidak pemilik database (dbo) milik dbo disimpan prosedur (disebut mengeksekusi myProc dalam contoh ini) di pub database, menggunakan pernyataan berikut:
exec dbo.myProc
				
Bukannya ini:
exec myProc
				
Teknik ini menghilangkan kebingungan tentang versi lain mungkin prosedur oleh pemilik yang berbeda dari sudut pandang coding dan pemeliharaan, dan juga memungkinkan SQL Server untuk mengakses rencana pelaksanaan prosedur tertentu lebih langsung.

Oleh tidak kualifikasi nama pemilik, SQL Server masuk kompilasi kode dan kunci KOMPILASI pada prosedur. Namun, akhirnya menentukan bahwa rencana baru ini tidak diperlukan (dengan asumsi tidak lain alasan berlaku), jadi itu tidak mengkompilasi ulang rencana saat ini karena kualifikasi. Namun, langkah ekstra untuk MENGKOMPILASI mengunci prosedur dapat menyebabkan pertengkaran memblokir dalam situasi yang parah. Merujuk kepada Q263889 INF: SQL menghalangi karena [[KOMPILASI]] kunci untuk detail lebih lanjut tentang situasi ini.

Jika Anda pemilik memenuhi syarat panggilan prosedur dengan owner.procedure, Anda lakukan tidak perlu untuk mendapatkan kunci kompilasi, jadi pertikaian berkurang.

Mengidentifikasi dan memecahkan masalah

Jika Anda tidak melakukannya, lihat artikel berikut di dalam Basis Pengetahuan Microsoft untuk informasi tentang menangkap Profiler data untuk membantu menganalisis kinerja sistem Anda:
224587 CARA: Memecahkan masalah kinerja aplikasi dengan SQL Server

Melihat Profiler Data

Termasuk SQL Server Profiler SP:Recompile acara yang dapat Anda gunakan untuk memonitor jumlah recompiles terjadi. The SP:Recompile peristiwa terjadi setiap kali prosedur yang disimpan recompiles selama eksekusi.
  • Kelompok jejak Profiler oleh Event kelas

    1. Pada Berkas menu, klik Properti.
    2. Pada Kolom data tab, gunakan tombol UP untuk memindahkan Acara kelas dan Teks di bawah Kelompok menuju, dengan Acara kelas terlebih dahulu. Gunakan tombol ke bawah untuk menghapus semua kolom lain di bawah Kelompok judul.
    3. Klik Oke.
    Memeriksa jumlah SP:Recompile peristiwa.

    Anda dapat memperluas grup SP:Recompile untuk melihat rincian setiap kemunculan. The Teks kolom acara menunjukkan nama prosedur yang tersimpan yang recompiled. Jika beberapa prosedur yang menyebabkan recompiles, mereka yang diurutkan jumlah kejadian. Jika Anda memiliki sejumlah besar SP:Recompile acara dan Anda mengalami tinggi penggunaan CPU, fokus pada menyelesaikan prosedur yang memiliki jumlah tertinggi recompiles. Catatan sistem proses ID (SPID) dan mulai waktu SP:Recompile acara untuk satu contoh tertentu procedure(s) disimpan dan Ikuti langkah-langkah di bawah ini.

    Jika Anda tidak melihat apapun SP:Recompile peristiwa, tapi Anda masih mengalami masalah kinerja, lihat artikel berikut pada Basis Pengetahuan Microsoft:
    224587 CARA: Memecahkan masalah kinerja aplikasi dengan SQL Server
  • Menentukan pernyataan yang memicu peristiwa mengkompilasi ulang

    1. Pada Berkas menu, klik Properti.
    2. Pada Kolom data tab, gunakan tombol ke bawah untuk menghapus semua kolom lain di bawah Kelompok judul.
    3. Pada Peristiwa tab, menghapus semua acara kecuali SP: mulai, SP:StmtStarting, SP:Recompile, dan SP: selesai. Jika Anda tidak menangkap SP:StmtStarting acara, Anda dapat menggantikan SP:StmtCompleted, tapi tidak termasuk kedua karena melakukan jadi lipat informasi yang Anda butuhkan untuk melihat melalui.
    4. Jika Anda telah mengidentifikasi sebuah contoh khusus dari disimpan prosedur recompilation untuk memeriksa, Anda dapat membatasi data yang Anda lihat untuk SPID spesifik dan kerangka waktu terjadinya dengan menggunakan Filter tab.
    5. Klik Oke.

    The SP:Recompile acara akan dibangkitkan secara langsung setelah SP:StmtStarted acara pernyataan disimpan prosedur yang menyebabkan recompilation. Setelah mengkompilasi ulang acara selesai, Anda akan melihat mengulang The SP:StmtStarted acara, menunjukkan bahwa pernyataan mengeksekusi dengan baru dihasilkan rencana.

    Perhatikan contoh berikut:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    Jika Anda mengeksekusi kode ini dalam Query Analyzer dan melihat kejadian di atas di jejak Profiler, Anda akan melihat urutan berikut:

    Perkecil tabel iniPerbesar tabel ini
    Acara kelasTeks
    SP: mulaiRecompProc
    SP:StmtStartingmembuat tabel # t (int)
    SP:StmtStartingPilih * dari # t
    SP:RecompileRecompProc
    SP:StmtStartingPilih * dari # t
    SP: selesaiRecompProc


    Anda dapat memberitahu segera bahwa pernyataan yang disebabkan recompilation adalah:
    select * from #t
    						
    Karena tampaknya baik sebelum dan setelah SP:Recompile acara.

    Jika Anda telah menangkap hanya SP:StmtCompleted acara, tetapi tidak SP:StmtStarting acara, SP:Recompile akan menunjukkan langsung sebelum pernyataan yang menyebabkan sebagai di bawah ini:

    Perkecil tabel iniPerbesar tabel ini
    Acara kelasTeks
    SP: mulaiRecompProc
    SP:RecompileRecompProc
    SP:StmtCompletedPilih * dari # t
    SP: selesaiRecompProc


    Anda dapat melihat bahwa SP:Recompile acara dibangkitkan sebelum SP:StmtCompleted acara untuk "Pilih * dari # t" pernyataan, yang menyebabkan recompilation. Ini masuk akal, seperti pernyataan tidak dapat diselesaikan sampai Setelah rencana permintaan baru dihasilkan untuk mengkompilasi ulang. Semua sisa contoh dalam artikel penggunaan SP:StmtStarting acara. Jika Anda telah menangkap hanya SP:StmtCompleted acara, hanya ingat untuk melihat pernyataan setelah SP:Recompile, seperti yang dijelaskan di atas.

    Catatan bahwa jika Anda mengeksekusi ini khususnya disimpan prosedur berkali-kali, SQL Server akan menggunakan kembali yang ada rencana untuk prosedur ini. Anda hanya akan melihat acara mengkompilasi ulang pertama pelaksanaan prosedur, atau jika Anda drop dan menciptakan kembali prosedur setiap saat Anda menjalankan script. Alasan untuk recompilation di spesifik ini kasus dibahas dalam "Recompilations Due to Interleaving Data definisi Bahasa (DDL) dan bahasa (DML) operasi manipulasi Data"bagian ini Artikel; ini adalah hanya sebuah contoh untuk menggambarkan bagaimana untuk dengan mudah menentukan yang pernyataan yang menyebabkan recompilation.

Recompilations karena untuk baris modifikasi

Jika persentase yang cukup data yang berubah dalam tabel direferensikan oleh prosedur yang disimpan sejak saat rencana permintaan awal mengkompilasi dihasilkan, SQL Server akan ulang stored procedure untuk memastikan bahwa ia memiliki rencana berdasarkan data statistik terbaru. Sebagai contoh, pertimbangkan disimpan prosedur berikut:
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
				
Untuk pelaksanaan kedua RowModifications prosedur, Anda akan melihat peristiwa berikut di Profiler:

Perkecil tabel iniPerbesar tabel ini
Acara kelasTeks
SP: mulaiRowModifications
SP:StmtStartingmembuat tabel # t (int, b char(10))
SP:StmtStartingPilih * dari # t
SP:StmtStartingMasukkan # t pilih * dari SomeTable
SP:StmtStartingPilih count(*) dari # t di mana = 37
SP:RecompileRowModifications
Auto-UpdateStatsa
SP:StmtStartingPilih count(*) dari # t di mana = 37
SP: selesaiRowModifications

CATATAN: Eksekusi pertama juga akan menampilkan SP:Recompile acara untuk "Pilih * dari # t" pernyataan. Alasan untuk recompilation dalam kasus khusus ini dibahas dalam "Recompilations Due to Interleaving Data definisi bahasa (DDL) dan bahasa manipulasi Data Operasi (DML)"bagian dari artikel ini. Untuk contoh ini, fokus pada SP:Recompile ditampilkan di atas karena itu terjadi setiap kali prosedur dijalankan.

Dalam contoh ini, "Pilih count(*) dari # t di mana = 37" menyebabkan recompilation prosedur karena perubahan dalam jumlah baris karena meja diciptakan. Kehadiran Auto-UpdateStats acara menegaskan bahwa recompilation adalah karena baris modifikasi. The Teks kolom menunjukkan kolom yang statistik yang diubah.

Ketika # t meja diciptakan, jumlah baris adalah nol. Rencana untuk asli "Pilih * dari # t" dikembangkan dengan hitungan baris, serta rencana untuk permintaan "select count (*)". Namun, sebelum "Pilih count(*)" dijalankan, 1.000 baris baru dimasukkan ke dalam tabel t #. Karena jumlah data yang memadai telah berubah, Pengoptimal recompiles prosedur untuk memastikan bahwa itu memilih rencana yang paling efisien untuk pernyataan. Recompilation ini akan terjadi pada setiap pelaksanaan yang disimpan prosedur karena penyisipan 1.000 baris akan selalu dapat dilihat sebagai cukup signifikan untuk menjamin recompilation.

Algoritma SQL Server menggunakan untuk menentukan apakah rencana harus recompiled adalah algoritma sama digunakan untuk auto-update statistik seperti yang dijelaskan dalam artikel berikut di Basis Pengetahuan Microsoft:
195565 INF: Bagaimana SQL Server 7.0 dan SQL Server 2000 Autostats bekerja
Dalam contoh di atas, disimpan prosedur kecil cukup bahwa recompilation tidak akan memiliki efek yang terlihat kinerja. Namun, jika Anda memiliki sebuah besar disimpan prosedur yang melakukan aktiviti-aktiviti serupa mengakibatkan beberapa recompilations, Anda mungkin melihat penurunan kinerja.

Ada metode berikut untuk melawan recompilations karena untuk baris modifikasi:
  • Mengeksekusi pernyataan menggunakan sp_executesql.
    Ini adalah metode yang disukai. Pernyataan yang dijalankan dengan menggunakansp_executesql prosedur yang disimpan tidak dikompilasi sebagai bagian dari prosedur yang tersimpan rencana. Oleh karena itu, ketika menjalankan pernyataan, SQL Server akan bebas untuk baik menggunakan rencana yang ada dalam cache untuk pernyataan atau buat yang baru di jangka waktu. Dalam kedua kasus, rencana untuk memanggil stored procedure adalah tidak terpengaruh dan tidak harus menjadi recompiled.

    Pernyataan EXECUTE akan memiliki efek yang sama; Namun, tidak dianjurkan. Menggunakan EXECUTE pernyataan ini tidak seefisien menggunakan sp_executesql karena tidak memungkinkan untuk parameterization dari permintaan.

    The RowModifications prosedur yang diberikan di atas dapat ditulis menggunakan sp_executesql sebagai berikut:

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- assume SomeTable exists with the same definition as #t, 
    -- and has over 1000 rows
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2
    						

    Untuk pelaksanaan kedua RowModifications2 prosedur, Anda akan melihat peristiwa berikut di Profiler:

    Perkecil tabel iniPerbesar tabel ini
    Acara kelasTeks
    SP: mulaiRowModifications2
    SP:StmtStartingmembuat tabel # t (int, b char(10))
    SP:StmtStartingPilih * dari # t
    SP:StmtStartingMasukkan # t pilih * dari SomeTable
    SP:StmtStartingexec sp_executesql N'select Count(*) dari # t di mana = @ ', N'@a int', @ = 37
    SP: mulai
    SP:StmtStartingPilih count(*) dari # t di mana = @ a
    Auto-UpdateStatsa
    SP:StmtStartingPilih count(*) dari # t di mana = @ a
    SP: selesai
    SP: selesaiRowModifications2


    Perhatikan bahwa ada tidak ada SP:Recompile agenda RowModifications2 prosedur. Ada lengkap SP: mulai untuk SP: selesai agenda sp_executesql Call konteks, dan Auto-UpdateStats acara untuk kolom a. Namun, karena panggilan ini keluar dari konteks yang disimpan prosedur, RowModifications2 prosedur tidak perlu recompiled dalam hal ini.

    Untuk informasi lebih lanjut tentang menggunakan sp_executesql disimpan prosedur, lihat "sp_executesql (T-SQL)" dan "menggunakan sp_executesql"topik di SQL Server buku Online.
  • Gunakan sub-procedures untuk melaksanakan pernyataan yang menyebabkan recompilations.
    Dalam kasus ini, pernyataan masih dapat menyebabkan recompilation, tapi bukannya rekompilasi besar panggilan disimpan prosedur, itu hanya akan mengkompilasi ulang sub-procedure kecil.
  • Gunakan opsi menjaga rencana.
    Sementara tabel telah peraturan khusus mengenai recompilations yang, dalam beberapa kasus, dapat lebih ketat daripada algoritma standar recompilation. Anda dapat menggunakan menjaga rencana pilihan untuk bersantai sementara tabel ambang kembali ke default algoritma. Untuk selengkapnya, lihat "menghindari Recompilation oleh menggunakan the menjaga rencana Pilihan"bagian dari artikel ini.
CATATAN: The RowModifications prosedur adalah contoh sangat sederhana prosedur yang recompiled karena untuk baris modifikasi. Silakan tinjau peringatan berikut mengenai contoh ini:

  • Meskipun contoh menggunakan sementara meja, situasi ini berlaku untuk disimpan prosedur yang referensi permanen tabel juga. Jika cukup jumlah data dalam tabel direferensikan telah diubah sejak permintaan rencana yang dihasilkan, prosedur yang tersimpan akan recompiled. The perbedaan dalam bagaimana temporary tables dianggap untuk recompilation tujuan dijelaskan dalam "menghindari Recompilation oleh menggunakan the menjaga rencana Option" bagian dari artikel ini.
  • Hukuman mati pertama kepada dua prosedur di atas juga menyebabkan recompilation pada pilih pertama dari tabel sementara # t. Alasan untuk recompilation ini dibahas di "Recompilations Due to Interleaving Data definisi bahasa (DDL) dan operasi manipulasi Data bahasa (DML)" bagian dari artikel ini.
  • Pernyataan "Pilih count(*) dari # t" digunakan dalam hal ini contoh, daripada sederhana "Pilih * dari # t" pernyataan. Untuk menghindari berlebihan recompilations, SQL Server tidak mempertimbangkan mengkompilasi ulang "sepele rencana" (seperti sebagai sebuah pilih * dari tabel) karena untuk baris modifikasi.

Recompilations karena untuk Interleaving Data definisi bahasa (DDL) dan operasi manipulasi Data bahasa (DML)

Jika operasi DDL dilakukan dalam prosedur atau batch, prosedur atau batch adalah recompiled ketika pertemuan berikutnya DML pertama operasi mempengaruhi tabel yang terlibat dalam DDL.

Mempertimbangkan mengikuti contoh disimpan prosedur:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
				
Jika Anda mengeksekusi kode ini dalam Query Analyzer dan melihat kejadian di atas di jejak Profiler, Anda akan melihat urutan berikut:

Perkecil tabel iniPerbesar tabel ini
Acara kelasTeks
SP: mulaiInterleave
SP:StmtStartingmembuat tabel t1 (int)
SP:StmtStartingPilih * dari t1
SP:RecompileInterleave
SP:StmtStartingPilih * dari t1
SP:StmtStartingmembuat indeks idx_t1 pada t1(a)
SP:StmtStartingPilih * dari t1
SP:RecompileInterleave
SP:StmtStartingPilih * dari t1
SP:StmtStartingmembuat tabel t2 (int)
SP:StmtStartingPilih * dari t2
SP:RecompileInterleave
SP:StmtStartingPilih * dari t2
SP: selesaiInterleave


Dalam kasus ini, disimpan prosedur recompiled tiga kali selama eksekusi. Untuk memahami mengapa hal ini terjadi, pertimbangkan bagaimana Pengoptimal mengembangkan rencana untuk prosedur ini disimpan:
  1. Selama awal kompilasi prosedur, t1 tabel dan t2 tidak ada. Oleh karena itu, tidak ada rencana untuk pertanyaan referensi ini tabel dapat dibuat. Mereka harus dihasilkan pada waktu eksekusi.
  2. Seperti prosedur mengeksekusi untuk pertama kalinya, yang pertama langkah ini adalah untuk menciptakan tabel t1. Langkah berikutnya adalah sebuah pilih dari tabel t1--yang ada tidak ada rencana untuk. Oleh karena itu, prosedur recompiled saat ini untuk mengembangkan rencana untuk pernyataan pilih. Rencana yang dihasilkan untuk saat ini Pilih dari t1, dan juga pilih dari t1 setelah penciptaan indeks. Tidak ada rencana dapat dihasilkan untuk Pilih dari t2 karena t2 masih ada belum.
  3. Langkah berikutnya adalah untuk menciptakan sebuah indeks pada t1. Setelah itu, Pilih lain dilakukan pada t1, yang kini memiliki rencana dari pertama mengkompilasi ulang. Namun, karena skema t1 telah berubah sejak rencana ini dihasilkan, prosedur harus menjadi recompiled lagi untuk menghasilkan rencana baru untuk Pilih dari t1. Dan karena t2 masih ada, tidak ada rencana dapat dihasilkan untuk Pilih dari t.
  4. Selanjutnya, t2 tabel dibuat dan pilih dari t2 dijalankan. Karena tidak ada rencana yang ada untuk pernyataan, prosedur recompiled terakhir kalinya.
Recompilations ini terjadi pada setiap pelaksanaan yang disimpan prosedur. Untuk mengurangi recompilations, memodifikasi prosedur untuk melakukan semua DDL operasi pertama, diikuti oleh DML operasi, seperti ditunjukkan pada berikut:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Then DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave
				
Pelaksanaan pertama NoInterleave prosedur akan menunjukkan kegiatan berikut di Profiler:

Perkecil tabel iniPerbesar tabel ini
Acara kelasTeks
SP: mulaiNoInterleave
SP:StmtStartingmembuat tabel t1 (int)
SP:StmtStartingmembuat indeks idx_t1 pada t1(a)
SP:StmtStartingmembuat tabel t2 (int)
SP:StmtStartingPilih * dari t1
SP:RecompileNoInterleave
SP:StmtStartingPilih * dari t1
SP:StmtStartingPilih * dari t1
SP:StmtStartingPilih * dari t2
SP: selesaiNoInterleave


Dalam hal ini semua pernyataan DDL yang dilakukan atas depan. Pengoptimal mengkompilasi prosedur ini sebagai berikut:
  1. Selama awal kompilasi prosedur, t1 tabel dan t2 tidak ada. Oleh karena itu, tidak ada rencana untuk pertanyaan referensi ini tabel dapat dibuat. Mereka harus dihasilkan pada waktu eksekusi.
  2. Langkah-langkah pertama yang melakukan prosedur adalah DDL operasi, membuat tabel t1 dan t2, serta indeks pada t1.
  3. Langkah berikutnya adalah pilih pertama dari t1. Karena ada tidak ada rencana yang tersedia untuk pernyataan ini pilih, prosedur recompiled. Karena semua objek ada, rencana yang dihasilkan untuk semua pilih pernyataan dalam prosedur saat ini.
  4. Seluruh prosedur mengeksekusi menggunakan rencana dihasilkan. Karena tidak ada perubahan pada objek direferensikan, ada tidak ada perlu mengkompilasi ulang prosedur lebih lanjut.
CATATAN: Kedua dan berikutnya eksekusi membuat penggunaan yang ada permintaan rencana dan cache, dan tidak menghasilkan apapun recompilations sama sekali. Prosedur yang membuat, mengubah, atau menjatuhkan tabel harus diubah untuk memastikan bahwa semua pernyataan DDL berada pada awal prosedur.

Recompilations berkat tertentu sementara meja operasi

Penggunaan tabel sementara pada prosedur yang disimpan dapat menyebabkan stored procedure untuk recompiled setiap kali prosedur dijalankan.

Untuk menghindari hal ini, mengubah prosedur yang tersimpan sehingga memenuhi persyaratan sebagai berikut:
  • Semua pernyataan yang berisi nama sementara tabel merujuk pada tabel sementara yang dibuat di disimpan prosedur yang sama, dan tidak dalam memanggil disebut disimpan prosedur atau dalam string dijalankan menggunakan EXECUTE pernyataan atau sp_executesql prosedur yang tersimpan.
  • Semua pernyataan yang berisi nama sementara tabel muncul sintaksis setelah meja sementara pada prosedur yang disimpan atau memicu.
  • Ada tidak ada pernyataan menyatakan KURSOR pilih yang pernyataan referensi meja sementara.
  • Semua pernyataan yang berisi nama sementara setiap meja mendahului pernyataan DROP meja yang referensi meja sementara.

    DROP TABLE pernyataan tidak diperlukan untuk sementara tabel yang dibuat di disimpan prosedur. Meja secara otomatis jatuh ketika prosedur telah selesai.
  • Tidak ada pernyataan yang menciptakan tabel sementara (seperti buat Tabel atau pilih... KE) muncul dalam pernyataan kontrol aliran seperti jika... LAIN atau sementara.

Menghindari Recompilation dengan menggunakan pilihan rencana tetap

Tabel sementara penggunaan dalam disimpan prosedur memperkenalkan tertentu kompleksitas untuk query optimizer. Perhitungan baris dan informasi statistik tabel dapat bervariasi secara dramatis selama hidup yang disimpan prosedur eksekusi. Untuk memastikan bahwa optimizer menggunakan rencana terbaik dalam semua kasus-kasus temporary tables, algoritma khusus ini dikembangkan menjadi lebih agresif dengan recompilations. Algoritma menyatakan bahwa jika meja sementara dibuat dengan prosedur yang disimpan telah berubah lebih dari enam kali, prosedur akan recompiled ketika pernyataan berikutnya referensi meja sementara.

Perhatikan contoh berikut:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
				
Dalam kasus ini, Anda akan melihat peristiwa berikut di Profiler untuk Kedua eksekusi:

Perkecil tabel iniPerbesar tabel ini
Acara kelasTeks
SP: mulaiuseKeepPlan
SP:StmtStartingmembuat tabel # t (int)
SP:StmtStarting -Tujuh masukkan pernyataan-
SP:StmtStartingPilih count(*) dari # t1
SP:RecompileuseKeepPlan
SP:StmtStartingPilih count(*) dari # t1
SP: selesaiuseKeepPlan

Prosedur recompiled pada pilih yang terjadi setelah perubahan tujuh t # sementara meja.

Agresif ini recompilation sangat membantu dalam kasus di mana perubahan distribusi data tabel sementara dapat secara dramatis mempengaruhi rencana query yang optimal untuk pernyataan referensi itu. Namun, dalam kasus dari besar prosedur yang memodifikasi Sementara tabel sering, tapi tidak dalam cara yang signifikan, recompilations dapat mengakibatkan kinerja secara keseluruhan lebih lambat. MENJAGA rencana pilihan pernyataan pilih diperkenalkan untuk situasi ini.

TETAP RENCANA menghilangkan disimpan prosedur recompilations disebabkan oleh lebih dari enam perubahan Sementara tabel dalam prosedur dan beralih kembali ke standar algoritma untuk recompilation karena untuk baris modifikasi dibahas di atas di "Karena recompilations baris modifikasi" bagian dari artikel ini. TETAP RENCANA tidak mencegah recompilations sama sekali, itu hanya mencegah orang-orang yang disebabkan oleh lebih dari enam perubahan temporary tables dirujuk dalam prosedur. Dalam contoh di atas, jika Anda menghapus komentar dari garis "pilihan (tetap rencana)" di prosedur yang tersimpan, SP:Recompile acara tidak akan dihasilkan.

Jika Anda menghapus komentar dari "pilihan (KEEP rencana)" baris kode di atas dan melaksanakannya, Anda akan melihat peristiwa berikut di Profiler:

Perkecil tabel iniPerbesar tabel ini
Acara kelasTeks
SP: mulaiuseKeepPlan
SP:StmtStartingmembuat tabel # t (int)
SP:StmtStarting -Tujuh masukkan pernyataan-
SP:StmtStartingPilih count(*) dari # t1 pilihan (KEEP RENCANA)
SP: selesaiuseKeepPlan


Perhatikan tidak ada SP:Recompile acara.

Recompilations karena untuk pernyataan-pernyataan SET tertentu yang dieksekusi di disimpan prosedur

Opsi SET lima berikut ditetapkan untuk ON secara default:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Jika Anda mengeksekusi pernyataan SET untuk mengatur pilihan ini ke OFF, prosedur yang tersimpan akan recompiled setiap kali itu berjalan. Alasan untuk ini adalah bahwa perubahan pilihan ini dapat mempengaruhi hasil query yang memicu recompilation.

Pertimbangkan kode contoh berikut:
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
				
Dalam kasus ini, Anda akan melihat peristiwa berikut di SQL Profiler untuk setiap pelaksanaan prosedur disimpan:
+---------------------------------------------------+
| Event Class     | Text                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
				
Menggantikan pilihan SET dengan salah satu dari lima pilihan terdaftar di atas akan menunjukkan hasil yang sama. Juga, menggunakan pilihan rencana tetap di sini tidak akan membantu untuk menghindari recompilation karena penyebab recompilation dari pernyataan SET.

Cara yang direkomendasikan untuk menghindari recompilation adalah untuk tidak menggunakan salah satu SET pernyataan ini lima di disimpan prosedur. Untuk informasi tambahan, lihat artikel berikut di Basis Pengetahuan Microsoft:
294942 PRB: MENGATUR CONCAT_NULL_YIELDS_NULL dapat menyebabkan disimpan prosedur untuk mengkompilasi ulang
Namun, sebagai tidak direkomendasikan, menjalankan SET pernyataan untuk me-reset sambungan pilihan ke nilai yang sama sebagai yang disimpan prosedur, itu juga dapat menghindari mengkompilasi ulang, melakukan hal itu sebagai:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
Jejak SQL Profiler akan menunjukkan kegiatan SP:Recompile tidak lebih.

Tabel berikut mencantumkan pernyataan MENETAPKAN beberapa umum dan apakah atau tidak mengubah pernyataan SET pada prosedur yang disimpan menyebabkan mengkompilasi ulang:
Perkecil tabel iniPerbesar tabel ini
Menetapkan pernyataanMengkompilasi ulang
Set quoted_identifierTidak
Set arithabortYa
Set ansi_null_dflt_onYa
Set ansi_defaultsYa
Set ansi_warningsYa
Set ansi_paddingYa
Set concat_null_yields_nullYa
Set numeric_roundabortTidak
Set nocountTidak
Set rowcountTidak
Set xact_abortTidak
Set implicit_transactionsTidak
Set arithignoreTidak
Set lock_timeoutTidak
Set fmtonlyTidak

Referensi

308737 INF: Bagaimana mengidentifikasi penyebab Recompilation di acara SP:Recompile

Untuk informasi tentang menggunakan SQL Server Profiler, melihat SQL Server buku Online.

Properti

ID Artikel: 243586 - Kajian Terakhir: 20 September 2011 - Revisi: 2.0
Berlaku bagi:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
Kata kunci: 
kbinfo kbmt KB243586 KbMtid
Penerjemahan Mesin
PENTING: Artikel ini diterjemahkan menggunakan perangkat lunak mesin penerjemah Microsoft dan bukan oleh seorang penerjemah. Microsoft menawarkan artikel yang diterjemahkan oleh seorang penerjemah maupun artikel yang diterjemahkan menggunakan mesin sehingga Anda akan memiliki akses ke seluruh artikel baru yang diterbitkan di Pangkalan Pengetahuan (Knowledge Base) dalam bahasa yang Anda gunakan. Namun, artikel yang diterjemahkan menggunakan mesin tidak selalu sempurna. Artikel tersebut mungkin memiliki kesalahan kosa kata, sintaksis, atau tata bahasa, hampir sama seperti orang asing yang berbicara dalam bahasa Anda. Microsoft tidak bertanggung jawab terhadap akurasi, kesalahan atau kerusakan yang disebabkan karena kesalahan penerjemahan konten atau penggunaannya oleh para pelanggan. Microsoft juga sering memperbarui perangkat lunak mesin penerjemah.
Klik disini untuk melihat versi Inggris dari artikel ini:243586

Berikan Masukan

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com