I was testing for performance of one of my queries. I was trying different indexes. When I applied a column-store index on my table, the table size reduced by 70-80 %. How is this possible?

  • 1
    This is due to the compression algorithm used for columnstore indexes, which stores columnwize and not row based. this gives you great compression values – Stijn Wynants Feb 13 '17 at 8:37
  • Thanks for reply, but does this affect performance of queries as compared to row based index ? – Mad Frog Feb 13 '17 at 8:40
  • 1
    Please read the documentation about columnstore indexes. As for performance improvements, it depends on queries. – vonPryz Feb 13 '17 at 8:44
  • Columnstore are usually massively faster than rowstore indexes for table scans, but columnstore indexes do not support seek operations. There are ways optimize your columnstore indexes to avoid table-wide scans. Two common approaches are to A) start with a clustered index to optimize for segment elimination or to B) leveraging table partitioning, However, even if you are able to leverage segment elimination, columnstore indexes are still only appropriate for queries that are hitting 100K+ rows (I'd recommend 1M+ rows). – Brian Aug 29 '17 at 21:19

A columnstore arranges the data on disk differently to how a "normal" table does it. The column's values are split into segments of just over one million values. Each segment is compressed. Since a single column's values can show a lot of repitition (think "country code" or "product name") the compression ratio can be significant.

Read performance can improve from several factors. First, only the columns required in the query are read off disk. Second, compression means much less IO for a given number of values compared to rowstore. Third, aggregate functions can be performed in what's called "batch mode," which is optimised for CPU cache utilisation.

Compression is also available for rowstores. My experience is that CPU utilisation increases but IO drops, for a nett improvement in elapsed query time. This was for moderately large databases performing reporting & analytics.

Of course, your mileage may vary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.