Bagikan melalui

Paket yang dioptimalkan memaksa dengan Penyimpanan Kueri

  • Artikel

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

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 berdasarkan estimasi waktu yang dihabiskan dalam pengoptimalan (berdasarkan pohon input pengoptimal kueri) menentukan apakah skrip pemutaran ulang pengoptimalan dibuat.

Setelah kompilasi selesai, beberapa metrik runtime tersedia untuk menilai apakah estimasi sebelumnya sudah benar. Jika Mesin Database mengonfirmasi ambang batas disilangkan, 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. 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.

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 menangkap rencana kueri yang dicakup dengan memaksa rencana yang dioptimalkan, skrip pemutaran ulang pengoptimalan 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 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 dikumulasikan. Dalam hal ini, skrip pemutaran ulang pengoptimalan yang tidak valid 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 rencana 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 yang ada, atau database yang dipulihkan dari versi SQL Server yang lebih rendah, telah mengoptimalkan paksa paket yang 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_FORCING petunjuk 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 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 selengkapnya di sys.query_store_plan.

Contoh

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

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.

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 sp_query_store_force_plan tersimpan mengharapkan bahwa @replica_group_id parameter diteruskan sebagai nilai parameter ketiga saat mencoba menonaktifkan memaksa rencana yang dioptimalkan di Penyimpanan Kueri. Ini dapat digunakan untuk menonaktifkan pemaksaan rencana yang dioptimalkan untuk rencana paksa tertentu pada replika tertentu. Nilai digunakan @replica_group_id = 1 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 tempat memaksa paket yang dioptimalkan dinonaktifkan oleh Penyimpanan Kueri

Contoh berikut mengkueri semua paket yang dipaksa di Penyimpanan Kueri tempat 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 pemakaian rencana yang dioptimalkan untuk kueri menggunakan DISABLE_OPTIMIZED_PLAN_FORCING petunjuk 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

Konten terkait