Praktik Terbaik Saran Penyimpanan Kueri

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru Azure SQL Database Azure SQL Managed Instance basis data SQL di Microsoft Fabric

Artikel ini merinci praktik terbaik untuk menggunakan Query Store hints. Petunjuk Penyimpanan Kueri memungkinkan pembentukan bentuk rencana kueri tanpa memodifikasi kode aplikasi.

Untuk informasi selengkapnya tentang mengonfigurasi dan mengelola dengan Penyimpanan Kueri, lihat Memantau performa dengan menggunakan Penyimpanan Kueri.
Untuk informasi tentang menemukan informasi yang bisa ditindaklanjuti dan mengoptimalkan performa dengan Penyimpanan Kueri, lihat Mengoptimalkan performa dengan Penyimpanan Kueri.
Untuk praktik terbaik secara umum mengenai Penyimpanan Kueri (Query Store), lihat Praktik terbaik untuk memantau beban kerja dengan Penyimpanan Kueri.

Penggunaan untuk petunjuk Query Store

Pertimbangkan kasus penggunaan berikut sebagai yang ideal untuk petunjuk Query Store. Untuk informasi selengkapnya, lihat Kapan menggunakan petunjuk Query Store.

Caution

Karena Pengoptimal Kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri, sebaiknya hanya menggunakan petunjuk sebagai upaya terakhir untuk pengembang dan administrator database berpengalaman. Untuk informasi selengkapnya, lihat petunjuk Kueri .

Ketika kode tidak dapat diubah

Menggunakan petunjuk Penyimpanan Kueri memungkinkan Anda memengaruhi rencana eksekusi kueri tanpa mengubah kode aplikasi atau objek database. Tidak ada fitur lain yang memungkinkan Anda menerapkan petunjuk kueri dengan cepat dan mudah.

Anda dapat menggunakan petunjuk Penyimpanan Kueri, misalnya untuk mendukung beban kerja ekstraksi-transformasi-pemuat (ETL), tanpa mengubah ulang penerapan kode. Pelajari cara meningkatkan pemuatan massal dengan petunjuk Query Store melalui video ini berdurasi 14 menit:

Query Store adalah metode penyetelan kueri yang ringan, tetapi jika kueri menjadi bermasalah, hal tersebut harus diatasi dengan perubahan kode yang lebih substansial. Jika Anda secara teratur menemukan kebutuhan untuk menerapkan petunjuk dari Query Store pada kueri, pertimbangkan penulisan ulang kueri secara lebih menyeluruh. Pengoptimal Kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri. Sebaiknya hanya gunakan petunjuk sebagai upaya terakhir untuk pengembang berpengalaman dan administrator database.

Untuk informasi tentang petunjuk kueri mana yang dapat diterapkan, lihat Petunjuk kueri yang didukung.

Di bawah beban transaksi tinggi atau dengan kode misi penting

Jika perubahan kode tidak praktis karena persyaratan waktu aktif yang tinggi atau beban transaksional, petunjuk dari Query Store dapat dengan cepat menerapkan petunjuk kueri ke beban kerja kueri yang ada. Menambahkan dan menghapus petunjuk Penyimpanan Kueri sangat mudah.

Petunjuk Query Store dapat ditambahkan dan dihapus ke batch kueri untuk menyesuaikan performa untuk windows yang disetel untuk ledakan beban kerja yang ekstrem.

Sebagai pengganti panduan perencanaan

Sebelum adanya petunjuk Query Store, pengembang harus mengandalkan "plan guides" untuk menyelesaikan tugas serupa, yang bisa rumit digunakan. Petunjuk Query Store diintegrasikan dengan fitur Query Store dari SQL Server Management Studio (SSMS), untuk eksplorasi visual kueri.

Untuk mencari melalui semua rencana dengan menggunakan cuplikan kueri, panduan pelaksanaan diperlukan. Fitur petunjuk Penyimpanan Kueri tidak memerlukan kueri yang cocok persis untuk memengaruhi rencana kueri yang dihasilkan. Petunjuk Penyimpanan Kueri dapat diterapkan pada query_id dalam himpunan data Penyimpanan Kueri.

Petunjuk Query Store mengambil alih petunjuk tingkat pernyataan yang tertanam dan panduan rencana yang ada.

Pertimbangkan tingkat kompatibilitas yang lebih baru

Petunjuk pada Query Store dapat menjadi metode yang berharga ketika tingkat kompatibilitas database yang lebih baru tidak tersedia bagi Anda, misalnya karena spesifikasi vendor atau penundaan pengujian yang lebih besar. Ketika tingkat kompatibilitas yang lebih tinggi tersedia untuk database, pertimbangkan untuk meningkatkan tingkat kompatibilitas database kueri individual untuk memanfaatkan pengoptimalan performa dan fitur terbaru SQL Server.

Misalnya, jika Anda memiliki instans SQL Server 2022 (16.x) dengan database di tingkat kompatibilitas 140, Anda masih dapat menggunakan petunjuk Penyimpanan Kueri untuk menjalankan kueri individual di tingkat kompatibilitas 160. Anda dapat menggunakan petunjuk berikut:

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION(USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';

Untuk tutorial lengkap, lihat Petunjuk Contoh Penyimpanan Kueri.

Pilih tingkat kompatibilitas yang lebih lama setelah melakukan peningkatan

Kasus lain di mana petunjuk Penyimpanan Kueri dapat membantu adalah di mana kueri tidak dapat dimodifikasi langsung setelah migrasi atau peningkatan instans SQL Server. Gunakan petunjuk Penyimpanan Kueri untuk menerapkan tingkat kompatibilitas sebelumnya untuk kueri hingga dapat ditulis ulang atau ditangani agar berfungsi dengan baik dalam tingkat kompatibilitas terbaru. Identifikasi kueri outlier yang mengalami regresi dengan tingkat kompatibilitas yang lebih tinggi menggunakan laporan kueri yang diregresi Query Store, menggunakan alat Query Tuning Assistant selama migrasi, atau telemetri aplikasi tingkat kueri lainnya. Untuk informasi selengkapnya tentang perbedaan antara tingkat kompatibilitas, tinjau Perbedaan antara tingkat kompatibilitas.

Setelah performa menguji tingkat kompatibilitas baru dan menyebarkan petunjuk Penyimpanan Kueri dengan cara ini, Anda dapat meningkatkan seluruh tingkat kompatibilitas database sambil menjaga kueri kunci bermasalah pada tingkat kompatibilitas sebelumnya, tanpa perubahan kode apa pun.

Memblokir eksekusi kueri bermasalah di masa mendatang

Anda dapat menggunakan ABORT_QUERY_EXECUTION petunjuk kueri untuk memblokir eksekusi kueri bermasalah yang diketahui di masa mendatang, misalnya kueri yang tidak penting menyebabkan konsumsi sumber daya yang tinggi dan memengaruhi beban kerja aplikasi penting.

Note

Petunjuk kueri ABORT_QUERY_EXECUTION hanya tersedia di Azure SQL Database, Azure SQL Managed Instance^AUTD, dan SQL Server 2025 (17.x).

Misalnya, untuk memblokir eksekusi query_id 39 di masa mendatang, jalankan sys.sp_query_store_set_hints sebagai berikut:

EXECUTE sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

Untuk membuka blokir kueri yang sama, jalankan sys.sp_query_store_clear_hints:

EXECUTE sys.sp_query_store_clear_hints @query_id = 39;

Untuk informasi selengkapnya, lihat Contoh petunjuk Penyimpanan Kueri.

Pertimbangan berikut berlaku:

Saat Anda menentukan petunjuk ini untuk kueri, upaya untuk menjalankan kueri gagal dengan kesalahan 8778, tingkat keparahan 16, Eksekusi kueri telah dibatalkan karena petunjuk ABORT_QUERY_EXECUTION ditentukan.
Untuk membebaskan kueri, Anda dapat menghapus petunjuk dengan meneruskan nilai query_id ke parameter @query_id dalam prosedur simpan sys.sp_query_store_clear_hints.
- Prosedur tersimpan ini menghapus semua petunjuk untuk kueri. Jika Anda ingin mempertahankan petunjuk yang ada saat membuka blokir kueri, gunakan sys.sp_query_store_set_hints, menghapus ABORT_QUERY_EXECUTION petunjuk tetapi menyimpan petunjuk lain.

Anda bisa menggunakan tampilan sistem untuk menemukan kueri di Penyimpanan Kueri yang diblokir, seperti dalam contoh kueri berikut:

SELECT qsh.query_id,
       q.query_hash,
       qt.query_sql_text
FROM sys.query_store_query_hints AS qsh
     INNER JOIN sys.query_store_query AS q
         ON qsh.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS qt
         ON q.query_text_id = qt.query_text_id
WHERE UPPER(qsh.query_hint_text) LIKE '%ABORT[_]QUERY[_]EXECUTION%';

Untuk mendapatkan nilai query_id, setidaknya satu eksekusi kueri harus direkam di Query Store. Eksekusi ini tidak harus berhasil. Ini berarti bahwa eksekusi kueri yang kehabisan waktu atau dibatalkan di masa mendatang dapat diblokir.
Jika Anda perlu memblokir atau membuka blokir semua kueri dengan hash kueri tertentu, pertimbangkan untuk menggunakan skrip otomatis. Misalnya, dbo.sp_query_store_modify_hints_by_query_hash adalah contoh prosedur tersimpan yang memanggil prosedur tersimpan sistem sys.sp_query_store_set_hints atau sys.sp_query_store_clear_hints dalam perulangan untuk semua nilai query_id yang cocok dengan hash kueri.
Jika kueri sudah dijalankan saat Anda memblokirnya, eksekusinya akan berlanjut. Anda dapat menggunakan pernyataan KILL untuk membatalkan kueri.
- Eksekusi kueri yang dimatikan tidak direkam di Penyimpanan Kueri. Jika kueri belum ada di Penyimpanan Kueri, Anda harus membiarkan kueri selesai atau kehabisan waktu untuk mendapatkan query_id yang bisa Anda blokir.
Saat kueri diblokir oleh ABORT_QUERY_EXECUTION petunjuk, kolom execution_type dan execution_type_desc dalam tampilan sys.query_store_runtime_stats masing-masing diatur ke 4 dan Pengecualian.
Seperti semua petunjuk Penyimpanan Kueri, Anda harus memiliki izin pada database ALTER untuk mengatur dan menghapus petunjuk ABORT_QUERY_EXECUTION.

Pertimbangan saran Query Store

Pertimbangkan skenario berikut saat menerapkan petunjuk Query Store.

Perubahan distribusi data

Panduan eksekusi rencana, rencana paksa melalui Penyimpanan Kueri, dan petunjuk Penyimpanan Kueri menggantikan pengambilan keputusan pengoptimal. Saran Query Store mungkin bermanfaat sekarang, tetapi tidak pada masa depan. Misalnya, jika petunjuk Penyimpanan Kueri membantu kueri dalam distribusi data sebelumnya, dalam beberapa kasus itu mungkin tidak produktif jika operasi DML berskala besar mengubah data. Distribusi data baru dapat menyebabkan pengoptimal membuat keputusan yang lebih baik daripada petunjuk. Skenario ini adalah konsekuensi paling umum dari memaksa perilaku rencana.

Evaluasi kembali strategi petunjuk Penyimpanan Kueri Anda secara teratur

Mengevaluasi kembali strategi petunjuk Penyimpanan Kueri yang ada dalam kasus berikut:

Setelah diketahuinya perubahan distribusi data besar.
Saat sumber daya yang tersedia untuk database berubah. Misalnya, saat ukuran komputasi Azure SQL Database, SQL Managed Instance, atau komputer virtual SQL Server Anda berubah.
Di mana perbaikan rencana berlangsung lama. Hints Query Store paling baik digunakan untuk perbaikan jangka pendek.
Regresi performa yang tidak terduga.

Potensi dampak luas

Petunjuk Penyimpanan Kueri memengaruhi semua eksekusi kueri, terlepas dari kumpulan parameter, aplikasi sumber, pengguna, atau kumpulan hasil. Dalam kasus regresi kinerja yang tidak disengaja, petunjuk Penyimpanan Kueri yang dibuat dengan sys.sp_query_store_set_hints dapat dengan mudah dihapus dengan sys.sp_query_store_clear_hints.

Muat perubahan pengujian dengan hati-hati untuk sistem misi penting atau sensitif sebelum menerapkan petunjuk Query Store dalam produksi.

Parameterisasi paksa dan petunjuk RECOMPILE tidak didukung

Menerapkan petunjuk kueri dengan Query Store hints tidak didukung saat opsi database PARAMETERISASI diatur ke FORCED. Untuk informasi selengkapnya, lihat Panduan untuk Menggunakan Parameterisasi Paksa.

Petunjuk RECOMPILE tidak kompatibel dengan parameterisasi paksa yang ditentukan pada level database. Jika database menggunakan parameterisasi paksa dan RECOMPILE adalah bagian dari string petunjuk yang diatur di Query Store untuk sebuah kueri, Mesin Database akan mengabaikan RECOMPILE dan hanya menerapkan petunjuk lain jika ada. Selain itu, mulai Juli 2022 di Azure SQL Database, peringatan (kode kesalahan 12461) dikeluarkan yang menyatakan bahwa RECOMPILE petunjuk diabaikan.