Clustered and columnstore indexes

Question

I just started a new job where we have many tables from ETL processes. I was told not to put any columnstore or clustered indexes on any tables since the server does not have the resources (CPU). So basically everything is heaps. There are noclustered indexes on many tabels. We get data from many source systems and after data has landed we transform the data and combine it...

I just want to hear if it sounds right in some settings to not use any columnstore or clustered indexes. Does it require more CPU to use clustered indexes when tables used for analytics?

Answer 1

Pretty much every implementation decision is a trade off between competing factors. Building a columnstore index is CPU intensive but afterwards queries touching many rows are fast, and updates are slow. Which is most important for your workload, on average? Is there a time window in which that amount of CPU can be consumed without breaking other parts of the system? Is the additional cost repaid in future benefit? What is the problem to which a columnstore is the solution, and has this been addressed in other aspects of the system?

You mention ETL. Often such tables are only processed as scans where every row is touched by every operation. In such cases indexes will slow processing because they must be written in addition to the table.

Likely there are reasons for the current restrictions. Understand why they were put in place. If those circumstances no longer hold (maybe a server version upgrade or better hardware now) try an experiment in a test environment.