optimalkan untuk beban kerja ad hoc (opsi konfigurasi server)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

Pengoptimal opsi beban kerja ad hoc digunakan untuk meningkatkan efisiensi paket cache untuk beban kerja yang berisi banyak batch penggunaan tunggal dan ad hoc. Ketika opsi ini diatur ke 1, Mesin Database menyimpan stub rencana kecil yang dikompilasi dalam cache paket saat batch dikompilasi untuk pertama kalinya, alih-alih paket lengkap yang dikompilasi. Opsi ini mungkin membantu meringankan tekanan memori dengan tidak mengizinkan cache rencana diisi dengan rencana yang dikompilasi yang tidak digunakan kembali. Namun, mengaktifkan opsi ini dapat memengaruhi kemampuan Anda untuk memecahkan masalah paket sekali pakai.

Stub paket yang dikompilasi memungkinkan Mesin Database untuk mengenali bahwa batch ad hoc ini dikompilasi sebelumnya, dan hanya menyimpan stub paket yang dikompilasi. Ketika batch ini dipanggil (dikompilasi atau dijalankan) lagi, Mesin Database mengkompilasi batch, menghapus stub paket yang dikompilasi dari cache paket, dan menambahkan rencana lengkap yang dikompilasi ke cache rencana.

Anda dapat menemukan stub paket yang dikompilasi dengan mengkueri sys.dm_exec_cached_plans tampilan katalog dan mencari "Paket Yang Dikompilasi" di cacheobjtype kolom. Stub memiliki yang unik plan_handle. Stub paket yang dikompilasi tidak memiliki rencana eksekusi yang terkait dengannya, dan kueri untuk handel paket tidak mengembalikan showplan grafis atau XML.

Lacak Bendera 8032 mengembalikan parameter batas cache ke pengaturan RTM SQL Server 2005 (9.x), yang secara umum memungkinkan cache menjadi lebih besar. Gunakan pengaturan ini ketika entri cache yang sering digunakan kembali tidak masuk ke dalam cache dan ketika opsi optimalkan untuk beban kerja ad hoc gagal mengatasi masalah dengan cache rencana.

Peringatan

Bendera Pelacakan 8032 dapat menyebabkan performa yang buruk jika cache besar membuat lebih sedikit memori yang tersedia untuk konsumen memori lain, seperti kumpulan buffer.

Keterangan

Mengatur opsi optimalkan untuk beban kerja ad hoc agar 1 hanya memengaruhi paket baru; paket yang sudah ada dalam cache paket tidak terpengaruh.

Untuk segera memengaruhi rencana kueri yang sudah di-cache, cache paket perlu dibersihkan menggunakan ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, atau SQL Server harus dimulai ulang.

Rekomendasi

Hindari memiliki sejumlah besar paket sekali pakai dalam cache paket. Penyebab umumnya meliputi:

  • Jenis data parameter kueri yang tidak ditentukan secara konsisten. Ini terutama berlaku untuk panjang string tetapi dapat berlaku untuk jenis data apa pun yang memiliki maxlength, presisi, atau skala. Misalnya, jika parameter bernama @Greeting diteruskan sebagai nvarchar(10) pada satu panggilan dan nvarchar(20) pada panggilan berikutnya, paket terpisah dibuat untuk setiap ukuran parameter.

  • Kueri yang tidak diparameterkan. Jika kueri memiliki satu atau beberapa parameter yang nilainya dikodekan secara permanen dikirimkan ke Mesin Database, sejumlah besar rencana kueri dapat ada untuk setiap kueri. Paket bisa ada untuk setiap kombinasi jenis dan panjang data parameter kueri yang digunakan.

Jika jumlah paket penggunaan tunggal mengambil bagian yang signifikan dari memori Mesin Database Server SQL di server OLTP, maka paket ini adalah paket ad hoc, gunakan opsi server ini untuk menurunkan penggunaan memori dengan berbagai objek ini.

Jika opsi optimalkan untuk beban kerja ad hoc diaktifkan, Anda tidak dapat melihat rencana eksekusi untuk kueri sekali pakai, karena hanya stub paket yang di-cache. Bergantung pada lingkungan dan beban kerja Anda, Anda mungkin mendapat manfaat dari dua fitur berikut:

  • Fitur Penyimpanan Kueri, yang diperkenalkan di SQL Server 2016 (13.x), membantu Anda menemukan perbedaan performa yang disebabkan oleh perubahan rencana kueri dengan cepat. Penyimpanan Kueri diaktifkan secara default pada database baru di SQL Server 2022 (16.x) dan versi yang lebih baru.

  • Parameterisasi paksa dapat meningkatkan performa database tertentu dengan mengurangi frekuensi kompilasi kueri dan kompilasi ulang. Database yang mendapat manfaat dari parameterisasi paksa umumnya mengalami volume kueri bersamaan yang tinggi dari sumber seperti aplikasi point-of-sale.

    Parameterisasi paksa dapat menyebabkan masalah performa karena sensitivitas parameter. Untuk informasi selengkapnya, lihat Menyelidiki dan mengatasi masalah sensitif parameter. Untuk SQL Server 2022 (16.x) dan versi yang lebih baru, Anda juga dapat mengaktifkan pengoptimalan Paket Sensitif Parameter.

Contoh

Untuk menemukan jumlah paket cache sekali pakai, jalankan kueri berikut:

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Konten terkait