Mulai menggunakan Asisten Penyetelan Kueri (QTA)
Anda berencana untuk memigrasikan dua database produksi dari SQL Server 2012 ke instans SQL Server 2022 baru. Karena database sedang digunakan, Anda awalnya ingin meninggalkan tingkat kompatibilitas pada pengaturan asli dan menunjuk ulang aplikasi. Estimator kardinalitas telah berubah sejak SQL Server 2014, dan Anda ingin mengukur performa kueri sebelum Anda mengubah tingkat kompatibilitas database ke SQL Server 2022 (160).
Anda perlu menemukan dan memperbaiki kueri apa pun yang regresi ketika Anda memindahkan tingkat kompatibilitas dan menerapkan algoritma estimator kardinalitas baru. Menggunakan pendekatan ini mengukur garis besar performa untuk dibandingkan setelah Anda meningkatkan tingkat kompatibilitas.
Mengaktifkan Penyimpanan Kueri mengumpulkan metrik yang Anda butuhkan untuk menemukan kueri regresi, dan menggunakan Query Tuning Assistant (QTA) memandu Anda memperbaiki kueri regresi. Unit ini menyediakan gambaran umum Penyimpanan Kueri dan QTA.
Gambaran umum Penyimpanan Kueri
Fitur Penyimpanan Kueri diperkenalkan pada SQL Server 2016 untuk terus mengumpulkan informasi tentang eksekusi dan performa kueri dalam database. Penyimpanan Kueri beroperasi seperti perekam data penerbangan untuk mengumpulkan informasi runtime tentang kueri dan paket. Jika Anda menyimpan data runtime ini, Anda dapat melacak performa dari waktu ke waktu. Jika terjadi kesalahan, ada riwayat informasi untuk menemukan penyebab masalah.
Dimulai dengan SQL Server 2022, dan di Azure SQL Database dan SQL Managed Instance, Penyimpanan Kueri diaktifkan untuk database baru secara default. Di SQL Server 2016, SQL Server 2017, dan SQL Server 2019, Penyimpanan Kueri tidak diaktifkan secara default, tetapi database apa pun pada instans SQL Server 2016 atau yang lebih tinggi dapat mengaktifkan, menonaktifkan, dan mengonfigurasi Penyimpanan Kueri. Lihat unit berikutnya untuk instruksi tentang cara mengaktifkan Penyimpanan Kueri atau mengonfirmasi konfigurasinya pada database.
Penyimpanan Kueri dapat bekerja pada database dengan tingkat kompatibilitas yang lebih lama daripada instans SQL Server. Misalnya, jika Anda memigrasikan database dari SQL Server 2012 ke SQL Server 2022 dan meninggalkan tingkat kompatibilitas di 110, Penyimpanan Kueri masih dapat beroperasi pada database.
Namun, banyak fitur Pemrosesan Kueri Cerdas dan peningkatan performa otomatis lainnya hanya diaktifkan untuk tingkat kompatibilitas database yang lebih baru. Oleh karena itu, Anda harus mencoba menguji performa aplikasi Anda pada tingkat kompatibilitas database SQL Server terbaru. Penyimpanan Kueri dan QTA dapat membantu pengujian performa ini.
Saat diaktifkan pada database, Penyimpanan Kueri mengumpulkan dan melaporkan statistik berikut untuk kueri Anda:
- Kueri yang Diregresi
- Total Konsumsi Sumber Daya
- Kueri yang Mengonsumsi Sumber Daya Teratas
- Kueri Dengan Rencana Paksa
- Kueri Dengan Variasi Tinggi
- Statistik Tunggu Kueri
- Kueri Terlacak
Kueri yang mengalami kemunduran terjadi saat pengoptimal kueri menggunakan rencana kueri baru yang menyebabkan performa menurus. Regresi dapat terjadi setelah perubahan penting seperti menambahkan, menghilangkan, atau mengubah indeks, memperbarui statistik, atau mengubah kardinalitas data.
Sebelum Penyimpanan Kueri, SQL Server tidak memberikan wawasan tentang penyebab regresi, dan mengidentifikasi masalah adalah masalah bagi pengembang dan administrator database. Sekarang Anda dapat menggunakan Penyimpanan Kueri untuk menemukan kueri yang diregresi dan memaksa pengoptimal menggunakan paket tertentu dari riwayat.
Umum untuk beberapa dari mungkin ribuan kueri untuk mengonsumsi sumber daya sistem terbanyak. Penyimpanan Kueri mengidentifikasi kueri mana yang memiliki konsumsi tertinggi, baik karena regresi atau karena penyetelan yang buruk. Bergantung pada konfigurasi, Anda dapat memfilter hasil berdasarkan durasi, CPU, memori, I/O, atau jumlah eksekusi.
Anda bisa menggunakan Penyimpanan Kueri untuk memantau performa yang sedang berlangsung dan untuk pengujian A/B untuk membandingkan performa sebelum dan sesudah menerapkan satu perubahan. Misalnya, Anda bisa menyetel performa kueri dengan menambahkan indeks ke tabel yang dirujuk kueri, sehingga pencarian gabungan lebih cepat. Membandingkan statistik di Penyimpanan Kueri sebelum dan sesudah Anda menambahkan indeks memberi tahu Anda apakah indeks memengaruhi performa. Anda juga dapat membandingkan statistik setelah menambahkan perangkat keras baru atau memperbarui aplikasi.
Gambaran umum Asisten Penyetelan Kueri
Asisten Penyetelan Kueri (QTA) menggunakan data dari Penyimpanan Kueri untuk menemukan kueri yang mulai diregresi. QTA secara otomatis bereksperimen untuk menemukan solusi yang mempercepat kueri, sebelum kueri kurangformulasi ke titik yang memengaruhi pengguna.
Anda bisa menggunakan Penyimpanan Kueri dan QTA untuk memantau dan mengoptimalkan performa database setelah peningkatan. Setelah memigrasikan database ke SQL Server 2016 atau yang lebih tinggi, Anda membiarkan tingkat kompatibilitas database tidak berubah, dan mengaktifkan Penyimpanan Kueri untuk mengumpulkan statistik performa kueri dasar.
Anda kemudian mengubah tingkat kompatibilitas, dan terus menggunakan data Penyimpanan Kueri untuk mengukur statistik performa kueri Anda. Anda dapat membandingkan statistik untuk mengetahui apakah setiap kueri melakukan lebih baik, sama, atau lebih buruk daripada sebelum peningkatan.
Saat Anda mengubah tingkat kompatibilitas untuk meningkatkan database, SQL Server mengubah versi estimator kardinalitas yang digunakannya. QTA menemukan kemungkinan pola regresi kueri karena perubahan estimator kardinalitas, dan eksperimen untuk menemukan peningkatan performa. Anda kemudian dapat membuat panduan rencana untuk kueri yang menunjukkan peningkatan.
Ringkasan
Penyimpanan Kueri terus mengukur statistik performa kueri Anda, sama seperti aktivitas pengambilan perekam data penerbangan pesawat terbang. Anda dapat mengaktifkan Penyimpanan Kueri pada database apa pun di SQL Server 2016 atau yang lebih tinggi, terlepas dari tingkat kompatibilitas. Gunakan Penyimpanan Kueri untuk terus memantau performa kueri, dan untuk pengujian A/B untuk mengukur efek dari satu perubahan.
Saat Anda meningkatkan database ke SQL Server 2014 atau yang lebih tinggi, perubahan pada estimator kardinalitas mungkin memperlambat kueri yang cepat di versi SQL Server sebelumnya. Idealnya, Anda ingin menemukan dan memperbaiki regresi apa pun sebelum memengaruhi pengguna. Mengaktifkan Penyimpanan Kueri pada database Anda berarti statistik terus dikumpulkan pada kueri. Anda kemudian dapat menggunakan QTA untuk mengidentifikasi dan memperbaiki kueri regresi sebelum menjadi masalah.