Memutuskan apakah Query Tuning Assistant (QTA) tepat untuk Anda
Anda mengetahui potensi regresi rencana kueri dan kehilangan performa setelah peningkatan versi database. Untuk membantu mempertahankan performa setelah peningkatan database, Anda perlu menemukan metode terbaik untuk mengidentifikasi dan mengurangi kueri yang diregresi. Unit ini menjelaskan cara menggunakan Query Store dan Query Tuning Assistant (QTA) untuk membantu memastikan bahwa performa yang berkurang bukan masalah setelah peningkatan.
Mulai menggunakan Penyimpanan Kueri dan Asisten Penyetelan Kueri
QTA bergantung pada data Penyimpanan Kueri untuk menemukan kueri yang mengalami kemunculan setelah peningkatan. Penyimpanan Kueri memungkinkan Anda mengumpulkan metrik pada versi database yang lebih lama sebelum Anda memutakhirkan.
Penyimpanan Kueri diperkenalkan di SQL Server 2016, dan QTA diperkenalkan di SQL Server 2017. Versi database apa pun yang berjalan pada instans SQL Server 2022 dapat menggunakan kedua fitur ini. Alat-alat ini diintegrasikan ke dalam SQL Server Management Studio (SSMS), dan beroperasi di tingkat database.
Tingkat kompatibilitas database menentukan versinya, yang pada gilirannya menentukan versi estimator kardinalitas yang digunakannya. Estimator kardinalitas memprediksi berapa banyak baris yang mungkin dikembalikan kueri, sehingga pengoptimal kueri dapat memilih paket dengan biaya terendah. SQL Server 2014 memperkenalkan algoritma estimator kardinalitas yang ditingkatkan yang menguntungkan sebagian besar kueri, tetapi jarang memiliki dampak performa negatif.
Untuk mengukur dampak performa, Penyimpanan Kueri melaporkan kueri dan kueri yang diregresi yang mengonsumsi sumber daya sistem terbanyak. QTA membandingkan data performa kueri Query Store sebelum dan sesudah peningkatan database, dan eksperimen pada kueri untuk meningkatkan performa.
Catatan
QTA tidak tersedia untuk database Azure SQL Database atau SQL Managed Instance. Untuk database ini, pertimbangkan untuk menggunakan ekstensi migrasi Azure SQL untuk Azure Data Studio.
Koreksi QTA dan paket otomatis
Ketika SQL Server menjalankan kueri Transact-SQL (T-SQL), SQL Server menganalisis kemungkinan rencana yang dapat menjalankan kueri. SQL Server menyimpan paket untuk kueri yang berhasil dijalankan dan menggunakannya kembali saat kueri dijalankan lagi.
SQL Server memilih rencana optimal untuk kueri dan menggunakannya hingga keadaan memaksanya untuk memilih yang baru. Keadaan ini dapat mencakup mesin database yang menyusun ulang rencana, indeks yang ditambahkan atau dihapus, atau statistik yang diubah.
Rencana baru tidak selalu merupakan perbaikan dari rencana yang lama. Anda dapat menjalankan perintah berikut untuk mencari kueri yang memiliki rencana yang diregresi.
SELECT * FROM sys.dm_db_tuning_recommendations
Anda kemudian dapat menggunakan prosedur tersimpan sp_force_plan untuk memaksa SQL Server menggunakan paket tertentu yang direkomendasikan.
EXEC sp_force_plan @query_id = 1187, @plan_id = 1975
Prosedur ini sp_force_plan adalah proses manual yang berpotensi melelahkan jika banyak kueri yang meregres pada database yang ditingkatkan. SQL Server 2017 memperkenalkan fitur baru bernama koreksi paket otomatis untuk menyetel kueri otomatis dan menghapus kebutuhan intervensi manual. Anda dapat mengaktifkan koreksi paket otomatis pada database dengan menjalankan pernyataan berikut:
ALTER DATABASE <database-name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Saat diatur pada tingkat database, koreksi paket otomatis menginstruksikan SQL Server untuk menggunakan rencana kueri terakhir yang baik. SQL Server terus memantau rencana untuk menemukan regresi saat rencana dijalankan dan untuk memastikannya memberikan performa yang optimal.
Koreksi paket otomatis bekerja secara berbeda dari QTA. Menggunakan rencana baik terakhir dapat berarti bergulir kembali ke estimator kardinalitas sebelumnya. Sebaliknya, QTA melakukan eksperimennya menggunakan versi estimator kardinalitas yang dipetakan ke tingkat kompatibilitas database target.
Ringkasan
QTA tersedia di SQL Server 2022 dan bergantung pada Penyimpanan Kueri untuk dioperasikan. QTA harus memiliki data garis besar Penyimpanan Kueri untuk database pada tingkat kompatibilitas sebelumnya, sehingga dapat mengamati kueri dan membuat perbandingan setelah peningkatan.
Koreksi paket otomatis, yang diperkenalkan di SQL Server 2017, menghapus kebutuhan untuk mengidentifikasi dan memaksa rencana kueri secara manual. Koreksi paket otomatis dapat diaktifkan di tingkat database, tetapi mungkin mengembalikan versi estimator kardinalitas. QTA menggunakan versi estimator kardinalitas yang dipetakan ke tingkat kompatibilitas target.