Infrastruktur Pembuatan Profil Kueri

Artikel
03/18/2023

Berlaku untuk: SQL Server Azure SQL Database

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 pembuatan profil standar.
Dalam versi SQL Server yang lebih tinggi, jika infrastruktur pembuatan profil ringan diaktifkan, maka dimanfaatkan oleh statistik kueri langsung alih-alih pembuatan profil standar saat dilihat melalui Monitor Aktivitas atau secara langsung mengkueri sys.dm_exec_query_profiles DMV.

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

Peristiwa query_post_execution_showplan diperluas. Untuk mengaktifkan peristiwa yang diperluas, lihat Memantau Aktivitas Sistem Menggunakan Kejadian yang Diperluas.
Peristiwa pelacakan XML Showplan di SQL Trace dan SQL Server Profiler. Untuk informasi selengkapnya tentang peristiwa pelacakan ini, lihat Kelas Peristiwa XML Showplan.

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

Infrastruktur pembuatan profil statistik eksekusi kueri ringan

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

Catatan

Prosedur tersimpan yang dikompilasi secara asli 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.

Acara query_thread_profile baru yang diperluas juga diperkenalkan yang memanfaatkan pembuatan profil ringan. Kejadian yang diperluas ini memaparkan statistik eksekusi per operator yang memungkinkan lebih banyak wawasan 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 selengkapnya tentang overhead performa pembuatan profil kueri, lihat posting blog Pilihan Pengembang: Kemajuan kueri - kapan saja, di mana saja.

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

Statistik eksekusi kueri ringan yang membuat profil infrastruktur 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 Berlaku untuk. 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. Saat kueri yang berisi petunjuk baru ini selesai, peristiwa baru query_plan_profile diperluas juga merupakan output yang menyediakan XML rencana eksekusi aktual yang mirip dengan peristiwa query_post_execution_showplan diperluas.

Catatan

Peristiwa query_plan_profile diperluas juga memanfaatkan pembuatan profil ringan meskipun petunjuk kueri tidak digunakan.

Sesi sampel menggunakan peristiwa query_plan_profile diperluas 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 di tingkat database menggunakan konfigurasi lingkup database LIGHTWEIGHT_QUERY_PROFILING: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Sys.dm_exec_query_plan_stats DMF baru diperkenalkan untuk mengembalikan rencana eksekusi aktual terakhir yang diketahui terakhir 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 query_post_execution_plan_profile baru yang diperluas mengumpulkan yang 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. Sesi sampel menggunakan peristiwa query_post_execution_plan_profile diperluas 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 Acara yang Diperluas 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 Acara yang Diperluas menggunakan pembuatan profil 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 termasuk versi paling awal yang tindakannya tersedia.

Cakupan	Pembuatan Profil Standar	Pembuatan Profil Ringan
Global	Sesi XEvent dengan `query_post_execution_showplan` XE; Dimulai dengan SQL Server 2012 (11.x)	Bendera Pelacakan 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
Global	-	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; Dimulai dengan 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 informasi selengkapnya tentang overhead performa pembuatan profil 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), Pembuatan Profil Ringan nonaktif secara default tetapi diaktifkan ketika jejak XEvent yang mengandalkan dimulai, dan kemudian dinonaktifkan query_post_execution_plan_profile 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 Profil Ringan di tingkat global dengan traceflag 7412 untuk menghindari overhead aktivasi/penonaktifan berulang.

Lihat Juga

Monitor dan Selaraskan Kinerja
Alat Penyetelan dan Pemantauan Performa
Buka Monitor Aktivitas (SQL Server Management Studio)
Monitor Aktivitas
Memantau Performa 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 pembuatan profil statistik eksekusi kueri ringan

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v1

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v2

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v3

Contoh 1 - Sesi Acara yang Diperluas menggunakan pembuatan profil standar

Contoh 2 - Sesi Acara yang Diperluas menggunakan pembuatan profil ringan

Panduan penggunaan Infrastruktur Pembuatan Profil Kueri

Keterangan

Lihat Juga

Saran dan Komentar

Sumber Daya Tambahan: