Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Berlaku untuk:
SQL Server 2017 (14.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric
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, data mungkin harus disimpan ke disk, yang merupakan operasi berbiaya tinggi. Umpan balik alokasi memori mencoba mengingat kebutuhan memori dari eksekusi sebelumnya (dengan umpan balik persentil, dari beberapa eksekusi sebelumnya). Berdasarkan informasi riwayat kueri ini, umpan balik alokasi memori menyesuaikan alokasi yang diberikan kepada kueri tersebut untuk eksekusi selanjutnya.
Fitur ini telah dirilis dalam tiga gelombang. Umpan balik alokasi memori mode batch, yang kemudian diikuti oleh umpan balik alokasi memori mode baris, dan SQL Server 2022 (16.x) memperkenalkan persistensi di disk untuk umpan balik alokasi memori menggunakan Query Store, serta algoritma yang disempurnakan yang dikenal sebagai percentile grant.
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 2017 (14.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance (tingkat kompatibilitas database 140 dan yang lebih tinggi).
Rencana eksekusi kueri mencakup memori minimum yang diperlukan untuk eksekusi dan ukuran pemberian memori yang ideal agar semua baris pas dalam memori. Kinerja menurun ketika ukuran alokasi memori ditetapkan secara tidak tepat. 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 adaptif untuk alokasi memori dalam mode batch. Untuk eksekusi pertama kueri, durasinya adalah 88 detik karena tumpahan tinggi:
DECLARE @EndTime AS DATETIME = '2016-09-22 00:00:00.000';
DECLARE @StartTime AS 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 spill dilaporkan ke umpan balik memory grant dan dapat ditampilkan melalui extended event spilling_report_to_memory_grant_feedback. Peristiwa ini mengembalikan ID simpul dari rencana dan ukuran data luapan dari simpul tersebut.
Alokasi memori yang disesuaikan muncul dalam rencana aktual (setelah eksekusi) melalui properti GrantedMemory.
Anda dapat melihat properti ini pada operator akar dari showplan grafis atau pada 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 alokasi memori dan skenario yang sensitif terhadap parameter
Nilai parameter yang berbeda mungkin juga memerlukan rencana kueri yang berbeda agar tetap optimal. Jenis kueri ini didefinisikan sebagai "peka parameter."
Untuk rencana yang sensitif terhadap parameter, umpan balik alokasi memori akan menonaktifkan dirinya sendiri untuk kueri tersebut jika kueri tersebut memiliki kebutuhan memori yang tidak stabil. Fitur umpan balik pemberian memori dinonaktifkan setelah kueri dijalankan berulang kali beberapa kali, dan hal ini dapat diamati dengan memantau extended event memory_grant_feedback_loop_disabled. Kondisi ini diatasi dengan mode persistensi dan persentil untuk umpan balik pemberian memori yang diperkenalkan dalam SQL Server 2022 (16.x). Fitur persistensi untuk umpan balik peruntukan memori mengharuskan Query Store diaktifkan pada database dan diatur ke mode "baca-tulis".
Untuk informasi selengkapnya tentang parameter sniffing dan sensitivitas parameter, lihat Panduan Arsitektur Pemrosesan Kueri.
Penembolokan umpan balik peruntukan memori
Umpan balik dapat disimpan dalam rencana yang di-cache untuk satu kali eksekusi. Namun, eksekusi-eksekusi berturut-turut dari pernyataan tersebutlah yang memperoleh manfaat dari penyesuaian umpan balik alokasi memori. Fitur ini berlaku untuk eksekusi pernyataan berulang. Umpan balik alokasi memori hanya akan mengubah rencana yang di-cache. Sebelum SQL Server 2022 (16.x), perubahan tidak dicatat dalam Query Store.
Umpan balik tidak disimpan secara persisten jika rencana dikeluarkan dari cache. Umpan balik juga akan hilang jika ada failover. Pernyataan yang menggunakan OPTION (RECOMPILE) membuat rencana eksekusi baru dan tidak menyimpannya dalam 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 alokasi memori menggunakan extended event memory_grant_updated_by_feedback. 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 alokasi memori, pengatur 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 alokasi memori dapat dinonaktifkan pada tingkat database atau pernyataan dengan tetap mempertahankan tingkat kompatibilitas database 140 atau yang lebih tinggi. Untuk menonaktifkan fungsi umpan balik pemberian memori mode batch dari semua eksekusi kueri yang berasal dari database, jalankan pernyataan Transact-SQL di bawah ini dalam konteks database terkait.
Di SQL Server 2017 (14.x):
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;Di SQL Server 2019 (15.x) dan versi yang lebih baru, dan di 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 Transact-SQL dalam konteks database yang berlaku.
Di SQL Server 2017 (14.x):
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;Di SQL Server 2019 (15.x) dan versi yang lebih baru, dan di Azure SQL Database:
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. Contohnya:
SELECT *
FROM Person.Address
WHERE City = 'SEATTLE'
AND PostalCode = 98104
OPTION (USE HINT('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));
USE HINT Petunjuk kueri lebih diutamakan daripada konfigurasi terlingkup database atau pengaturan bendera pelacakan.
Umpan balik pemberian memori mode baris
Berlaku untuk: SQL Server 2019 (15.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance (tingkat kompatibilitas database 150 dan yang lebih tinggi).
Fitur umpan balik alokasi memori mode baris mengembangkan fitur umpan balik alokasi memori mode batch dengan menyesuaikan ukuran alokasi memori untuk operator mode batch maupun mode baris.
Untuk mengaktifkan umpan balik pemberian memori mode baris di Azure SQL Database, aktifkan tingkat kompatibilitas database 150 atau yang lebih tinggi untuk database yang Anda sambungkan ketika menjalankan kueri.
Contoh:
ALTER DATABASE [<database name>]
SET COMPATIBILITY_LEVEL = 150;
Seperti halnya umpan balik pemberian memori mode batch, aktivitas umpan balik pemberian memori mode baris dapat dilihat melalui memory_grant_updated_by_feedback XEvent. Kami juga memperkenalkan dua atribut baru pada rencana eksekusi kueri untuk visibilitas yang lebih baik terhadap status terkini operasi umpan balik pemberian memori, baik untuk mode baris maupun mode batch.
Umpan balik alokasi memori tidak memerlukan Query Store, namun peningkatan persistensi yang diperkenalkan di SQL Server 2022 (16.x) mengharuskan Query Store diaktifkan pada database dan berada dalam status "baca-tulis". Untuk informasi selengkapnya tentang persistensi, lihat Umpan balik pemberian memori mode persentil dan persistensi di bagian selanjutnya artikel ini.
Aktivitas umpan balik alokasi memori mode baris dapat dilihat melalui extended event memory_grant_updated_by_feedback.
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
LastRequestedMemorymenunjukkan memori yang diberikan dalam Kilobyte (KB) dari eksekusi kueri sebelumnya. - Atribut
IsMemoryGrantFeedbackAdjustedini memungkinkan Anda memeriksa status umpan balik alokasi memori untuk pernyataan tersebut dalam rencana eksekusi kueri aktual.
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 alokasi memori telah diterapkan dan mungkin akan disesuaikan lebih lanjut untuk eksekusi berikutnya. |
| Ya: Penyesuaian Persentil | Umpan balik alokasi memori sedang diterapkan menggunakan algoritma alokasi persentil, yang mempertimbangkan riwayat yang lebih panjang, bukan 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 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.
Fitur ini diperkenalkan di SQL Server 2022 (16.x). Namun, peningkatan kinerja ini tersedia untuk kueri yang beroperasi pada tingkat kompatibilitas database 140 (diperkenalkan di SQL Server 2017 (14.x)) atau lebih tinggi, atau petunjuk QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 140 ke atas, dan ketika Query Store diaktifkan untuk database serta berada dalam keadaan "read write".
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 alokasi 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, pada fase awal proyek ini, penyesuaian alokasi memori hanya disimpan bersama rencana eksekusi di cache—jika rencana tersebut dikeluarkan dari cache, proses umpan balik harus dimulai lagi, yang menyebabkan performa buruk pada beberapa eksekusi pertama kueri setelah rencana tersebut dikeluarkan. Solusi baru adalah menyimpan secara persisten informasi grant bersama informasi kueri lainnya di Query Store sehingga manfaatnya tetap bertahan meskipun terjadi pengosongan cache. Persistensi dan persentil umpan balik alokasi memori mengatasi keterbatasan yang ada pada umpan balik alokasi memori dengan cara yang tidak intrusif.
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 mungkin tidak selaras dengan kebutuhan sebenarnya dari kueri yang sedang dijalankan. Umpan balik pemberian memori dalam skenario ini tidak membantu performa karena kami selalu menyesuaikan memori berdasarkan nilai hibah yang terakhir digunakan. Gambar berikut menunjukkan perilaku yang dimungkinkan oleh umpan balik pemberian memori tanpa mode persentil dan persistensi.
Seperti yang Anda lihat, dalam perilaku kueri yang tidak lazim tetapi mungkin terjadi ini, osilasi antara jumlah memori aktual yang dibutuhkan dan yang dialokasikan mengakibatkan pemborosan memori sekaligus kekurangan memori jika eksekusi kueri itu sendiri berubah-ubah dari segi jumlah memori yang dibutuhkan. Dalam skenario ini, umpan balik alokasi memori menonaktifkan dirinya karena menyadari bahwa fitur tersebut lebih banyak menimbulkan dampak buruk daripada manfaat.
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 nilai persentil tinggi berdasarkan kebutuhan ukuran alokasi memori sebelumnya untuk eksekusi rencana yang tersimpan di cache guna menghitung ukuran alokasi memori, dengan menggunakan data yang disimpan dalam Query Store. Penyesuaian persentil, yang akan melakukan penyesuaian alokasi 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 alokasi memori mode baris dapat dinonaktifkan pada tingkat database atau pernyataan, sambil tetap mempertahankan tingkat kompatibilitas database 150 atau lebih tinggi. Untuk menonaktifkan umpan balik pemberian memori mode baris untuk semua eksekusi kueri yang berasal dari database, jalankan pernyataan Transact-SQL dalam konteks database yang berlaku:
ALTER DATABASE SCOPED CONFIGURATION
SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
Untuk mengaktifkan kembali umpan balik pemberian memori mode baris untuk semua eksekusi kueri yang berasal dari basis data, jalankan perintah berikut dalam konteks basis data yang berlaku:
ALTER DATABASE SCOPED CONFIGURATION
SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
Anda juga dapat menonaktifkan umpan balik alokasi memori mode baris untuk kueri tertentu dengan menetapkan DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK sebagai petunjuk kueri USE HINT. Contohnya:
SELECT *
FROM Person.Address
WHERE City = 'SEATTLE'
AND PostalCode = 98104
OPTION (USE HINT('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));
USE HINT Petunjuk kueri lebih diutamakan daripada konfigurasi terlingkup database atau pengaturan bendera pelacakan.
Aktifkan persistensi dan persentil untuk umpan balik pemberian memori
Umpan balik persistensi dan persentil diaktifkan secara bawaan 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 a higher value
Query Store harus diaktifkan untuk setiap database yang menggunakan bagian persistensi dari fitur ini.
Nonaktifkan 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 alokasi memori juga akan menghapus umpan balik yang telah dikumpulkan.
Pengaturan default untuk MEMORY_GRANT_FEEDBACK_PERSISTENCE adalah ON.
Pertimbangan untuk umpan balik alokasi 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 kini disimpan di Query Store, sehingga terdapat sedikit peningkatan pada kebutuhan penggunaan Query Store.
Alokasi memori berbasis persentil cenderung memilih pendekatan yang mengurangi spill. Karena tidak lagi hanya didasarkan pada eksekusi terakhir, melainkan pada pengamatan terhadap beberapa eksekusi sebelumnya, hal ini dapat meningkatkan penggunaan memori untuk beban kerja yang berfluktuasi dengan variasi yang besar dalam kebutuhan alokasi memori antar eksekusi.
Mulai SQL Server 2022 (16.x), saat Query Store untuk replika sekunder diaktifkan, umpan balik alokasi memori bersifat sadar 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. Penyimpanan Kueri tersedia pada replika grup ketersediaan sekunder mulai dari SQL Server 2025 (17.x). Untuk informasi selengkapnya, lihat Query Store untuk sekunder yang dapat diakses.