Performa kueri yang konsisten dari waktu ke waktu
Kami menjalankan beban aplikasi yang intensif (ribuan operasi / detik) terhadap database SQL Server dengan data yang cukup banyak. Beberapa tabel memiliki miliaran baris, beberapa di antaranya memiliki banyak sisipan dan pembaruan.
Kinerja DB secara umum cukup baik, tetapi secara berkala kami mendapatkan masalah kinerja kueri; kueri yang agak sederhana yang sebelumnya berfungsi dengan baik mungkin membutuhkan waktu 10-100x tiba-tiba.
Ini tampaknya terkait dengan statistik tabel / indeks dan pengoptimal kueri - sering kali pembaruan statistik akan memperbaiki masalah, namun di lain waktu pembaruan statistik akan memperburuk situasi (menjalankan kembali pembaruan statistik biasanya akan menyelesaikannya masalah akhirnya).
Apa yang tampaknya terjadi adalah bahwa pengoptimal memutuskan untuk menggunakan indeks yang salah secara objektif untuk beberapa kueri; tiba-tiba, setelah menggunakan yang benar selama berhari-hari dan berminggu-minggu.
Pertanyaan saya adalah: Mengapa ini terjadi dan apa yang dapat kita lakukan?
Database ini telah berjalan selama bertahun-tahun dengan beban yang pada dasarnya sama, kueri yang hampir sama, dan jumlah pembaruan yang sama. Untuk 99,995% kueri, seharusnya tidak ada alasan untuk memutuskan strategi indeks yang berbeda dari waktu ke waktu, terlepas dari masukannya (dan - memang - melakukannya akan terbukti benar-benar merusak kinerja kueri).
Seperti yang ditunjukkan di atas, memperbarui statistik secara otomatis pada jadwal akan sering menghasilkan masalah yang mengerikan - jika sampel statistik miring (yang tampaknya terjadi setidaknya 5% dari waktu) kita berakhir di dunia yang menyakitkan.
Apakah ada cara untuk memberi tahu SQL Server (pada tabel tertentu) bahwa histogram statistik dan kepadatan tidak akan berubah seiring waktu, jadi harap terus gunakan rencana kueri yang sama untuk kueri yang melibatkan tabel ini? Jika tidak, bagaimana kita dapat memastikan hasil yang dapat diprediksi dari pembaruan statistik dari waktu ke waktu (menghindari masalah statistik miring yang dijelaskan di atas)?
Tidak ada prosedur yang disimpan. Kami memiliki kendali atas SQL, sehingga berpotensi dapat diubah tetapi itu BANYAK kode sehingga akan disayangkan jika kami harus mengubah setiap kueri (misalnya menambahkan klausa tambahan).
Sebuah pertanyaan tindak lanjut: sniffing parameter tampaknya hanya relevan untuk prosedur yang tersimpan, apakah itu benar?
Jawaban
Saya sarankan Anda terlebih dahulu menentukan apakah statistiknya atau jika parameternya mengendus yang merugikan Anda.
Terlepas dari hal di atas, saya sarankan Anda membaca artikel Erland tentang masalah ini.
Apa yang harus dilakukan sulit untuk mengatakannya. Kami tidak tahu apakah itu statistik atau sniffing.
Tapi mungkin menambahkan OPTIMIZE FORbisa menjadi "solusi". Ini lebih murah daripada RECOMPILEkarena Anda tidak perlu menerima rencana produksi pada setiap eksekusi. Dan itu memberi Anda prediktabilitas. Ini, tentu saja, mengasumsikan bahwa Anda tidak memiliki kasus di mana statistik sangat berbeda sehingga masukan parameter yang sama menghasilkan rencana yang berbeda karena alasan statistik.
Cobalah untuk mengidentifikasi satu kueri. Lihat apakah Anda memiliki satu atau banyak rencana untuk kueri tersebut. Uji dengan OPTIMIZE FORdan / atau RECOMPILE. Opsi "global" pada skala database yang Anda miliki adalah menonaktifkan parameter sniffing untuk database. Ini berarti pengoptimal mengoptimalkan karena tidak memiliki petunjuk tentang nilainya. Semua ini dan lebih banyak lagi di artikel Erland.
Pengendusan parameter tidak hanya berlaku untuk prosedur yang tersimpan. Ini juga berlaku untuk SQL berparameter (biasanya dieksekusi menggunakan sp_executesql), yang kemungkinan jauh lebih umum saat ini daripada prosedur tersimpan.
Jawaban yang dihasilkan dari komentar
Anda bisa mendapatkan rencana kueri yang salah karena statistik yang salah yang Anda dapatkan setelah statistik pembaruan. Tetapi Anda juga bisa mendapatkan rencana kueri yang salah karena parameter sniffing ketika setelah memperbarui statistik, parameter pertama yang didapat kueri Anda tidak seperti biasanya. Tidak mungkin untuk memahami dari pertanyaan Anda masalah mana yang disajikan. Cobalah untuk mengompilasi ulang rencana kueri saat kueri semakin buruk daripada memperbarui statistik untuk memisahkan dua masalah yang berbeda. - Denis Rubashkin
Ada banyak faktor yang dapat menyebabkan "membangun kembali" rencana eksekusi. Jadi itu akan menjelaskan mengapa itu berfungsi dengan baik untuk sementara waktu dan tiba-tiba mulai bekerja lambat. Saat Anda memperbarui statistik, semua rencana eksekusi yang ada hubungannya dengan objek ini menjadi tidak valid dan ini akan menyebabkan eksekusi berikutnya untuk membuat rencana baru. Bergantung pada nilai yang digunakan untuk itu, itu mungkin memperbaiki atau tidak masalah (sebagian besar nilai memperbaikinya sementara yang lain tidak, yang menjelaskan mengapa kadang-kadang berhasil, kadang tidak).
Cara lain untuk "memperbaiki" rencana eksekusi adalah dengan menggunakan Query Store (menurut saya ini dimulai dengan SQL Server 2016) dan "memperbaiki" rencana yang akan digunakan. Ini bisa memiliki beberapa kelemahan jika data berubah banyak (karena SQL Server tidak akan dapat menghasilkan rencana yang lebih baik) tetapi dapat memperbaiki masalah semacam itu (saya memiliki kueri yang berjalan dengan rencana eksekusi perbaikan sejak 2 tahun sekarang dan tidak tidak memiliki masalah parameter sniffing sejak). - Dominique Boucher