Real-time operational analytics has become a viable option in SQL Server 2016 and 2017, especially if you combine columnar storage together with in-memory tables. Here is just a brief overview of two possible solutions for operational analytics—one with on-disk and another with in-memory storage. You will learn more about in-memory tables in Chapter 11, Introducing SQL Server In-Memory OLTP and Chapter 12, In-Memory OLTP Improvements in SQL Server 2017.

The following figure shows the architecture of an operational analytics solution with on-disk tables:

The majority of the data does not change much (so-called warm data), or may even be historical, immutable data (so-called cold data). You use a filtered nonclustered columnstore index over this warm or cold data. For hot data—data that is changed frequently—you can use a regular filtered nonclustered index. You can also use additional nonclustered indexes over the whole table. The table can be organized as a heap or as B-tree.

For in-memory tables, you can implement a slightly different architecture. You have to take into account that nonclustered columnstore indexes on an in-memory table cannot be filtered. Therefore, they must cover both warm and hot data areas. However, they are updateable, and in-memory updates are much faster than on-disk updates.

You can combine a columnstore index with other in-memory index types, namely with hash indexes and nonclustered indexes.

The in-memory operational analytics solution is shown in the following figure: