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