Penyetelan otomatis

  • Artikel

Berlaku untuk: SQL Server 2017 (14.x) dan azure SQL DatabaseAzure SQL Managed Instance yang lebih baru

Penyetelan otomatis adalah fitur database yang memberikan wawasan tentang potensi masalah performa kueri, merekomendasikan solusi, dan secara otomatis memperbaiki masalah yang diidentifikasi.

Penyetelan otomatis, diperkenalkan di SQL Server 2017 (14.x), memberi tahu Anda setiap kali potensi masalah performa terdeteksi dan memungkinkan Anda menerapkan tindakan korektif, atau memungkinkan Mesin Database secara otomatis memperbaiki masalah performa. Penyetelan otomatis SQL Server mengidentifikasi dan memperbaiki masalah performa yang disebabkan oleh regresi pilihan rencana eksekusi kueri. Penyetelan otomatis di Azure SQL Database juga membuat indeks yang diperlukan dan menghilangkan indeks yang tidak digunakan. Untuk informasi selengkapnya tentang rencana eksekusi kueri, lihat Rencana Eksekusi.

Mesin Database SQL Server memantau kueri yang dijalankan pada database dan secara otomatis meningkatkan performa beban kerja. Mesin Database memiliki mekanisme kecerdasan bawaan yang dapat secara otomatis menyetel dan meningkatkan performa kueri Anda dengan menyesuaikan database secara dinamis dengan beban kerja Anda. Ada dua fitur penyetelan otomatis yang tersedia:

  • Koreksi rencana otomatis mengidentifikasi rencana eksekusi kueri yang bermasalah, seperti masalah sensitivitas parameter atau sniffing parameter, dan memperbaiki masalah performa terkait rencana eksekusi kueri dengan memaksa rencana baik terakhir yang diketahui sebelum regresi terjadi. Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)) dan Azure SQL Database dan Azure SQL Managed Instance]

  • Manajemen indeks otomatis mengidentifikasi indeks yang harus ditambahkan dalam database Anda, dan indeks yang harus dihapus. Berlaku untuk: Azure SQL Database

Mengapa penyetelan otomatis?

Tiga tugas utama dalam administrasi database klasik adalah memantau beban kerja, mengidentifikasi kueri Transact-SQL penting, dan mengidentifikasi indeks yang harus ditambahkan untuk meningkatkan performa, atau indeks yang jarang digunakan dan dapat dihapus untuk meningkatkan performa. Mesin Database SQL Server memberikan wawasan terperinci tentang kueri dan indeks yang perlu Anda pantau. Namun, terus memantau database adalah tugas yang sulit dan melelahkan, terutama saat berhadapan dengan banyak database. Mengelola sejumlah besar database mungkin tidak mungkin dilakukan secara efisien. Alih-alih memantau dan menyetel database Anda secara manual, Anda mungkin mempertimbangkan untuk mendelegasikan beberapa tindakan pemantauan dan penyetelan ke Mesin Database menggunakan fitur penyetelan otomatis.

Bagaimana cara kerja penyetelan otomatis?

Penyetelan otomatis adalah proses pemantauan dan analisis berkelanjutan yang terus-menerus mempelajari karakteristik beban kerja Anda dan mengidentifikasi potensi masalah dan peningkatan.

Automatic tuning process.

Proses ini memungkinkan database untuk beradaptasi secara dinamis dengan beban kerja Anda dengan menemukan indeks dan rencana apa yang dapat meningkatkan performa beban kerja Anda dan indeks apa yang memengaruhi beban kerja Anda. Berdasarkan temuan ini, penyetelan otomatis menerapkan tindakan penyetelan yang meningkatkan performa beban kerja Anda. Selain itu, penyetelan otomatis terus memantau performa database setelah menerapkan perubahan apa pun untuk memastikan bahwa itu meningkatkan performa beban kerja Anda. Tindakan apa pun yang tidak meningkatkan performa secara otomatis dikembalikan. Proses verifikasi ini adalah fitur utama yang memastikan setiap perubahan yang dilakukan oleh penyetelan otomatis tidak mengurangi performa keseluruhan beban kerja Anda.

Koreksi paket otomatis

Koreksi paket otomatis adalah fitur penyetelan otomatis yang mengidentifikasi regresi pilihan rencana eksekusi dan secara otomatis memperbaiki masalah dengan memaksa rencana baik terakhir yang diketahui. Untuk informasi selengkapnya tentang rencana eksekusi kueri dan Pengoptimal Kueri, lihat Panduan Arsitektur Pemrosesan Kueri.

Penting

Koreksi paket otomatis bergantung pada Penyimpanan Kueri yang diaktifkan dalam database untuk pelacakan beban kerja.

Apa itu regresi pilihan rencana eksekusi?

Mesin Database SQL Server dapat menggunakan rencana eksekusi yang berbeda untuk menjalankan kueri Transact-SQL. Rencana kueri bergantung pada statistik, indeks, dan faktor lainnya. Rencana optimal yang harus digunakan untuk menjalankan kueri Transact-SQL mungkin berubah dari waktu ke waktu tergantung pada perubahan faktor-faktor ini. Dalam beberapa kasus, rencana baru mungkin tidak lebih baik dari yang sebelumnya, dan rencana baru dapat menyebabkan regresi performa, seperti sensitivitas parameter atau masalah terkait sniffing parameter.

Query execution plan choice regression.

Setiap kali Anda melihat regresi pilihan rencana telah terjadi, Anda harus menemukan rencana yang baik sebelumnya dan memaksanya untuk digunakan alih-alih yang saat ini. Ini dapat dilakukan dengan menggunakan sp_query_store_force_plan prosedur . Mesin Database di SQL Server 2017 (14.x) menyediakan informasi tentang rencana yang diregresif dan tindakan korektif yang direkomendasikan. Selain itu, Mesin Database memungkinkan Anda untuk sepenuhnya mengotomatiskan proses ini dan membiarkan Mesin Database memperbaiki masalah apa pun yang ditemukan terkait dengan perubahan rencana.

Penting

Koreksi paket otomatis harus digunakan dalam cakupan peningkatan tingkat kompatibilitas database, setelah garis besar ditangkap, untuk secara otomatis mengurangi risiko peningkatan beban kerja. Untuk informasi selengkapnya tentang kasus penggunaan ini, lihat Menjaga stabilitas performa selama peningkatan ke SQL Server yang lebih baru.

Koreksi pilihan paket otomatis

Mesin Database dapat secara otomatis beralih ke paket baik terakhir yang diketahui setiap kali regresi pilihan paket terdeteksi.

Query execution plan choice correction.

Mesin Database secara otomatis mendeteksi potensi regresi pilihan rencana, termasuk rencana yang harus digunakan alih-alih rencana yang salah. Rencana eksekusi yang dihasilkan yang dipaksa oleh koreksi rencana otomatis akan sama atau mirip dengan rencana baik terakhir yang diketahui. Karena rencana yang dihasilkan mungkin tidak identik dengan rencana baik terakhir yang diketahui, performa rencana paksa dapat bervariasi. Dalam kasus yang jarang terjadi, perbedaan performa mungkin signifikan dan negatif; dalam hal ini, koreksi rencana otomatis akan secara otomatis berhenti mencoba memaksa rencana penggantian.

Ketika Mesin Database menerapkan rencana baik terakhir yang diketahui sebelum regresi terjadi, mesin database secara otomatis memantau performa rencana paksa. Jika rencana paksa tidak lebih baik dari rencana yang diregresi, rencana baru akan tidak diberlakukan dan Mesin Database akan mengkompilasi rencana baru. Jika Mesin Database memverifikasi bahwa rencana paksa lebih baik daripada rencana yang diregresi, rencana paksa akan dipertahankan. Ini akan dipertahankan sampai kompilasi ulang terjadi (misalnya, pada pembaruan statistik atau perubahan skema berikutnya). Untuk informasi selengkapnya tentang memaksa rencana dan jenis paket yang dapat dipaksakan, lihat Batasan paksa paket.

Catatan

