Infrastruktur Pembuatan Profil Kueri

Artikel
03/05/2025

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Database SQL di Microsoft Fabric

Mesin Database SQL Server menyediakan kemampuan untuk mengakses informasi runtime pada rencana eksekusi kueri. Salah satu tindakan terpenting ketika masalah performa terjadi, adalah mendapatkan pemahaman yang tepat tentang beban kerja yang dijalankan dan bagaimana penggunaan sumber daya didorong. Untuk ini, akses ke rencana eksekusi aktual penting.

Meskipun penyelesaian kueri adalah prasyarat untuk ketersediaan rencana kueri aktual, statistik kueri langsung dapat memberikan wawasan real time tentang proses eksekusi kueri saat data mengalir dari satu operator rencana kueri ke operator paket kueri lainnya. Rencana kueri langsung menampilkan kemajuan kueri keseluruhan dan statistik eksekusi run-time tingkat operator seperti jumlah baris yang dihasilkan, waktu yang berlalu, kemajuan operator, dll. Karena data ini tersedia secara real time tanpa perlu menunggu kueri selesai, statistik eksekusi ini sangat berguna untuk men-debug masalah performa kueri, seperti kueri yang berjalan lama, dan kueri yang berjalan tanpa batas waktu dan tidak pernah selesai.

Infrastruktur pembuatan profil statistik eksekusi kueri standar

Infrastruktur profil statistik eksekusi kueri, atau pembuatan profil standar, harus diaktifkan untuk mengumpulkan informasi tentang rencana eksekusi, yaitu jumlah baris, penggunaan CPU dan I/O. Metode berikut untuk mengumpulkan informasi rencana eksekusi untuk sesi target memanfaatkan infrastruktur pembuatan profil standar:

Catatan

Mengklik tombol Sertakan Statistik Kueri Langsung di SQL Server Management Studio memanfaatkan infrastruktur profil standar.
Dalam versi SQL Server yang lebih tinggi, jika infrastruktur pembuatan profil ringan diaktifkan, maka akan digunakan oleh statistik kueri langsung alih-alih pembuatan profil standar saat dilihat melalui Activity Monitor atau secara langsung melakukan kueri sys.dm_exec_query_profiles DMV.

Metode berikut untuk mengumpulkan informasi rencana eksekusi secara global untuk semua sesi memanfaatkan infrastruktur pembuatan profil standar:

Peristiwa ekstensi query_post_execution_showplan. Untuk mengaktifkan Peristiwa yang Diperluas, lihat Memantau Aktivitas Sistem Menggunakan Peristiwa yang Diperluas.
Peristiwa trace XML Showplan di SQL Trace dan SQL Server Profiler. Untuk informasi selengkapnya tentang peristiwa penelusuran ini, lihat Showplan XML Event Class.

Saat menjalankan sesi peristiwa yang diperluas yang menggunakan peristiwa query_post_execution_showplan, sys.dm_exec_query_profiles DMV juga diisi, yang memungkinkan statistik kueri langsung untuk semua sesi, menggunakan Activity Monitor atau langsung mengkueri DMV. Untuk informasi selengkapnya, lihat Statistik Kueri Langsung.

Infrastruktur profil statistik eksekusi kueri yang ringan

Dimulai dengan SQL Server 2014 (12.x) SP2 dan SQL Server 2016 (13.x), infrastruktur pembuatan profil statistik eksekusi kueri ringan yang baru, atau dikenal sebagai pembuatan profil ringan, diperkenalkan.

Catatan

Prosedur tersimpan yang dikompilasi secara native tidak didukung dengan pembuatan profil ringan.

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v1

Berlaku untuk: SQL Server (SQL Server 2014 (12.x) SP2 melalui SQL Server 2016 (13.x)).

Dimulai dengan SQL Server 2014 (12.x) SP2 dan SQL Server 2016 (13.x), overhead performa untuk mengumpulkan informasi tentang rencana eksekusi dikurangi dengan pengenalan pembuatan profil ringan. Tidak seperti pembuatan profil standar, pembuatan profil ringan tidak mengumpulkan informasi runtime CPU. Namun, pembuatan profil ringan masih mengumpulkan jumlah baris dan informasi penggunaan I/O.

Sebuah acara query_thread_profile baru yang diperluas juga diperkenalkan, yang memanfaatkan pembuatan profil ringan. Acara diperluas ini memperlihatkan statistik eksekusi per operator, memungkinkan pemahaman lebih dalam tentang performa setiap simpul dan utas. Sesi sampel menggunakan peristiwa yang diperluas ini dapat dikonfigurasi seperti dalam contoh di bawah ini:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Catatan

Untuk informasi lebih lanjut tentang beban kinerja profiling kueri, silakan lihat postingan blog Pilihan Pengembang: Kemajuan kueri - kapan saja, di mana saja.

Saat menjalankan sesi peristiwa yang diperluas yang menggunakan peristiwa query_thread_profile, maka DMV sys.dm_exec_query_profiles juga diisi menggunakan pembuatan profil ringan, yang memungkinkan statistik kueri langsung untuk semua sesi, menggunakan Activity Monitor atau dengan langsung mengkueri DMV.

Infrastruktur pemprofilan statistik eksekusi kueri ringan v2

Berlaku untuk: SQL Server (SQL Server 2016 (13.x) SP1 melalui SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1 menyertakan versi pembuatan profil ringan yang direvisi dengan overhead minimal. Pembuatan profil ringan juga dapat diaktifkan secara global menggunakan bendera pelacakan 7412 untuk versi yang dinyatakan di atas di bagian Berlaku pada. Sys.dm_exec_query_statistics_xml DMF baru diperkenalkan untuk mengembalikan rencana eksekusi kueri untuk permintaan dalam penerbangan.

Dimulai dengan SQL Server 2016 (13.x) SP2 CU3 dan SQL Server 2017 (14.x) CU11, jika pembuatan profil ringan tidak diaktifkan secara global maka argumen petunjuk kueri USE HINT baru QUERY_PLAN_PROFILE dapat digunakan untuk mengaktifkan pembuatan profil ringan di tingkat kueri, untuk sesi apa pun. Ketika sebuah kueri yang berisi petunjuk baru ini selesai, peristiwa query_plan_profile yang diperluas baru juga dihasilkan, yang menyediakan XML rencana eksekusi aktual yang mirip dengan peristiwa query_post_execution_showplan yang diperluas.

Catatan

Peristiwa query_plan_profile yang diperluas juga memanfaatkan profiling ringan meskipun petunjuk kueri tidak digunakan.

Sesi contoh menggunakan peristiwa diperluas query_plan_profile dapat dikonfigurasi seperti contoh di bawah ini:

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v3

Berlaku untuk: SQL Server (dimulai dengan SQL Server 2019 (15.x)) dan Azure SQL Database

SQL Server 2019 (15.x) dan Azure SQL Database menyertakan versi pembuatan profil ringan yang baru direvisi yang mengumpulkan informasi jumlah baris untuk semua eksekusi. Pembuatan profil ringan diaktifkan secara default pada SQL Server 2019 (15.x) dan Azure SQL Database. Dimulai dengan SQL Server 2019 (15.x), bendera pelacakan 7412 tidak berpengaruh. Pembuatan profil ringan dapat dinonaktifkan pada tingkat database menggunakan LIGHTWEIGHT_QUERY_PROFILING konfigurasi cakupan database: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

DMF sys.dm_exec_query_plan_stats baru diperkenalkan untuk mengembalikan rencana eksekusi aktual terakhir yang diketahui untuk sebagian besar kueri, dan disebut statistik rencana kueri terakhir. Statistik rencana kueri terakhir dapat diaktifkan di tingkat database menggunakan konfigurasi lingkup database LAST_QUERY_PLAN_STATS: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Peristiwa diperluas query_post_execution_plan_profile baru mengumpulkan setara dengan rencana eksekusi aktual berdasarkan pembuatan profil ringan, tidak seperti query_post_execution_showplan yang menggunakan pembuatan profil standar. SQL Server 2017 (14.x) juga menawarkan acara ini dimulai dengan CU14. Sebuah sesi contoh menggunakan peristiwa diperluas query_post_execution_plan_profile dapat dikonfigurasi seperti contoh di bawah ini:

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Contoh 1 - Sesi Extended Event menggunakan pembuatan profil standar

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Contoh 2 - Sesi Event Ekstensi menggunakan profiling ringan

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Panduan penggunaan Infrastruktur Pembuatan Profil Kueri

Tabel berikut ini meringkas tindakan untuk mengaktifkan pembuatan profil standar atau pembuatan profil ringan, baik secara global (di tingkat server) atau dalam satu sesi. Juga mencakup versi paling awal yang tersedia untuk tindakan ini.

Cakupan	Pembuatan Profil Standar	Pembuatan Profil Ringan
Global	Sesi XEvent dengan `query_post_execution_showplan` XE; Dimulai dengan SQL Server 2012 (11.x)	Trace Flag 7412; Dimulai dengan SQL Server 2016 (13.x) SP1
Global	SQL Trace dan SQL Server Profiler dengan `Showplan XML` peristiwa pelacakan; Dimulai dengan SQL Server 2000	Sesi XEvent dengan `query_thread_profile` XE; Dimulai dengan SQL Server 2014 (12.x) SP2
Mendunia	-	Sesi XEvent dengan `query_post_execution_plan_profile` XE; Dimulai dengan SQL Server 2017 (14.x) CU14 dan SQL Server 2019 (15.x)
Sesi	Gunakan `SET STATISTICS XML ON`; Dimulai dengan SQL Server 2000	`QUERY_PLAN_PROFILE` Gunakan petunjuk kueri bersama dengan sesi XEvent dengan `query_plan_profile` XE; Dimulai dengan SQL Server 2016 (13.x) SP2 CU3 dan SQL Server 2017 (14.x) CU11
Sesi	Gunakan `SET STATISTICS PROFILE ON`; Dimulai dengan SQL Server 2000	-
Sesi	Klik tombol Statistik Kueri Langsung di SSMS; Mulai dari SQL Server 2014 (12.x) SP2	-

Keterangan

Penting

Karena kemungkinan pelanggaran akses acak saat menjalankan prosedur tersimpan pemantauan yang mereferensikan sys.dm_exec_query_statistics_xml, pastikan KB 4078596 diinstal di SQL Server 2016 (13.x) dan SQL Server 2017 (14.x).

Dimulai dengan pembuatan profil ringan v2 dan overhead rendahnya, server apa pun yang belum terikat CPU dapat menjalankan pembuatan profil ringan terus menerus, dan memungkinkan profesional database untuk memanfaatkan eksekusi yang sedang berjalan kapan saja, misalnya menggunakan Monitor Aktivitas atau langsung mengkueri sys.dm_exec_query_profiles, dan mendapatkan rencana kueri dengan statistik runtime.

Untuk mendapatkan informasi lebih lanjut tentang beban performa dari profiling kueri, lihat posting blog Pilihan Pengembang: Kemajuan kueri - kapan saja, di mana saja.

Catatan

Extended Events yang memanfaatkan pembuatan profil ringan akan menggunakan informasi dari pembuatan profil standar jika infrastruktur pembuatan profil standar sudah diaktifkan. Misalnya, sesi peristiwa yang diperluas menggunakan query_post_execution_showplan sedang berjalan, dan sesi lain yang menggunakan query_post_execution_plan_profile dimulai. Sesi kedua masih akan menggunakan informasi dari pembuatan profil standar.

Catatan

Pada SQL Server 2017 (14.x), Profiling Ringan dinonaktifkan secara default namun diaktifkan ketika jejak XEvent yang mengandalkan query_post_execution_plan_profile dimulai, dan kemudian dinonaktifkan lagi ketika jejak dihentikan. Sebagai konsekuensinya, jika jejak Xevent berdasarkan query_post_execution_plan_profile sering dimulai dan dihentikan pada instans SQL Server 2017 (14.x), sangat disarankan untuk mengaktifkan Lightweight Profiling di tingkat global dengan menggunakan traceflag 7412, untuk menghindari beban berlebih akibat aktivasi/penonaktifan yang berulang.

Lihat Juga

Monitor dan Selaraskan Kinerja
Alat Penyetelan dan Pemantauan Performa
Buka Pemantau Aktivitas (SQL Server Management Studio)
Monitor Aktivitas
Memantau Kinerja Dengan Menggunakan Penyimpanan Kueri
Memantau Aktivitas Sistem Menggunakan Kejadian yang Diperluas
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Bendera pelacakan
Referensi Operator Logis dan Fisik Showplan
rencana eksekusi aktual
Statistik Kueri Langsung

Bagikan melalui

Infrastruktur Pembuatan Profil Kueri

Infrastruktur pembuatan profil statistik eksekusi kueri standar

Infrastruktur profil statistik eksekusi kueri yang ringan

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v1

Infrastruktur pemprofilan statistik eksekusi kueri ringan v2

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v3

Contoh 1 - Sesi Extended Event menggunakan pembuatan profil standar

Contoh 2 - Sesi Event Ekstensi menggunakan profiling ringan

Panduan penggunaan Infrastruktur Pembuatan Profil Kueri

Keterangan

Lihat Juga

Saran dan Komentar

Sumber Daya Tambahan: