Meningkatkan database menggunakan Asisten Penyetelan Kueri

Artikel
05/23/2023

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure Synapse Analytics Analytics Platform System (PDW)

Saat bermigrasi dari versi SQL Server yang lebih lama ke SQL Server 2014 (12.x) atau yang lebih baru, dan meningkatkan tingkat kompatibilitas database ke yang terbaru tersedia, beban kerja mungkin terpapar risiko regresi performa. Ini juga dimungkinkan untuk tingkat yang lebih rendah saat meningkatkan antara SQL Server 2014 (12.x) dan versi yang lebih baru.

Dimulai dengan SQL Server 2014 (12.x), dan dengan setiap versi baru, semua perubahan pengoptimal kueri terjaga ke tingkat kompatibilitas database terbaru, sehingga rencana eksekusi tidak diubah tepat pada titik peningkatan, melainkan ketika pengguna mengubah COMPATIBILITY_LEVEL opsi database menjadi yang terbaru yang tersedia. Untuk informasi selengkapnya tentang perubahan pengoptimal kueri yang diperkenalkan di SQL Server 2014 (12.x), lihat Estimator Kardinalitas. Untuk informasi selengkapnya tentang tingkat kompatibilitas dan bagaimana tingkat kompatibilitas dapat memengaruhi peningkatan, lihat Tingkat Kompatibilitas dan Peningkatan Mesin Database.

Kemampuan pembatasan yang disediakan oleh tingkat kompatibilitas database ini, dalam kombinasi dengan Penyimpanan Kueri memberi Anda tingkat kontrol yang besar atas performa kueri dalam proses peningkatan jika peningkatan mengikuti alur kerja yang direkomendasikan yang terlihat di bawah ini. Untuk informasi selengkapnya tentang alur kerja yang direkomendasikan untuk meningkatkan tingkat kompatibilitas, lihat Mengubah Mode Kompatibilitas Database dan Menggunakan Penyimpanan Kueri.

Recommended database upgrade workflow using Query Store

Kontrol atas peningkatan ini lebih ditingkatkan dengan SQL Server 2017 (14.x) di mana penyetelan otomatis diperkenalkan dan memungkinkan mengotomatiskan langkah terakhir dalam alur kerja yang direkomendasikan di atas.

Dimulai dengan SQL Server Management Studio v18, fitur Query Tuning Assistant (QTA) baru akan memandu pengguna melalui alur kerja yang direkomendasikan untuk menjaga stabilitas performa selama peningkatan ke versi SQL Server yang lebih baru, seperti yang didokumentasikan di bagian Menjaga stabilitas performa selama peningkatan ke SQL Serverskenario penggunaan Penyimpanan Kueri yang lebih baru. Namun, QTA tidak kembali ke rencana baik yang diketahui sebelumnya seperti yang terlihat pada langkah terakhir dari alur kerja yang direkomendasikan. Sebagai gantinya, QTA akan melacak regresi apa pun yang ditemukan dalam tampilan Kueri Yang Diregresi Penyimpanan Kueri, dan melakukan iterasi melalui kemungkinan permutasi variasi model pengoptimal yang berlaku sehingga rencana baru yang lebih baik dapat diproduksi.

Penting

QTA tidak menghasilkan beban kerja pengguna. Jika menjalankan QTA di lingkungan yang tidak digunakan oleh aplikasi Anda, pastikan Anda masih dapat menjalankan beban kerja pengujian perwakilan pada Mesin Database SQL Server yang ditargetkan dengan cara lain.

Alur kerja Asisten Penyetelan Kueri

Titik awal QTA mengasumsikan bahwa database dari versi SQL Server sebelumnya dipindahkan (melalui CREATE DATABASE ... UNTUK LAMPIRKAN atau PULIHKAN) ke versi Mesin Database SQL Server yang lebih baru, dan tingkat kompatibilitas database sebelum peningkatan tidak segera diubah. QTA akan memandu melalui langkah-langkah berikut:

