The Limits of Transactional Data Storage

Tables that contain millions or billions of rows of analytic data do not scale well when implemented as classic transactional tables. To fully understand why this is the case, it is helpful to compare how data is stored in both transactional and analytic tables. The following example table contains five columns. Figure 3-1 shows how data is stored in an OLTP (rowstore) version of this table.

In a clustered rowstore index, each row is stored on pages sequentially, one row after another in the order prescribed by the clustered index. If many columns from a single row or small subset of rows are required by a query, then this is an exceptionally efficient storage structure. For example, retrieving all columns from row 1 through row -5 would require low effort as the data is contiguous and ordered. Figure 3-2 highlights the data needed to satisfy this query.

Transactional queries write single rows or small ranges of rows that often correlate to a lookup on a single index. Here, a numeric identity is used to filter out five rows of interest from a larger table.

The basic unit of SQL Server storage is the page. A page is comprised of 8 kilobytes of data. Pages contain all data and index storage for a given table. If a single row is required for a query, then the entire page it is stored on will be read into memory, even if the remaining data on the page is not needed. Therefore, transactional queries rely on clustered or nonclustered indexes to ensure that index seeks can return data in an ordered fashion, such as the example in Figure 3-2. In this scenario, reading those five rows will not require much more effort, even if the table grows to be significantly larger.

Analytic queries, though, are quite different. They often aggregate a select few columns, but do so across many rows. Consider a typical OLAP query against the example table where a single column is aggregated across a large portion of the table that happens to include all of the rows shown here. Figure 3-3 shows how this would look.

Even though only a single column was summed, any page that contains values for that column will need to be read into memory. Even a covering nonclustered index would still necessitate reading data for each row, though the number of pages read can be reduced in that fashion.

Now consider a larger table with 50 million rows that is stored on 250,000 pages using a clustered rowstore index. The transactional query demonstrated in Figure 3-2 would still only read 5 rows that are stored consecutively and could therefore ignore most of the remaining 249,995 rows and the pages they are stored on. Additional rows would be read that happened to be stored on the same page(s) as those five rows, but the added burden of that data is measured in kilobytes and is comparatively insignificant.

The analytic query presented in Figure 3-3 aggregates only a single column, but does so across many rows. If the requested order data spans one-fourth of the table, then processing this query in the transactional table would force SQL Server to read about one-fourth of the pages in the table, since the values for column 2 are dispersed within each row throughout the table. Regardless of the filter used that reduces the row count needed for the query, every page in the range specified by the filter would need to be read.

As an analytic table continues to grow and spans billions of rows and/or terabytes of storage (or more), the ability to read large swaths of its data becomes too slow and resource intensive to be realistic. A better solution is needed that allows analytic queries to read column data without the rest of the underlying columns being brought along for the ride.

Introducing Columnstore Indexes

The technology needed to solve this problem has existed in some form for a long time. Applications such as SQL Server Analysis Services and PowerPivot have used columnar data storage formats for years, but their implementation was hidden from view of the user.

While this may sound like a sales pitch, there are no exaggerations here. Comparing a large analytic data set stored in a classic rowstore clustered index vs. a columnstore indexed table yields vast differences in storage and performance that will be demonstrated and quantified throughout the remainder of this book.

Note that all demonstrations of columnstore indexes in this book are tested on SQL Server 2019. Those running an earlier version should test thoroughly before implementing any suggestions in this book as the features available may be different.

Figure 3-4 illustrates how data stored in a columnstore index is stored using the same table from Figure 3-1.

Note the critical difference: Data is ordered by column rather than by row. Each value for column 1 is stored together in a single structure, whereas each other column is stored in its own sets of pages. In the rowstore table shown in Figure 3-3, a single column was aggregated, but every page had to be read in order to return data for that one column.

Figure 3-5 shows the same query against an analytic columnstore indexed table.

Because data is grouped physically by column, it is no longer necessary to read the entire table to return a single aggregated column. The other four columns can be ignored. This greatly reduces reads against storage systems and reduces memory usage as less pages need to be read into memory. For each new row that is inserted into this table, only a single additional value for the second column needs to be read.

A significant benefit of this data structure is that compression becomes more effective. A set of values for a single column will tend to have more repeated values that will more easily compress, whereas values from different columns are less likely to overlap and compress well. Better compression means more data can fit on pages, saving additional storage and memory.

An important clarification on columnstore indexes is that they are not “just another index.” They are not comparable to nonclustered rowstore indexes, XML indexes, spatial indexes, memory-optimized indexes, or other types of indexing included in SQL Server. They comprise a unique architecture that provides benefits well beyond what a typical index can deliver.

Note that throughout this book, when not otherwise specified, all demonstrations and discussion will reference clustered columnstore indexes. Chapter 11 will delve into non-clustered columnstore indexes in more detail.

Benefits of Columnstore Indexes in SQL Server

There are many reasons why columnstore indexes can be an ideal solution for storing large analytic data. They cross a variety of areas from cost to convenience to speed and illustrate how a seemingly simple feature can provide exceptional value with a low time and resource cost to implement. This is not a complete list of the benefits of columnstore indexes, but highlights the keys to performance and efficiency that make them attractive for use with analytic data. The remainder of this book will dive into greater detail about the benefits and optimal use cases for columnstore indexes.