LessThanDot

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

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_statsWHEREobject_id=OBJECT_ID('StorageTestTable')

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')

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

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

About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

5 Comments

Hi Denis,
Thanks for this quick write up . Memory optimized SPs in my testing running fast too. Probably around 25 to 40 % every time.
My question is that is Hekaton fast for standalone queries too.. For eg. I created another non-memory optimized table as below :

Awesome test. I can’t test it myself but could you increase the rows to 5 million. How fast can you copy data from one table to another ? An how fast can you merge data from one table to another. Create an empty testtable3 with the same structure. Then use this one in a stored procedure :
WITH SOURCE AS (
SELECT DISTINCT col1, col2
FROM dbo.testtable2
)
MERGE dbo.testtable3 as target
USING source on
target.col1 = source.col1
WHEN NOT MATCHED THEN
INSERT (col1, col2) VALUES (source.col1, source.col2);
– dont forget the ;

Run the stored procedure twice. First it will add 5 million rows to an empty table. Then it will look up 5 million rows to a 5 million row table. Repeat those for native and regular and let me know the results.