In-memory OLTP is a revolutionary tool introduced on SQL Server 2014. On SQL Server 2016 this will be even better, with a broader surface of supported tools. Check this article to know the news.
During the Microsoft Ignite conference, held in the beginning of May, we got the nice news regarding the new SQL Server 2016. By the way, Microsoft already released the SQL Server 2016 CTP2, which is awesome for SQL Server addicted people, like me :). You can find more details here.
One of the features that I am eager to put my hands on, is the improved In-Memory OLTP. Still in the Microsoft Ignite, Sunil Argawal and Kevin Farlee delivered a session where the main new improvements of the In-Memory OLTP in SQL Server 2016 were announced. In this article I’m going to pass each one of those improvements, however if you prefer, here is the link for the full Ignite session.
In-memory OLTP on SQL Server 2014
In the past few months I’ve been delivering sessions, and even pre-con, about In-Memory OLTP and most of the people are very excited with the final results, but in other hands, concerned because of the current limitations.
The true, is that this is a revolutionary feature of SQL Server, and we are just in the seeing the beginning of a very complete engine.
Nowadays, it is not easy to fully-migrate existing applications to In-Memory OLTP, all because of its limitations. Just to refresh our minds, here are some of the main ones:
- Total size for durable tables: 250 GB.
- Removal of In-Memory FG not allowed.
- No table changes.
- No index changes.
- Including add new indexes.
- This is a problem when you set bad value for the BUCET_COUNT.
- Row size limit is 8060 bytes.
- A bunch of not supported t-sql.
* To have better understanding about most of the limitations, I recommend you to watch this good compilation, made by Brent Ozar: sp_Blitz Result: Hekaton Tables In Use.
Even with the existing limitations, we can find scenarios where the In-Memory OLTP is very useful and there are big companies/applications using this in production with success!
For database architects, the In-Memory OLTP feature came as an option to improve performance of critical environments. Bellow you have some examples of its utility:
- Staging table for ETL process.
- Better together with Columnstore indexes.
- Temporary data.
- E.g: Website sessions table.
- TempDB alternative.
- Impact absorber.
- For highly concurrent system.
- Performance Critical OLTP systems.
In-memory OLTP on SQL Server 2016
There are few articles about In-Memory OLTP out there, so I think that we can go ahead and see the improvements made on SQL Server 2016!
The Microsoft team did a really good job and the evolution from the previous version to this new one is notable! I’m anxious to have the SQL Server 2016 officially released in order to have this trick up my sleeve 🙂
Total supported size for In-Memory Tables (per database)
SQL Server 2014
The size of durable tables in a database could reach up to 256 GB.
SQL Server 2016
From SQL Server 2016 in-memory durable tables would be able to reach the very good value of 2 TB!
Garbage Collection of Checkpoint Files
SQL Server 2014
The Garbage Collector process is not efficient enough, causing an excessive disk usage in some situations. We are talking about the Checkpoint files (data and delta files) that are stored in the disk in order to keep the data of durable tables. Those files are stored in a container (memory optimized Filegroup) based in FileStream, and this is the problem.
SQL Server 2016
On SQL Server 2016 the storage management is decoupled from FileStream, allowing the unused file to be re-used/cleared immediately after they are de-referenced from transaction log.
Security
SQL Server 2014
One of the In-Memory OLTP adoption blockers was the security. Not because this is unsafe, but because TDE is not supported. Because of this, the question to answer is: “For my application, is the performance most important than the security?” If not, the In-memory OLTP is not the way to go, unfortunately.
SQL Server 2016
More points for the feature now, because Transparent Data Encryption (TDE) will be supported on SQL Server 2016! Now you can be fast, with security. Formula-1 Style 😉
Storage Scaling
SQL Server 2014
The more processing power, the more log records are created, and this information should go to the checkpoint files in an efficient way.
The flow from the transaction log to the checkpoint files is not scalable at certain point, but in the current version we can have only one offline checkpoint thread and that’s why Microsoft recommends the use of up to 2 socket and 64 cores, and no more.
SQL Server 2016
From SQL Server 2016 this won’t be a problem anymore! The limitation was extinguished and we will be able to have one offline checkpoint thread per container.
Development/Maintainability Challenge
SQL Server 2014
No modifications are allowed, which makes the process of tuning very difficult…
Not only this, simple tasks as respond to shifting data patterns or application modifications could be a nightmare.
SQL Server 2016
Finally the ALTER is now supported!! Now you can not only enjoy the power of an ALTER TABLE command, as well as ALTER PROC and sp_recompile!
Another concern is the option to change the HASH index bucket_count, and this will be possible through an index REBUILD.
This is a great news, but the sp_rename is won’t be supported still. Oh, and in order to alter a table you will need to have enough space to allocate two times the table size, as this is an offline operation.
Parallelism
SQL Server 2014
The in-memory OLTP feature is optimized for OLTP workloads, and because of this parallel plans are not used, so the degree of parallelism is always one.
This brings a problem when joining a memory optimized table (MOT) with a columnstore, as the parallelism will be killed and the great benefit of the columnstore will be lost.
SQL Server 2016
Parallel plans are now supported! Now you can take the benefit of columnstore while joining with MOT. Real-time analytics are now possible, in the speed of light! It’s the revolution!!
More improvements
Now it is easier to convert existing applications! Before we were limited to BIN2 collation… Now the full range of collations is supported!
And more! In-memory OLTP will support DML Triggers (AFTER triggers, natively compiled) and some other T-SQL that weren’t supported before, as following:
- {LEFT|RIGHT} OUTER JOIN
- Disjunction (OR, NOT)
- UNION [ALL]
- SELECT DISTINCT
- Subqueries (EXISTS, IN, scalar)
- FOREIGN KEY
- CHECK
- UNIQUE constraints and indexes
- Nested Stored procedures (EXECUTE)
- Natively compiled scalar UDFs
- Indexes on NULLable columns
To close this, the following “visual” tools will be available:
- Migration Assistant for Stored Procedure.
- Best Practices Analyzer support.
- Table Designer supporter in SSMS.
Isn’t awesome?? Yes, it is. Microsoft showed in this new version of SQL Server that In-Memory came to stay and brought to us most of the essential tools that we were expecting. The fact of integrate in-memory and columnstore indexes is a huge evolution as well.
Remember, SQL Server 2016 is only in CTP2 and will be released next year, so maybe we can expect more good news!
With experience working in Portugal, Holland, Germany and United Kingdom, he's always available to learn and share his knowledge, in order to contribute to SQL Server community,
View all posts by Murilo Miranda
- Understanding backups on AlwaysOn Availability Groups – Part 2 - December 3, 2015
- Understanding backups on AlwaysOn Availability Groups – Part 1 - November 30, 2015
- AlwaysOn Availability Groups – Curiosities to make your job easier – Part 4 - October 13, 2015