What Are Natively Compiled Stored Procedures?
These procedures are a new feature introduced in SQL Server 2014 that allows for faster and more efficient execution of queries and business logic that access memory-optimized tables. Memory-optimized tables are tables that are stored entirely in memory and use a lock-free data structure to support high concurrency and low latency.
These procedures are different from the classic interpreted stored procedures in several ways:
- They are compiled into machine code when they are created, not when they are executed. The machine code is stored as a DLL file on the SQL Server machine.
- They can only reference memory-optimized tables, not disk-based tables.
- They have certain restrictions on the Transact-SQL syntax and features they can use compared to interpreted stored procedures. For example, they must use schema binding, atomic blocks, and explicit transaction isolation levels.
When to Use Natively Compiled Stored Procedures?
The main benefit of using natively compiled stored procedures is performance. By eliminating the interpretation overhead and optimizing the query execution plan, these procedures can run much faster than interpreted stored procedures. In some cases, the performance improvement can be up to 30 times. This can be especially useful for applications that require high throughput, low latency, and real-time analytics.
Some examples of scenarios where natively compiled stored procedures can be beneficial are:
- Processing high volumes of data from IoT devices or sensors
- Performing complex calculations or aggregations on large data sets
- Implementing business rules or validations on transactional data
- Loading or transforming data into memory-optimized tables
- Querying data from memory-optimized tables for reporting or analysis
When Not to Use Natively Compiled Stored Procedures?
However, natively compiled stored procedures also have some drawbacks and limitations that you should consider before using them:
- They require more memory than interpreted stored procedures because they store the machine code and the metadata in memory.
- They are more complicated to modify than interpreted stored procedures because they need to be dropped and recreated every time you make a change.
- They are incompatible with some Transact-SQL features you may need or want to use, such as cursors, triggers, dynamic SQL, error handling, and specific data types and functions.
- They depend on memory-optimized tables, which also have limitations and considerations, such as no foreign keys, identity columns, check constraints, and parallelism.
Conclusion
Natively compiled stored procedures are a powerful tool for improving the performance of queries and business logic that access memory-optimized tables in SQL Server. However, they are not a silver bullet that can solve all your problems. Therefore, you should carefully evaluate your requirements and expectations before deciding whether to use them or not.
In this blog post, we explained when to use and when not to use natively compiled stored procedures in SQL Server. We hope this helps you make an informed decision about whether to adopt this feature or not.
Thank you for reading! If you have any questions, please contact us!