How long does reindexing take on SQL Server?

Question

I've inherited a very large and very messy database recently and need to clean it up. To give some idea of size, the primary database currently contains 3 big tables each with around 300 million rows and takes up about 225GB storage space. Over 5 million rows are added each day.

Because of a critical disk space shortage (predecessor did not archive old data or manage it's size at all), I was forced to delete about 280 million rows from the largest table. This process took over 25 hours to complete and the database needed to be cut off from customer-facing applications during that time.

Now, I need to reindex the table because selects and inserts take a very long time. However, I can't just take the database offline indefinitely, I need to be able to estimate the amount of time needed to perform the reindex. I have never reindexed a table so large before, so I don't really have any good reference points to draw upon.

The primary table includes a clustered, monotonically-increasing primary key, and a non-unique non-clustered key as well. I have plenty of disk space available to perform the reindex.

So my question is this: About how long do people thing this will take me? What is a good rule of thumb for estimating reindexing time?

Answer 1

No way to estimate how long it will take - so many different things would have an impact. Similar to what Paul writes about the length of a CHECKDB, very similar things would come into play with indexing and more. The best answer would be how long has it taken in the past? If you can't determine that, next best option would probably be to try it on a similar non-production environment, but even that won't necessarily match (i.e. concurrency concerns, etc. would be different).

As a side note, you may want to look into ONLINE INDEX OPERATIONS in Sql 2005 and above...I can't add a second hyperlink, but google "online index operations sql server" and click the top link.

Answer 2

Have you actually checked how fragmented your tables / indexes are? Try running the following query against the database(the query runs on SQL2005 or above). Note this query will impact your server and should be run at a quiet time:

SELECT    OBJECT_NAME(i.OBJECT_ID) AS TableName,
        i.name AS IndexName,
        indexstats.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') indexstats
        INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
    AND i.index_id = indexstats.index_id

You can selectively reindex the tables/indexes which are most fragmented.

Answer 3

Any database operation is highly dependant on the hardware you are running it on.

Since you say you cut out a very large amount of rows already, it shouldn't be a problem to run it during off-peak hours.

You could set up replication and offer that database to your customers while you clean up the old one and set it in read-only so they can still get any data they might need.

Answer 4

Once you get the table rebuilt initially, you'll want to set up maintenance jobs to periodically defrag/rebuild the indexes. Michelle Ufford aka @SQLFool has an excellent set of scripts for this:

http://sqlfool.com/2009/06/index-defrag-script-v30/

You set the thresholds of when you want to defrag vs rebuild. It automatically detects which indexes it can rebuild online, and does that as well, giving you some uptime benefits.

Be aware that index operations can throw a ton of activity into the transaction log, which can really slow down database mirroring and transaction log backups.