Monitoring Performance of Natively Compiled Stored Procedures
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This article discusses how you can monitor the performance of natively compiled stored procedures and other natively compiled T-SQL modules.
Using Extended Events
Use the sp_statement_completed extended event to trace execution of a query. Create an extended event session with this event, optionally with a filter on object_id for a particular natively compiled stored procedure. The extended event is raised after the execution of each query. The CPU time and duration reported by the extended event indicate how much CPU the query used and the execution time. A natively compiled stored procedure that uses a lot of CPU time may have performance problems.
The line_number, along with the object_id in the extended event can be used to investigate the query. The following query can be used to retrieve the procedure definition. The line number can be used to identify the query within the definition:
SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;
Using Data Management Views and Query Store
SQL Server and Azure SQL Database support collecting execution statistics for natively compiled stored procedures, both on the procedure level and the query level. Collecting execution statistics is not enabled by default due to performance impact.
Execution statistics are reflected in the system views sys.dm_exec_procedure_stats and sys.dm_exec_query_stats, as well as in Query Store.
Procedure-Level Execution Statistics
SQL Server: Enable or disable statistics collection on natively compiled stored procedures at the procedure-level using sys.sp_xtp_control_proc_exec_stats (Transact-SQL). The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules on the current instance:
EXEC sys.sp_xtp_control_proc_exec_stats 1
Azure SQL Database and SQL Server: Enable or disable statistics collection on natively compiled stored procedures at the procedure level using the database-scoped configuration option XTP_PROCEDURE_EXECUTION_STATISTICS
. The following statement enables collection of procedure-level execution statistics for all natively compiled T-SQL modules in the current database:
ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;
Query-Level Execution Statistics
SQL Server: Enable or disable statistics collection on natively compiled stored procedures at the query-level using sys.sp_xtp_control_query_exec_stats (Transact-SQL). The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules on the current instance:
EXEC sys.sp_xtp_control_query_exec_stats 1
Azure SQL Database and SQL Server: Enable or disable statistics collection on natively compiled stored procedures at the statement level using the database-scoped configuration option XTP_QUERY_EXECUTION_STATISTICS
. The following statement enables collection of query-level execution statistics for all natively compiled T-SQL modules in the current database:
ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;
Sample Queries
After you collect statistics, the execution statistics for natively compiled stored procedures can be queried for a procedure with sys.dm_exec_procedure_stats (Transact-SQL), and for queries with sys.dm_exec_query_stats (Transact-SQL).
The following query returns the procedure names and execution statistics for natively compiled stored procedures in the current database, after statistics collection:
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;
The following query returns the query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected, ordered by total worker time, in descending order:
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;
Query Execution Plans
Natively compiled stored procedures support SHOWPLAN_XML (estimated execution plan). The estimated execution plan can be used to inspect the query plan, to find any bad plan issues. Common reasons for bad plans are:
Stats were not updated before the procedure was created.
Missing indexes
Showplan XML is obtained by executing the following Transact-SQL:
SET SHOWPLAN_XML ON
GO
EXEC my_proc
GO
SET SHOWPLAN_XML OFF
GO
Alternatively, in SQL Server Management Studio, select the procedure name and click Display Estimated Execution Plan.
The estimated execution plan for natively compiled stored procedures shows the query operators and expressions for the queries in the procedure. SQL Server 2014 (12.x) does not support all SHOWPLAN_XML attributes for natively compiled stored procedures. For example, attributes related to query optimizer costing are not part of the SHOWPLAN_XML for the procedure.
See Also
Feedback
https://aka.ms/ContentUserFeedback.
Binnenkort: Gedurende 2024 worden GitHub Issues uitgefaseerd als het feedbackmechanisme voor inhoud. Dit wordt vervangen door een nieuw feedbacksysteem. Ga voor meer informatie naar:Feedback verzenden en bekijken voor