Q

What to consider when deploying In-memory OLTP in SQL Server 2014

In this expert answer, Michael Steineke from Edgenet talks about what to consider when deploying In-memory OLTP in SQL Server 2014.

What are the main considerations a company should make when deciding to use in-memory OLTP in SQL Server 2014?

Michael Steineke: With the in-memory OLTP engine, it's not just about taking a table and putting it into memory. You have to decide which tables you want to convert to in-memory OLTP.

Microsoft SQL Server 2014 has an assessment tool that looks at your system and suggests what tables are good candidates for in-memory. Once converted, Microsoft refers to them as in-memory optimized tables and the table structure is different than the normal B-tree that you would have on disk. Indexing, for example, is all in memory.

In-memory OLTP also has a new kind of stored procedure called the natively compiled stored procedure. When you decide which tables to convert to in-memory, you should convert stored procedures for those tables into natively compiled stored procedures. You write the natively compiled stored procedure in standard T-SQL, but when you deploy it to the server, it compiles it in native C -- so it isn't executed the same way a normal SQL Server stored procedure would run.

We chose to convert four tables in our database to in-memory OLTP. We knew what our bottlenecks were, what the problems were with locking and latching contention, and the speed of doing inserts whenever we would get data dumps.

For the particular system we're running for in-memory OLTP, the machine is set up for 48 GB of RAM, but I think only about 6.5 GB is used for the in-memory data. We also did scale testing and had no issue running it on the system we had. We took one of our larger retailer customers and looked if they had all their products in the store and if we'd be able to handle the processing -- we were far from hitting maximum memory.

Michael Steineke is vice president of IT at Edgenet, an Atlanta-based software and services provider for retailers.

This was first published in April 2014

Dig deeper on Microsoft SQL Server Tools and Utilities

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close