Umpan balik pemberian memori

Berlaku untuk: SQL Server 2017 (14.x) dan yang lebih baru, Azure SQL Managed Instance, Azure SQL Database

Terkadang kueri dijalankan dengan peruntukan memori yang terlalu besar atau terlalu kecil. Jika pemberian memori terlalu besar, kami menghambat paralelisme di server. Jika terlalu kecil, kita mungkin meluap ke disk, yang merupakan operasi yang mahal. Umpan balik pemberian memori mencoba mengingat kebutuhan memori dari eksekusi sebelumnya (dengan umpan balik persentil, beberapa eksekusi sebelumnya). Berdasarkan informasi kueri historis ini, umpan balik pemberian memori menyesuaikan pemberian yang diberikan ke kueri yang sesuai untuk eksekusi berikutnya.

Fitur ini telah dirilis dalam tiga gelombang. Umpan balik pemberian memori mode batch, diikuti dengan umpan balik pemberian memori mode baris, dan SQL Server 2022 (16.x) memperkenalkan umpan balik pemberian memori pada persistensi disk menggunakan Penyimpanan Kueri, dan algoritma yang ditingkatkan yang dikenal sebagai pemberian persentil.

Catatan

Untuk fitur umpan balik kueri lainnya, lihat Umpan balik estimasi kardinalitas (CE) dan umpan balik Tingkat paralelisme (DOP).

Umpan balik pemberian memori mode batch

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2017 (14.x)), Azure SQL Database

Rencana eksekusi kueri mencakup memori minimum yang diperlukan untuk eksekusi dan ukuran pemberian memori yang ideal agar semua baris pas dalam memori. Performa menderita ketika ukuran peruntukan memori salah berukuran. Pemberian hibah yang berlebihan mengakibatkan memori yang terbuang dan mengurangi konkurensi. Pemberian memori yang tidak mencukupi menyebabkan tumpahan yang mahal ke disk. Dengan mengatasi beban kerja berulang, umpan balik pemberian memori mode batch menghitung ulang memori aktual yang diperlukan untuk kueri lalu memperbarui nilai pemberian untuk rencana yang di-cache. Ketika pernyataan kueri yang identik dijalankan, kueri menggunakan ukuran pemberian memori yang direvisi, mengurangi pemberian memori berlebihan yang berdampak pada konkurensi dan memperbaiki hibah memori yang diremehkan yang menyebabkan tumpahan mahal ke disk.

Grafik berikut menunjukkan salah satu contoh penggunaan umpan balik pemberian memori adaptif mode batch. Untuk eksekusi pertama kueri, durasinya adalah 88 detik karena tumpahan tinggi:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Dengan umpan balik pemberian memori diaktifkan, untuk eksekusi kedua, durasinya adalah 1 detik (turun dari 88 detik), tumpahan dihapus sepenuhnya, dan pemberiannya lebih tinggi:

Ukuran umpan balik peruntukan memori

Untuk kondisi pemberian memori yang berlebihan, jika memori yang diberikan lebih dari dua kali ukuran memori yang digunakan aktual, umpan balik pemberian memori akan menghitung ulang pemberian memori dan memperbarui rencana cache. Paket dengan pemberian memori di bawah 1 MB tidak akan dihitung ulang untuk kelebihan penggunaan.

Untuk kondisi pemberian memori berukuran tidak mencukupi yang mengakibatkan tumpahan ke disk untuk operator mode batch, umpan balik pemberian memori akan memicu perhitungan ulang pemberian memori. Peristiwa tumpahan dilaporkan ke umpan balik pemberian memori dan dapat muncul melalui peristiwa yang spilling_report_to_memory_grant_feedback diperluas. Kejadian ini mengembalikan ID simpul dari paket dan ukuran data yang ditumpahkan dari simpul tersebut.

Pemberian memori yang disesuaikan muncul dalam rencana aktual (pasca-eksekusi) melalui GrantedMemory properti .

Anda dapat melihat properti ini di operator akar showplan grafis atau dalam output XML showplan:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Agar beban kerja Anda memenuhi syarat secara otomatis untuk peningkatan ini, aktifkan tingkat kompatibilitas 140 untuk database.

