Slow Subscriptions Query from the the ReportServer

Question

Slow Subscriptions Query from the the ReportServer

SSC Guru

Points: 184394

August 15, 2019 at 5:20 pm

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.

SELECT
    S.SubscriptionID,
    S.Report_OID,
    S.ReportZone,
    S.Locale,
    S.InactiveFlags,
    S.DeliveryExtension,
    S.ExtensionSettings,
    SUSER_SNAME(Modified.Sid),
    Modified.UserName,
    S.ModifiedDate,
    S.Description,
    S.LastStatus,
    S.EventType,
    S.MatchData,
    S.Parameters,
    S.DataSettings,
    A.TotalNotifications,
    A.TotalSuccesses,
    A.TotalFailures,
    SUSER_SNAME(Owner.Sid),
    Owner.UserName,
    CAT.Path,
    S.LastRunTime,
    CAT.Type,
    SD.NtSecDescPrimary,
    S.Version,
    Owner.AuthType
FROM
    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
WHERE
    (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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

The looks like the query SSRS fires when someone views My Subscriptions, usually with a parameter at the end for the user. I never followed it enough in any trace captures to say how or why the other columns are there, especially with SecData, but I would guess some are if the subscription is edited.

Sue

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Thanks. I haven't had the opportunity to trace through all that SSRS is submitting. It looks like this query is being passed as shown without a parameter for the user, so it is causing blocking issues in the SSRS database.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question