Rebuild Indexes vs Update Statistics

Question

Rebuild Indexes vs Update Statistics

Parsons, Tim 20

I have a 165TB database on SQL2022 server and is part of a DAG.

ETL (load/delete) is done every night on 35k tables and the largest table average load is 5-6 million rows.

The largest table is 6.5TB (30 tables over 1TB) and has six Non-Clustered indexes with 15%-55% fragmentation.

We are experiencing high I/O, poor query plans, high storage usage and ETL times progressively taking longer.

What would be best for performance: Updating statistics or rebuilding indexes and why?

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Erland Sommarskog 138K MVP Volunteer Moderator

The short answer is: It depends.

I realise that this is not the answer you want to hear, but there are rarely short simple answers to situations like this. Typically, you have to dig into one query at a time to see how it can be improved by better indexing. Since you are loading lots of data, breaking up the operations in chunks can also help, if done in the right way.

As for your question: Keep in mind that rebuilding an index updates the statistics the index as well, and with fullscan. That is, you get statistics that are based on all the data rather than just a sample. So this is a bad thing to do:

ALTER INDEX ALL ON tbl REBUILD
UPDATE STATISTICS tbl

The second statement will replace fullscan statistics with sampled statistics which are less accurate. (But in many cases, they are still perfectly satisfactory.)

Rebuilding all indexes on 35000 tables may take more time than you have to for the operation. One thing to keep in mind that fragmentation in the sense that pages do not come in order on the disk matters a lot less today with SSDs than what it did in the days of spinning disk. But another thing is how much data there is on every page. If every page is half-full, this means that you 50% of your buffer cache contains - vacuum. I don't which sort of fragmentation you are talking about, but I suspect that it is the former, because that is easier to compute for SQL Server.

Parsons, Tim 20 Reputation points

2026-10-13T18:34:37.64+00:00

Thank you Sir and I understand there are too many nuances to list in this forum. With a db and tables this size, I don't believe that being told (not from you) that rebuilding indexes is no longer needed/required and that updating statistics is sufficient to keep the system functional.
Erland Sommarskog 138K Reputation points MVP Volunteer Moderator

2026-10-13T18:44:49+00:00

Thanks for understanding the limits of a forum like this. Keeping statistics up to date is more important than rebuilding indexes, but if the indexing is not optimal neither will help.

If you have an operation on a not-so-large table (< 50GB), you could try an index rebuild on that one to see the effect. For a table of a size of 50 GB, expect at least an hour for the rebuild.