Slow Subscriptions Query from the the ReportServer

  • 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

  • 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

  • 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

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

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