January 19, 2017 at 12:28 pm
Hi,
Would anyone please explain or point me to resources that make it very clear how to choose between the 2 types of columnstore indexes (Clustered & Non-Clustered)?
I'm trying to determine what's the best way to take advantage of them on a few large tables used for heavy reads in an OLTP (sql server 2016).
I've read a number of msdn articles and believe I understand the differences in their features, storage, limitations. I haven't been able to find much info/ examples on choosing between the 2 and In some cases it seems to me that either would work.
Thanks!
SN
March 6, 2017 at 1:40 am
This was removed by the editor as SPAM
March 6, 2017 at 1:45 am
JasonClark - Monday, March 6, 2017 1:40 AMplease have a look on here: http://www.sqlserverlogexplorer.com/overview-of-cluster-and-noncluster-index/
The question was about clustered and nonclustered *columnstore* indexes, and the link covers normal (rowstore) clustered and nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2017 at 1:48 am
SN, I'm not all that clued up on columnstore indexes (yet), what I read recently is that clustered columnstores are mostly for analytics systems (OLAP-style), while nonclustered columnstore indexes are more useful on normal clustered indexes on OLTP systems, when you need reporting-type queries on the OLTP tables.
I would encourage you to read through the stairway on columnstore indexes , if you haven't already - http://www.sqlservercentral.com/stairway/121631/
I'll ask the author of that to drop by here when he has time, correct my statement if necessary
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2017 at 7:44 am
GilaMonster - Monday, March 6, 2017 1:48 AMSN, I'm not all that clued up on columnstore indexes (yet), what I read recently is that clustered columnstores are mostly for analytics systems (OLAP-style), while nonclustered columnstore indexes are more useful on normal clustered indexes on OLTP systems, when you need reporting-type queries on the OLTP tables.I would encourage you to read through the stairway on columnstore indexes , if you haven't already - http://www.sqlservercentral.com/stairway/121631/
I'll ask the author of that to drop by here when he has time, correct my statement if necessary
Thanks for pointing me to this question, Gail!
The next level of the stairway is actually completely devoted to the subject of which type of columnstore index to choose for which situations. Both have their pros and cons. This level has already gone through review and has been submitted for publication, but I have not yet heard a go-live date.
So because you probably want the information NOW, here is a very short summry of the considerations.
1. On SQL Server 2012, you have no choice. If you want to use columnstore, it's going to be nonclustered.
2. On SQL Server 2014, you do have a choice. The most relevant differences are:
A) Nonclustered (still) makes the table read only, requiring cumbersome workarounds when loading new data; clustered does not have this limitation (but at the price of fragmentation and other issues building up over time).
B) Clustered columnstore can not be combined with other (rowstore) indexes, nonclustered columnstore has no such restriction, This can be relevant when your workload mixes large scale analytics (which benefit from columnstores) with single-row or small-set lookups (which usually perform better when you have a suitable rowstore index). This is what Gail already alludes to in her reply.
C) For a table with a clustered columnstore index, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints are not supported. Nonclustered columnstore does not have this restriction.
D) Clustered columnstore always includes all columns; nonclustered allows you to specify. This becomes relevant if a table has one or more columns with a data type or an other property that is not supported for columnstores.
E) Clustered columnstore *replaces* original table with compressed data, reducing storage size. A nonclustered columnstore index is an *extra* copy of the data. (The copy is compressed so it doesn't double the size, and you might be able to drop some of your existing rowstore indexes so you might end up saving data - but the total storage size of a table with nonclustered colunstore index will always be far more than he same table with clustered columnstore)
On SQL Server 2016, the options change yet again. I will have a short summary (much too short!) on the changes in SQL Server 2016 in the final level of the stairway (level 13), which is currently in the hands of my editors,
A short summary of the most important shows that a lot of the differences that applied in SQL 2014 have been lfited, and some new differences take their place:
- Read-only restriction for nonclustered columnstore indexes (point A above) is removed
- Clustered columnstore can now also be combined with other indexes (point B) and does allow constraint (point C)
- Less restrictions on columns in index (reduces impact of point D)
- New option introduced to filter a columnstore index (applies to nonclustered columnstore index only)
- New option introduced to create a columnstore index on a memory-optimized table (technically called a clustered columnstore index but it is not the same structure as a clustered columnstore on a disk-based table)
- New option to delay the compression of new rows. For systems where new rows are modified a few times in the first 30 minutes and then hardly ever, this can benefit performance by ensuring that the overhead of updating data after compression won't impact performance (applies to nonclustered columnstore index only)
If you want to know more about columnstore indexes on SQL Server 2016, the best place to go would be Niko Neugebauer's amazing blog series at http://www.nikoport.com/columnstore/
March 8, 2017 at 12:41 pm
OLTP to me implies insert, update and deletes. Updates and Deletes kill a columnstore pretty quickly, requiring the index to be rebuilt. With a clustered columnstore (CCI), your table is not available while the rebuild is running. NCCI can be rebuilt without taking the table offline, although your reporting may be impacted.
If your OLTP workload for these tables only does inserts, you can use a CCI. If you have any Updates or Deletes, you must either plan for the occasional CCI rebuild or use a NCCI instead.
Wes
(A solid design is always preferable to a creative workaround)
April 27, 2018 at 6:51 am
great article about differences and how to choose between NCCI and CCI
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/18/columnstore-index-differences-between-clusterednonclustered-columnstore-index/
and more about performance impact:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance?view=sql-server-2017
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply