Mar 20, 2014

Do you remember my blog about ‘Hekaton‘? You don’t? Well, you can click here to have a look. What I want to do now, though, is update you on that. ‘Hekaton‘ was a code word. So, as is usually the practice, Microsoft have dropped that now that SQL Server 2014 is close to RTM (it’s at CTP2 at the time of writing). Just a quick aside… I don’t know about you, but I like the code words for things, I reckon they’re WAAAAAAY cooler than the eventual, boring names. Now, back to the topic at hand. ‘Hekaton’ is now called Memory Optimised Tables. In fact, SQL Server 2014 have a number of what are called ‘in-memory database capabilities‘. They include a buffer pool extension (using non-volatile storage as an extension to the SQL Server buffer pool); significant changes to Columnstore Indexes; and finally, the realisation of ‘Hekaton’. So, what’s happened? As I said above, the technology is now called Memory Optimised Tables (MOTs). MOTs are SQL Server tables that have been converted to C structs and compiled as DLL’s that are loaded into memory. The query processor in SQL Server 2014 converts Transact-SQL queries into the appropriate C calls. From that point of view, they look like plain old ordinary tables. Why would I want to do this? Where are the performance gains? I can use caching to get some very impressive performance as it is. Well, the primary feature of MOTs is that they lack any locking to manage transaction isolation. Therefore, they’re most likely to be of benefit to you when you need to optimise performance that supports concurrent access to the same tables. So, when data in a MOT is updated, SQL Server uses an optimistic concurrency row-versioning mechanism to track any changes to rows. Since this occurs in-memory, data modifications are extremely quick, and conflicts are relatively rare. If a conflict occurs, the transaction is terminated. This means that you should design any applications to handle concurrency conflicts in much the same way as you would handle deadlock conditions. To create MOTs, you can use Transact-SQL or the GUI in SQL Server Management Studio (SSMS). Databases in which you want MOTs must have a ‘filegroup’ allocated for memory-optimised data. So, you would add, or create, a filegroup thus:

ALTER DATABASE … ADD FILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA; And then: ALTER DATABASE … ADD FILE (NAME = ’…’ FILENAME = ‘…’) TO FILEGROUP mem_data;

Now, you can create your memory-optimised table:

CREATE TABLE dbo.OptimizedTable (…) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

You can query MOTs in two ways:

1. Using normal Transact-SQL statements, in which case the SQL Server 2014 query engine will provide an interop table to interpret the SQL statement.
2. Create a natively compiled stored procedure using the standard ‘CREATE PROCEDURE’ that will be converted to native C code. These can only be used against MOTs.

You would construct one of these wonderful critters in this way:

CREATE PROCEDURE dbo.OptimizedProcedure WITH NATIVE_COMPLILATION, SCHEMABINDING, EXECUTE AS Owner AS…;

I don’t know about you, but I for one am excited about taking SQL Server 2014 for a spin around the block and see what it can do. There is some incredible technology lurking beneath the hood…time to unleash the beast!