Konfigurasikan Penyimpanan Kueri sesuai dengan pengaturan yang direkomendasikan untuk durasi beban kerja (dalam hari) yang ditetapkan oleh pengguna. Pikirkan tentang durasi beban kerja yang cocok dengan siklus bisnis khas Anda.
Minta untuk memulai beban kerja yang diperlukan, sehingga Penyimpanan Kueri dapat mengumpulkan garis besar data beban kerja (jika belum ada).
Tingkatkan ke tingkat kompatibilitas database target yang dipilih oleh pengguna.
Minta agar lulus kedua data beban kerja dikumpulkan untuk perbandingan dan deteksi regresi.
Iterasi melalui regresi apa pun yang ditemukan berdasarkan tampilan Kueri Yang Diregresi Penyimpanan Kueri, bereksperimen dengan mengumpulkan statistik runtime tentang kemungkinan permutasi variasi model pengoptimal yang berlaku, dan mengukur hasilnya.
Laporkan peningkatan yang diukur, dan secara opsional memungkinkan perubahan tersebut dipertahankan menggunakan panduan rencana.

Untuk informasi selengkapnya tentang melampirkan database, lihat Lepas database dan Lampirkan.

Lihat di bawah ini bagaimana QTA hanya mengubah langkah terakhir alur kerja yang direkomendasikan untuk meningkatkan tingkat kompatibilitas menggunakan Penyimpanan Kueri yang terlihat di atas. Alih-alih memiliki opsi untuk memilih antara rencana eksekusi yang saat ini tidak efisien dan rencana eksekusi baik terakhir yang diketahui, QTA menyajikan opsi penyetelan yang khusus untuk kueri yang diregresi yang dipilih, untuk membuat status baru yang ditingkatkan dengan rencana eksekusi yang disetel.

Recommended database upgrade workflow using QTA

Ruang pencarian internal Penyetelan QTA

QTA hanya SELECT menargetkan kueri yang dapat dijalankan dari Penyimpanan Kueri. Kueri berparameter memenuhi syarat jika parameter yang dikompilasi diketahui. Kueri yang bergantung pada konstruksi runtime seperti tabel sementara atau variabel tabel saat ini tidak memenuhi syarat.

QTA menargetkan kemungkinan pola regresi kueri yang diketahui karena perubahan versi Kardinalitas Estimator (CE ). Misalnya, saat meningkatkan database dari SQL Server 2012 (11.x) dan tingkat kompatibilitas database 110, ke SQL Server 2017 (14.x) dan kompatibilitas database tingkat 140, beberapa kueri dapat mengalami kemunculan karena dirancang khusus untuk bekerja dengan versi CE yang ada di SQL Server 2012 (11.x) (CE 70). Ini tidak berarti bahwa mengembalikan dari CE 140 ke CE 70 adalah satu-satunya opsi. Jika hanya perubahan spesifik dalam versi yang lebih baru yang memperkenalkan regresi, maka dimungkinkan untuk mengisyaratkan kueri tersebut untuk menggunakan hanya bagian yang relevan dari versi CE sebelumnya yang bekerja lebih baik untuk kueri tertentu, sambil masih menggunakan semua peningkatan lain dari versi CE yang lebih baru. Dan juga memungkinkan kueri lain dalam beban kerja yang belum mengalami kemunculan untuk mendapatkan manfaat dari peningkatan CE yang lebih baru.

Pola CE yang dicari oleh QTA adalah sebagai berikut:

Independensi vs. Korelasi: Jika asumsi kemerdekaan memberikan estimasi yang lebih baik untuk kueri tertentu, petunjuk USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') kueri menyebabkan SQL Server menghasilkan rencana eksekusi dengan menggunakan selektivitas minimum saat memperkirakan AND predikat filter untuk memperhitungkan korelasi. Untuk informasi selengkapnya, lihat MENGGUNAKAN petunjuk kueri HINT dan Versi CE.
Penahanan Sederhana vs. Penahanan Dasar: Jika penahanan gabungan yang berbeda memberikan estimasi yang lebih baik untuk kueri tertentu, petunjuk USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') kueri menyebabkan SQL Server menghasilkan rencana eksekusi dengan menggunakan asumsi Penahanan Sederhana alih-alih asumsi Base Containment default. Untuk informasi selengkapnya, lihat MENGGUNAKAN petunjuk kueri HINT dan Versi CE.
Fungsi bernilai tabel multi-pernyataan (MSTVF) menebak kardinalitas tetap 100 baris vs. 1 baris: Jika estimasi tetap default untuk TVF 100 baris tidak menghasilkan rencana yang lebih efisien daripada menggunakan estimasi tetap untuk TVF 1 baris (sesuai dengan default di bawah model CE pengoptimal kueri SQL Server 2008 R2 (10.50.x) dan versi sebelumnya), kemudian petunjuk QUERYTRACEON 9488 kueri digunakan untuk menghasilkan rencana eksekusi. Untuk informasi selengkapnya tentang MSTVF, lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).

