Capturing natively complied stored procedure by Extended Events
An Extended Events session is the equivalent of a trace. When you create a session, you add an event and actions to fire with that event, define one or more targets for the data collected about the event, and optionally create predicates that define filters for the event.
Events in Extended Events correspond to events in SQL Trace, but many more are supported in Extended Events to give you better diagnostic capabilities. An example of an event is sp_statement_starting (SP:StmtStarting) and sp_statement_completed (SP:StmtCompleted).
CREATE EVENT SESSION [stored_proc] ON SERVER
ADD EVENT sqlserver.sp_statement_completed
( ACTION (sqlserver.session_id, sqlserver.sql_text)
)
ADD TARGET package0.event_file
(
SET filename=N'C:\ExamBook762Ch4\query.xel', max_file_size=(5),max_rollover_files=(4)
),
ADD TARGET package0.ring_buffer;
The SP:StmtCompleted event class indicates that a Transact-SQL statement within a stored procedure has completed.
The SP:StmtStarting event class indicates that a Transact-SQL statement within a stored procedure has started.
The SP:StmtCompleted event class maps to the sp_statement_completed Extended Events event. If you specify sp_statement_completed as the event name in the query, the "event_field" column shows the fields that are included by default with the event. Looking at the fields, you can see that there is a "duration" field. To create the filter in the equivalent Extended Events session, you would add a predicate such as "WHERE duration > 0". (https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/convert-an-existing-sql-trace-script-to-an-extended-events-session?view=sql-server-ver15)
Answer:
