Description

On environments with large amounts of data in the Collection.Shards database, [xdb_collection].[GetContactFacetsChanges] and [xdb_collection].[GetInteractionsChanges] stored procedures use large amounts of CPU resources.

The xConnectSearchIndexer service uses these stored procedures to get the database entities that have been changed recently to index the changes. By default the changes are retrieved 4 times a second for Sitecore XP 9.0 and every 2 seconds for XP 9.1+.

Solution

To decrease CPU load and resources consumption, consider the following solutions:

• Decrease the frequency of indexing. This decreases the load coming from the previously-mentioned stored procedures. However, this might cause more changes to be indexed at once. Therefore, it needs to be adjusted considering the solution specifics. See the <Frequency> setting in the \App_data\jobs\continuous\IndexWorker\App_data\Config\Sitecore\SearchIndexer\sc.XConnect.SearchIndexer.xml file.
• Move Collection databases to a dedicated server (or servers).
• Scale up your SQL server.