Troubleshooting a large increase in Epi SQL Server calls

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.