Catatan

Sebagai upaya terakhir, jika petunjuk tercakup sempit tidak menghasilkan hasil yang cukup baik untuk pola kueri yang memenuhi syarat, penggunaan penuh CE 70 juga dipertimbangkan, dengan menggunakan petunjuk USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') kueri untuk menghasilkan rencana eksekusi.

Penting

Petunjuk apa pun memaksa perilaku tertentu yang mungkin ditangani dalam pembaruan SQL Server di masa mendatang. Sebaiknya Anda hanya menerapkan petunjuk ketika tidak ada opsi lain, dan berencana untuk mengunjungi kembali kode yang diisyaratkan dengan setiap peningkatan baru. Dengan memaksa perilaku, Anda mungkin mendahului beban kerja Anda dari manfaat peningkatan yang diperkenalkan dalam versi SQL Server yang lebih baru.

Mulai Asisten Penyetelan Kueri untuk peningkatan database

QTA adalah fitur berbasis sesi yang menyimpan status sesi dalam msqta skema database pengguna tempat sesi dibuat untuk pertama kalinya. Beberapa sesi penyetelan dapat dibuat pada database tunggal dari waktu ke waktu, tetapi hanya satu sesi aktif yang dapat ada untuk database tertentu.

Membuat sesi peningkatan database

Di SQL Server Management Studio, buka Object Explorer dan sambungkan ke Mesin Database.
Untuk database yang dimaksudkan untuk meningkatkan tingkat kompatibilitas database, klik kanan nama database, pilih Tugas, pilih Peningkatan Database, dan pilih Sesi Peningkatan Database Baru.
Di jendela Wizard QTA, dua langkah diperlukan untuk mengonfigurasi sesi:
1. Di jendela Penyiapan , konfigurasikan Query Store untuk menangkap siklus bisnis lengkap data beban kerja yang setara untuk dianalisis dan disambungkan.
  - Masukkan durasi beban kerja yang diharapkan dalam hari (minimum adalah 1 hari). Ini akan digunakan untuk mengusulkan pengaturan Penyimpanan Kueri yang direkomendasikan untuk secara tentatif memungkinkan seluruh garis besar dikumpulkan. Menangkap garis besar yang baik penting untuk memastikan setiap kueri yang mengalami kemunduran yang ditemukan setelah mengubah tingkat kompatibilitas database dapat dianalisis.
  - Atur tingkat kompatibilitas database target yang dimaksudkan tempat database pengguna harus berada, setelah alur kerja QTA selesai. Setelah selesai, pilih Berikutnya.
2. Di jendela Pengaturan, dua kolom memperlihatkan status Penyimpanan Kueri saat ini dalam database yang ditargetkan, serta pengaturan yang Direkomendasikan.
  - Pengaturan yang Direkomendasikan dipilih secara default, tetapi memilih tombol radio di atas kolom Saat ini menerima pengaturan saat ini, dan juga memungkinkan penyempurnaan konfigurasi Penyimpanan Kueri saat ini.
  - Pengaturan Ambang Batas Kueri Kedaluarsa yang diusulkan adalah dua kali jumlah durasi beban kerja yang diharapkan, dalam hari. Ini karena Query Store perlu menyimpan informasi tentang beban kerja garis besar dan beban kerja peningkatan pasca-database. Setelah selesai, pilih Berikutnya.
  Penting
  
  Ukuran Maks yang diusulkan adalah nilai arbitrer yang mungkin cocok untuk beban kerja yang singkat. Namun, perlu diingat bahwa mungkin tidak cukup untuk menyimpan informasi tentang beban kerja peningkatan garis besar dan pasca-database untuk beban kerja yang sangat intensif, yaitu ketika banyak paket yang berbeda dapat dihasilkan. Jika Anda mengantisipasi hal ini akan terjadi, masukkan nilai yang lebih tinggi yang sesuai.
