Pemaksaan rencana eksekusi yang dioptimalkan dengan Query Store

Berlaku untuk: SQL Server 2022 (16.x) Azure SQL Database SQL database di Microsoft Fabric

Pengoptimalan kueri merupakan proses multitahap untuk menghasilkan rencana eksekusi kueri yang "cukup baik". Dalam beberapa kasus, kompilasi kueri, bagian dari pengoptimalan kueri, dapat mewakili persentase besar dari keseluruhan waktu eksekusi kueri dan menggunakan sumber daya sistem yang signifikan. Pemaksaan rencana yang dioptimalkan adalah bagian dari keluarga fitur pemrosesan kueri cerdas. Rencana yang dioptimalkan memaksa mengurangi overhead kompilasi untuk mengulangi kueri paksa dan mengharuskan Penyimpanan Kueri diaktifkan dan dalam mode "baca tulis". Setelah rencana eksekusi kueri dihasilkan, langkah-langkah kompilasi tertentu disimpan untuk digunakan kembali sebagai skrip pemutaran ulang pengoptimalan. Skrip pemutaran ulang pengoptimalan disimpan sebagai bagian dari XML showplan terkompresi di Query Store, dalam atribut tersembunyi OptimizationReplay.

Implementasi memaksa rencana yang dioptimalkan

Ketika kueri pertama kali melewati proses kompilasi, ambang yang didasarkan pada estimasi waktu yang dihabiskan untuk pengoptimalan (berdasarkan pohon input untuk pengoptimal kueri) menentukan apakah skrip pemutaran ulang optimasi dibuat.

Setelah kompilasi selesai, beberapa metrik runtime tersedia untuk menilai apakah estimasi sebelumnya sudah benar. Jika Database Engine mengonfirmasi bahwa ambang batas telah terlampaui, skrip pemutaran ulang pengoptimalan dapat dipertahankan secara persisten. Metrik runtime ini mencakup jumlah objek yang diakses, jumlah gabungan, jumlah tugas pengoptimalan yang dijalankan selama pengoptimalan, dan waktu pengoptimalan aktual.

Manfaat potensial dari penggunaan skrip pemutaran ulang pengoptimalan juga dibandingkan dengan beban tambahan untuk menyimpan skrip pemutaran ulang pengoptimalan. Estimasi waktu relatif untuk memutar ulang skrip pemutaran ulang pengoptimalan dibandingkan dengan waktu yang dihabiskan untuk menjalankan proses pengoptimalan normal. Perkiraan ini didasarkan pada jumlah tugas pengoptimalan yang disimpan dalam skrip pemutaran ulang pengoptimalan, dan jumlah tugas pengoptimalan yang dijalankan selama kompilasi normal. Jika memutar ulang skrip pemutaran ulang pengoptimalan menunjukkan manfaat substansial dalam mengurangi waktu kompilasi, skrip pemutaran ulang pengoptimalan tetap ada.

Considerations

Ketika fitur paksa rencana yang dioptimalkan diaktifkan, kriteria kelayakan untuk paksa rencana yang dioptimalkan adalah:

Hanya rencana kueri yang menjalani pengoptimalan penuh yang memenuhi syarat, dan hal ini dapat diverifikasi dengan adanya properti StatementOptmLevel="FULL".
Pernyataan dengan petunjuk RECOMPILE dan kueri terdistribusi tidak memenuhi syarat.

Namun, jika Query Store secara independen merekam rencana kueri yang sebelumnya dikecualikan oleh pemaksaan rencana yang dioptimalkan, skrip pemutaran ulang optimasi dibuat untuk kompilasi ulang kedua atas kueri yang sama, bergantung pada peristiwa kompilasi ulang default. Pelajari selengkapnya tentang kompilasi ulang dalam Rencana Eksekusi Kompilasi Ulang.

Bahkan jika skrip pemutaran ulang pengoptimalan dihasilkan, skrip tersebut mungkin tidak disimpan di Query Store jika kriteria kebijakan pengambilan yang dikonfigurasi untuk Query Store tidak terpenuhi, terutama jumlah eksekusi pernyataan tersebut serta total waktu kompilasi dan eksekusinya secara kumulatif. Dalam hal ini, skrip pemutaran ulang pengoptimalan yang tidak valid dihapus dari memori secara asinkron.

Mengaktifkan dan menonaktifkan pemaksaan rencana eksekusi yang dioptimalkan

Anda dapat mengaktifkan atau menonaktifkan pemaksaan rencana yang dioptimalkan pada database. Saat pemaksaan rencana yang dioptimalkan diaktifkan pada sebuah database, Anda dapat menonaktifkannya untuk setiap kueri dengan menggunakan hint kueri DISABLE_OPTIMIZED_PLAN_FORCING. Anda juga dapat menonaktifkan pemaksaan paket yang dioptimalkan untuk paket kueri yang dipaksakan di Query Store.

Aktifkan atau nonaktifkan pemaksaan rencana yang dioptimalkan untuk suatu database

Pemaksaan rencana yang dioptimalkan diaktifkan secara default untuk database baru yang dibuat di SQL Server 2022 (16.x) dan versi yang lebih baru. Query Store harus diaktifkan untuk setiap database yang menggunakan pemaksaan rencana yang dioptimalkan. Instans yang ditingkatkan dengan database yang sudah ada, atau database yang dipulihkan dari versi SQL Server yang lebih lama, mengaktifkan pemaksaan rencana yang dioptimalkan secara default.

Untuk mengaktifkan pemaksaan rencana yang dioptimalkan pada tingkat database, gunakan konfigurasi cakupan database ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Anda harus mengaktifkan Query Store jika belum aktif. Temukan contoh kode di Contoh A, atau pelajari selengkapnya tentang Penyimpanan Kueri di Memantau performa dengan menggunakan Penyimpanan Kueri.

Untuk menonaktifkan pemaksaan rencana yang dioptimalkan di tingkat database, gunakan konfigurasi berlingkup database ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.

Nonaktifkan pemaksaan rencana yang dioptimalkan dengan hint kueri

Saat fitur pemaksaan rencana yang dioptimalkan diaktifkan pada database, Anda dapat menonaktifkan pemaksaan rencana yang dioptimalkan untuk kueri tertentu dengan menggunakan DISABLE_OPTIMIZED_PLAN_FORCINGhint kueri.

Temukan contoh penerapan petunjuk kueri ini di Contoh E.

Paksa rencana dengan Query Store, tetapi nonaktifkan pemaksaan rencana yang dioptimalkan

Prosedur sp_query_store_force_plan menyertakan disable_optimized_plan_forcing parameter. Untuk menggunakan parameter ini, prosedur tersimpan sp_query_store_force_plan memerlukan parameter tambahan. Parameter tambahan disebut @replica_group_id. Secara default, primer @replica_group_id memiliki nilai satu (1) bahkan dalam kasus di mana tidak ada replika sekunder yang dikonfigurasi.

Temukan contoh penerapan parameter yang sesuai ke prosedur tersimpan sp_query_store_force_plan di Contoh C.

Tampilan katalog sys.query_store_plan menyertakan kolom yang menunjukkan apakah rencana memiliki skrip pemutaran ulang optimasi yang terkait, serta menambahkan status baru ke kolom alasan kegagalan yang sudah ada, khusus untuk skrip pemutaran ulang optimasi yang terkait. Pelajari selengkapnya di sys.query_store_plan.

Examples

Sampel kode dalam artikel ini menggunakan database sampel AdventureWorks2025 atau AdventureWorksDW2025, yang dapat Anda unduh dari halaman beranda Sampel dan Proyek Komunitas Microsoft SQL Server.

A. Aktifkan Query Store dan penerapan paksa rencana yang dioptimalkan untuk database

Kode berikut mengaktifkan Query Store pada database, lalu mengaktifkan pemaksaan rencana yang dioptimalkan untuk database tersebut. Pelajari selengkapnya tentang opsi mengaktifkan Query Store dalam ALTER DATABASE SET opsi.

Sebelum menjalankan kode, sambungkan ke database pengguna yang sesuai.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Pilih semua kueri yang memiliki skrip pemutaran ulang pengoptimalan

Contoh kode berikut memilih semua query_ids yang memiliki skrip pemutaran ulang pengoptimalan di Query Store. Sambungkan ke database pengguna yang sesuai sebelum menjalankan kode contoh.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Paksa rencana dan nonaktifkan pemaksaan rencana yang dioptimalkan di Query Store

Kode berikut memaksakan rencana di Query Store, tetapi menonaktifkan pemaksaan rencana yang dioptimalkan. Sebelum menjalankan kode berikut, ganti @query_id dan @plan_id dengan kombinasi yang sesuai untuk instans Anda. Prosedur tersimpan sp_query_store_force_plan mengharuskan agar parameter @replica_group_id diberikan sebagai nilai parameter ketiga saat mencoba menonaktifkan pemaksaan rencana eksekusi yang dioptimalkan di Query Store. Ini dapat digunakan untuk menonaktifkan pemaksaan rencana yang dioptimalkan untuk rencana paksa tertentu pada replika tertentu. Nilai @replica_group_id = 1 digunakan untuk menonaktifkan fitur pada replika utama.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

Pelajari selengkapnya di sp_query_store_force_plan.

D. Pilih semua kueri yang pemaksaan rencana yang dioptimalkan dinonaktifkan oleh Query Store

Contoh berikut membuat kueri terhadap semua rencana eksekusi yang dipaksakan di Query Store dengan is_optimized_plan_forcing_disabled diatur ke 1. Sebelum menjalankan kode, sambungkan ke database pengguna yang sesuai.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Menonaktifkan memaksa rencana yang dioptimalkan untuk kueri

Contoh berikut menonaktifkan pemaksaan rencana eksekusi yang dioptimalkan untuk sebuah kueri dengan menggunakan DISABLE_OPTIMIZED_PLAN_FORCINGpetunjuk kueri.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-06-24