Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Overview of Applications, Indexes and Limitations for SQL Server 2014 In-Memory OLTP Tables


By:   |   Last Updated: 2013-12-30   |   Comments (5)   |   Related Tips: More > 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


Last Updated: 2013-12-30


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, August 20, 2014 - 10:04:21 AM - Priya Back To Top

Hi,

Your post was very helpful to understand in-memory tables. We have a scenario where we have a 20 GB database. The application part of it works fine without any issue. But the reporting part is very slow. They are using BO for reporting. There are about 20 reports that are really slow. So we were thinking of finding the tables related to that 20 reports and having them as in-memory tables. Is it a good idea to do that?

 


Thursday, March 06, 2014 - 2:44:50 AM - Sam Khan Back To Top

Ok  so these mainly usefull in dataware house database.Also can be used for history transaction table or might be tables on production tables which are not altered.

Nicefeatures but for ware house database can be used for production databases but designing plays an imp role.


Monday, January 20, 2014 - 11:27:12 PM - manu Back To Top

Good to know about new features coming in..thanks for the post!


Wednesday, January 15, 2014 - 3:36:58 AM - Arshad Back To Top

Thanks Sree, good to hear that it was helpful for you!


Monday, December 30, 2013 - 11:20:06 AM - sree Back To Top

Short and Useful info, all at one place...Thanks for the post!


Learn more about SQL Server tools