Jendela Penyetelan menyimpulkan konfigurasi sesi, dan menginstruksikan langkah berikutnya untuk membuka dan melanjutkan sesi. Setelah selesai, pilih Selesai.

Menjalankan alur kerja peningkatan database

Untuk database yang dimaksudkan untuk meningkatkan tingkat kompatibilitas database, klik kanan nama database, pilih Tugas, pilih Peningkatan Database, dan pilih Pantau Sesi.
Halaman manajemen sesi mencantumkan sesi saat ini dan sebelumnya untuk database dalam cakupan. Pilih sesi yang diinginkan, dan pilih Detail.

Catatan

Jika sesi saat ini tidak ada, pilih tombol Refresh .

Daftar berisi informasi berikut:
- ID Sesi
- Nama Sesi: Nama yang dihasilkan sistem terdiri dari nama database, tanggal, dan waktu pembuatan sesi.
- Status: Status sesi (Aktif atau Tertutup).
- Deskripsi: Yang dihasilkan sistem terdiri dari tingkat kompatibilitas database target yang dipilih pengguna dan jumlah hari untuk beban kerja siklus bisnis.
- Waktu Dimulai: Tanggal dan waktu saat sesi dibuat.
Catatan

Hapus Sesi menghapus data apa pun yang disimpan untuk sesi yang dipilih. Namun, menghapus sesi tertutup tidak menghapus panduan rencana yang disebarkan sebelumnya. Jika Anda menghapus sesi yang telah menyebarkan panduan rencana, maka Anda tidak dapat menggunakan QTA untuk memutar kembali. Sebagai gantinya , cari panduan paket menggunakan tabel sistem sys.plan_guides , dan hapus secara manual menggunakan sp_control_plan_guide.
Titik masuk untuk sesi baru adalah langkah Pengumpulan Data.

Catatan

Tombol Sesi kembali ke halaman manajemen sesi, meninggalkan sesi aktif apa adanya.

Langkah ini memiliki tiga substeps:
1. Pengumpulan Data Garis Besar meminta pengguna untuk menjalankan siklus beban kerja perwakilan, sehingga Query Store dapat mengumpulkan garis besar. Setelah beban kerja selesai, periksa eksekusi Selesai dengan beban kerja dan pilih Berikutnya.
  
  Catatan
  
  Jendela QTA dapat ditutup saat beban kerja berjalan. Kembali ke sesi yang tetap dalam status aktif di lain waktu akan dilanjutkan dari langkah yang sama di mana sesi tersebut ditinggalkan.
2. Upgrade Database akan meminta izin untuk meningkatkan tingkat kompatibilitas database ke target yang diinginkan. Untuk melanjutkan ke substep berikutnya, pilih Ya.
  
  Halaman berikut mengonfirmasi bahwa tingkat kompatibilitas database berhasil dimutakhirkan.
3. Pengumpulan Data yang Diamati meminta pengguna untuk menjalankan siklus beban kerja perwakilan lagi, sehingga Query Store dapat mengumpulkan garis besar komparatif yang akan digunakan untuk mencari peluang pengoptimalan. Saat beban kerja dijalankan, gunakan tombol Refresh untuk terus memperbarui daftar kueri yang diregresi, jika ada yang ditemukan. Ubah Kueri untuk memperlihatkan nilai guna membatasi jumlah kueri yang ditampilkan. Urutan daftar dipengaruhi oleh Metrik (Durasi atau CpuTime) dan Agregasi (Rata-rata adalah default). Pilih juga berapa banyak Kueri yang akan ditampilkan. Setelah beban kerja selesai, periksa eksekusi Selesai dengan beban kerja dan pilih Berikutnya.
  
  Daftar berisi informasi berikut:
  - ID Kueri
  - Teks Kueri: Pernyataan T-SQL yang dapat diperluas dengan memilih tombol ... .
  - Jalankan: Menampilkan jumlah eksekusi kueri tersebut untuk seluruh koleksi beban kerja.
  - Metrik Garis Besar: Metrik yang dipilih (Durasi atau CpuTime) dalam md untuk pengumpulan data garis besar sebelum peningkatan kompatibilitas database.
  - Metrik yang Diamati: Metrik yang dipilih (Durasi atau CpuTime) dalam md untuk pengumpulan data setelah peningkatan kompatibilitas database.
  - % Ubah: Perubahan persen untuk metrik yang dipilih antara status peningkatan kompatibilitas database sebelum dan sesudah. Angka negatif menunjukkan jumlah regresi terukur untuk kueri.
  - Dapat ditunjukkan: Benar atau Salah tergantung pada apakah kueri memenuhi syarat untuk eksperimen.
