Copyright Johan Kronberg 2009-2024 Latest posts RSS feed X: johankronberg LinkedIn Profile GitHub: krompaco
Site generated by: Record Collector

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 and tagged with these categories: Episerver, Microsoft SQL Server, Troubleshooting