Contoh:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Umpan balik pemberian memori dan skenario sensitif parameter

Nilai parameter yang berbeda mungkin juga memerlukan rencana kueri yang berbeda agar tetap optimal. Jenis kueri ini didefinisikan sebagai "peka parameter."

Untuk paket sensitif parameter, umpan balik pemberian memori akan menonaktifkan dirinya sendiri pada kueri jika memiliki persyaratan memori yang tidak stabil. Fitur umpan balik peruntukan memori dinonaktifkan setelah beberapa eksekusi kueri berulang dan ini dapat diamati dengan memantau peristiwa yang memory_grant_feedback_loop_disabled diperluas. Kondisi ini dimitigasi dengan mode persistensi dan persentil untuk umpan balik pemberian memori yang diperkenalkan di SQL Server 2022 (16.x). Fitur persistensi umpan balik peruntukan memori mengharuskan Penyimpanan Kueri diaktifkan dalam database dan diatur ke mode "baca tulis".

Untuk informasi selengkapnya tentang sniffing parameter dan sensitivitas parameter, lihat Panduan Arsitektur Pemrosesan Kueri.

Penembolokan umpan balik peruntukan memori

Umpan balik dapat disimpan dalam paket cache untuk satu eksekusi. Namun, ini adalah eksekusi berturut-turut dari pernyataan itu, yang mendapat manfaat dari penyesuaian umpan balik pemberian memori. Fitur ini berlaku untuk eksekusi pernyataan berulang. Umpan balik pemberian memori hanya akan mengubah paket yang di-cache. Sebelum SQL Server 2022 (16.x), perubahan tidak diambil di Penyimpanan Kueri.

Umpan balik tidak dipertahankan jika paket dikeluarkan dari cache. Umpan balik juga akan hilang jika ada failover. Pernyataan menggunakan OPTION (RECOMPILE) membuat paket baru dan tidak menyimpannya di cache. Karena tidak di-cache, tidak ada umpan balik pemberian memori yang dihasilkan, dan tidak disimpan untuk kompilasi dan eksekusi tersebut. Namun, jika pernyataan yang setara (yaitu, dengan hash kueri yang sama) yang tidak digunakan OPTION (RECOMPILE) di-cache dan kemudian dijalankan kembali, eksekusi kedua dan yang lebih baru berturut-turut dapat memperoleh manfaat dari umpan balik pemberian memori.

Melacak aktivitas umpan balik pemberian memori

Anda dapat melacak peristiwa umpan balik pemberian memori menggunakan peristiwa yang memory_grant_updated_by_feedback diperluas. Kejadian ini melacak riwayat jumlah eksekusi saat ini, berapa kali rencana telah diperbarui oleh umpan balik pemberian memori, pemberian memori tambahan yang ideal sebelum modifikasi dan pemberian memori tambahan yang ideal setelah umpan balik pemberian memori telah memodifikasi rencana cache.

Umpan balik pemberian memori, gubernur sumber daya, dan petunjuk kueri

Memori aktual yang diberikan mematuhi batas memori kueri yang ditentukan oleh gubernur sumber daya atau petunjuk kueri.

Menonaktifkan umpan balik pemberian memori mode batch tanpa mengubah tingkat kompatibilitas

Umpan balik pemberian memori dapat dinonaktifkan di cakupan database atau pernyataan sambil tetap mempertahankan tingkat kompatibilitas database 140 dan yang lebih tinggi. Untuk menonaktifkan umpan balik pemberian memori mode batch untuk semua eksekusi kueri yang berasal dari database, jalankan pernyataan SQL di bawah ini dalam konteks database yang berlaku:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Saat diaktifkan, pengaturan ini akan muncul sebagai diaktifkan di sys.database_scoped_configurations.

Untuk mengaktifkan kembali umpan balik pemberian memori mode batch untuk semua eksekusi kueri yang berasal dari database, jalankan pernyataan SQL dalam konteks database yang berlaku:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Anda juga dapat menonaktifkan umpan balik pemberian memori mode batch untuk kueri tertentu dengan menunjuk DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK sebagai petunjuk kueri USE HINT. Misalnya:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Petunjuk kueri USE HINT lebih diutamakan daripada konfigurasi cakupan database atau pengaturan bendera pelacakan.

