While going through couple of posts for Sql server enhancements , I started deep diving some of important features which are mentioned below.
In-Memory OLTP Engine and AMR Tool:
The most awaited and important new feature in SQL Server 2014 is the In-Memory OLTP engine (code-name Hekaton).
Purpose: It moves required tables and stored procedures into memory which in turns drastically reduces system I/O and improve performance of your OLTP applications.
Microsoft claims that some applications can expect up to a 20x performance improvement. Edgenet, an early adopter, saw a 7x performance increase in its online and retail supply application.
- The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays.
- The In-Memory OLTP tables are copied into memory and made durable by transaction log written to disk.
- An all-new lock-free engine processes the transactions for memory-resident tables.
- Stored procedure performance is improved by compiling the stored procedures into native code DLLs hence no need to recompile each time if execution plan in cache get expired.
- Standard T-SQL stored procedures are interpreted, which adds overhead to the execution process.
- Compiling the stored procedures to native Win64 code makes them directly executable, thereby maximizing their performance and minimizing execution time.
- To help you evaluate how the In-Memory OLTP engine will improve your database performance, Microsoft includes the new Analysis, Migrate, and Report (AMR) tool.
- Like its name suggests, the AMR tool analyzes your database and helps you identify the tables and stored procedures that would benefit from moving them into memory.
- It lists the expected performance improvements as well as any incompatibilities that need to be addressed.
- In addition, the AMR tool can help you perform the actual migration of tables to the new memory-optimized format.
- The In-Memory OLTP engine works with commodity server hardware, but it has a number of limitations.
- For instance, not all of the data types are supported.
- Some of the data types that aren’t supported for memory-optimized tables include geography, hierarchyid, image, text, ntext, varchar(max), and xml.
- In addition, several database features can’t be used with the new In-Memory OLTP capability.
- Database mirroring, snapshots, computed columns, triggers, clustered indexes, identity columns, FILESTREAM storage, and FOREIGN KEY, CHECK, and UNIQUE constraints aren’t supported.
- The In-Memory OLTP engine is supported on Windows Server 2012 R2, Windows Server 2012, and Windows Server 2008 R2 SP2.
- You need to be using the SQL Server 2014 Enterprise, Developer, or Evaluation edition. Notably, In-Memory OLTP won’t be supported on the SQL Server 2014 Standard edition.
With SQL Server 2014’s new In-Memory OLTP engine, you can load tables and stored procedures in memory, which provides very fast response times.
The goal isn’t to load all the database tables and stored procedures in memory, but rather just those tables that are crucial to performance and those stored procedures that have complex logical calculations.
To help you identify which tables and stored procedures will give you the best performance gain after being migrated to In-Memory OLTP, SQL Server 2014 provides the new Analysis, Migrate, and Report (AMR) tool. Built into SQL Server Management Studio (SSMS), the AMR tool which consists of:
- A) Transaction performance collector (which collects data about existing tables and stored procedures in order to analyze workloads) and transaction performance analysis reports (which gives recommendations about the tables and stored procedures to migrate to In-Memory OLTP based on the collected data)
- B) Memory Optimization Advisor (which guides you through the process of migrating a table to a memory-optimized table)
- C) Native Compilation Advisor (which helps you identify T-SQLelements that need to be changed before migrating a stored procedure to a natively compiled stored procedure)
The AMR tool leverages the new Transaction Performance Collection Sets for gathering information about workloads and the Management Data Warehouse (a relational database) to store the collected data.
The Transaction Performance Collection Sets includes:
- A) Stored Procedure Usage Analysis collection set which captures information about stored procedures for a future migration to natively compiled stored procedures
- B) Table Usage Analysiscollection set which captures information about disk-based tables for a future migration to memory optimized tables.
Awaiting for your feedback and suggestions.