Clustered colunstore index vs Clustered date index on partitioned table

Question

Clustered colunstore index vs Clustered date index on partitioned table

stricknyn

SSCertifiable

Points: 6403
More actions
July 15, 2023 at 2:12 am

#4235531

Hello everyone. Was just looking for some input on where I may get the best performance between implementing a clustered columnstore index or a clustered index on a date (ServiceStartDate) on a table also being partitioned by the ServiceStartDate

Since I can only have one clustered index on the table I'm trying to figure out which direction I should go to get the best performance. A few details on the table.
The table has 600 million + rows and about 30 columns.
The main querying would utilize ServiceStartDate in the where clause
Table is being partitioned by month and each partition in the current year (most queries are current year) has about 4 million records with the remaining in the 1st partition as an archive.

Trying to gauge the best route to go.
Thanks for any help provided!
Strick

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Dennis Jensen Mr or Mrs. 500 Points: 587 More actions · Answer 1

Okay I do not recall all the details but a word of warning. With a Partitioned Table you have to include that partition within everything you do or you totally kill the benefits of the partition scheme.

That being said I do not recall exactly but you should look into how a Columnstore works in regards to a Partitioned Table as I think it might one of those that breaks that scheme.

As for the clustered index on the partitioned table that partition should be your clustered index since I think it either is or should be.

frederico_fonseca SSCoach Points: 16028 More actions · Answer 2

Dennis Jensen wrote:

Okay I do not recall all the details but a word of warning. With a Partitioned Table you have to include that partition within everything you do or you totally kill the benefits of the partition scheme.
That being said I do not recall exactly but you should look into how a Columnstore works in regards to a Partitioned Table as I think it might one of those that breaks that scheme.
As for the clustered index on the partitioned table that partition should be your clustered index since I think it either is or should be.

Columnstore and partitioned tables work just fine - I have a few of those (rather big record size - 30Bilion rows with a 5 billion growth per quarter) and no issues at all.

when querying using the partition column, both partition elimination and columstore segment work as expected in terms of filtering.

Dennis Jensen Mr or Mrs. 500 Points: 587 More actions · Answer 3

Thanks nice to know, but like I said the partitioning can be a bit sensitive in regards to other things so do becareful with that aspect.

Priyanka Chouhan SSC Rookie Points: 29 More actions · Answer 4

As you wanted to know the best performance on the clustered column index or index on the date

Clustered column index organizes data into column segments for compression and improving the query performance on large data sets. It supports workloads of queries and involves scanning and aggregating the portion of the table,

One limitation for it is that it is a single-row lookup.

Clustered Index shows the physical order of data in the table for the specific column, TO look for point lookups and queries that will benefit the data retrieval.

It's advisable to implement clustered index on the ServiceStartDate column here as it will be suitable for filtering and retrieving data access.

As your given pattern shows ServiceStartDate and this table is already partitioned by month, the clustered index will be more suitable. This will help you to improve your query performance and benefit query filtering by ServiceStartDate.

So it's important to test both indexing options with relative queries to check the performance of the use cases you are using.

Dennis Jensen Mr or Mrs. 500 Points: 587 More actions · Answer 5

Okay I checked with my colleague as I remembered we looked into this in MS SQL so here we go. One cannot use a Columnstore in conjunction with a Table Partition as they are both table structure based and as such not compatible.

frederico_fonseca SSCoach Points: 16028 More actions · Answer 6

while that may have been the case in older versions, actual versions DO ALLOW it for sure.

partitioned_columnstore

ratbak Ten Centuries Points: 1334 More actions · Answer 7

That's not true. They are used somewhat commonly together for large data warehouses.

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-2016

https://www.brentozar.com/archive/2020/09/how-to-create-a-table-with-a-partitioned-clustered-columnstore-index/

https://www.nikoport.com/2017/12/28/columnstore-indexes-part-116-partitioning-specifics/

Dennis Jensen Mr or Mrs. 500 Points: 587 More actions · Answer 8

Okay well then I will share this information with my colleague as that was not his understanding and of the two of us he has dealt with that far more than I have. Still thanks for that corrective feedback. Will go over what was supplied and discuss it with my colleague perhaps he did not fully communicate what he meant.