| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | monikerRange |
|---|---|---|---|---|---|---|---|---|
Natively compiled stored procedures & set options |
The SET options of a session do not affect a stored procedure execution, except that certain SET options cause stored procedures to not run. |
MikeRayMSFT |
mikeray |
10/26/2017 |
sql |
in-memory-oltp |
conceptual |
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
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.