Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a nightly ETL process that involves data from many different sources being flattened, and loaded into a completely denormalized table on SQL Server 2008 for reporting. The clustered index on the table consists of a composite key of four columns.

Due to where the data is coming from, there really isn't any way to load the data in the same order as the clustered index. Keeping the index active during load or dropping/rebuilding it after the load both take more time than the load itself without an index. On the other hand no clustered with proper non-clustered indexes can still result in some reporting queries taking hours to process.

The only things I can think of to speed this up would be partitioning and throwing more hardware at the problem. Am I missing anything else that would help?

share|improve this question
    
Have you tried loading the data into a staging table and then loading it in the main as per clustering key ? –  Kin Apr 23 at 23:07
    
We haven't, however the table is being rebuilt in full each night, mainly due to certain dimensions in the source data that can change from day to day. The alternate would be an incremental approach with a lot of updates going on to existing records. –  John Apr 23 at 23:08

1 Answer 1

up vote 1 down vote accepted

There are few ways to work around this problem:

As you mention, partitioning will help. If you partition on the leading column in the index and make sure all partitions are equally sized, you can gain super scale. However, the total CPU required to build the index wont be much lower if you partition. But it will save you memory (if you have a slow I/O system, this can be useful)

If this is a traditional data warehouse fact table, you might be better off with a columnstore index

Instead of doing a mega update - which is slow. Consider this pattern:

  • Load the changes you want to make into a staging table with the same schema and cluster index as the main table
  • Join the staging table with the old table and use INSERT INTO a new table with a cluster index. Enabled Trace Flag 610 while doing this.

This way, you can keep the benefit of the old table being sorted and avoid resorting all the rows (only the ones that have changed).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.