Memantau Performa Prosedur Tersimpan yang Dikompilasi Secara Asli

Artikel
05/30/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Artikel ini membahas bagaimana Anda dapat memantau performa prosedur tersimpan yang dikompilasi secara asli dan modul T-SQL asli lainnya yang dikompilasi.

Menggunakan Kejadian yang Diperluas

Gunakan peristiwa sp_statement_completed diperluas untuk melacak eksekusi kueri. Buat sesi acara yang diperluas dengan peristiwa ini, secara opsional dengan filter di object_id untuk prosedur tersimpan yang dikompilasi secara asli. Peristiwa yang diperluas dinaikkan setelah eksekusi setiap kueri. Waktu dan durasi CPU yang dilaporkan oleh peristiwa yang diperluas menunjukkan berapa banyak CPU yang digunakan kueri dan waktu eksekusi. Prosedur tersimpan yang dikompilasi secara asli yang menggunakan banyak waktu CPU mungkin memiliki masalah performa.

line_number, bersama dengan object_id dalam peristiwa yang diperluas dapat digunakan untuk menyelidiki kueri. Kueri berikut dapat digunakan untuk mengambil definisi prosedur. Nomor baris dapat digunakan untuk mengidentifikasi kueri dalam definisi:

SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;

Menggunakan Tampilan Manajemen Data dan Penyimpanan Kueri

SQL Server dan Azure SQL Database mendukung pengumpulan statistik eksekusi untuk prosedur tersimpan yang dikompilasi secara asli, baik pada tingkat prosedur maupun tingkat kueri. Mengumpulkan statistik eksekusi tidak diaktifkan secara default karena dampak performa.

Statistik eksekusi tercermin dalam tampilan sistem sys.dm_exec_procedure_stats dan sys.dm_exec_query_stats, serta di Penyimpanan Kueri.

Statistik Eksekusi Tingkat Prosedur

SQL Server: Mengaktifkan atau menonaktifkan koleksi statistik pada prosedur tersimpan yang dikompilasi secara asli pada tingkat prosedur menggunakan sys.sp_xtp_control_proc_exec_stats (Transact-SQL). Pernyataan berikut memungkinkan pengumpulan statistik eksekusi tingkat prosedur untuk semua modul T-SQL yang dikompilasi secara asli pada instans saat ini:

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL Database dan SQL Server: Aktifkan atau nonaktifkan pengumpulan statistik pada prosedur tersimpan yang dikompilasi secara asli pada tingkat prosedur menggunakan opsi XTP_PROCEDURE_EXECUTION_STATISTICSkonfigurasi cakupan database . Pernyataan berikut memungkinkan pengumpulan statistik eksekusi tingkat prosedur untuk semua modul T-SQL yang dikompilasi secara asli dalam database saat ini:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

Statistik Eksekusi Tingkat Kueri

SQL Server: Mengaktifkan atau menonaktifkan koleksi statistik pada prosedur tersimpan yang dikompilasi secara asli di tingkat kueri menggunakan sys.sp_xtp_control_query_exec_stats (Transact-SQL). Pernyataan berikut memungkinkan pengumpulan statistik eksekusi tingkat kueri untuk semua modul T-SQL yang dikompilasi secara asli pada instans saat ini:

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL Database dan SQL Server: Mengaktifkan atau menonaktifkan pengumpulan statistik pada prosedur tersimpan yang dikompilasi secara asli pada tingkat pernyataan menggunakan opsi XTP_QUERY_EXECUTION_STATISTICSkonfigurasi cakupan database . Pernyataan berikut memungkinkan pengumpulan statistik eksekusi tingkat kueri untuk semua modul T-SQL yang dikompilasi secara asli dalam database saat ini:

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

Kueri Sampel

Setelah Anda mengumpulkan statistik, statistik eksekusi untuk prosedur tersimpan yang dikompilasi secara asli dapat dikueri untuk prosedur dengan sys.dm_exec_procedure_stats (Transact-SQL), dan untuk kueri dengan sys.dm_exec_query_stats (Transact-SQL).

Kueri berikut mengembalikan nama prosedur dan statistik eksekusi untuk prosedur tersimpan yang dikompilasi secara asli dalam database saat ini, setelah pengumpulan statistik:

SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

Kueri berikut mengembalikan teks kueri serta statistik eksekusi untuk semua kueri dalam prosedur tersimpan yang dikompilasi secara asli dalam database saat ini yang statistiknya telah dikumpulkan, diurutkan berdasarkan total waktu pekerja, dalam urutan menurun:

SELECT st.objectid,
        OBJECT_NAME(st.objectid) AS 'object name',
        SUBSTRING(
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) AS 'query text',
        qs.creation_time, qs.last_execution_time, qs.execution_count,
        qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, 
        qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
        qs.min_elapsed_time, qs.max_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

Rencana Eksekusi Kueri

Prosedur tersimpan yang dikompilasi secara asli mendukung SHOWPLAN_XML (perkiraan rencana eksekusi). Perkiraan rencana eksekusi dapat digunakan untuk memeriksa rencana kueri, untuk menemukan masalah rencana buruk. Alasan umum untuk rencana buruk adalah:

Statistik tidak diperbarui sebelum prosedur dibuat.
Indeks yang tidak ada

XML Showplan diperoleh dengan menjalankan Transact-SQL berikut:

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

Atau, di SQL Server Management Studio, pilih nama prosedur dan klik Tampilkan Perkiraan Rencana Eksekusi.

Perkiraan rencana eksekusi untuk prosedur tersimpan yang dikompilasi secara asli menunjukkan operator dan ekspresi kueri untuk kueri dalam prosedur. SQL Server 2014 (12.x) tidak mendukung semua atribut SHOWPLAN_XML untuk prosedur tersimpan yang dikompilasi secara asli. Misalnya, atribut yang terkait dengan biaya pengoptimal kueri bukan bagian dari SHOWPLAN_XML untuk prosedur tersebut.

Lihat Juga

Prosedur Tersimpan yang Dikompilasi Secara Asli

Share via