Columnstore Index: Which Columnstore Index is right for my workload?

  • [アーティクル]

While we all understand that columnstore index in SQL Server 2016 can deliver amazing query performance for analytic queries but how do you choose among multiple flavors of columnstore index available. The SQL Server 2016 offers the following choices

  • Clustered columnstore index (CCI)
  • Clustered columnstore index with one or more nonclustered btree indexes (CCI/NCI)
  • Nonclustered columnstore index (NCCI)
  • In-Memory OLTP (i.e. Hekaton) with clustered columnstore index (HK/CS

 The table below lists the workload scenarios and the general guidelines columnstore-usage-table

Note, these are very general guidelines to help you understand the intended scenarios for each flavor of columnstore index. As you can imagine, the use of columnstore index is not restricted to these scenarios. In fact, some of the customer workloads that have been very successful with columnstore index didn’t fit the guidelines above. For OLTP workloads, if Update/Delete are < 10% of overall DML (Update, Delete, Insert) then CCI with one or more NCI may be an option to consider.

I will cover such scenarios in subsequent blogs. Please feel free to contact me or respond to this note to share your experiences with columnstore index. We want to use your learnings to improve columnstore in future releases.

Thanks,

Sunil Agarwal

SQL Server Tiger Team Twitter | LinkedIn

Follow us on Twitter: @mssqltiger | Team Blog: Aka.ms/sqlserverteam