Jika instans SQL Server dimulai ulang sebelum tindakan memaksa rencana diverifikasi, paket tersebut akan secara otomatis tidak diberlakukan. Jika tidak, memaksa paket tetap ada pada mulai ulang SQL Server.

Mengaktifkan koreksi pilihan paket otomatis

Anda dapat mengaktifkan penyetelan otomatis per database dan menentukan bahwa rencana baik terakhir harus dipaksa setiap kali beberapa regresi perubahan rencana terdeteksi. Penyetelan otomatis diaktifkan menggunakan perintah berikut:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Setelah Anda mengaktifkan opsi ini, Mesin Database akan secara otomatis memaksa rekomendasi apa pun di mana perkiraan perolehan CPU lebih tinggi dari 10 detik, atau jumlah kesalahan dalam paket baru lebih tinggi daripada jumlah kesalahan dalam paket yang direkomendasikan, dan memverifikasi bahwa paket paksa lebih baik daripada yang saat ini.

Untuk mengaktifkan penyetelan otomatis di Azure SQL Database dan Azure SQL Managed Instance, lihat Mengaktifkan penyetelan otomatis di Azure SQL Database menggunakan portal Microsoft Azure.

Alternatif - koreksi pilihan paket manual

Tanpa penyetelan otomatis, pengguna harus secara berkala memantau sistem dan mencari kueri yang telah mengalami kemunculan ulang. Jika ada paket yang mengalami kemunculan kembali, pengguna harus menemukan rencana yang baik sebelumnya dan memaksanya alih-alih paket saat ini dengan menggunakan sp_query_store_force_plan prosedur. Praktik terbaik adalah memaksa rencana baik terakhir yang diketahui karena rencana yang lebih lama mungkin tidak valid karena statistik atau perubahan indeks. Pengguna yang memaksa rencana baik terakhir yang diketahui harus memantau performa kueri yang dijalankan menggunakan rencana paksa dan memverifikasi bahwa rencana paksa berfungsi seperti yang diharapkan. Bergantung pada hasil pemantauan dan analisis, rencana harus dipaksakan atau pengguna harus menemukan cara lain untuk mengoptimalkan kueri, seperti menulis ulang. Rencana yang dipaksakan secara manual tidak boleh dipaksa selamanya, karena Mesin Database harus dapat menerapkan rencana yang optimal. Pengguna atau DBA pada akhirnya harus membatalkan rencana menggunakan sp_query_store_unforce_plan prosedur, dan membiarkan Mesin Database menemukan rencana optimal.

Tip

Atau, gunakan tampilan Kueri Paket Paksa untuk menemukan dan membatalkan rencana.

SQL Server menyediakan semua tampilan dan prosedur yang diperlukan untuk memantau performa dan memperbaiki masalah di Penyimpanan Kueri.

Di SQL Server 2016 (13.x), Anda dapat menemukan regresi pilihan paket menggunakan tampilan sistem Penyimpanan Kueri. Dimulai dengan SQL Server 2017 (14.x), Mesin Database mendeteksi dan menunjukkan potensi regresi pilihan rencana dan tindakan yang direkomendasikan yang harus diterapkan di DMV sys.dm_db_tuning_recommendations (Transact-SQL). DMV menunjukkan informasi tentang masalah, pentingnya masalah, dan detail seperti kueri yang diidentifikasi, ID rencana yang diregresi, ID paket yang digunakan sebagai garis besar untuk perbandingan, dan pernyataan Transact-SQL yang dapat dijalankan untuk memperbaiki masalah.

jenis description datetime skor detail ...
FORCE_LAST_GOOD_PLAN Waktu CPU berubah dari 4 mdtk menjadi 14 mdtk 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Waktu CPU berubah dari 37 ms menjadi 84 mdtk 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Beberapa kolom dari tampilan ini dijelaskan dalam daftar berikut:

  • Jenis tindakan FORCE_LAST_GOOD_PLANyang direkomendasikan .
  • Deskripsi yang berisi informasi mengapa Mesin Database berpikir bahwa perubahan rencana ini adalah potensi regresi performa.
  • Tanggalwaktu ketika potensi regresi terdeteksi.
  • Skor rekomendasi ini.
  • Detail tentang masalah seperti ID rencana yang terdeteksi, ID rencana yang diregresi, ID paket yang harus dipaksa untuk memperbaiki masalah, skrip Transact-SQL yang mungkin diterapkan untuk memperbaiki masalah, dll. Detail disimpan dalam format JSON.

Gunakan kueri berikut untuk mendapatkan skrip yang memperbaiki masalah dan informasi tambahan tentang perkiraan perolehan:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Berikut adalah hasil yang ditetapkan.

reason skor skrip query_id plan_id saat ini plan_id yang direkomendasikan estimated_gain error_prone
Waktu CPU berubah dari 3 mdtk menjadi 46 mdtk 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

Kolom estimated_gain mewakili perkiraan jumlah detik yang akan disimpan jika paket yang direkomendasikan akan digunakan untuk eksekusi kueri alih-alih paket saat ini. Rencana yang direkomendasikan harus dipaksakan alih-alih rencana saat ini jika perolehan lebih besar dari 10 detik. Jika ada lebih banyak kesalahan (misalnya, waktu habis atau eksekusi yang dibatalkan) dalam paket saat ini daripada dalam paket yang direkomendasikan, kolom error_prone akan diatur ke nilai YES. Rencana rawan kesalahan adalah alasan lain mengapa rencana yang direkomendasikan harus dipaksakan alih-alih paket saat ini.

Meskipun Mesin Database menyediakan semua informasi yang diperlukan untuk mengidentifikasi regresi pilihan rencana, pemantauan berkelanjutan dan memperbaiki masalah performa mungkin menjadi proses yang melelahkan. Penyetelan otomatis membuat proses ini jauh lebih mudah.

Catatan

Data di sys.dm_db_tuning_recommendations DMV tidak bertahan setelah menghidupkan ulang mesin database. sqlserver_start_time Gunakan kolom di sys.dm_os_sys_info untuk menemukan waktu mulai mesin database terakhir.

Manajemen indeks otomatis

Di Azure SQL Database, manajemen indeks mudah karena Azure SQL Database mempelajari tentang beban kerja Anda dan memastikan bahwa data Anda selalu diindeks secara optimal. Desain indeks yang tepat sangat penting untuk performa optimal beban kerja Anda, dan manajemen indeks otomatis dapat membantu Anda mengoptimalkan indeks Anda. Manajemen indeks otomatis dapat memperbaiki masalah performa dalam database yang salah diindeks, atau memelihara dan meningkatkan indeks pada skema database yang ada. Penyetelan otomatis di Azure SQL Database melakukan tindakan berikut:

  • Mengidentifikasi indeks yang dapat meningkatkan performa kueri Transact-SQL Anda yang membaca data dari tabel.
  • Mengidentifikasi indeks atau indeks berlebihan yang tidak digunakan dalam jangka waktu yang lebih lama yang dapat dihapus. Menghapus indeks yang tidak perlu meningkatkan performa kueri yang memperbarui data dalam tabel.

Mengapa Anda memerlukan manajemen indeks?

Indeks mempercepat beberapa kueri Anda yang membaca data dari tabel, namun mereka dapat memperlambat kueri yang memperbarui data. Anda perlu menganalisis dengan cermat kapan harus membuat indeks dan kolom apa yang perlu Anda sertakan dalam indeks. Beberapa indeks mungkin tidak diperlukan setelah beberapa waktu. Oleh karena itu, Anda perlu mengidentifikasi dan menghilangkan indeks ini secara berkala yang tidak membawa manfaat apa pun. Jika Anda mengabaikan indeks yang tidak digunakan, performa kueri yang memperbarui data akan berkurang tanpa manfaat apa pun untuk kueri yang membaca data. Indeks yang tidak digunakan juga memengaruhi performa keseluruhan sistem karena pembaruan tambahan memerlukan pengelogan yang tidak perlu.

