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.
OLTP limitations addressed in SQL Server 2016
Dig Deeper on Microsoft SQL Server Tools and Utilities
Related Q&A from Mark Fontecchio
Installing SAP HANA is no easy task. Read about why Scotts Miracle-Gro decided on a phased rollout of its HANA implementation. Continue Reading
Escaping the no-win Oracle SLA as a DBA is all about tying technology to business, according to two Oracle Database experts. Continue Reading
PayPal Database Engineer John Kanagaraj says an AWR workaround lets you prioritize and compress data in earlier ODB versions. Continue Reading