I recently implemented a Memory Optimized Table solution at one of the large financial organizations while working on Comprehensive Database Performance Health Check. It was indeed a very large scale project where I had to help a customer get 10x speed to their original transactions with the help of Natively Compiled Stored Procedures.
First, we changed the database to support In-Memory OLTP technology and right after that added memory-optimized filegroup and files. The next task was to create memory optimized tables and once those were created and populated, the most challenging task was to access the data quickly and efficiently.
As my client was continuously insisting to get 10x performance, there was the only option available for us is to create Natively Compiled Stored Procedures. Once we created those SP, our performance was improved quite a lot.
Natively Compiled Stored Procedures uses less number of the instructions to execute the query and uses way lesser CPU cycles compared to interpreted (regular) stored procedure. When my client, saw the performance they were indeed very much stunned with the performance of the new stored procedure.
One of the question, which they asked was if it is natively compiled where is the compiler installed. Well, the answer is very simple – the C code compiler is installed at the location: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\Xtp\VC\bin
You can see the compiler related to the files in the blog post. If you modify any EXE or DLL, you may be not able to compile your SP. Here are additional blog posts which you may find interesting on the same topic.
- SQL SERVER – Memory Optimized Tables, Transactions, Isolation Level and Error
- How to List All Memory Optimized Tables in SQL Server? – Interview Question of the Week #227
- SQL SERVER – Identify and Filter In-Memory Optimized Tables – SQL in Sixty Seconds #079
Reference: Pinal Dave (https://blog.sqlauthority.com)