Menemukan kumpulan indeks optimal yang meningkatkan performa kueri yang membaca data dari tabel Anda dan memiliki dampak minimal pada pembaruan mungkin memerlukan analisis berkelanjutan dan kompleks.

Azure SQL Database menggunakan kecerdasan bawaan dan aturan tingkat lanjut yang menganalisis kueri Anda, mengidentifikasi indeks yang akan optimal untuk beban kerja Anda saat ini, dan mengidentifikasi indeks yang mungkin perlu dihapus. Azure SQL Database memastikan bahwa Anda memiliki serangkaian indeks minimal yang diperlukan yang mengoptimalkan kueri yang membaca data, dengan dampak yang diminimalkan pada kueri lain.

Manajemen indeks otomatis

Selain deteksi, Azure SQL Database dapat secara otomatis menerapkan rekomendasi yang diidentifikasi. Jika Anda menemukan bahwa aturan bawaan meningkatkan performa database, Anda mungkin mengizinkan Azure SQL Database mengelola indeks Anda secara otomatis.

Saat Azure SQL Database menerapkan rekomendasi CREATE INDEX atau DROP INDEX, Azure SQL Database secara otomatis memantau performa kueri yang terpengaruh oleh indeks. Indeks baru akan dipertahankan hanya jika performa kueri yang terpengaruh ditingkatkan. Indeks yang dihilangkan akan dibuat ulang secara otomatis jika ada beberapa kueri yang berjalan lebih lambat karena tidak adanya indeks.

Pertimbangan manajemen indeks otomatis

Tindakan yang diperlukan untuk membuat indeks yang diperlukan di Azure SQL Database mungkin menggunakan sumber daya dan memengaruhi performa beban kerja secara sementara. Untuk meminimalkan dampak pembuatan indeks pada performa beban kerja, Azure SQL Database menemukan jendela waktu yang sesuai untuk setiap operasi manajemen indeks. Tindakan penyetelan ditunda jika database memerlukan sumber daya untuk menjalankan beban kerja Anda, dan dimulai ulang ketika database memiliki cukup sumber daya yang tidak digunakan yang dapat digunakan untuk tugas pemeliharaan. Salah satu fitur penting dalam manajemen indeks otomatis adalah verifikasi tindakan. Saat Azure SQL Database membuat atau menghilangkan indeks, proses pemantauan menganalisis performa beban kerja Anda untuk memverifikasi bahwa tindakan meningkatkan performa keseluruhan. Jika tidak membawa peningkatan signifikan - tindakan segera dikembalikan. Dengan cara ini, Azure SQL Database memastikan bahwa tindakan penyetelan otomatis tidak berdampak negatif pada performa beban kerja Anda. Indeks yang dibuat oleh penyetelan otomatis transparan untuk operasi pemeliharaan pada skema yang mendasar. Perubahan skema seperti menjatuhkan atau mengganti nama kolom tidak diblokir oleh adanya indeks yang dibuat secara otomatis. Indeks yang secara otomatis dibuat oleh Azure SQL Database segera dihilangkan saat tabel atau kolom terkait dihilangkan.

Alternatif - manajemen indeks manual

Tanpa manajemen indeks otomatis, pengguna atau DBA perlu mengkueri tampilan sys.dm_db_missing_index_details (Transact-SQL) secara manual atau menggunakan laporan Dasbor Performa di Management Studio untuk menemukan indeks yang mungkin meningkatkan performa, membuat indeks menggunakan detail yang disediakan dalam tampilan ini, dan memantau performa kueri secara manual. Untuk menemukan indeks yang harus dihilangkan, pengguna harus memantau statistik penggunaan operasional indeks untuk menemukan indeks yang jarang digunakan.

Azure SQL Database menyederhanakan proses ini. Azure SQL Database menganalisis beban kerja Anda, mengidentifikasi kueri yang dapat dijalankan lebih cepat dengan indeks baru, dan mengidentifikasi indeks yang tidak digunakan atau diduplikasi. Temukan informasi selengkapnya tentang identifikasi indeks yang harus diubah di Temukan rekomendasi indeks di portal Microsoft Azure.

Langkah berikutnya