SQL Server 2012 notes Sharing-9: Understanding Columnstore Indexes

Advantages and Usage Scenarios

SQL Server in-memory Columnstore indexes store and manage data by using column-based data stores and column-based query processing. A columnstore index is appropriate for a data warehouse workload that primarily performs bulk load and read-only queries. Using Columnstore index archiving can improve query performance up to 10 times times compared to traditional row-oriented storage, providing up to 7 times times more data compression than using uncompressed data sizes.

Comparison of SQL 2012 and SQL 2014 Columnstore indexes

In SQL Server 2012, once you enable Columnstore indexing, you will not be able to perform a change write operation on a datastore-indexed data store, which means that the Columnstore index is appropriate for querying read-only, or basic, archival data that does not change. Once you add a Columnstore index to a table, the table becomes read-only. If we need to do an INSERT, Update, delete, and so on, we need to disable the Columnstore index first, then table operations, and finally rebuild the Columnstore index. This feature makes Columnstore indexes more suitable for data warehouses where static data is stored.

However, this situation has improved in SQL Server 2014. This technology can increase the query performance of the data warehouse several times. Unlike normal indexes or heaps that use the b-tree structure (in the form of rows), Columnstore indexes store data as columns and use active compression to reduce disk I/O required for query requests. However, there are still a number of restrictions on Columnstore indexes in SQL Server 2012, which is enhanced in SQL Server 2014 and breaks the limitations of previous versions. when you create a columnstore index in SQL Server 2014, the table's write functionality is not affected, which means that users can issue inserts, UPDATE, delete, and so on in a table with a Columnstore index. users in SQL Server 2012 can only create nonclustered Columnstore indexes in the table, including all the columns in the tables in a single index. SQL Server 2014 adds support for creating clustered Columnstore indexes. Users can still create a nonclustered Columnstore index as in SQL Server 2012, but this nonclustered Columnstore index is a read-only query and cannot be updated. Only clustered Columnstore indexes can be updated.

Resources:

Http://tech.it168.com/a2013/0829/1527/000001527798.shtml

Disadvantages of row storage

SQL Server typically extracts all index pages when processing a query, including the portion of the query that is not used, that is, reading records for all columns, even if some are not needed. To read out unnecessary data, not only waste a lot of memory, but also increase I/O, the overall performance of the system has a great impact.

The benefits of column storage

Suppose we have a table with 3 columns of data, and these 3 columns are int, varchar, and bool from left to right, and the table has 100 (row) records. For the first column of data that is of type int, the compression algorithm is easy to apply and the compression rate is very high. This will also indicate that we do not have to access all the columns of the table and only need to access a subset of the related columns of interest, which, on the other hand, can reduce the I/O to the disk and raise the cache, so disk storage is more efficient and less leveraged, just like index maintenance.

Columnstore Index Query

Figure I (Microsoft Official)

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" Http://img1.51cto.com/attachment/201406/9/639838_1402281707Vjcp.png "height=" 268 "/>

Figure II (Source network)

Source: http://www.uml.org.cn/sjjm/201212141.asp

650) this.width=650; "title=" clipboard[1] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [1] "src=" http://img1.51cto.com/attachment/201406/9/639838_1402281709Fa2V.png "height=" 379 "/>

Three reasons why Columnstore indexes can provide faster queries (source network)

The first reason: because the column data in the index is compressed, it takes up less space, so SQL Server only needs to scan fewer pages.

For the second reason, because only fewer pages need to be scanned, SQL Server can keep them in memory, greatly increasing the likelihood that the data will stay in the memory buffer. Therefore, in those systems with large memory, you will see a greater performance difference, especially if the index is scanned multiple times and the corresponding row store index cannot be loaded into memory.

Third, SQL Server only needs to extract the index pages of the columns used in the query, and the regular indexes need to fetch all the index pages, including the ones that are not used in the query. As a result, you get the benefit again, fewer pages need to be processed, faster results, and SQL Server is more likely to keep the column cache in memory buffers.

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!