I have been playing around with SQl Server 2014 Hekaton tables and stored procedures. I have noticed that native compiled stored procedures are between 20 and 40% faster than regular stored procedures
If you want to test for yourself, then run the following code
The codeblock below will create a database with a filegroup where we will store Hekaton tables
CREATE DATABASE HekatonDB
ON
PRIMARY(NAME = [HekatonDB_data],
FILENAME = 'C:DBTestHekatonDBHekatonDB_data.mdf', size=500MB),
FILEGROUP [HekatonDB_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = [HekatonDB_hk_fs_dir],
FILENAME = 'C:DBTestHekatonDBHekatonDB_hk_fs_dir')
LOG ON (name = [HekatonDB_log], Filename='C:DBTestHekatonDBHekatonDB_log.ldf', size=500MB)
COLLATE Latin1_General_100_BIN2;
Next, it is time to create the Hekaton table
USE HekatonDB
GO
CREATE TABLE [dbo].[StorageTestTable]
( [Col1] int NOT NULL,
[Col2] char(100) NOT NULL,
CONSTRAINT [StorageTestTable_PK] PRIMARY KEY NONCLUSTERED HASH ([Col1]) WITH(BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA)
go
Lets insert some data, the following inserts 531441 rows in my table
insert [StorageTestTable]
select row_number() over(order by s1.name),replicate('a',100)
from sys.sysobjects s1, sys.sysobjects s2, sys.sysobjects s3
Let’s see how much memory SQL Server is using for this table
SELECT
memory_allocated_for_table_kb AS MemTableAllocKb,
memory_used_by_table_kb AS MemTableUsedKb,
memory_allocated_for_indexes_kb AS MemIndexAllocKb,
memory_used_by_indexes_kb AS MemIndexUsedKb
FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id = OBJECT_ID('StorageTestTable')
Here is the output
MemTableAllocKb MemTableUsedKb MemIndexAllocKb MemIndexUsedKb 76096 74733 8192 8192
As you can see it is about 75MB for the table and 8MB for the index
In order to create a native compiled stored procedure you need to do a couple of things different.
Native compiled stored procedures must be schema-bound
Execution context is required
Atomic blocks, create a transaction if there is none, otherwise, create a savepoint
Session settings are fixed at create time
Here is our native compiled stored procedure
CREATE PROCEDURE usp_TestHekatonNative
WITH NATIVE_COMPILATION, -- native compiled
EXECUTE AS OWNER, -- Execution context
SCHEMABINDING -- schema-bound
AS
BEGIN ATOMIC WITH -- atomic block
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, -- Session settings
LANGUAGE = 'english' -- Session settings
)
SELECT COUNT(*)
FROM dbo.[StorageTestTable]
END
GO
And here is our regular stored procedure
CREATE PROCEDURE usp_TestHekatonRegular
AS
SELECT COUNT(*)
FROM dbo.[StorageTestTable]
GO
Time to run the tests
Enable time statistics
SET STATISTICS TIME ON
Run these two procs a bunch of times
exec usp_TestHekatonNative
exec usp_TestHekatonRegular
Does the native compiled stored procedure run between 20 and 40% faster on your systems as well?
Please post your numbers in the comment section