Pemecahan masalah disimpan prosedur recompilation

• 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.

• 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.

Praktek terbaik

exec dbo.myProc
				

exec myProc
				

Mengidentifikasi dan memecahkan masalah

Melihat Profiler Data

• 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

  (http://support.microsoft.com/kb/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 kelas	Teks
  SP: mulai	RecompProc
  SP:StmtStarting	membuat tabel # t (int)
  SP:StmtStarting	Pilih * dari # t
  SP:Recompile	RecompProc
  SP:StmtStarting	Pilih * dari # t
  SP: selesai	RecompProc

  
  
  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 kelas	Teks
  SP: mulai	RecompProc
  SP:Recompile	RecompProc
  SP:StmtCompleted	Pilih * dari # t
  SP: selesai	RecompProc

  
  
  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

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
				

• 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 kelas	Teks
  SP: mulai	RowModifications2
  SP:StmtStarting	membuat tabel # t (int, b char(10))
  SP:StmtStarting	Pilih * dari # t
  SP:StmtStarting	Masukkan # t pilih * dari SomeTable
  SP:StmtStarting	exec sp_executesql N'select Count(*) dari # t di mana = @ ', N'@a int', @ = 37
  SP: mulai
  SP:StmtStarting	Pilih count(*) dari # t di mana = @ a
  Auto-UpdateStats	a
  SP:StmtStarting	Pilih count(*) dari # t di mana = @ a
  SP: selesai
  SP: selesai	RowModifications2

  
  
  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.

• 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)

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
				

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.

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
				

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.

Recompilations berkat tertentu sementara meja operasi

• 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

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
				

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

• ANSI_DEFAULTS
• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• CONCAT_NULL_YIELDS_NULL

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
				

+---------------------------------------------------+
| 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                  | 
+---------------------------------------------------+
				

Set ANSI_DEFAULTS OFF

exec test_recompile
				

Referensi

Kembali ke atas | Berikan Masukan