Troubleshooting a large increase in Epi SQL Server calls

Recently spent some time investigating massive database dependency counts on an Epi CMS site that was using Geta Tags.

The Geta Tags maintenance job was running fine but possibly due to a bunch of recently removed content types was not able to remove now missing ContentGUIDs while cleaning up.

Looks like this resulted in uncached netFindContentCoreDataByContentGuid stored procedure calls with non-existing ContentGUIDs when ITagEngine.GetContentReferencesByTags() was used.

I put together an analysis query that returned rows showing that content GUIDs in Geta Tags DDS store had no content relation.

SELECT t.*, c.* FROM (
	SELECT DISTINCT GuidValue FROM tblBigTableReference WHERE [Index] > -1 AND GuidValue IS NOT NULL AND PropertyName = 'PermanentLinks'
	AND pkId IN (
		SELECT StoreId FROM [VW_Geta.Tags.Models.Tag]
	)
) AS t
LEFT JOIN tblContent c ON t.GuidValue = c.ContentGUID
WHERE c.ContentGuid IS NULL

To get sorted (hopefully) I made it into this DELETE-statement.

DELETE FROM tblBigTableReference WHERE [Index] > -1 AND GuidValue IS NOT NULL AND PropertyName = 'PermanentLinks' AND pkId IN
(
	SELECT StoreId FROM [VW_Geta.Tags.Models.Tag]
)
AND GuidValue IN
(
	SELECT GuidValue FROM
	(
		SELECT DISTINCT GuidValue FROM tblBigTableReference WHERE [Index] > -1 AND GuidValue IS NOT NULL AND PropertyName = 'PermanentLinks' AND pkId IN
		(
			SELECT StoreId FROM [VW_Geta.Tags.Models.Tag]
		)
	) AS t
	LEFT JOIN tblContent c ON t.GuidValue = c.ContentGUID
	WHERE c.ContentGuid IS NULL
)

Things seem to be back in shape now.

Published Wednesday 27 January 2021 04:42 and tagged with these categories: Episerver, Microsoft SQL Server, Troubleshooting

Comments

Add Your Comment