Columnstore Indexes in SQL Server
If you are not using columnstore indexes in your SQL Server data warehouse, you may be missing some valuable low hanging fruit. Opticity, a software company with which we do business, saw their query performance go from six minutes to two seconds with the use of columnstore indexes. Columnstore indexes can help your organization realize hugely improved performance for data warehouse queries through xVelocity in-memory.
Some background on columnstore index:
- A columnstoreindex is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. Columnstore indexes work well for mostly read-only queries that perform analysis on large data sets. Often, these are queries for data warehousing workloads. Columnstore indexes give high performance gains for queries that use full table scans, and are not well-suited for queries that seek into the data, searching for a particular value.
Columnstore index benefits:
- Columns often have similar data, which results in high compression rates.
- High compression rates improve query performance by using a smaller in-memory footprint. In turn, query performance can improve because SQL Server can perform more query and data operations in-memory.
- A new query execution mechanism called batch-mode execution has been added to SQL Server that reduces CPU usage by a large amount. Batch-mode execution is closely integrated with, and optimized around, the columnstorestorage format. Batch-mode execution is sometimes known as vector-based or vectorized
- Queries often select only a few columns from a table, which reduces total I/O from the physical media.