SQL Server’s In-Memory tables are accompanied by Natively Compiled Stored Procedures and Functions that run at amazing speed. However, the speed boost comes with tradeoffs.
The complete T-SQL language is not implemented, only key parts and we’ll discuss these limitations and how to get around some of them.
- Transaction handling is different in Natively Compiled code. Most important is the use of optimistic concurrency and the requirement to code for failure.
- Query Plans are never recompiled. When statistics change, they must be manually recompiled
Natively compiled functions have the potential for speeding up DML on disk based tables. We’ll take a look at them and delve into performance comparisons with traditional T-SQL and SQLCLR.
How the speed improvements are achieved is a fascinating topic. We’ll take a look at the C code that SQL produces and uses as an intermediate step. Somewhere in the C is the query plan and we will hunt for them.
Management and performance measurement haven’t been neglected by the SQL team but they’re more difficult with in-memory tables and natively compiled code. The Profiler doesn’t work at all for Natively Compiled procedures. The DMVs and extended events that reveal what’s happening under the hood are the final topic for examination.
If you want your application to be more than just fast, consider using Natively Compiled T-SQL. It can make your database run really, really fast.