Analisis Tampilan memungkinkan pemilihan kueri mana yang akan bereksperimen dan menemukan peluang pengoptimalan. Kueri untuk menampilkan nilai menjadi cakupan kueri yang memenuhi syarat untuk bereksperimen. Setelah kueri yang diinginkan dicentang, pilih Berikutnya untuk memulai eksperimen.

Catatan

Kueri dengan Tunable = False tidak dapat dipilih untuk eksperimen.

Penting

Perintah menyarankan bahwa setelah QTA pindah ke fase eksperimen, kembali ke halaman Analisis Tampilan tidak akan dimungkinkan.
Jika Anda tidak memilih semua kueri yang memenuhi syarat sebelum pindah ke fase eksperimen, Anda perlu membuat sesi baru di lain waktu, dan mengulangi alur kerja. Ini memerlukan reset tingkat kompatibilitas database ke nilai sebelumnya.
Lihat Temuan memungkinkan pemilihan kueri mana yang akan menyebarkan pengoptimalan yang diusulkan sebagai panduan rencana.

Daftar berisi informasi berikut:
- ID Kueri
- Teks Kueri: Pernyataan T-SQL yang dapat diperluas dengan memilih tombol ... .
- Status: Menampilkan status eksperimen saat ini untuk kueri.
- Metrik Garis Besar: Metrik yang dipilih (Durasi atau CpuTime) dalam ms untuk kueri seperti yang dijalankan di Langkah 2 Substep 3, mewakili kueri yang diregresi setelah peningkatan kompatibilitas database.
- Metrik yang Diamati: Metrik yang dipilih (Durasi atau CpuTime) dalam md untuk kueri setelah eksperimen, untuk pengoptimalan yang diusulkan dengan cukup baik.
- % Ubah: Perubahan persen untuk metrik yang dipilih antara status sebelum dan sesudah eksperimen, mewakili jumlah peningkatan terukur untuk kueri dengan pengoptimalan yang diusulkan.
- Opsi Kueri: Tautkan ke petunjuk yang diusulkan yang meningkatkan metrik eksekusi kueri.
- Dapat Menyebarkan: Benar atau Salah tergantung pada apakah pengoptimalan kueri yang diusulkan dapat disebarkan sebagai panduan rencana.
Verifikasi menunjukkan status penyebaran kueri yang dipilih sebelumnya untuk sesi ini. Daftar di halaman ini berbeda dari halaman sebelumnya dengan mengubah kolom Bisa Menyebarkan ke Bisa Putar Kembali. Kolom ini bisa True atau False tergantung pada apakah pengoptimalan kueri yang disebarkan dapat digulung balik dan panduan rencananya dihapus.

Jika nanti ada kebutuhan untuk mengembalikan pengoptimalan yang diusulkan, maka pilih kueri yang relevan dan pilih Putar Kembali. Panduan rencana kueri tersebut dihapus dan daftar diperbarui untuk menghapus kueri yang digulung balik. Perhatikan pada gambar di bawah ini bahwa kueri 8 dihapus.

Catatan

Menghapus sesi tertutup tidak menghapus panduan rencana yang disebarkan sebelumnya. Jika Anda menghapus sesi yang telah menyebarkan panduan rencana, maka Anda tidak dapat menggunakan QTA untuk memutar kembali. Sebagai gantinya , cari panduan paket menggunakan tabel sistem sys.plan_guides , dan hapus secara manual menggunakan sp_control_plan_guide.