1

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?

0
3

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.

1
  • Thanks. Yes I have to try to understand the reasons more deeply and increase my knowledge. – xhr489 Sep 9 '20 at 21:26

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.