Bad performance from spGetResourceItemAttributesByTrustees

Products

IT Management Suite

Issue/Introduction

The customer noticed that while trying to access Manage>Jobs and Tasks OR Manage>Computers>Jobs and Tasks middle frame, for example, it took too long to load and also caused a time out when they tried with different users who are in other security roles.

According to the profiler, this is the process taking the most time:

"w3wp","15468","625","ResourceService","GetItemActions","13:07:41.02662","352298.6"

which related to this one:

EXECUTE spGetResourceItemAttributesByTrustees @ResourceGuidList=N'037dd354-
48f0-487d-b6a6-eed80d1989d8',
@trustees=N'{0345EB06-E553-441E-B933-BFBCA61154C3},{0D06C41B-0718-4D0A-9AAB-
9647C4A99BEC},{24B19AC7-3C20-44EF-AAEE-B41B69510E58},{37D0A6C1-83A3-417E-
956E-8C88B9DF5082},{4CC4B6D4-6702-4F1E-A311-DEA5AB602B4A},{50DB0BA1-1B26-
41FD-B2AA-0CEA37BEFC84},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{6701BD82-
572F-4503-8FF3-B13BFA5ED1D9},{7BC93288-8239-4E08-83BE-3886ADDF595E},
{85717AC1-FF55-420D-AFF0-DF70365C2A53},{88875300-48D2-46F3-87B2-
A40E9107C951},{8964C365-3DE3-42D6-B15C-58D512684600},{A3717E73-D77B-45F9-
92F4-CB4E82CEC0F7},{A5FB375F-0A19-4218-B9EA-5F37F96D36E3},{ADB35CED-7401-
4032-A257-49B2418D6BA8},{AF020ABD-023D-4B93-869D-B9C8AF3F6052},{B698E79A-
09A3-4C1A-B450-C718E9884D6A},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},
{BF43FB65-39D6-41FB-843C-6C8D91358B58},{BFE403F6-E167-4EBE-8901-
DBC2BF85F395},{C0C0C3F4-2010-4893-A3F2-651E9C7C44C6},{D1102925-283A-494E-
B187-1EF775B67336},{DF1A7AD2-2AD3-46D2-AB59-5EB2DD4B302B},{DFB7FECA-7290-
4758-BDA7-04C013D32E8F},{E4A61107-4841-4BCD-B9FD-3E19C3AB29B1},{F8A0B700-
AB28-44EF-BD44-191A18CD23D1}'

For some reason it was taking too long: 352298.6.

Environment

ITMS 8.5 RU2

Cause

Known issue. "spGetResourceItemAttributesByTrustees" stored procedure needed to be optimized in order to take advantage of the new "fnAC_HasFullScopeAccess" function added to the product to avoid excessive checks for permissions when using different security roles other than Symantec Administrators role.

.

Resolution

This issue has been reported to the Symantec Development team. A fix is currently targeted for ITMS 8.5 RU3 release.

Workaround:

Dev modified "spGetResourceItemAttributesByTrustees" with the following query:

ALTER PROCEDURE [dbo].[spGetResourceItemAttributesByTrustees]
    @ResourceGuidList nvarchar(max),
    @trustees         nvarchar(max)
AS
BEGIN
    DECLARE @ResCnt int
    -- create temp table for resources
    CREATE TABLE #r ([Guid] uniqueidentifier primary key)
    INSERT INTO #r
        SELECT   DISTINCT ui
            FROM dbo.fnListToGuidTableDal2InLine( @ResourceGuidList )
    SET @ResCnt = @@ROWCOUNT

    DECLARE      @TrusteeGuids TABLE ( Guid uniqueidentifier PRIMARY KEY )
    INSERT INTO @TrusteeGuids
        SELECT   DISTINCT st.TrusteeGuid
            FROM dbo.fnListToTable( @trustees, DEFAULT ) fn
            JOIN sec_Trustee st WITH (NOLOCK) ON st.Trustee = fn.nstr

    IF EXISTS ( SELECT TOP 1 1 FROM @TrusteeGuids WHERE Guid = '2E1F478A-4986-4223-9D1E-B5920A63AB41' )
    BEGIN
        -- full access ( Symantec Admin)
        SELECT    vi.[Guid], vi.[Attributes]
            FROM vResourceItem vi
            JOIN #r            rl ON rl.Guid = vi.Guid
    END
    ELSE IF ( @ResCnt <= 100 )
    BEGIN
        DECLARE @Rs TABLE ( [Guid] uniqueidentifier , [Attributes] int )
        INSERT @Rs
            SELECT    vi.[Guid], vi.[Attributes]
                FROM vResourceItem vi
                JOIN #r            rl ON rl.Guid = vi.Guid

        SELECT    DISTINCT rs.[Guid], rs.[Attributes]
            FROM @Rs             rs
            JOIN ScopeMembership sm WITH (NOLOCK) ON sm.ResourceGuid = rs.Guid
            CROSS APPLY
            (
                SELECT     ff.FolderGuid
                    FROM   FolderBaseFolder ff WITH (NOLOCK)
                    JOIN   sec_EntitySource ss WITH (NOLOCK) ON ss.EntityGuid = ff.FolderGuid
                    JOIN   sec_EntityTrustee st WITH (NOLOCK) ON st.EntityGuid = ss.SourceGuid
                    JOIN   @TrusteeGuids     tt               ON tt.Guid = st.TrusteeGuid
                    WHERE ff.ParentFolderGuid = sm.ScopeCollectionGuid
                    AND    st.Permission >= CAST(0x2000000000000000 AS bigint)
            ) aa
    END
    ELSE
    BEGIN
        DELETE         rs
            FROM       #r              rs
            LEFT JOIN ScopeMembership sm WITH (NOLOCK) ON sm.ResourceGuid = rs.Guid
            WHERE      sm.ScopeCollectionGuid IS NULL
            OR         sm.ScopeCollectionGuid NOT IN
            (
                SELECT     ff.FolderGuid
                    FROM   FolderBaseFolder ff WITH (NOLOCK)
                    JOIN   sec_EntitySource ss WITH (NOLOCK) ON ss.EntityGuid = ff.FolderGuid
                    JOIN   sec_EntityTrustee st WITH (NOLOCK) ON st.EntityGuid = ss.SourceGuid
                    JOIN   @TrusteeGuids     tt               ON tt.Guid = st.TrusteeGuid
                    WHERE ff.ParentFolderGuid = '42441BEE-BC0F-469C-8A66-06288CB1B8AF' -- Organizational Views
                    AND    st.Permission >= CAST(0x2000000000000000 AS bigint)
            )

        SELECT    vi.[Guid], vi.[Attributes]
            FROM vResourceItem vi
            JOIN #r            rl ON rl.Guid = vi.Guid
    END

DROP TABLE #r
END