Now that we have seen In-Memory OLTP in the previous blog let us talk about Natively Compiled Stored Procedures. These procedures are a special form of Transact-SQL stored procedure compiled to native code that access memory-optimized tables.
According to SQL Server Books Online Native compilation is converting programming constructs to native code, which consists of processor instructions that can be executed by the CPU without the need for further compilation or interpretation. The result of compiling tables and stored procedures are DLLs that are loaded into the SQL Server process. The result of compiling tables and stored procedures are DLLs that are loaded into the SQL Server process. Which means In-Memory OLTP compiles to memory-optimized tables as well as to natively compiled stored procedures to native DLLs when they are created and every time the server restarts.
To retrieve the DLL path for our In-Memory table we create here we could use the following script as
-- retrieve the path of the DLL for table t1 select name, description FROM sys.dm_os_loaded_modules where name like '%xtp_t_' + cast(db_id('ContosoRetailDW') as varchar(10)) + '_' + cast(object_id('dbo.FactSales_MemoryTable') as varchar(10)) + '.dll' go
Natively compiled stored procedures are marked as NATIVE_COMPILATION to indicate all the stored procedure is compiled to a native code for efficient execution ( In other words the DLL for the stored procedures could interact to the DLL for the memory optimized table DLL directly). I will simply copy the stored procedure we created in the previous blog here change it to support native comilation.
--Memory Optimized Stored procedure IF OBJECT_ID('SP_SampleMemorySELECT','P') IS NOT NULL DROP PROCEDURE [dbo].[SP_SampleMemorySELECT]; GO CREATE PROC SP_SampleMemorySELECT with native_compilation, schemabinding, execute as owner AS BEGIN ATOMIC with (transaction isolation level=snapshot, language=N'us_english') SELECT SalesQuantity ,SUM(TotalCost) AS TotalCost FROM dbo.FactSales_MemoryTable GROUP BY SalesQuantity ; END GO
It is important to keep our memory optimized tables’ statistics up-to-date. It is a good practice to pick times where there isn’t too much workload to update statistics. After each statistics update natively compiled stored procedures should be recreate so that they could benefit from the updated statics.
To update our In-Memory table statics we could use the following dynamic SQL
--Update statistics for all memory optimized tables in the current database-- DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'UPDATE STATISTICS [' + schema_name(schema_id) + N'].[' + name + N'] WITH FULLSCAN, NORECOMPUTE; ' FROM sys.tables WHERE is_memory_optimized=1 EXEC sp_executesql @sql GO
There are limitations on natively compiled stored procedure and you will get an error similar to the ones below when you try execute them:
1, It cannot sort more than 8000 rows otherwise you will get an error like “Msg 41396, Level 16, State 0, Procedure ProcedureName, Line xx The number of rows to be sorted in this query exceeds the limit 8000. Reduce the number of rows selected or the number of JOINs in the query.” You could use top( N ) function or WHERE clause to limit the number of rows returned
2. It cannot sort aggregate values and expressions not appearing verbatim in the GROUP BY list.