When should indexes be dropped and recreated?

Question

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:

http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx

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

Answer 1

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).

Answer 2

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.

Answer 3

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.