Index Size and Table size

Question

All:

SQL Version: SQL 2014

My table size is 2.5 TB, it includes 10 indexes. The data size is 850 GB and index size is 1650 GB. The all 10 indexes fragmention is greater than 70%. My maintenance window duration is 4 hours. I think it's difficult (or not possible) to complete index rebuild operation for this table within MW. Please suggest which approach isthe fastest way to reduce the fragmention level in 4 hrs MW?

• create new table with original table structure without indexes and start data copy from original table to new table. Then rename the table and create indexes. will this appraoch work for me?

After index rebuild, the index size will be same (no reduce in the index size). Is this my understanding is correct?

Answer 1

create new table with original table structure without indexes and start data copy from original table to new table. Then rename the table and create indexes. will this appraoch work for me?

You could test various strategies on a test server, but I would be surprised if you would find that this strategy to be a winner. You are essentially rolling your own index rebuild, but in a more complicated way. And with bigger risk that you mess things up.

Keep in mind that even if your maintenance window does not permit you to rebuild all indexes, you can rebuild some indexes, and keep the other. If you build a new table, at which moment are you going shift it in? What if you only have six of ten indexes in place by then? In that case, there could be queries that suffer badly when there are no good indexes for them.

You could also ask yourself: does the fragmentation as such matter? If you still have spinning disks, it does, but it is questionable whether logical fragmentation matters when data is on SSDs. Tibor Karaszi investigated this in a number of blog posts: http://sqlblog.karaszi.com/category/indexes/ and the outcome was not really conclusive.

After index rebuild, the index size will be same (no reduce in the index size). Is this my understanding is correct?

As they say, it depends. To know, you need to run sys.dm_db_index_physical_stats with the last parameter as DETAILED. and chek the column avg_page_space_used_in_percent. Say that this value is 50 % for the leaf level. If you rebuild that index with a fill factor of 100, that index will be about half the size after the index rebuild.

And in my opinion, this value is more important than the logical fragmentation. If this value is low, this means that you fill up the buffer cache with nothing at all.

...but it cuts both ways. If there is heavy activity with insert and updates, a 100 % full index is likely to see quite a bit of page splits which will hamper update performance. And if this is a concern, it may be better to rebuild to a lower fill factor to have some space to grow in.

Finally, I should add that there are some options for index rebuilds. If you have Enterprise Edition, you can rebuild indexes online, which means that the table is not locked while the operation is running. This opens for a possibility to rebuild indexes outside maintenance windows, but as the index rebuild takes resources it may still not be permissible.

Later version have introduced resumable index rebuilds, so that you can start the index rebuild in one manitenance window, stop it and continue in the next. But this is not available on SQL 2014.

Answer 2

I am going to say something that may be obvious, but it hasn't been mentioned before:

If your concern is about index fragmentation, have you considered doing an index REORGANIZE instead of a REBUILD? The reorganize should be quicker. It's not as good as a rebuild, since it will only "fix" the leaf level of the index, but it may be sufficient depending on the goal that you want to reach.
You may get away with just doing a reorganize during your "normal" maintenance window, and then perhaps allocate a bigger window for rebuilding indexes, maybe on weekends or on days of lower activity.