Menyetel kueri pasca migrasi
Misalkan Anda seorang administrator database untuk firma hukum. Selama akhir pekan, Anda telah memigrasikan beberapa database ke SQL Server 2019, termasuk database Customer Relationship Management (CRM). Anda berada di kantor lebih awal pada Senin pagi, mengharapkan tim penjualan tiba dalam beberapa jam, membuat dan menerima panggilan telepon pelanggan. Tim penjualan tergantung pada database CRM, dan bagi mereka untuk terlibat secara efektif dengan pelanggan, aplikasi harus merespons secara instan.
Setelah memigrasikan database, Anda meninggalkan tingkat kompatibilitas di pengaturan sebelumnya. Aplikasi CRM terhubung ke server baru. Anda ingin mengukur performa kueri dalam sistem baru terhadap beban kerja Senin biasa sebelum Anda mengubah tingkat kompatibilitas. Mempertahankan performa kueri sangat penting saat meningkatkan database.
Daripada menunggu tim penjualan menemukan bahwa aplikasi CRM lambat, Anda ingin proses mengukur beban kerja dan performa secara otomatis. Proses ini harus meyakinkan Anda bahwa Anda dapat memperbaiki kueri lambat setelah mengubah tingkat kompatibilitas.
Query Store
Fitur Penyimpanan Kueri diluncurkan di SQL Server 2016 untuk terus mengumpulkan informasi tentang kueri Anda. Penyimpanan Kueri mengumpulkan informasi yang bisa Anda gunakan untuk memecahkan masalah kueri yang kurang format, bekerja dengan penginstalan lokal dan cloud. Rencana kueri yang di-cache disimpan dalam cache prosedur tetapi SQL Server hanya menyimpan rencana eksekusi terbaru.
Kemungkinan rencana eksekusi untuk kueri akan berubah dari waktu ke waktu karena perubahan skema atau penambahan atau penghapusan indeks. Selain itu, tekanan pada memori dapat menyebabkan rencana dikeluarkan dari cache rencana.
Secara default, Penyimpanan Kueri tidak diaktifkan saat Anda membuat atau memigrasikan database ke SQL Server 2017 atau SQL Server 2019. Namun, Anda bisa mengaktifkan fitur Penyimpanan Kueri sebelum memperbarui tingkat kompatibilitas database pada database yang dimigrasikan. Aktifkan Penyimpanan Kueri dengan mengklik kanan database di Object Explorer, memilih Properti, lalu memilih halaman Penyimpanan Kueri. Dalam daftar pilih Mode Operasi (Diminta), pilih dari Nonaktif, Baca Saja, atau Baca Tulis. Anda juga dapat mengaktifkan Penyimpanan Kueri dengan menggunakan Transact-SQL. Untuk mengaktifkan Penyimpanan Kueri untuk database bernama CustomerServices, Anda akan menjalankan perintah berikut:
ALTER DATABASE CustomerServices SET QUERY_STORE = ON
Saat kueri awalnya dikompilasi, teks kueri dan perencanaan pertama diteruskan ke Penyimpanan Kueri. Jika kueri dikompresi ulang, paket di Penyimpanan Kueri diperbarui. Setelah paket baru dibuat, Query Store menambahkan dan menyimpannya bersama dengan rencana sebelumnya dan statistik eksekusi.
Saat kueri dijalankan, Penyimpanan Kueri menyimpan statistik runtime. Selama kompilasi atau kompilasi ulang, SQL Server memeriksa apakah perencanaan ada di Penyimpanan Kueri yang harus diterapkan ke kueri. Ketika rencana paksa berbeda dari yang ada di cache prosedur, kueri dikompresi ulang.
Hingga Anda mengubah tingkat kompatibilitas, database tidak diekspos ke perubahan Pengoptimal Kueri terbaru. Anda dapat mengumpulkan beban kerja umum dan membuat garis besar untuk mengukur tingkat kompatibilitas yang ada. Setelah mengumpulkan informasi kueri yang cukup untuk memiliki garis besar performa, Anda mengubah tingkat kompatibilitas database ke versi server.
Untuk membuat garis besar yang memadai, pastikan Anda mengumpulkan data yang cukup dari periode aktivitas bisnis khusus. Saat Anda pindah ke tingkat kompatibilitas server saat ini, beban kerja kemudian diekspos ke Pengoptimal Kueri terbaru. Performa kueri kemungkinan akan meningkat, karena Pengoptimal Kueri baru harus menghasilkan rencana yang lebih baik.
Terkadang, SQL Server akan menggunakan rencana kueri yang kurang optimal, sehingga tiba-tiba mulai dieksekusi secara perlahan dan tanpa alasan yang jelas. Anda bisa memperbaiki masalah ini dengan memaksa paket sebelumnya di Penyimpanan Kueri. Menggunakan Penyimpanan Kueri mirip dengan menggunakan petunjuk kueri USE PLAN, tetapi tidak memerlukan perubahan pada aplikasi pengguna.
Setelah memindahkan tingkat kompatibilitas, Penyimpanan Kueri terus menyediakan dukungan kueri untuk mempertahankan performa terbaik. Ini mengukur dan mengaudit Kueri yang Diregresi, Konsumsi Sumber Daya Keseluruhan, Kueri Penggunaan Sumber Daya Teratas, Kueri dengan Paket Paksa, Kueri dengan Variasi Tinggi, dan Kueri Terlacak.
Penyetelan kueri otomatis
Peran Penghitung Kardinalitas (CE) dalam SQL Server adalah untuk memperkirakan jumlah baris yang dikembalikan dalam kueri. Pengoptimal Kueri menggunakan hasil CE untuk menghasilkan rencana eksekusi. Hingga SQL Server 2014, Kardinalitas Estimator tetap sangat tidak berubah. Rilis SQL Server 2014 melihat perombakan ke algoritma CE untuk meningkatkan perkiraan dan memberikan hasil kueri tercepat. Namun, tingkat kompatibilitas database menentukan CE mana yang akan digunakan.
Meskipun performa kueri harus sangat ditingkatkan dalam SQL Server 2014 dan seterusnya, database yang dimigrasikan dengan tingkat kompatibilitas yang diperbarui mungkin mengalami penurunan performa. Penyetelan Kueri Otomatis diperkenalkan pada SQL Server 2017. Gunakan bersama dengan Penyimpanan Kueri untuk menemukan dan secara otomatis memperbaiki kueri regresi. Setelah memigrasikan database Anda dan menangkap beban kerja database di tingkat kompatibilitas sebelumnya, Anda kemudian meningkatkan tingkat kompatibilitas dan terus menjalankan Penyimpanan Kueri.
Penyetelan Otomatis menggunakan data di Penyimpanan Kueri untuk menemukan kueri yang mulai mengalami regresi kembali karena perubahan dalam Penghitung Kardinalitas setelah memindahkan tingkat kompatibilitas. Eksperimen penyetelan otomatis pada kueri regresi untuk meningkatkan performa, memungkinkan Anda membuat panduan rencana tempat penyempurnaan dilakukan.
Cara menyetel performa kueri di SQL Server 2019
Dalam contoh firma hukum Anda, karena Anda telah meningkatkan server database ke SQL Server 2019, Anda dapat menggunakan Penyimpanan Kueri dan Penyetelan Otomatis untuk mengoptimalkan performa dan memastikan bahwa perubahan tingkat kompatibilitas tidak menyebabkan regresi. Kami merekomendasikan proses berikut:
- Tingkatkan server database ke SQL Server 2019 tetapi jangan ubah tingkat kompatibilitas.
- Mengaktifkan Penyimpanan Kueri.
- Izinkan Penyimpanan Kueri mengumpulkan data performa di seluruh jam kerja normal.
- Tingkatkan tingkat kompatibilitas database.
- Periksa untuk melihat apakah Penyimpanan Kueri telah menemukan regresi. Jika demikian, Anda dapat memaksa rencana kueri terakhir yang diketahui baik untuk kembali ke tingkat performa yang lebih tinggi.
Penyimpanan Kueri mengotomatiskan proses pemantauan performa kueri Anda. Ini adalah alat yang berguna untuk berjalan di latar belakang untuk memastikan database Anda terus memberikan hasil dalam waktu tercepat. Dikombinasikan dengan Penyimpanan Kueri, Asisten Penyetelan Kueri (QTA) menggunakan data di Penyimpanan Kueri untuk menemukan kueri yang mengalami regresi dalam database Anda yang ditingkatkan.
Setelah memigrasikan database dan meningkatkan tingkat kompatibilitas ke SQL Server 2014 atau lebih tinggi, Penghitung Kardinalitas baru digunakan untuk menebak jumlah baris yang dikembalikan oleh kueri. Anda menggunakan QTA untuk menemukan dan memperbaiki kueri yang mengalami regresi kembali dengan pengalihan ke CE yang lebih baru.