Long Pending Blog Post for almost half an year
In case of Hekaton engine of SQL Server 2014 one has to plan the number of hash buckets to ensure good performance of selects/inserts. Hash data structure and their collisions/chaining are familiar to regular software person but in a database world these are new things. Most of the information about sizing of the table is shared at http://msdn.microsoft.com/en-us/library/dn205318(v=sql.120).aspx (plan for 1-2 x times the size of the index – always better to over-provision) . With respect to range queries – one has to create an index(same old syntax) while creation of table. A data structure Bw Tree which is used underneath is a good reference when you have time. Basically simple way to understand is – hash structure is pointer to the linked list of row and does not have order and good for equality operator(=), range queries(<,>) on other hand might require ordered traversal. So they require different structure to support this requirement.
DMV sys.dm_db_xtp_hash_index_stats provides information about the hash buckets – how full they are etc.
Another issue associated with present release of Hekaton which I am hoping will go away in final release is tight binding between generated code representing in-memory table/procedure – right now purging of older versions is not possible without the restart of the sql server process itself.
DMV sys.dm_os_loaded_modules opens the lid on what module(representation of the native table/procedure) is loaded for helping the engine.
Few challenges in this CTP release Foreign-Keys, DML triggers, CHECK constraints ,DDL command to change index – more documented end to end – http://msdn.microsoft.com/en-us/library/dn133181(v=sql.120).aspx
For now we suggest use of this technology for folks who are challenged by heavy locking, read intensive table, cpu usage or want to use for session persistence. And in near future when SSIS integration becomes friendlier – for ETL purposes(say support for merge), right now staging table usage is okay(as we might want to push in lot of data).
We also request for proper planning for the growth of the data and not use it for unbounded data.
Is adoption of Hekaton without changes possible as some folks would like to claim – I would never say that. Presently Foreign-Keys and Check Constraints, identity are not present -that by itself in majority of the cases requires changes in existing systems.
Since in the background MVCC mechanism is used – applications have to take care of errors when updates results in failure due to value change just like deadlock errors. So respectfully listen to the spiel and hope “no change” comes one day and for now look at the workloads which can take advantage of this feature.
Is there a tool which can help in adopting the Hekaton – yes – http://blogs.technet.com/b/dataplatforminsider/archive/2013/09/17/new-amr-tool-simplifying-the-migration-to-in-memory-oltp.aspx
Other in-memory technologies which are present in the SqlServer stack are Tabular option in SSAS and excel powerpivot. Non Microsoft technologies which have done this – BerkelyDB/TimesTen or Coherence, SolidDB, GigaSpaces.
For folks like us who saw IMDB and then pulled back – this is sweet revenge. Finally changes have been done at engine level for minimizing log i/o, reduce latches by using a data structure which prevents contention and great integration with existing technology pillars like availability.
For good “from the horse’s mouth” discussion – look for Sunil Agarwal’s – DBI-B307 Tech-Ed session on your favourite search engine.