Umpan balik pemberian memori mode baris

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2019 (15.x)), Azure SQL Database

Umpan balik pemberian memori mode baris diperluas pada fitur umpan balik peruntukan memori mode batch dengan menyesuaikan ukuran pemberian memori untuk operator mode batch dan baris.

Untuk mengaktifkan memori mode baris memberikan umpan balik di Azure SQL Database, aktifkan tingkat kompatibilitas database 150 atau lebih tinggi untuk database yang anda sambungkan saat menjalankan kueri.

Contoh:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Seperti halnya memori mode batch memberikan umpan balik, aktivitas umpan balik peruntukan memori mode baris terlihat melalui memory_grant_updated_by_feedback XEvent. Kami juga memperkenalkan dua atribut rencana eksekusi kueri baru untuk visibilitas yang lebih baik ke dalam status operasi umpan balik pemberian memori saat ini untuk mode baris dan batch.

Umpan balik pemberian memori tidak memerlukan Penyimpanan Kueri, namun, peningkatan persistensi yang diperkenalkan di SQL Server 2022 (16.x) mengharuskan Penyimpanan Kueri diaktifkan untuk database dan dalam status "baca tulis". Untuk informasi selengkapnya tentang persistensi, lihat Umpan balik pemberian memori mode persentil dan persistensi nanti di artikel ini.

Aktivitas umpan balik pemberian memori mode baris terlihat melalui peristiwa yang memory_grant_updated_by_feedback diperluas.

Dimulai dengan umpan balik pemberian memori mode baris, dua atribut rencana kueri baru ditampilkan untuk rencana pasca-eksekusi aktual: IsMemoryGrantFeedbackAdjusted dan LastRequestedMemory, yang ditambahkan ke MemoryGrantInfo elemen XML rencana kueri.

• Atribut LastRequestedMemory menunjukkan memori yang diberikan dalam Kilobyte (KB) dari eksekusi kueri sebelumnya.
• Atribut ini IsMemoryGrantFeedbackAdjusted memungkinkan Anda memeriksa status umpan balik pemberian memori untuk pernyataan dalam rencana eksekusi kueri yang sebenarnya.

Nilai yang muncul dalam atribut ini adalah sebagai berikut:

IsMemoryGrantFeedbackAdjusted Nilai	Deskripsi
Tidak: Eksekusi Pertama	Umpan balik pemberian memori tidak menyesuaikan memori untuk kompilasi pertama dan eksekusi terkait.
Tidak: Pemberian Akurat	Jika tidak ada tumpahan ke disk dan pernyataan menggunakan setidaknya 50% dari memori yang diberikan, umpan balik pemberian memori tidak dipicu.
Tidak: Umpan balik dinonaktifkan	Jika umpan balik pemberian memori terus dipicu dan berfluktuasi antara operasi peningkatan memori dan penurunan memori, mesin database akan menonaktifkan umpan balik pemberian memori untuk pernyataan tersebut.
Ya: Menyesuaikan	Umpan balik pemberian memori telah diterapkan dan mungkin disesuaikan lebih lanjut untuk eksekusi berikutnya.
Ya: Penyesuaian Persentil	Umpan balik pemberian memori sedang diterapkan menggunakan algoritma pemberian persentil, yang melihat lebih banyak riwayat daripada hanya eksekusi terbaru.
Ya: Stabil	Umpan balik pemberian memori telah diterapkan dan memori yang diberikan sekarang stabil, yang berarti bahwa apa yang terakhir diberikan untuk eksekusi sebelumnya adalah apa yang diberikan untuk eksekusi saat ini.

Persentil dan persistensi mode memori memberikan umpan balik

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2022 (16.x)), Azure SQL Database

Fitur ini diperkenalkan di SQL Server 2022 (16.x), namun peningkatan performa ini tersedia untuk kueri yang beroperasi di tingkat kompatibilitas database 140 (diperkenalkan di SQL Server 2017) atau yang lebih tinggi, atau QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n petunjuk 140 dan yang lebih tinggi, dan ketika Penyimpanan Kueri diaktifkan untuk database dan berada dalam status "baca tulis".

