Marcin Szeliga

Index Strategies – Part 2 – Memory Optimized Tables and Columnstore Indexes

October 30, 2014 by

In the first part we started discussion about choosing the right table structure and described the heaps — in my opinion a mostly overused option. Now let’s see better alternatives.

Four different table structures — continued discussion

For years Microsoft has been trying to convince us that Enterprise edition is worth the additional money. For SQL Server 2014 this is even more true than it was previously. So, if you are lucky Enterprise Edition owner, you can adapt your database model to leverage unique features of this edition. It will make the design simpler and result in a better performance.

In order to improve structure of tables, SQL Server 2014 Enterprise Edition offers two interesting options: memory-optimized tables and clustered columnstore indexes.

Memory-Optimized Tables

Because the entire memory-optimized table resides in memory (the copy of it is stored on disk for durability only) and this kind of table has lock-free structures, it fits nicely into any logging scenarios. In other words, if you have a table used primary for logging, memory-optimized one should be your first choice.

Before a memory-optimized table can be created, a dedicated filegroup with a file (actually this file is a folder — Hekaton, the in-memory OLTP engine uses storage built on top on filetable feature) have to be added to a database (the script that creates this database can be found in the first article):

Secondly, there are many limitations for the memory-optimized tables. Fortunately, they count for little in our scenario — logging tables usually don’t have foreign keys, check constraints or defined triggers. Still, there is one important exception — the memory-optimized tables don’t support distributed transactions. What is more, this kind of table cannot be altered in any way, drop and re-create excepted, and 1252 character set must be used for varchar or char columns (and come to that, if you want to put an index on varchar column, you will need a BIN collation. So, our table definition can look like this below:

If we insert once again the same 100000 rows into the memory-optimized table, in spite of using 50 of concurrent sessions (the same methodology was used to load data into the heap) the average time will drop from 18 seconds to 10.

However, this improvement has its price — we have to have enough memory to store all of these rows in it:

Memory usage by memory optimized objects

Now let’s do our UPDATE test. Again, we will update some rows with the aid of SQLQueryStress using five concurrent sessions and two queries — first one is the UPDATE statement and the second one is a simple parameter substitution:

For the heap, SQL Server updated about 800 rows per second. The memory-optimized table, with more than 50 000 updates per second, it’s completely different story. Now, I would like to draw your attention to the fact that the rows are not stored in pages any more so there are no side effects like forwarding pointers in heaps.

With DELETEs, the space which was used by deleted rows will be reclaim by background process (by the way, this process is integrated with index scans, so this space can be recover even quicker). Therefore, also in this aspect memory optimized tables are better than heaps.

In summary, memory-optimized tables are great for logging, if only we have enough free memory to store the whole table in it.

Columnstore indexes

The second scenario in which heaps are traditionally being used is staging. The classic example is like this: to speedup data loading and avoid fragmentation, new data is loaded into an empty heap, and later this heap is switched into destination table as its last non-empty partition. This procedure is well-defined and worldwide proofed.

However, if your server is not CPU constrained (most of them are not) but the I/O is its bottleneck (as it is in most cases) maybe you should revalue this strategy. The main idea is that compression (or uncompression) data on the fly, is more efficient than copy it in uncompressed state and apply compression afterwards.

The new approach, dedicated to SQL Server 2014 Enterprise Edition, can be implemented with clustered columnstore indexes — first, they are finally read-write, second, they can be partitioned.

To demonstrate this concept, we will need a partition function and a partition scheme:

To keep this example as simply as possible the partitioning function was defined for identity column. And the third table definition was slightly adjusted, just to simplify inserting rows into the third partition:

Finally, let’s convert this table into columnstore index by executing following statement:

This allows us to insert the same rows using exactly the same INSERT INTO … DEFAULT VALUES statement as for inserting rows into the heap and into the memory-optimized table. This time execution of 2000 inserts with 50 concurrent sessions took 11 seconds, so slightly longer than for the memory-optimized table but still about 40% faster than for the heap.

And without any additional work, such as adding constraints or switching partitions, we have already achieved our goal — the new data was loaded into the right partition:

One more time, the UPDATE’s performance was measured with execution of the same statement in 5 concurrent sessions. This time SQL Server updates about 7 thousand rows per second — more or less 8 times faster than for the heap but 7 to 10 times slower than for the memory-optimized table.

But what about reclaiming unused space? In SQL Server 2014 columnstore indexes use two different storages — columnar data is still read-only, so all changes are located into row-based delta storage. Both storages are automatically used by SQL Server to satisfy queries, so you will always have the actual data, but space used by deleted rows will not be reclaim automatically:

To get back this space the columnstore index has to be rebuilt. Unfortunately, this operation cannot be done online:

Due to the fact that this table was partitioned, all you need is to rebuilt only one partition, so the downtime can be minimized:

To sum up — clustered columnstore indexes can not only greatly simplify data load but also speed up this process.

What is coming next?

All the next parts of this series will be dedicated to b-tree, clustered and nonclustered, indexes. In the upcoming article we will finish our debate about choosing the right table structure. And if you find that the clustered index is the best option, then I will strongly recommend to evaluate with me all the plus points of nonclustered indexes.

Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server. View all posts by Marcin Szeliga