Parameter sniffing
Concept
Perilaku mesin database bergaya SQL Server yang mengompilasi rencana untuk nilai parameter yang terlihat saat kompilasi dan menggunakan kembali rencana yang di-cache, yang dapat berkinerja buruk untuk nilai parameter yang sangat berbeda; juga disebut sensitivitas parameter.
Description
Parameter sniffing adalah perilaku mesin database bergaya SQL Server yang mengompilasi rencana kueri untuk nilai parameter yang terlihat saat kompilasi, lalu rencana yang di-cache digunakan kembali. Perilaku ini juga disebut sensitivitas parameter. Perilaku tersebut bukan cacat dengan sendirinya: mesin memeriksa nilai parameter saat ini ketika mengompilasi atau mengompilasi ulang sebuah kueri dan meneruskannya ke pengoptimal kueri, yang kemudian dapat menghasilkan rencana eksekusi yang lebih efisien untuk nilai-nilai tersebut. Kesulitan muncul ketika rencana yang di-cache kemudian digunakan untuk nilai parameter yang tampak sangat berbeda dari nilai aslinya, karena rencana yang cocok untuk satu nilai dapat berkinerja buruk untuk nilai lain.
Panduan arsitektur pemrosesan kueri Microsoft untuk SQL Server menjelaskan parameter sniffing dalam bagian tentang sensitivitas parameter. Menurut panduan tersebut, proses ini berlaku untuk prosedur tersimpan, kueri yang dijalankan melalui sp_executesql, dan kueri yang disiapkan. Perilaku ini berinteraksi langsung dengan penggunaan ulang rencana, karena rencana yang telah dikompilasi disimpan dalam plan cache dan dapat diambil kembali oleh eksekusi berikutnya. Kumpulan nilai pertama yang memicu kompilasi karena itu membentuk rencana yang diterima oleh panggilan berikutnya, itulah sebabnya hasilnya dapat bervariasi tergantung pada panggilan mana yang kebetulan mengompilasi rencana tersebut.
Panduan itu menyebut satu solusi: hint kueri OPTION (RECOMPILE). Dengan hint ini, pengoptimal memperlakukan parameter dan variabel lokal sebagai konstanta waktu kompilasi, yang dapat digabungkan menjadi literal. Nilai yang digunakan adalah nilai yang berlaku tepat sebelum pernyataan dijalankan, yang belum tentu nilai yang awalnya diteruskan. Hal ini dapat membantu ketika data input sangat bervariasi atau tidak seimbang. Panduan tersebut juga memperingatkan bahwa hint ini mengompilasi ulang kueri pada setiap eksekusi, yang bisa mahal, dan menyarankan untuk mengukur dampaknya terhadap keseluruhan beban kerja sebelum mengadopsinya. Panduan itu juga merujuk pada artikel terpisah tentang optimisasi Parameter Sensitive Plan untuk bacaan lebih lanjut.
Sebuah detail terkait dari dokumentasi yang sama menyangkut variabel lokal. Nilainya tidak di-sniff pada waktu kompilasi. Sebaliknya, pengoptimal memperkirakan berdasarkan informasi densitas, atau, ketika tidak ada statistik, menggunakan tebakan persentase tetap untuk perbandingan kesetaraan dan rentang. Perbedaan ini penting saat mendiagnosis kueri yang lambat, karena parameter dan variabel lokal dapat menghasilkan estimasi yang berbeda dan karenanya rencana yang berbeda.
Dalam praktiknya, parameter sniffing muncul sebagai kueri yang berjalan cepat untuk sebagian input dan lambat untuk yang lain, atau sebagai kinerja yang berubah setelah sebuah rencana dihapus dari cache dan dikompilasi ulang. Episode M365.FM "Fix Bad Data Upstream with T-SQL Contracts" mencantumkan parameter sniffing sebagai salah satu kegagalan umum di perusahaan, dan menggambarkannya sebagai sesuatu yang dapat menyamar sebagai keacakan. Episode tersebut juga membingkai bentuk rencana sebagai persoalan tata kelola, dengan menyatakan bahwa jika bentuk rencana berubah, belanja dan keandalan ikut berubah, sehingga perilaku rencana diterjemahkan menjadi stabilitas biaya dan risiko operasional.
Episode "Design Reliable and Resilient Azure Architectures" menempatkan parameter sniffing dalam alur pemecahan masalah untuk Azure SQL Database. Episode ini menyarankan untuk mengaktifkan Intelligent Insights dan Query Performance Insight, menangkap kueri yang berjalan lama, meninjau rencana eksekusi, dan mengidentifikasi indeks yang hilang atau parameter sniffing. Episode ini juga menyebutkan rekomendasi penyetelan otomatis Azure SQL dan saran indeks melalui Azure Advisor, bersama dengan optimisasi kueri, pengindeksan yang tepat, serta pertimbangan untuk menskalakan DTU atau vCore atau beralih ke Hyperscale.
Secara keseluruhan, sumber-sumber ini memperlakukan parameter sniffing sebagai trade-off penyimpanan rencana: menggunakan kembali rencana yang telah dikompilasi menghemat pekerjaan kompilasi, sementara rencana yang disetel untuk satu nilai parameter mungkin tidak cocok untuk nilai lainnya. Keputusan yang umum terkait hal ini meliputi memeriksa rencana eksekusi, memeriksa apakah distribusi data tidak seimbang, dan menimbang biaya kompilasi dari opsi seperti OPTION (RECOMPILE) terhadap manfaat rencana yang lebih sesuai.
Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.
| Type | Concept |
|---|
Frequently asked questions
Questions marked with AI were written with AI from the facts on this page.
Apa itu parameter sniffing? (AI)
Ini adalah perilaku mesin bergaya SQL Server yang mengompilasi rencana kueri untuk nilai parameter yang terlihat saat kompilasi dan menggunakan kembali rencana yang di-cache. Hal ini dapat berkinerja buruk ketika nilai parameter berikutnya sangat berbeda. Perilaku ini juga disebut sensitivitas parameter.
Bagaimana OPTION (RECOMPILE) dapat memengaruhi masalah parameter sniffing? (AI)
Menurut panduan pemrosesan kueri Microsoft, hint ini membuat pengoptimal memperlakukan parameter dan variabel lokal sebagai konstanta waktu kompilasi yang dapat digabungkan menjadi literal, yang dapat membantu dengan data yang sangat bervariasi atau tidak seimbang. Kueri dikompilasi ulang pada setiap eksekusi, yang bisa mahal, sehingga dampaknya terhadap keseluruhan beban kerja sebaiknya diukur terlebih dahulu.
Apakah variabel lokal di-sniff dengan cara yang sama seperti parameter? (AI)
Tidak. Dokumentasi menyatakan bahwa nilai variabel lokal tidak di-sniff pada waktu kompilasi. Pengoptimal justru memperkirakan berdasarkan informasi densitas atau, tanpa statistik, menggunakan tebakan persentase tetap untuk perbandingan kesetaraan dan rentang.
Community wiki
Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.
Experts and companies for this topic
Nobody has listed this topic yet. Add it to your profile.
Maintained by Mirko Peters (Microsoft MVP) · Last updated: