Paket yang dioptimalkan memaksa dengan Penyimpanan Kueri

Berlaku untuk: SQL Server 2022 (16.x)

Pengoptimalan kueri adalah proses multi-fase untuk menghasilkan rencana eksekusi kueri "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. Memaksa 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 Penyimpanan Kueri, dalam atribut tersembunyi OptimizationReplay .

Implementasi memaksa rencana yang dioptimalkan

Ketika kueri pertama kali melewati proses kompilasi, ambang batas berdasarkan estimasi waktu yang dihabiskan dalam pengoptimalan (berdasarkan pohon input pengoptimal kueri) akan menentukan apakah skrip pemutaran ulang pengoptimalan dibuat.

Setelah kompilasi selesai, beberapa metrik runtime tersedia untuk menilai apakah estimasi sebelumnya sudah benar. Jika dikonfirmasi bahwa ambang batas telah dilewati, skrip pemutaran ulang pengoptimalan memenuhi syarat untuk persistensi. Metrik runtime ini mencakup jumlah objek yang diakses, jumlah gabungan, jumlah tugas pengoptimalan yang dijalankan selama pengoptimalan, dan waktu pengoptimalan aktual.

Manfaat potensial menggunakan skrip pemutaran ulang pengoptimalan juga dibandingkan dengan overhead penyimpanan skrip pemutaran ulang pengoptimalan. Estimasi waktu relatif untuk memutar ulang skrip pemutaran ulang pengoptimalan dibandingkan dengan waktu yang dihabiskan untuk menjalankan proses pengoptimalan normal, berdasarkan 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.

Pertimbangan

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

  1. Hanya rencana kueri yang melalui pengoptimalan penuh yang memenuhi syarat, yang dapat diverifikasi dengan kehadiran StatementOptmLevel="FULL" properti.
  2. Pernyataan dengan petunjuk RECOMPILE dan kueri terdistribusi tidak memenuhi syarat.

Namun, jika Penyimpanan Kueri secara independen mengambil rencana kueri yang dicakup dengan memaksa rencana yang dioptimalkan, skrip pemutaran ulang pengoptimalan akan dibuat untuk kompilasi ulang kedua dari kueri yang sama, tunduk 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 bertahan di Penyimpanan Kueri jika kriteria kebijakan penangkapan yang dikonfigurasi Penyimpanan Kueri tidak terpenuhi, terutama jumlah eksekusi pernyataan tersebut dan waktu kompilasi dan eksekusinya yang dikumulasi. Dalam hal ini, skrip pemutaran ulang pengoptimalan yang tidak valid akan dihapus dari memori secara asinkron.

Mengaktifkan dan menonaktifkan memaksa paket yang dioptimalkan

Anda dapat mengaktifkan atau menonaktifkan memaksa paket yang dioptimalkan untuk database. Saat memaksa paket yang dioptimalkan diaktifkan untuk database, Anda dapat menonaktifkannya untuk kueri individual menggunakan DISABLE_OPTIMIZED_PLAN_FORCING petunjuk kueri. Anda juga dapat menonaktifkan memaksa paket yang dioptimalkan untuk rencana kueri yang dipaksa di Penyimpanan Kueri.

Mengaktifkan atau menonaktifkan memaksa paket yang dioptimalkan untuk database

Memaksa paket yang dioptimalkan diaktifkan secara default untuk database baru yang dibuat di SQL Server 2022 (16.x) dan yang lebih tinggi. Penyimpanan Kueri harus diaktifkan untuk setiap database tempat pemakaian paket yang dioptimalkan digunakan. Instans yang ditingkatkan dengan database atau database yang ada yang dipulihkan dari versi SQL Server yang lebih rendah akan memiliki memaksa paket yang dioptimalkan diaktifkan secara default.

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

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

Menonaktifkan memaksa paket yang dioptimalkan dengan petunjuk kueri

Saat fitur memaksa paket yang dioptimalkan diaktifkan dalam database, Anda dapat menonaktifkan pemakaian paket yang dioptimalkan untuk kueri individual dengan menggunakan DISABLE_OPTIMIZED_PLAN_FORCINGpetunjuk kueri.

Temukan contoh penerapan petunjuk kueri ini di Contoh E.

Paksa paket dengan Penyimpanan Kueri, tetapi nonaktifkan paksa paket yang dioptimalkan

Prosedur sp_query_store_force_plan menyertakan disable_optimized_plan_forcing parameter. Untuk menggunakan parameter ini, parameter tambahan diperlukan oleh prosedur tersimpan sp_query_store_force_plan. Parameter tambahan disebut replica_group_id. Secara default, primer replica_group_id akan 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 sys.query_store_plan katalog menyertakan kolom yang menunjukkan apakah paket memiliki skrip pemutaran ulang pengoptimalan terkait, dan menambahkan status baru ke kolom alasan kegagalan yang ada khusus untuk skrip pemutaran ulang pengoptimalan terkait. Pelajari lebih lanjut di sys.query_store_plan (Transact-SQL).

Contoh

J. Mengaktifkan Penyimpanan Kueri dan paket yang dioptimalkan untuk database

Kode berikut mengaktifkan Penyimpanan Kueri pada database, lalu mengaktifkan paksa rencana yang dioptimalkan pada database. Pelajari selengkapnya tentang opsi mengaktifkan Penyimpanan Kueri di opsi ALTER DATABASE SET (Transact-SQL).

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 Penyimpanan Kueri. 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 paket dan nonaktifkan memaksa paket yang dioptimalkan di Penyimpanan Kueri

Kode berikut memaksa rencana di Penyimpanan Kueri, tetapi menonaktifkan memaksa 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 akan mengharapkan bahwa @replica_group_id parameter diteruskan sebagai nilai parameter ketiga saat mencoba menonaktifkan paksa rencana yang dioptimalkan di Penyimpanan Kueri. Ini dapat digunakan untuk menonaktifkan pemaksaan rencana yang dioptimalkan untuk rencana paksa tertentu pada replika tertentu. Nilai 1 - @replica_group_id=1 akan digunakan untuk menonaktifkan fitur pada replika utama.

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

Pelajari lebih lanjut di sp_query_store_force_plan (Transact-SQL).

D. Pilih semua kueri tempat memaksa paket yang dioptimalkan dinonaktifkan oleh Penyimpanan Kueri

Contoh berikut mengkueri semua paket yang telah dipaksa di Penyimpanan Kueri tempat is_optimized_plan_forcing_disabled telah 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 pemakaian rencana yang dioptimalkan untuk kueri menggunakan DISABLE_OPTIMIZED_PLAN_FORCINGpetunjuk kueri. Contohnya menggunakan database sampel AdventureWorks.

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

Langkah berikutnya

Pelajari selengkapnya tentang Penyimpanan Kueri dan paket yang dioptimalkan memaksa dalam artikel berikut ini: