We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month.

For certain tables we are doing daily ETL processes and others we are doing weekly/monthly.

When there is a data import going into a table, is it necessary to drop and recreate the indexes?

Is there ever a point to dropping and recreating indexes or are they automatically updated?

The statistics are set to update automatically.

Thank you so much for your help and guidance.

I got this genius script:

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
       CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM   sys.indexes AS ix INNER JOIN sys.tables t
           ON t.object_id = ix.object_id
       INNER JOIN sys.schemas s
           ON t.schema_id = s.schema_id
       INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
                   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps
           ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
       INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
                   FROM sys.partitions
                   GROUP BY object_id, index_id) pc
           ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10 AND
       ix.name IS NOT NULL

from here:


Do you suggest I run this script daily and based on the findings run the generated code?


If this is cyclical ETL, and you are in a development (i.e. NOT LIVE) data environment, then you definitely should manage your indexes as a part of your load cycle.

I do this for several data sets every month, the largest of which adds around 100 GB monthly to a 5 TB data set.

I have done extensive testing, and from my own experience the most efficient way to load with regard to indexes is:

  1. DISABLE non-clustered indexes, leaving clustered index intact
  2. Perform load of raw into your data table
  3. REBUILD NC indexes

If you only add rows periodically as part of managed ETL, this is the way to go. This also ensures all your statistics are up to date.

For stats, it's important to note that adding 20GB to a 1TB database won't reach the tipping point for a stats auto-update, so you can add a whole month of data without ever updating statistics.

Rebuilding your NC indexes is a good way around this. You may want to also do a clustered index rebuild periodically if fragmentation gets high (depending on your table structure and your clustered key).

| improve this answer | |
  • 4
    You could also update stats as a separate part of your process, mixed in between NC rebuilds if doing those often is too expensive. – Aaron Bertrand Jun 25 '12 at 20:05

For a 1TB+ database, dropping and creating indexes daily would be overkill (even if you recreate only some of them).

If you're worried about insert/update speeds in your table due to the overhead added by index updates, then I recommend two things:

  1. Use surrogate PKs so that clustered index inserts will have minimal overhead.
  2. Profile your DWH and create non-clustered indexes where absolutely necessary.

You will have to live with non-clustered index updates during insert/updates operations.

If you're worried about index fragmentation, then I recommend creating daily jobs (SQL Agent jobs) for rebuilding the indexes. The rebuild period can actually be anything, depends on the fragmentation level. You should notice this in practice and setup the job schedule accordingly.

You can add some logic to the rebuild scripts, depending on the fragmentation level. Some good guidelines you can find here.

Bottom line is that under any circumstances you shouldn't do a full index rebuild on a database of that size.

| improve this answer | |
  • 6
    I have to disagree with a lot of this. It will depend on his use case, but that last line under any circumstances you shouldn't do a full index rebuild on a database of that size. isn't accurate at all. I do ETL on very large databases as my primary job duty and I see huge benefits from disabling and rebuilding indexes. – JNK Jun 25 '12 at 19:29
  • 1
    I wish this applied in my case too. On a slightly over 1TB database running on a production environment, I can barely afford to do a nightly non-clustered index rebuild for several tables with over 500 mil. rows. I have several ETL processes running each night and the last step I do from 3:00 AM is to rebuild indexes. – Marcel N. Jun 25 '12 at 19:39

Read up on Fragmentation

You want to rebuild (not drop/recreate) indexes when the fragmentation is "too high"

I have the IndexOptimize proc in this package scheduled to run every night.

| improve this answer | |

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.