This query is being run by ReportServer and it causes blocking and is slow. I'm wondering if anyone knows why the ReportServer is submitting this query and how the results are uses by SSRS.
Subscriptions AS S
INNER JOIN Catalog AS CAT
ON S.Report_OID = CAT.ItemID
INNER JOIN Users AS Owner
ON S.OwnerID = Owner.UserID
INNER JOIN Users AS Modified
ON S.ModifiedByID = Modified.UserID
LEFT OUTER JOIN SecData AS SD
ON CAT.PolicyID = SD.PolicyID AND SD.AuthType = Owner.AuthType
LEFT OUTER JOIN ActiveSubscriptions AS A WITH (NOLOCK)
ON S.SubscriptionID = A.SubscriptionID
(S.EventType = 'TimedSubscription' OR S.EventType = 'SnapshotUpdated');
The reason it is slow is because of the NtSecDescriptionPrimary column which is an image column. Remove this column from the query and the query returns in a second or less, with this column, because it is a BLOB it takes 20 seconds and causes blocking issues.
I can't find any indexes that would help in this case and I'm really just looking for how this query is used so I can determine a way to adjust how subscriptions are used/scheduled to reduce the contention.