Bagikan melalui

Meningkatkan database menggunakan Asisten Penyetelan Kueri

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Tidak didukung. Azure SQL Database Tidak didukung. Azure Synapse Analytics Tidak didukung. 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 dalam tingkat yang lebih rendah ketika 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 pada pengoptimal kueri dibatasi ke tingkat kompatibilitas database terbaru, sehingga rencana pelaksanaan tidak diubah langsung pada saat peningkatan tetapi ketika pengguna mengubah opsi COMPATIBILITY_LEVEL database ke yang paling baru yang tersedia. Untuk informasi lebih lanjut tentang perubahan pengoptimal kueri yang diperkenalkan di SQL Server 2014 (12.x), lihat Cardinality Estimator. 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 memperbarui tingkat kompatibilitas, lihat Mengubah Mode Kompatibilitas Database dan Menggunakan Query Store.

Alur kerja pemutakhiran database yang direkomendasikan menggunakan 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 baru Query Tuning Assistant (QTA) 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 versi SQL Server yang lebih baru dalam Skenario Penggunaan Query Store. 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 Query Store Kueri yang Diregresi, dan melakukan iterasi melalui kemungkinan permutasi variasi model pengoptimal yang berlaku sehingga dapat dihasilkan 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 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:

  1. 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.
  2. Minta untuk memulai beban kerja yang diperlukan, sehingga Query Store dapat mengumpulkan data beban kerja dasar (jika belum tersedia).
  3. Tingkatkan ke tingkat kompatibilitas database target yang dipilih oleh pengguna.
  4. Minta agar pengumpulan kedua data beban kerja dilakukan untuk perbandingan dan pendeteksian regresi.
  5. 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.
  6. 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.

Lihat di bawah ini bagaimana QTA hanya mengubah langkah-langkah terakhir dari alur kerja yang direkomendasikan untuk meningkatkan level kompatibilitas menggunakan Penyimpanan Kueri yang terlihat di atas. Bukannya memiliki opsi untuk memilih antara rencana eksekusi yang saat ini tidak efisien dan rencana eksekusi yang terakhir diketahui baik, QTA menyajikan opsi penyetelan yang khusus untuk kueri yang mengalami penurunan kinerja yang dipilih, untuk menciptakan kondisi baru yang lebih baik dengan rencana eksekusi yang disetel.

Alur kerja peningkatan database yang direkomendasikan menggunakan QTA

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 kemungkinan pola regresi kueri yang diketahui karena perubahan versi Kardinalitas Estimator (CE ). Misalnya, saat memperbarui 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 dapat menurun karena dirancang khusus untuk bekerja dengan versi CE yang tersedia 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 kemunduran untuk mendapatkan manfaat dari peningkatan CE terbaru.

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 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 kueri USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') menyebabkan SQL Server menghasilkan rencana eksekusi dengan menggunakan asumsi Penahanan Sederhana alih-alih asumsi Penahanan Dasar yang default. Untuk informasi selengkapnya, lihat USE HINT query hints dan Versi 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).

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 menggunakan petunjuk ketika tidak ada opsi lain, dan berencana untuk meninjau kembali kode yang telah diberi petunjuk pada setiap peningkatan baru. Dengan memaksakan perilaku, Anda mungkin menghalangi beban kerja Anda untuk 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

  1. Di SQL Server Management Studio, buka Object Explorer dan sambungkan ke Mesin Database.

  2. Untuk database yang dimaksudkan untuk meningkatkan tingkat kompatibilitas database, klik kanan nama database, pilih Tugas, pilih Peningkatan Database, dan pilih Sesi Peningkatan Database Baru.

  3. 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 akan digunakan untuk mengusulkan 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.

      Jendela penyetelan sesi peningkatan database baru

    2. Di jendela Pengaturan , dua kolom memperlihatkan status Penyimpanan Kueri saat ini di database yang ditargetkan, serta 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 beban kerja yang diharapkan, dalam hitungan hari. Ini karena Query Store perlu menyimpan informasi tentang beban kerja garis besar dan beban kerja peningkatan pasca-database. Setelah selesai, pilih Berikutnya.

      Jendela pengaturan peningkatan database baru

      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 dasar dan setelah peningkatan database untuk beban kerja yang sangat intensif, yaitu ketika banyak rencana yang berbeda dapat dihasilkan. Jika Anda mengantisipasi hal ini akan terjadi, masukkan nilai yang lebih tinggi yang sesuai.

  4. Jendela Penyetelan menyimpulkan konfigurasi sesi, dan menginstruksikan langkah berikutnya untuk membuka dan melanjutkan sesi. Setelah selesai, pilih Selesai.

    Jendela penyetelan peningkatan database baru

Menjalankan alur kerja peningkatan database

  1. Untuk database yang dimaksudkan untuk meningkatkan tingkat kompatibilitas database, klik kanan nama database, pilih Tugas, pilih Peningkatan Database, dan pilih Pantau Sesi.

  2. 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.

    Halaman Manajemen Sesi QTA

    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 rencana menggunakan tabel sistem sys.plan_guides, dan hapus secara manual menggunakan sp_control_plan_guide.

  3. 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 beban kerja selesai, centang Selesai dengan beban kerja dan pilih Berikutnya.

      Catatan

      Jendela QTA dapat ditutup ketika tugas berjalan. Kembali ke sesi yang tetap dalam status aktif di lain waktu akan dilanjutkan dari langkah yang sama di mana sesi tersebut ditinggalkan.

      QTA Langkah 2 Sub-langkah 1

    2. Upgrade Database akan meminta izin untuk meningkatkan tingkat kompatibilitas database ke target yang diinginkan. Untuk melanjutkan ke substep berikutnya, pilih Ya.

      QTA Langkah 2 Substep 2 - Tingkatkan tingkat kompatibilitas database

      Halaman berikut mengonfirmasi bahwa tingkat kompatibilitas database berhasil dimutakhirkan.

      QTA Langkah 2 Sub-langkah 2

    3. Pengumpulan Data yang Diamati meminta pengguna untuk menjalankan siklus beban kerja representatif lagi, sehingga Query Store dapat mengumpulkan patokan perbandingan 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 Selesai menjalankan beban kerja dan pilih Berikutnya.

      QTA Langkah 2 Subtahap 3

      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.

  4. 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.

    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.

    QTA Langkah 3

  5. 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 mengklik 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: 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.

    QTA Langkah 4

  6. 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.

    QTA Langkah 5

    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.

    QTA Langkah 5 - Putar Kembali

    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. Sebaliknya, cari panduan paket menggunakan tabel sistem sys.plan_guides, dan hapus secara manual menggunakan sp_control_plan_guide.

Izin

Memerlukan keanggotaan peran db_owner.

Lihat juga