I promised I will write with some tips about the new SQL Server 2016 features so here is my first post related to “In-Memory OLTP tables” (known to some ask Hekaton tables).
Did I hear anbody saying In-Memory OLTP tables have been around since SQL Server 2014? If you are one of those… don’t you agree that this feature, as promising as it was, was highly under-used so far? (to say the least). Granted, it was a game changer, but there were so many restrictions to use them that “when rubber meets the road” there were more often than not many people complaining on forums and community groups that In-Memory OLTP tables were simply not an option. Well, many of those restrictions are gone is SQL 2016. Even better, Microsoft is making it easier than ever to migrate your table structures to In-Memory thanks to reports and tools provided for this purpose.
Many limitations are now gone
For once, there were a myriad of limitations associated with the T-SQL clauses that were not available that are now gone, let me itemize some of them:
You can now add FOREIGN KEY constraints between memory-optimized tables, as long as the foreign key references a primary key.
You can use CHECK and UNIQUE constraints
Triggers are now your friend (there are some limitations, but in general Triggers are now kosher with In-Memory tables)
LOB types are OK too (this was a big one missing in SQL 2014)
You can create tables with row size greater than 8,060 bytes (another big deal preventing implementation in the past)
Any code page Collation (not a big deal, but I include it here for those of you who believe this was affecting your ability to implement them)
Indexes have less restrictions as well. For example, you can now create UNIQUE indexes and NULLable index key columns.
Syntax has not changed much since SQL 2014, you still create your table as usual (with the CREATE TABLE statement) and add the WITH (MEMORY_OPTIMIZED = ON) clause.
Also beginning in SQL Server 2016, you can use the ALTER TABLE statement to change the table definition and indexes on memory-optimized tables. You don’t need to do anything special nor add any additional T-SQL constructs, just use your ALTER statement as you would use it with a regular table and voila!
Want to hear more? Statistics for memory-optimized tables now work right off the box. Statistics are updated by data sampling rather than by a full scan. In the past you had to run a script to add automatic-update functionality to in-memory tables.
In-Memory OLTP related reports and tools
I started this post talking not only about restrictions that are now gone but also mentioned that there are reports and tools to migrate your tables to in-memory. One of those reports is the “Transaction Performance Analysis Report” that you can now run directly on SSMS. This is a report with a scatterplot chart showing the relative performance gain and amount of migration work required, you can’t ask for a better companion when it comes to migration of data to in-memory tables!
A new feature in SSMS is the ability to generate migration checklists. To do this, right-click a database in Object Explorer, point to Tasks, and then select Generate In-Memory OLTP Migration Checklists. This step launches a wizard that displays a welcome page. On the second page of the wizard, you specify a location in which to save the checklist and whether to generate a checklist for all tables and stored procedures in the database or for a specific list that you define. After you make this selection, the next page of the wizard includes a Script PowerShell Commands button and a Finish button. If you select the Script PowerShell Commands button, a text file opens with the PowerShell command associated with saving a SqlMigration Report.
When you click the Finish button, the wizard begins to generate a separate checklist for each table and stored procedure specified in the wizard. The status of each checklist is displayed in the table so that you can easily see whether any failed. The checklist includes steps such as checking for unsupported data types, looking for computed columns (yeah.. those are still not supported, such a bummer!), looking for unsupported indexes or unsupported constraints, etc.
Well, I hope all this information gets you excited about SQL Server 2016 and In-Memory OLTP tables. If you want more information about in-memory OLTP tables in general, here are some useful resources:
A good introduction by Artemakis Artemiou: https://www.simple-talk.com/sql/learn-sql-server/introducing-sql-server-in-memory-oltp/
Brent Ozar, as usual: https://www.brentozar.com/blitz/hekaton-memory-oltp-tables-use/
Main Microsoft Page for In-Memroy OLTP tables: https://msdn.microsoft.com/en-us/library/dn133186(v=sql.130).aspx
Have fun!