Bagikan melalui

Infrastruktur Pembuatan Profil Kueri

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric

Mesin Database SQL Server menyediakan akses ke informasi runtime tentang 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. Dengan demikian, akses ke rencana eksekusi yang sebenarnya 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 menggunakan infrastruktur pembuatan profil standar:

Note

Memilih tombol Sertakan Statistik Kueri Langsung di SQL Server Management Studio menggunakan infrastruktur pembuatan profil standar. Di versi SQL Server yang lebih baru, jika infrastruktur pembuatan profil ringan diaktifkan, infrastruktur tersebut digunakan 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 menggunakan infrastruktur pembuatan profil standar:

Saat menjalankan sesi peristiwa yang diperluas yang menggunakan query_post_execution_showplan peristiwa, maka sys.dm_exec_query_profiles DMV juga diisi, yang memungkinkan statistik kueri langsung untuk semua sesi, menggunakan Monitor Aktivitas atau secara 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.

Note

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

Statistik eksekusi kueri ringan yang membuat profil infrastruktur v1

Berlaku untuk: SQL Server 2014 (12.x) SP2 hingga 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 baru query_thread_profile yang diperluas juga diperkenalkan yang menggunakan 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 berikut:

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
);

Note

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 query_thread_profile peristiwa, 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.

Infrastruktur pemprofilan statistik eksekusi kueri ringan v2

Berlaku untuk: SQL Server 2016 (13.x) SP1 hingga 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 sebelumnya di Berlaku. 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 QUERY_PLAN_PROFILE baru dapat digunakan untuk mengaktifkan pembuatan profil ringan di tingkat kueri, untuk sesi apa pun. Ketika kueri yang berisi petunjuk baru ini selesai, peristiwa baru query_plan_profile yang diperluas juga merupakan output yang menyediakan XML rencana eksekusi aktual yang mirip dengan peristiwa yang query_post_execution_showplan diperluas.

Note

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

Sesi sampel menggunakan peristiwa yang query_plan_profile diperluas dapat dikonfigurasi seperti contoh berikut:

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 2019 (15.x) dan versi yang lebih baru, 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. Di SQL Server 2019 (15.x) dan versi yang lebih baru, bendera pelacakan 7412 tidak berpengaruh. Pembuatan profil ringan dapat dinonaktifkan di tingkat database menggunakan LIGHTWEIGHT_QUERY_PROFILINGkonfigurasi 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 LAST_QUERY_PLAN_STATSkonfigurasi cakupan database: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Peristiwa baru query_post_execution_plan_profile 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 yang query_post_execution_plan_profile diperluas dapat dikonfigurasi seperti contoh berikut:

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.

Scope Pembuatan Profil Standar Pembuatan Profil Ringan
Global Sesi Acara yang Diperluas 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 Sesi Acara yang Diperluas dengan query_thread_profile XE; Dimulai dengan SQL Server 2014 (12.x) SP2
Global N/A Sesi Acara yang Diperluas dengan query_post_execution_plan_profile XE; Dimulai dengan SQL Server 2017 (14.x) CU14 dan SQL Server 2019 (15.x)
Session Gunakan SET STATISTICS XML ON QUERY_PLAN_PROFILE Gunakan petunjuk kueri bersama dengan sesi Kejadian yang Diperluas dengan query_plan_profile XE; Dimulai dengan SQL Server 2016 (13.x) SP2 CU3 dan SQL Server 2017 (14.x) CU11
Session Gunakan SET STATISTICS PROFILE ON N/A
Session Pilih tombol Statistik Kueri Langsung di SSMS; Dimulai dengan SQL Server 2014 (12.x) SP2 N/A

Remarks

Important

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 lebih lanjut tentang beban kinerja profiling kueri, silakan lihat postingan blog Pilihan Pengembang: Kemajuan kueri - kapan saja, di mana saja.

Extended Events yang menggunakan pembuatan profil ringan 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 menggunakan informasi dari pembuatan profil standar.

Note

Pada SQL Server 2017 (14.x), Pembuatan Profil Ringan nonaktif secara default tetapi diaktifkan ketika jejak Extended Event yang mengandalkan dimulai, dan kemudian dinonaktifkan query_post_execution_plan_profile lagi ketika jejak dihentikan. Sebagai konsekuensinya, jika pelacakan Extended Event berdasarkan query_post_execution_plan_profile sering dimulai dan dihentikan pada instans SQL Server 2017 (14.x), Anda harus mengaktifkan Lightweight Profiling di tingkat global dengan flag pelacakan 7412 untuk menghindari overhead aktivasi/penonaktifan berulang.

Konten terkait

  • Last updated on