Please, rebuild your database indexes, now

I will make it quick and to the point: if you are expecting a lot of customers visiting your site tomorrow (and you should) for Black Friday, you should rebuild your database indexes, now.

On average, it will help you to serve more customers and they will be happier with a more responsive, faster website. On best cases it will help prevent catastrophes.

You might think I’m exaggerating the impact. I wish I am, but there is only one truth

This is from a busy Commerce site of today. This specific query relies on an index which was heavily fragmented – 98.xx% at the time we check. The query took 188 ms to complete, and that was growing. We got a go from the site owner to rebuild the index – which took a few seconds. The impact could not be more clear. The query time went almost straight to almost 0 ms, so fast that the query itself disappeared from the most time consuming list. I could not take the screenshot (because I don’t have the permission), but the number of DTUs used for SQL Azure database instance also went down significantly.

Think about it, we just shaved almost 200 ms from each request, by spending just a few seconds rebuilding this index. We lost nothing, yet we gain everything.

So it’s the time for you to start the Maintain Database indexes scheduled job in Admin view. Note that this might take a long time, and in older versions it can result in timeout exception, in such case, extra action is needed, and you might have to run the query directly in SQL Server Management Studio to avoid timeout issues.

The default query does not support building indexes online, but if you are running it yourself, it might make sense to add WITH (OPTION = ONLINE) to each rebuild statement, to reduce the chance of your table being taken offline for rebuilding index.

Happy Black Friday!

//To be continued



5 thoughts on “Please, rebuild your database indexes, now”

  1. Thanks for the reminder, Quan. It is interesting to see how quickly the indexes get fragmented on a busy site. Do you have any suggestion on the interval how often indexes need to be rebuilt? I would like to run it at least once an hour to keep them in shape… 🙂

    1. I would suggest to watch how the indexes get fragmented – it depends on how much data update you do.
      In other hands, I think it would not hurt if you run it daily. if the indexes are rebuilt frequently, then the fragmentation level will be low, and each time will be fairly short and painless.

  2. Note that reorganizing an index and rebuilding an index yields the same end result. It just takes longer time to reorganize a heavily fragmented index than it takes to rebuild it.

    What I’m saying is that if you intend to schedule something to combat fragmentation proactively, it should be a _reorganize_ task.

    1. The scheduled job takes 30% and 10% as thresholds for rebuild and reorganize. These are values recommended by SQL Server Book Online, and they are configurable. Unless you have very good reasons to change those thresholds, you can just schedule the job and let it do what is best.

Leave a Reply

Your email address will not be published. Required fields are marked *