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.
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:
- KB1000639 – How to reduce query execution time for the Link Database from Sitecore
- Sitecore high memory usage – not always a problem by briancaos
- Sitecore Memory Issues – Every memory optimization trick in the book by briancaos
- What Is Wrong With the Link Database from Ian’s Blog
- Improved Sitecore Links Database provider by Mikael Högberg
- How To Rebuild Your Sitecore Link Database from JohnDJones
- Rebuild Link Database taking way long from Sitecore Competencies
Brian Pedersen's Sitecore and .NET Blog 
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.
LikeLiked by 1 person
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 :)
LikeLike