Sitecore poor Index Update performance linked to missing Index in the Links database

Suddenly my index updates took forever to finish. I mean, one index update would take 2 minutes. And I have a lot of index updates. A lot.

UPDATE: 2022-01-19: Sitecore have suggested the same changes to the links database in KB1000639 – How to reduce query execution time for the Link Database.

Running jobs in Sitecore
Index Updates in Sitecore

After some panic and some SQL debugging we were lead to some long running SQL statements that looked like this:

SELECT *
  FROM [Sitecore_Web].[dbo].[Links]
  where TargetItemID = '562F77DD-6C00-4BE1-AF0E-9F9EEAA8CCEF' 
  and TargetDatabase = 'master'

One of these would take almost 2 minutes to finish.

Now, my master database contains 2.000.000+ items, increasing with at least 5.000 items per week. Each of my items points to a category item, and each category item can have 250.000+ links to them. So there is a lot of rows in the Links database. 36.000.000 to be exact.

Inside the Links database there is several indexes, but none of them on TargetItemID and TargetDatabase. So we tried to create the index ourselves:

CREATE NONCLUSTERED INDEX [IX_TargetDatabaseTargetItemID] ON [dbo].[Links]
(
	[TargetDatabase] ASC,
	[TargetItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

And voila! After a system restart, my index updates was back on track.

FINAL NOTES:

First of all, I usually don’t create indexes on databases that I do not own. And I cannot guarantee that the index solved all of my problems. It just so happens that after the index was introduced, my system is running fine.

MORE TO READ:

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in General .NET, Sitecore, Sitecore 5, Sitecore 6, Sitecore 7, Sitecore 8, Sitecore 9 and tagged , , , . Bookmark the permalink.

2 Responses to Sitecore poor Index Update performance linked to missing Index in the Links database

  1. Interesting read! Didn’t the `ndxTargetItemID` index cover this? My experience is that even though all queries are on (Source|Target)ItemID and (Source|Target)Database, having an index including the database doesn’t help much as there are very few unique values (mostly `master` or `web`). However, when just one of the where-columns are in the index, fragmentation seems to play a role.

    Liked by 1 person

  2. briancaos says:

    Our initial thoughts were the same. “ndxTargetItemID” indexes the [TargetItemID] and since there are only 3 values “core”, “master”, “web” in the [TargetDatabase], this would be sufficient. But that index is not at all fragmented, and it still took 2 minutes to extract 200.000+ rows without the index and a mere seconds with the index. I’m not proud of my solution, as I don’t think that you should modify a database that’s not your own. However it seems to have solved my problem, so the index stays :)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.