Meningkatkan database menggunakan Asisten Penyetelan Kueri

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru

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

Dalam SQL Server 2014 (12.x) dan versi yang lebih baru, semua perubahan pengoptimal kueri dikendalikan oleh tingkat kompatibilitas database terbaru. Jadi, rencana eksekusi tidak diubah tepat pada saat peningkatan, melainkan ketika pengguna mengubah opsi database COMPATIBILITY_LEVEL ke versi terbaru yang tersedia. Untuk informasi selengkapnya tentang perubahan pengoptimal kueri yang diperkenalkan di SQL Server 2014 (12.x), lihat Estimasi Kardinalitas (SQL Server). 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 diagram berikutnya. Untuk informasi selengkapnya tentang alur kerja yang direkomendasikan untuk meningkatkan tingkat kompatibilitas, lihat Mengubah tingkat kompatibilitas database dan menggunakan Penyimpanan Kueri.

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.

Dimulai dengan SQL Server Management Studio v18, fitur Query Tuning Assistant (QTA) 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 Server skenario 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 melacak regresi apa pun yang ditemukan dalam tampilan Penyimpanan Kueri Kueri yang Diregresi, dan melakukan iterasi melalui kemungkinan permutasi dari variasi model pengoptimal yang berlaku sehingga dapat menghasilkan rencana baru yang lebih baik.

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 Lampirkan Pernyataan Database atau RESTORE) ke versi Mesin Database SQL Server yang lebih baru, dan tingkat kompatibilitas database sebelum peningkatan tidak segera diubah. QTA memandu Anda 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 Query Store dapat mengumpulkan data beban kerja dasar (jika belum tersedia).
Tingkatkan ke tingkat kompatibilitas database target yang dipilih oleh pengguna.
Minta agar pengumpulan kedua data beban kerja dilakukan untuk perbandingan dan pendeteksian regresi.
Iterasi melalui regresi yang ditemukan berdasarkan tampilan Query Store Regressed Queries, bereksperimen dengan mengumpulkan statistik runtime tentang kemungkinan permutasi variasi model pengoptimal yang sesuai, serta mengukur hasilnya.
Laporkan peningkatan yang diukur, dan memungkinkan perubahan tersebut untuk dipertahankan secara opsional menggunakan panduan rencana.

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

Diagram berikut menunjukkan bagaimana QTA hanya mengubah langkah terakhir dari alur kerja yang direkomendasikan untuk meningkatkan tingkat kompatibilitas menggunakan Query Store yang telah dibahas sebelumnya. Alih-alih memilih antara rencana eksekusi yang saat ini tidak efisien dan rencana eksekusi baik terakhir yang diketahui, QTA menawarkan opsi penyesuaian yang khusus untuk kueri yang telah mengalami penurunan kinerja, dengan tujuan membentuk keadaan baru yang lebih baik melalui rencana eksekusi yang dioptimalkan.

Ruang pencarian internal Penyetelan QTA

QTA hanya fokus pada SELECT 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 pola regresi kueri yang mungkin dan sudah diketahui akibat perubahan pada versi Estimasi Kardinalitas (SQL Server). Misalnya, saat meng-upgrade database dari SQL Server 2012 (11.x) dan tingkat kompatibilitas database 110 ke SQL Server 2017 (14.x) dan tingkat kompatibilitas database 140, beberapa kueri mungkin mengalami regresi karena dirancang khusus untuk bekerja dengan versi CE (Cardinality Estimator) 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 hanya menggunakan 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 kemunduran untuk mendapatkan manfaat dari peningkatan CE terbaru.

Pola CE yang dicari oleh QTA adalah:

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 petunjuk kueri USE HINT dan Versi dari 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 Simple Containment alih-alih asumsi Base Containment default. Untuk informasi selengkapnya, lihat petunjuk kueri USE HINT dan Versi dari CE.
Multi-statement table-valued function (MSTVF) dengan tebakan kardinalitas tetap 100 baris vs. 1 baris: Jika estimasi tetap default untuk TVF 100 baris tidak menghasilkan rencana yang lebih efisien dibandingkan dengan penggunaan 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), sehingga petunjuk kueri QUERYTRACEON 9488 digunakan untuk menghasilkan rencana eksekusi. Untuk informasi selengkapnya tentang MSTVF, lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).

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 menggunakan petunjuk ketika tidak ada opsi lain, dan berencana untuk meninjau kembali kode yang telah diberi petunjuk pada setiap peningkatan baru. Dengan memaksa perilaku, Anda mungkin mencegah beban kerja Anda mendapatkan manfaat dari peningkatan yang diperkenalkan dalam versi SQL Server yang lebih baru.

Mulai Asisten Pengoptimalan Kueri dalam rangka 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 pemutakhiran 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 data beban kerja yang sebanding dengan satu siklus bisnis penuh untuk dianalisis dan dioptimalkan.
  - Masukkan durasi beban kerja yang diharapkan dalam hari (minimum adalah 1 hari). Ini digunakan untuk menyarankan pengaturan Query Store yang direkomendasikan untuk sementara memungkinkan seluruh baseline dikumpulkan. Menangkap tolok ukur yang baik penting untuk memastikan setiap kueri yang mengalami regresi yang ditemukan setelah mengubah tingkat kompatibilitas database dapat dianalisis.
  - Atur tingkat kompatibilitas database target yang diinginkan untuk database pengguna setelah alur kerja QTA selesai.
  Setelah selesai, pilih Berikutnya.
