Wednesday, 28 October 2015

The Endlessly Rebuilding Sitecore Index

This quick post provides information about the sitecore_suggested_test_index, which I have found to be problematic in some large Sitecore 8.x implementations.

As the the name implies this index supplies data for multivariate test recommendations. It's content is calculated from data in the reporting database, and it's updated synchronously whenever an item in the master database changes.

However, the reporting database gets updated constantly, which means that the suggested test index is almost always out of date. So to minimize the impact Sitecore keeps it fresh by running an hourly scheduled task  to rebuild it. You can find the item representing the task in Sitecore/System/Tasks/Content Testing. It's named "Rebuild Suggested Tests Index".

The problem is that the index covers the entire content section of the Sitecore tree and rebuilding it is quite resource intensive. I've seen the scheduled task take as long as 30 hours to complete in a large implementation, maxing the SQL server CPU for the entire period.

Sitecore support were helpful when I raised this issue. They acknowledged that the index rebuild can take a long time and that it's is likely to hit the SQL server hard. Unfortunately, they weren't able to provide a fix. At present, it seems that the only practical way to keep sitecore_suggested_test_index up to date is via the scheduled task.

Right now most of our customers aren't ready to start using M/V test recommendations, so we've been able to safely deactivate the scheduled task. But if you intend to use this functionality you should think about whether you architecture is equipped to deal with it.

If you've encountered and solved this problem, please leave a comment. I'd love to hear what you came up with.