By now, most SQL Server jocks and jockesses have heard that the “heka” in “hekaton” is the Greek word for 100 and refers to the potential, if perhaps elusive, 100-fold performance improvement using Hekaton in-memory tables for transaction processing in SQL Server 2014. Whether intended or not, it is curious to note that “heka” is also an ancient Egyptian word for “magic”, and well as the name of a god who represents the embodiment of magic.
We shall take a look at Hekaton tables in both theory and practice, but first we shall quickly scout the terrain to be covered. In-memory tables are new, very new, and perhaps the best way to understand them is to discard everything you know about SQL Server tables.
The 8KB page structure that characterizes an on-disk database does not appear in In-Memory tables. To be sure, long before Hekaton data pages spent much of their time in RAM. Under normal conditions, frequently used disk-based data pages are cached in RAM and do not need to be fetched from disk after the first time. Indeed, DBCC used to have a pintable command, discontinued in SQL Server 2005, that let administrators force SQL Server to keep some tables in RAM. This seemed to have little practical value, however, since the SQL Server lazywriter will preferentially flush out of memory only those tables that have not been recently accessed. Simply being in-memory does not make a Hekton memory-optimized table.
In contrast with a disk-based table, in which data pages are read into RAM as needed to satisfy queries, In-Memory tables are read into RAM as a stream when SQL starts and stay there. Row data in memory-optimized data is not accessed though page numbers and row offsets; row data is accessed through actual pointers enabling very fast access.
In-Memory data cannot be paged out to the disk. You must therefore have lots and lots and lots of RAM.
You can never be too rich or too thin, or have too much RAM.
– Attributed to the Duchess of Windsor
Because there is no classic page structure, classic indexes do not exist. There are no clustered indexes. Every Hekaton table must have at least one index; the choice is hash index or nonclustered index. Hash indexes favor point queries, that is, queries that look up a single row based on a key value. Unlike clustered indexes, the data stored in hash indexes has no apparent order and hash indexes are useless for querying multiple rows in some sort order.
Non-clustered indexes bear some superficial resemblance to their distant disk-based cousins. Non-clustered indexes are structures that have been dubbed “bw” trees in analogy with b-trees, the structure for disk-based data pages. However, while the “b” in b-tree means balanced, by all accounts the “bw” in bw-trees seems to mean “buzzword”. Perhaps it’s a jibe at SAP and their use of BW to mean “Business Warehousing” (which is clearly better than plain old “Data Warehousing”). In contrast with classic b-tree indexes, Hekaton non-clustered indexes have no permanent life on the hard drive. They are created from scratch each time the server is started and Hekaton table data is streamed into RAM.
Because Hekaton tables are optimized for high-performance transaction processing, many classic index features are not available. Among the more notable, indexes on character data require the BIN2 collation, which almost certainly will be inconsistent with the collation you are currently using for your disk-based tables.
In contrast with a classic clustered index, defining a hash index requires more than just choosing an appropriate column. You must specify a hash bucket count, and you can degrade performance by not choosing wisely. We will examine this decision in the first “Hekaton Tables in Practice” blog.
Hekaton memory-optimized tables do not allow check constraints and foreign-key constraints. You can implement such constraints yourself using stored procedures, but you must ensure that your stored procedure code does not compromise the performance that drew you to Hekaton in the first place. Triggers are not an option: Hekaton in-memory tables do not support triggers.
Sounds great! Stored procedure code translated into C-language code and then fully compiled into native machine code! Remember that the entire purpose of Hekaton is the improvement of transaction-processing performance. As a result, any T-SQL technique that is not directly applicable to this goal is very likely not allowed in natively compiled stored procedures. Even some that you might want for transaction processing, such as sequence objects, are not allowed.
The list of do’s and don’ts for natively compiled stored procedures is quite long, and we shall delve into it further in the Stored Procedures section, but for the time being a few of the most important points. Natively compiled stored procedures cannot access disk-based tables at all. Period. Since temporary tables are by definition disk-based, temp tables are out. UPDATE statements cannot include a FROM clause. Natively compiled stored procedures require the new ATOMIC transaction type, so the traditional transaction statements are not allowed. CASE statements are not allowed, and SELECT statements within stored procedures cannot use the Common Table Expression (CTE) structure.
Experienced programmers will probably have noticed that many of the requirements and restrictions of Hekaton memory-optimized technology are oriented towards a stream-processing approach. The idea is to pump transactions through the CPU as rapidly as possible. Structures that might interfere with this goal, such as the OR operator in a WHERE clause in a stored procedure are not allowed.
Hekaton memory-optimized data technology is a great gift from Microsoft to the transaction-processing community who will likely see great benefits. However, in-memory optimization imposes restrictions that render this technology of little value for databases that serve not only transactions but also modest analytical needs including data summaries and reporting.
To learn more about new enhancements to SQL Server 2014 including Hekaton tables, have a look at our new 3-day hands-on course – SQL Server 2014 Performance Enhancements. You can take the course from one of our global Education Centers or online from home or office.