• Umpan balik pemberian memori persentil diaktifkan secara default di SQL Server 2022 (16.x), tetapi tidak berpengaruh jika Penyimpanan Kueri tidak diaktifkan atau ketika Penyimpanan Kueri tidak dalam status "baca tulis".
• Persistensi untuk pemberian memori, CE, dan umpan balik DOP aktif secara default di SQL Server 2022 (16.x), tetapi tidak berpengaruh ketika Penyimpanan Kueri tidak diaktifkan atau ketika Penyimpanan Kueri tidak dalam status "baca tulis".
• Persentil dan persistensi untuk umpan balik pemberian memori tersedia di Azure SQL Database, dan diaktifkan secara default pada semua database, baik yang sudah ada maupun yang baru.
• Persentil dan persistensi untuk umpan balik pemberian memori saat ini tidak tersedia di Azure SQL Managed Instance.

Disarankan agar Anda memiliki garis besar performa untuk beban kerja Anda sebelum fitur diaktifkan untuk database Anda. Nomor dasar akan membantu Anda menentukan apakah Anda mendapatkan manfaat yang dimaksudkan dari fitur tersebut.

Umpan balik peruntukan memori (MGF) adalah fitur yang ada yang menyesuaikan ukuran memori yang dialokasikan untuk kueri berdasarkan performa sebelumnya. Namun, fase awal proyek ini hanya menyimpan penyesuaian pemberian memori dengan rencana dalam cache - jika rencana dikeluarkan dari cache, proses umpan balik harus dimulai lagi, sehingga menghasilkan performa yang buruk beberapa kali pertama kueri dijalankan setelah pengeluaran. Solusi baru adalah mempertahankan informasi pemberian dengan informasi kueri lain di Penyimpanan Kueri sehingga manfaatnya bertahan di seluruh pengeluaran cache. Persistensi umpan balik pemberian memori dan alamat persentil batasan umpan balik pemberian memori yang ada dengan cara yang tidak mengganggu.

Selain itu, penyesuaian ukuran pemberian hanya diperhitungkan untuk pemberian yang terakhir digunakan. Jadi, jika kueri atau beban kerja berparameter memerlukan ukuran pemberian memori yang bervariasi secara signifikan dengan setiap eksekusi, informasi pemberian terbaru bisa tidak akurat. Ini bisa di luar langkah dengan kebutuhan aktual kueri yang dijalankan. Umpan balik pemberian memori dalam skenario ini tidak membantu performa karena kami selalu menyesuaikan memori berdasarkan nilai hibah yang terakhir digunakan. Gambar berikutnya menunjukkan perilaku yang mungkin dengan umpan balik pemberian memori tanpa mode persentil dan persistensi.

Seperti yang Anda lihat, dalam perilaku kueri yang tidak biasa tetapi mungkin ini, osilasi antara jumlah memori yang diperlukan dan diberikan aktual menghasilkan memori yang sia-sia dan tidak cukup jika eksekusi kueri itu sendiri bergantian dalam hal jumlah memori. Dalam skenario ini, umpan balik pemberian memori menonaktifkan dirinya sendiri, mengenalinya melakukan lebih banyak bahaya daripada baik.

Menggunakan perhitungan berbasis persentil atas riwayat kueri baru-baru ini, alih-alih hanya eksekusi terakhir, kita dapat memuluskan nilai ukuran pemberian berdasarkan riwayat penggunaan eksekusi sebelumnya dan mencoba mengoptimalkan untuk meminimalkan tumpahan. Misalnya, beban kerja alternatif yang sama akan melihat perilaku pemberian memori berikut:

Pengoptimal kueri menggunakan persentil tinggi dari persyaratan ukuran pemberian memori masa lalu untuk eksekusi rencana yang di-cache untuk menghitung ukuran pemberian memori, menggunakan data yang bertahan di Penyimpanan Kueri. Penyesuaian persentil, yang akan melakukan penyesuaian pemberian memori didasarkan pada riwayat eksekusi terbaru. Seiring waktu, pemberian memori yang diberikan mengurangi tumpahan dan memori yang terbuang.

Persistensi juga berlaku untuk umpan balik DOP dan umpan balik CE.

Mengaktifkan dan menonaktifkan fitur umpan balik pemberian memori

Menonaktifkan umpan balik pemberian memori mode baris tanpa mengubah tingkat kompatibilitas

Umpan balik pemberian memori mode baris dapat dinonaktifkan di lingkup database atau pernyataan sambil tetap mempertahankan kompatibilitas database tingkat 150 dan yang lebih tinggi. Untuk menonaktifkan memori mode baris memberikan umpan balik untuk semua eksekusi kueri yang berasal dari database, jalankan pernyataan SQL dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Untuk mengaktifkan kembali memori mode baris memberikan umpan balik untuk semua eksekusi kueri yang berasal dari database, jalankan hal berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Anda juga dapat menonaktifkan umpan balik pemberian memori mode baris untuk kueri tertentu dengan menunjuk DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK sebagai petunjuk kueri USE HINT. Misalnya:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Petunjuk kueri USE HINT lebih diutamakan daripada konfigurasi cakupan database atau pengaturan bendera pelacakan.

Mengaktifkan persistensi dan persentil umpan balik peruntukan memori

Umpan balik persistensi dan persentil diaktifkan secara default di Azure SQL Database dan SQL Server 2022 (16.x).

Gunakan tingkat kompatibilitas database 140 atau lebih tinggi untuk database yang tersambung dengan Anda saat menjalankan kueri. Anda dapat mengubahnya melalui ALTER DATABASE:

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

Penyimpanan Kueri harus diaktifkan untuk setiap database tempat bagian persistensi fitur ini digunakan.

Menonaktifkan persentil

Untuk menonaktifkan persentil umpan balik pemberian memori untuk semua eksekusi kueri yang berasal dari database, jalankan hal berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

Pengaturan default untuk MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT adalah ON.

Menonaktifkan persistensi

Untuk menonaktifkan persistensi umpan balik pemberian memori untuk semua eksekusi kueri yang berasal dari database.

Jalankan hal berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

Menonaktifkan persistensi umpan balik pemberian memori juga akan menghapus umpan balik yang dikumpulkan yang ada.

Pengaturan default untuk MEMORY_GRANT_FEEDBACK_PERSISTENCE adalah ON.

Pertimbangan untuk umpan balik pemberian memori

Anda bisa menampilkan pengaturan Anda saat ini dengan mengkueri sys.database_scoped_configurations.

Catatan

Fitur ini tidak akan berfungsi jika keduanya BATCH_MODE_MEMORY_GRANT_FEEDBACK dan ROW_MODE_MEMORY_GRANT_FEEDBACK diatur ke OFF.

Data umpan balik yang diberikan sekarang disimpan di Penyimpanan Kueri, ada beberapa peningkatan dalam persyaratan penggunaan Penyimpanan Kueri.

Err pemberian memori berbasis persentil di sisi mengurangi tumpahan. Karena tidak lagi didasarkan pada eksekusi terakhir saja tetapi pada pengamatan beberapa eksekusi sebelumnya, ini dapat meningkatkan penggunaan memori untuk mengosilasikan beban kerja dengan varians luas dalam persyaratan pemberian memori antara eksekusi.

Dimulai dengan SQL Server 2022 (16.x), ketika Penyimpanan Kueri untuk replika sekunder diaktifkan, umpan balik pemberian memori sadar akan replika untuk replika sekunder dalam grup ketersediaan. Umpan balik pemberian memori dapat menerapkan umpan balik secara berbeda pada replika utama dan pada replika sekunder. Namun, umpan balik pemberian memori tidak bertahan pada replika sekunder, dan saat failover, umpan balik pemberian memori dari replika utama lama diterapkan ke replika utama baru. Setiap umpan balik yang diterapkan ke replika sekunder ketika menjadi replika utama hilang. Untuk informasi selengkapnya, lihat Penyimpanan Kueri untuk replika sekunder.

Konten terkait

• Pemrosesan kueri cerdas dalam database SQL
• Fitur pemrosesan kueri cerdas secara rinci
• KONFIGURASI ULANG (Transact-SQL)
• Monitor dan Selaraskan Kinerja
• ALTER DATABASE SCOPED CONFIGURATION (T-SQL)