Description

In certain situations, content testing functionality may cause high CPU utilization on SQL servers. This can also cause Sitecore XP to run out of available SQL connections and the following exception may arise:

Exception: System.InvalidOperationException
Message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
Source: System.Data
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   ...

This issue typically occurs daily when Sitecore XP attempts to rebuild the sitecore_suggested_test_index. This search index is used to make suggestions for content testing.

In Sitecore XP versions prior to 8.0 Update-4 (rev. 150621), the same issue may happen due to the suggested test index updates that occur after modifying content items.

You can find out more about suggested tests in the following article: Test lists.

Solution

To solve the issue, try one or more of the following solutions:

1. Disable index updates for item changes (for XP 8.0 update 3 and earlier)

Because sitecore_suggested_test_index is used only for reporting purposes, there may be no need to update it after every item modification. This is the default behavior for Sitecore 8.0 update 3 and previous versions. Instead, you may want to rebuild the index periodically, for example, once a day as configured by default for the rebuild task.

To disable unnecessary index updates, comment out the following lines in the appropriate configuration file (either \App_Config\Include\ContentTesting\Sitecore.ContentTesting.Lucene.IndexConfiguration.config or \App_Config\Include\ContentTesting\Sitecore.ContentTesting.Solr.IndexConfiguration.config):

<strategies>
 <strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/syncMaster" />
</strategies>

2. Disable the periodic index rebuild

After disabling daily maintenance of the sitecore_suggested_test_index, the suggested tests list shown in the Experience Optimization and Experience Editor applications will no longer contain relevant data. However, this eliminates the SQL server load caused by maintaining the index.

If this is acceptable, we then suggest removing the /sitecore/system/Tasks/Schedules/Content Testing/Rebuild Suggested Tests Index item from the master database. This item is responsible for periodic index rebuilds.

3. Reduce the frequency of index rebuilds

If you need the suggested tests list functionality, keep the regular index rebuilds enabled. Instead, you can reduce the frequency of index rebuilds.

To do this, alter the scheduled task responsible for rebuilding the index and in the /sitecore/system/Tasks/Schedules/Content Testing/Rebuild Suggested Tests Index item in the master database.

The exact schedule depends on the amount of site visits and the frequency of using reports.

4. Reduce the index's scope

Like any other content search index, the suggested tests index contains an item crawler that retrieves items from a database.

The crawler includes a root property that defines the content item to start indexing from. The default value, /sitecore/content, includes all content items.

It is possible to limit the suggested tests to a smaller subsection of the content tree. To do this, the root element of the crawler should be adjusted correspondingly.

This should decrease the load on the SQL server during the index rebuild process.

The index definition also includes template filters. You can further limit the number of items included in the suggested tests index by adjusting the allowed and excluded templates.

This can be useful if there are templates that are never tested.

5. Implement additional database index

You can also improve the performance of SQL queries executed during the suggested test index updates by optimizing SQL server indexes. To do this, create an additional non-clustered index on the Fact_PageViews table of the reporting database using the SQL script below:

CREATE NONCLUSTERED INDEX [<name>] ON [dbo].[Fact_PageViews] ([Date]) INCLUDE ([Views],[Value])

After adding this SQL index, make sure you specify regular maintenance intervals for this index per suggestions from the CMS Tuning Guide.