More FAQs about SQL Server 2014 In-Memory OLTP

Based on the interest in the new SQL Server 2014 In-Memory OLTP engine, it seems that SQL Server 2014 will be adopted quickly by people eager to use this new feature. I’ve written on this topic a handful of times since Ted Kummert, corporate vice president of the Data Platform group at Microsoft, first announced it at PASS 2012 in Seattle (when In-Memory OLTP was called “Hekaton”), but I keep getting questions about it. In the past, I’ve covered some of the basics and requirements for setting up the SQL Server 2014 In-Memory OLTP engine. In this column, I’ll dive into some of the more important details about SQL Server 2014 In-Memory OLTP.

Q: Will the In-Memory OLTP engine be available in the SQL Server 2014 Standard edition?

A: No. Although Microsoft hasn’t released all of the final details, In-Memory OLTP is expected to be included only in the SQL Server 2014 Enterprise, Developer, and Evaluation editions. Microsoft isn’t planning to include In-Memory OLTP in the Standard edition at this time.

Q: Is the new In-Memory OTLP engine the same as the previous SQL Server 6.5 Database Consistency Checker (DBCC) PINTABLE capability?

A: No. The previous SQL Server 6.5 PINTABLE capability enabled you to pin buffer pool pages or tables to memory, but this isn’t the same as SQL Server 2014 In-Memory OLTP. The PINTABLE technology used the same locking mechanism as all other SQL Server tables; simply moving the table into memory doesn’t avoid the use of locks and latches. In-Memory OLTP uses a completely new optimistic locking design that’s optimized for in-memory data operations. In addition, stored procedures are compiled into native Win64 code. The result is faster performance than what the PINTABLE technology could deliver.

Q: If the server fails, will I lose all of the data for those tables that are using In-Memory OLTP, since the data is in memory?

A: No. The In-Memory OLTP engine uses memory-optimized tables that can be configured to be fully durable. If you create the In-Memory OLTP tables using the SCHEMA_AND_DATA option, SQL Server 2014 will write the database transactions for memory-optimized tables to the transaction log, then later write the data to the data files. If a failure occurs, you can perform a database recovery with no data loss by using checkpoints and the transaction log entries. The memory-optimized tables will be repopulated following the restoration.

Q: Can I use the In-Memory OLTP without making changes to the database?

A: No. At the very minimum, you need to configure the database and use the new In-Memory table schema options to create a memory-optimized table. In addition, many data types such as Image, Ntext, Text, and XML aren’t supported. Other notable SQL Server features that aren’t supported include database mirroring, snapshots, triggers, computed columns, and compression. For a complete list of In-Memory OLTP limitations, see the Transact-SQL Constructs Not Supported by In-Memory OLTP and Supported SQL Server Features web pages.

Q: Will In-Memory OLTP solve any type of performance problem?

A: No. While the In-Memory OLTP engine can provide big performance boosts for many situations, there are several performance issues that In-Memory OLTP can’t address. For example, the In-Memory OLTP engine won’t fix bad database design, nor will it correct poorly performing queries. In addition, not all tables and stored procedures can be modified to take advantage of the In-Memory OLTP engine. Some tables or procedures might require unsupported data types or T-SQL features. SQL Server 2014’s Analysis, Migrate and Report (AMR) tool can help you find out where the In-Memory OLTP features can be effectively used.

Q: Do I need to be using a SQL Server 2014 instance to run the AMR Tool?

A: No. You need SQL Server 2014 SQL Server Management Studio (SSMS), but you can connect SSMS to an existing instance of SQL Server 2008, where you can configure a data collection warehouse and take advantage of the AMR tool to analyze your SQL Server 2008 workloads. The SQL Server 2014 Community Technology Preview 2 (CTP2) code is available at Download the Preview: Microsoft SQL Server 2014 CTP2.

Related: In-Memory OLTP Engine FAQs