2. Di jendela Pengaturan , dua kolom memperlihatkan status Penyimpanan Kueri saat ini di database yang ditargetkan, dan pengaturan yang Direkomendasikan .
  - Pengaturan yang Direkomendasikan dipilih secara default, tetapi memilih tombol radio di kolom Saat Ini menerima pengaturan saat ini, dan juga memungkinkan penyesuaian konfigurasi Penyimpanan Kueri saat ini.
  - Pengaturan Ambang Batas Kueri Kedaluarsa yang diusulkan adalah dua kali durasi waktu beban kerja yang diharapkan, dalam hitungan hari. Ini karena Penyimpanan Kueri perlu menyimpan informasi tentang beban kerja dasar dan beban kerja setelah peningkatan database.
  Setelah selesai, pilih Berikutnya.
  
  Penting
  
  Ukuran Maks yang diusulkan adalah nilai arbitrer yang mungkin cocok untuk beban kerja berwaktu singkat. Namun, menyimpan informasi tentang beban kerja garis dasar dan setelah peningkatan database mungkin tidak cukup untuk beban kerja intensif, terutama ketika banyak rencana berbeda mungkin 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 dari database yang termasuk 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 menyebarkan panduan rencana, maka Anda tidak dapat menggunakan QTA untuk mengembalikan. Sebagai gantinya, cari panduan rencana 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 sublangkah:
1. Pengumpulan Data Dasar meminta pengguna untuk menjalankan siklus beban kerja representatif, agar Query Store dapat mengumpulkan dasar. Setelah pekerjaan selesai, periksa Selesai dengan pekerjaan dijalankan dan pilih Berikutnya.
  
  Catatan
  
  Jendela QTA dapat ditutup ketika tugas berjalan. Saat Anda kembali ke sesi yang tetap berada dalam status aktif di lain waktu, sesi tersebut akan melanjutkan dari langkah yang terakhir kali Anda tinggalkan.
2. Meningkatkan Database 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 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 Selesai menjalankan beban kerja dan pilih Berikutnya.
  
  Daftar berisi informasi berikut:
  - ID Kueri
  - Teks Kueri: Pernyataan Transact-SQL yang dapat diperluas dengan memilih tombol ....
  - Jalankan: Menampilkan jumlah eksekusi kueri tersebut untuk seluruh koleksi beban kerja.
  - Metrik Garis Dasar: Metrik yang dipilih (Durasi atau CpuTime) dalam ms untuk pengumpulan data garis dasar sebelum peningkatan kompatibilitas database.
  - Metrik yang Diamati: Metrik yang dipilih (Durasi atau CpuTime) dalam ms 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.
View Analysis memungkinkan untuk memilih kueri mana yang ingin dieksperimenkan dan menemukan peluang pengoptimalan. Kueri untuk ditampilkan nilai menjadi ruang lingkup kueri yang memenuhi syarat untuk bereksperimen. Setelah kueri yang diinginkan dicentang, pilih Berikutnya untuk memulai eksperimen.

Kueri yang Tunable diatur ke False tidak dapat digunakan dalam eksperimen.

Penting

Perintah menyarankan bahwa setelah QTA pindah ke fase eksperimen, kembali ke halaman Analisis Tampilan tidak 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 Transact-SQL yang dapat diperluas dengan memilih tombol ....
- Status: Menampilkan status eksperimen saat ini untuk kueri.
- Metrik Dasar: Metrik yang dipilih (Durasi atau CpuTime) dalam ms untuk kueri saat dieksekusi di Langkah 2 Substep 3, mewakili kueri yang mengalami regresi setelah peningkatan kompatibilitas database.
- Metrik yang Diamati: Metrik yang dipilih (Durasi atau CpuTime) dalam ms untuk kueri setelah eksperimen, untuk pengoptimalan yang diusulkan yang cukup baik.
- % Ubah: Menentukan perubahan persen untuk metrik yang dipilih antara status sebelum dan sesudah eksperimen, mewakili jumlah peningkatan terukur untuk kueri dengan pengoptimalan yang diusulkan.
- Opsi Kueri: Tautan ke saran yang diusulkan untuk meningkatkan metrik eksekusi kueri.
- Bisa Diterapkan: Benar atau Salah berdasarkan apakah pengoptimalan kueri yang diusulkan dapat diterapkan sebagai panduan eksekusi.
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 menyebarkan panduan rencana, maka Anda tidak dapat menggunakan QTA untuk mengembalikan. Sebagai gantinya, cari panduan rencana menggunakan tabel sistem sys.plan_guides, dan hapus secara manual menggunakan sp_control_plan_guide.

Izin

Memerlukan keanggotaan peran db_owner.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-03-28