SQL Server 2014

A lot of blogs have already been written about the upcoming new version of SQL Server.

For starters: It will not be SQL2012 R2 but SQL2014.

One feature seems to be directly interesting to NAV. The feature has the codename "Hekaton". So it was called when they started the new version. Now they have a better name for it: "SQL Server in-memory OLTP engine".

The idea is that you can define tables to be in memory. What does this change with being in the cache-memory?

A lot.

A table in cache-memory can be dropped to make place for other data. The table is also working with pages and there can be page-locking.

An in-memory table is ALWAYS in memory. They do not use pages anymore but pointers in hash tables.

The beautiful thing is that you don’t need to change anything in the code to use those tables. You just need enough memory to keep them in memory!

It is possible to have all tables in memory of course, but that seems to be a little overkill.

I am thinking about some tables that are read a lot like "Item","G/L Account","Location", General and VAT posting setup, dimensions-setup tables,…

In certain cases also "Sales Header" and "Sales Line" can be useful to keep in memory (BUT DON’T KEEP FINISHED ORDERS IN THE TABLES! You have archive-tables for that!)

The ‘big’ negative is that you need to disable (or change the datatype to boolean like I do) the BLOB-fields because they are not supported.

Another interesting feature (maybe even more so than in-memory tables), are the in-memory indexes. They ONLY exist in memory and are created when the database is started. They don’t use diskspace, they don’t use transaction-log space, they don’t use backup space, they don’t use transaction-log backup space.

@Microsoft NAV development team: give us some extra properties on the NAV-keys to use this last feature as soon as SQL Server 2014 goes RTM! (Or even before RTM!). I think this will be a killer-feature of performance problems. And also of diskspace problems (yes, we still have them! Not because the NAV databases are that big, neither because the disks are t0o little but because diskspace is assigned very sparingly in a VM environment). Give us this extra tool in our performance tuning toolbox.

I have already read some blogs about SQL Server 2014, but this post seems the most complete (without going too much into detail):

https://www.simple-talk.com/sql/database-administration/exploring-in-memory-oltp-engine-(hekaton)-in-sql-server-2014-ctp1/

Just after reading the previous post and writing my own, I also read this post : http://www.sqlskills.com/blogs/bobb/thinking-about-hekaton-then-think-about-collations/.

It seems going Hekaton will not be so easy after all. There are a lot of limits. Luckily very few apply to NAV.

One Response to “SQL Server 2014”

  1. Just read this blog: http://www.sqlservercentral.com/blogs/aschenbrenner/2013/06/25/first-steps-with-extreme-transaction-processing-hekaton/

    Using memory-optimized tables is not so easy as I first thought…

Leave a Reply

You must be logged in to post a comment.

Create a new blog and join in the fun!