Introduction
Modern applications often demand lightning-fast performance from their databases, whether they’re handling large transactional workloads or complex analytical queries. SQL Server’s in-memory OLTP feature addresses these needs by using memory-optimized tables and natively compiled stored procedures to boost throughput and reduce latency. This post provides an overview of natively compiled stored procedures, how to create them, and best practices for performance monitoring and maintenance.
In-Memory OLTP and Natively Compiled Stored Procedures
When you use in-memory OLTP, natively compiled stored procedures can significantly improve database performance by compiling T-SQL code into machine code. This process reduces the overhead of interpretation and makes optimal use of memory-optimized tables. Keep in mind that natively compiled stored procedures can only reference in-memory (memory-optimized) tables.
Creating Natively Compiled Stored Procedures
Use the following template to create a natively compiled stored procedure:
CREATE OR ALTER PROC dbo.MyCompiledProc
@some_value INT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS CALLER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
-- <insert queries against memory-optimized tables here>
END
GO
Key Options Explained
- NATIVE_COMPILATION: Instructs SQL Server to compile T-SQL into machine code.
- SCHEMABINDING: Prevents modifications to objects referenced by the procedure that might break it.
- EXECUTE AS: Optional in SQL Server 2016 and later. In SQL Server 2014, it had to be
SELF
,OWNER
, or a specific user. - BEGIN ATOMIC: Executes all code in a single atomic transaction. The transaction commits if there are no errors before reaching the
END
statement. (UseTRY-CATCH-THROW
for better error handling.)
Additional Required Clauses
- TRANSACTION_ISOLATION_LEVEL: Must be one of
SNAPSHOT
,REPEATABLE READ
, orSERIALIZABLE
. - LANGUAGE: Specifies a language from
sys.syslanguages
.
Initial Compilation
Natively compiled stored procedures are compiled into machine code with help from the SQL Server Query Optimizer. This occurs either:
- At Creation Time: When the procedure is created, SQL Server compiles it immediately, translating T-SQL into machine code.
- At First Execution After a Server Restart: Upon restarting the SQL Server instance, natively compiled stored procedures will be compiled again on their first execution.
Recompilation
Natively compiled stored procedures do not automatically recompile based on data changes or updates to statistics. This behavior has both advantages and drawbacks:
Advantages
- Stable performance: The query plan does not change unexpectedly.
- Predictable execution plans: Execution plans stay the same unless manually recompiled.
Drawbacks
- Data distribution changes: Over time, changes in data can cause performance to degrade if the original query plan is no longer optimal.
Manual Recompilation
To manually recompile a natively compiled stored procedure, you can run the following command:
EXEC sp_recompile N'dbo.MyCompiledProc';
Performance Monitoring Query
The following query retrieves text and execution statistics for all queries within natively compiled stored procedures in the current database, ordered by total worker time:
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(qs.sql_handle) st
WHERE st.dbid = DB_ID()
AND st.objectid IN (
SELECT object_id
FROM sys.sql_modules
WHERE uses_native_compilation = 1
)
ORDER BY qs.total_worker_time DESC;
Requirement of BIN2 Collation for Natively Compiled Stored Procedures
For natively compiled stored procedures, SQL Server requires the use of BIN2 collation when performing string comparisons or creating indexes. This is because BIN2 provides a binary, case-sensitive, and accent-sensitive comparison that is more efficient in memory-optimized environments.
- Efficiency: Binary comparison is faster than other collations that account for linguistic rules.
- Consistency: Ensures deterministic and consistent string operations across servers and environments.
Key Considerations and Restrictions
- Query Optimization: Execution plans are created when the procedure is created and do not automatically update with changing data or statistics. Ensure your tables contain representative data before creating the procedures.
- Manual Recompilation: Use
sp_recompile
to manually refresh execution plans. - Unsupported Features: Certain features (such as
MERGE
, someCASE
expressions,OUTER JOIN
s,CURSOR
s, andCTEs
) are not supported in natively compiled stored procedures. Workarounds or rewrites may be required. - TempDB Restrictions: Temporary objects in
TempDB
are generally not supported. Use in-memory table variables instead. - ALTER PROCEDURE: Modifications require dropping and recreating the stored procedure.
Conclusion
Natively compiled stored procedures can offer substantial performance gains for in-memory OLTP workloads. By understanding the creation process, knowing how to monitor and recompile them, and being aware of their limitations and requirements (such as BIN2 collation), you can effectively optimize your in-memory database operations.