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
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… 🙂
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.
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.
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.
Hi Quan,
Thanks for great posts on performance! I have one quick question, above you write “you might have to run the query directly in SQL Server Management Studio to avoid timeout issues.” How can I go about doing this? We are currently experiencing this timeout upon running this in dev and test-environments, but it’s possible to make the job succeed by running it multiple times. Can this result in any errors?
Hi Michael,
The time to run the index maintenance job depends on how fragmented your indexes are. If they are just lightly fragmented then the job will run very quickly. By running the script in SSMS, you reduce the fragmentation level of your indexes, and that makes subsequent runs faster. Multiple runs should not result in any errors. Of course you should not run it every minute, but once a day is fine.
Ok thanks! I was interested in how to run this script in SSMS, and where to find it?
The errors was due to timeouts i.e. “Execution Timeout Expired” because the jobs never been run before, unfortunately. However, running them back to back (15 times) finally made the job succeed.
If you successfully ran it, you don’t have to run the script in SSMS. Just set up the scheduled job to run periodically (daily?) and you should be fine.
If you are in a later version (11.3, I’m not sure the exact version), you can try to set episerver::DataBaseIndicesJobCommandTimeOut in appSettings to increase the timeout value (in seconds)
Thanks for providing great content.
In your experience, how much does running the rebuild/reorganize degrade performance on a commerce site? Is it likely that the script would take the site down completely?
We’re on Commerce 8.11 so we’ll run scripts directly in SSMS, as the indexing job does not exist in our Episerver version.
Unless your database indexes are heavily fragmented, the script will complete in just a few minutes. A best practice from many implementations is to run the scheduled job daily at lowest traffic. I have never seen it bring down a site, fail to run it, however, did.
If you are on 8.11, upgrading to 13.x would bring significant benefits outside of just having the job to run
Thanks a lot! Yes, I have read about the benefits of upgrading our solution, so it’s a priority.