監視原生編譯預存程序的效能

發行項
05/04/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體

本文討論如何監視原生編譯預存程序和其他原生編譯 T-SQL 模組的效能。

使用擴充的事件

使用 sp_statement_completed 擴充事件來追蹤查詢的執行。以此事件建立擴充事件工作階段，選擇性地針對特定原生編譯預存程序篩選 object_id。執行每項查詢之後都將引發此擴充事件。擴充事件所報告的 CPU 時間和持續時間代表了查詢的 CPU 使用率和執行時間。原生編譯預存程序若佔用大量的 CPU 時間，可能就會導致效能問題。

line_number 連同擴充事件中的 object_id 皆可用來調查查詢。使用下列查詢即可擷取程序定義。行號可用來識別定義內的查詢：

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

使用資料管理檢視和查詢存放區

SQL Server 和 Azure SQL Database 支援收集原生編譯預存程序在程序層級和查詢層級的執行統計資料。收集執行統計資料會影響效能，所以預設並未啟用。

執行統計資料會反映在系統檢視表 sys.dm_exec_procedure_stats 和 sys.dm_exec_query_stats，也會反映在查詢存放區。

程序層級執行統計資料

SQL Server：使用 sys.sp_xtp_control_proc_exec_stats (Transact-SQL) 在程序層級啟用或停用原生編譯預存程序的統計資料收集。下列陳述式可以在目前的執行個體上啟用所有原生編譯 T-SQL 模組的程序層級執行統計資料收集：

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL Database 和 SQL Server：使用 database-scoped configuration 選項 XTP_PROCEDURE_EXECUTION_STATISTICS，在程序層級啟用或停用原生編譯預存程序的統計資料收集。下列陳述式可以在目前的資料庫上啟用所有原生編譯 T-SQL 模組的程序層級執行統計資料收集：

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

查詢層級執行統計資料

SQL Server：使用 sys.sp_xtp_control_query_exec_stats (Transact-SQL) 在查詢層級啟用或停用原生編譯預存程序的統計資料收集。下列陳述式可以在目前的執行個體上啟用所有原生編譯 T-SQL 模組的查詢層級執行統計資料收集：

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL Database 和 SQL Server：使用 database-scoped configuration 選項 XTP_QUERY_EXECUTION_STATISTICS，在陳述式層級啟用或停用原生編譯預存程序的統計資料收集。下列陳述式可以在目前的資料庫上啟用所有原生編譯 T-SQL 模組的陳述式層級執行統計資料收集：

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

範例查詢

收集統計資料之後，使用 sys.dm_exec_procedure_stats (Transact-SQL) 和 sys.dm_exec_query_stats (Transact-SQL) 分別可查詢原生編譯預存程序的程序層級和查詢層級執行統計資料。

下列查詢會在收集統計資料之後傳回目前資料庫中原生編譯預存程序的程序名稱和執行統計資料：

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;

下列查詢會傳回目前資料庫中已收集統計資料的原生編譯預存程序內，所有查詢的查詢文字和執行統計資料，依工作者時間總計以遞減順序排序：

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;

查詢執行計畫

原生編譯預存程序支援 SHOWPLAN_XML (估計執行計畫)。估計執行計畫可用來檢查查詢計劃，以找出任何的計劃錯誤問題。計劃錯誤的常見原因包括：

在建立程序之前未先更新統計資料。
遺漏索引。

執行程序表 XML 可透過執行下列 Transact-SQL 取得：

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

或者，在 SQL Server Management Studio 中選取程序名稱，然後按一下 [顯示估計執行計畫]。

原生編譯預存程序的估計執行計畫會顯示程序內各查詢的查詢運算子和運算式。 SQL Server 2014 (12.x) 並未支援原生編譯預存程序的所有 SHOWPLAN_XML 屬性。例如，與查詢最佳化工具成本相關的屬性並未納入程序的 SHOWPLAN_XML。

另請參閱

原生編譯的預存程序