By: Arshad Ali | Comments (5) | Related: > In Memory OLTP
Problem
SQL Server 2014's In-Memory OLTP feature is intended to significantly improve performance and reduce processing time for servers with large amounts of memory and multi-core processors. What types of applications can take advantage of this new feature? What are the different types of indexes which can be created on memory optimized tables? Are there any considerations, restrictions or limitations? Check out this tip to learn more.
Solution
Looking at declining hardware prices, SQL Server 2014 is being released with an In-memory OLTP feature which leverage today's beefy servers with large amounts of memory and multi-core processors. The In-memory OLTP feature allows us to create a table which resides completely in physical memory significantly improving the performance of the OLTP workload and reduces the processing time required. With this new feature, SQL Server 2014 is well positioned to take full advantage of changing hardware trends and modern applications.
In my last tip, "SQL Server 2014 In-Memory OLTP Architecture and Data Storage", I talked about this feature in detail and explained how this feature is different from DBCC PINTABLE and what the architectural design principals for the SQL Server team were when they were working on this feature. In this tip, I am going to take this discussion further along with the types of applications that can take advantage of this great feature. In addition, I will talk about the different types of indexes that can be created on memory optimized tables and in which scenarios each is preferred. I will also discuss considerations, restrictions and limitations you need to keep in mind when working with memory optimized table.
What applications can take advantage of the SQL Server 2014 In-memory OLTP feature?
- Application - As its name implies, In-memory OLTP is meant for OLTP workloads where you have relatively short-lived transactions and a high degree of concurrent transactions from many users\connections. For example, applications supporting order processing, online booking, stock trading, etc.
- Data - All the performance critical data which are referenced very frequently and can fit in the available memory are a good candidate. Storing the data in memory optimized tables will avoid transaction locking\latching which causes stalls and blocking.
Indexes on memory optimized tables...
Only non-clustered indexes can be created on memory optimized tables and these indexes don't duplicate data, but rather they just point to the rows in the chain. These indexes are not logged and don't get stored on disk, in other words they are maintained online and re-created during the database recovery process. Does this mean indexes will not get fragmented? Yes you are right, they will not and hence you don't have the overhead of maintaining these indexes and will not have impact on disk IO.
There are two type of indexes which can be created on memory optimized tables (both of them are non-clustered) and each of makes sense in specific scenarios. First is the Hash index which is an ideal candidate if you have lots have queries which are going to do equi-joins (using "=" operator). The second type of index is a Range index which is an ideal candidate if you have lots of queries which require a range selection (using ">" or "<" operators). I am going to talk in detail about these index types in my next tip along with different scenarios and examples.
A very important point to note with indexes is that they get created when you create the table and there is no way to drop, add or modify indexes on memory optimized tables. Having said that, you need to do thorough analysis and proper index planning based on the workload and queries, before you create memory optimized tables. Please note, if you installed SQL Server 2014 CTP1 it only supports Hash indexes, so you need to install SQL Server 2014 CTP2 or later version to use Range indexes as well.
Restrictions, constraints and limitations when creating memory optimized tables...
Below are the restrictions when creating memory optimized tables:
- First and foremost, no schema changes are permitted once a memory optimized table is created. In other words, no ALTER TABLE statements are supported for memory optimized tables. This applies to indexes as well, all the indexes need to be created as part of the table creation. There is no support for CREATE INDEX, DROP INDEX or ALTER INDEX statements. In case any schema change is required, you need to drop the existing memory optimized table and recreate it with the new structure expected.
- A memory-optimized table must always have at least one index and if you are create a primary key on table this requirement will be satisfied.
- No unique index is supported other than the primary key.
- A maximum of 8 indexes can be created on a memory optimized table including the one supporting the primary key.
- You cannot specify a column to be an identity; although you can use SEQUENCE to get similar behavior.
- DML triggers are not supported.
- Foreign keys and check constraints are not supported.
- The maximum row size for memory optimized table can be up to 8060 bytes; it means no LOB (Large Object) data types with MAX size, XML or CLR are supported. It also does not allow off row data storage.
- As I said above, a maximum 8060 bytes can be stored in a row for a memory optimized table and this restriction is enforced at the time of table creation. If you try creating a memory optimized table with two columns with varchar(4500) data type the table creation logic will fail because the combined length of the columns must be less than 8060 bytes.
Next Steps
- Review SQL Server 2014 In-Memory OLTP Architecture and Data Storage from this tip series.
- Review Overcoming storage speed limitations with Memory-Optimized Tables for SQL Server tip.
- Review Guidelines for Using Indexes on Memory-Optimized Tables article on MSDN.
- Review Breakthrough performance with in-memory technologies blog.
- Review my previous tips.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
