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:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Statistik Kueri Langsung

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

• Peristiwa query_post_execution_showplan yang diperluas. Untuk mengaktifkan Kejadian 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 Extended Events yang menggunakan peristiwa query_post_execution_showplan, DMV sys.dm_exec_query_profiles juga terisi, yang memungkinkan statistik kueri langsung untuk semua sesi, menggunakan Activity Monitor atau dengan mengkueri DMV secara langsung. 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.

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 profiling standar, profiling ringan tidak mengumpulkan informasi waktu proses 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
);

Saat menjalankan sesi extended event yang menggunakan peristiwa query_thread_profile, sys.dm_exec_query_profiles DMV juga terisi menggunakan pembuatan profil ringan, yang memungkinkan statistik kueri secara langsung untuk semua sesi, menggunakan Activity Monitor atau dengan mengkueri DMV secara langsung.

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.

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

Sesi contoh yang menggunakan extended event query_plan_profile dapat dikonfigurasi seperti pada 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 contoh yang menggunakan peristiwa yang diperluas query_post_execution_plan_profile dapat dikonfigurasi seperti pada 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.

Remarks

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 profil ringan menggunakan informasi dari profil standar jika infrastruktur 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.

Konten terkait

• Monitor dan Selaraskan Kinerja
• Alat pemantauan dan penyetelan performa
• Buka Monitor Aktivitas di SQL Server Management Studio (SSMS)
• Monitor Aktivitas
• Memantau kinerja dengan menggunakan Query Store
• Memantau Aktivitas Sistem Menggunakan Kejadian yang Diperluas
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• Atur bendera pelacakan dengan DBCC TRACEON (Transact-SQL)
• Referensi operator logis dan fisik showplan
• rencana eksekusi aktual
• Statistik Kueri Langsung