Infrastruktur Pembuatan Profil Kueri
Berlaku untuk:
SQL ServerAzure 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
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk