Natively Compiled Stored Procedures and Execution Set Options
Session options are fixed in atomic blocks, as described in Atomic Blocks. A stored procedure's execution is not affected by a session's SET options, since atomic blocks are required. However, certain SET options, such as SET NOEXEC and SET SHOWPLAN_XML, cause stored procedures (including natively compiled stored procedures) to not execute.
When a natively compiled stored procedure is executed with any STATISTICS option turned on, statistics are gathered for the procedure as a whole and not per statement. For more information, see SET STATISTICS IO (Transact-SQL), SET STATISTICS PROFILE (Transact-SQL), SET STATISTICS TIME (Transact-SQL), and SET STATISTICS XML (Transact-SQL). To obtain execution statistics on a per-statement level in natively compiled stored procedures, use an Extended Event session on the sp_statement_completed event, which starts when each individual query in a stored procedures execution completes. For more information on creating Extended Event sessions, see CREATE EVENT SESSION (Transact-SQL).
SHOWPLAN_XML is supported for natively compiled stored procedures. SHOWPLAN_ALL and SHOWPLAN_TEXT are not supported with natively compiled stored procedures.
SET FMTONLY in not supported with natively compiled stored procedures. Use sp_describe_first_result_set (Transact-SQL) instead.
Send feedback about: