Slow Performance caused by spAssignResourcesToTypedScopeCollection

Products

• IT Management Suite

Issue/Introduction

While working in the SMP Console (adding software, creating policies, targets, reports, etc), slowness is reported with occasional timeouts. Also, it has been noticed that the SQL cluster runs on ~100% CPU all the time.

The query driving most of the CPU is the highlighted query in the screenshot below which is part of the stored procedure "spAssignResourcesToTypedScopeCollection":

Notice that the query has consumed ~9.5 hours of CPU time during the last 4 hours.

Environment

ITMS 8.6, 8.7

Cause

Known Issue. Since there is a mismatch in datatypes returned from the function "fnListToGuidTableDal2InLine", the query is ending up scanning the non-clustered index on ItemResourceType instead of doing a seek on the primary key.

Resolution

This issue was addressed in the ITMS 8.7 RU1 release.

For those on ITMS 8.7 RTM version, a pointfix is available. Please refer to: 

261271 "CUMULATIVE POST ITMS 8.7 GA POINT FIXES"

For those on ITMS 8.6 RU3, please use the following updated "spAssignResourcesToTypedScopeCollection" stored procedure:

/****** Object:  StoredProcedure [dbo].[spAssignResourcesToTypedScopeCollection]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spAssignResourcesToTypedScopeCollection]
                    @scopeCollectionGuid UNIQUEIDENTIFIER,
                    @resourceGuidList    NVARCHAR( MAX )
                AS
                BEGIN
                    -- Fallback logic
                    IF NOT EXISTS ( SELECT Guid FROM ItemClass WHERE [Guid] = @scopeCollectionGuid )
                        SET @scopeCollectionGuid = 'CAFC430C-6705-4a21-8E39-7AA0DA861A87'

                    -- Place the resource guids into a temp table
                    CREATE TABLE #resourceGuids ( ResourceGuid UNIQUEIDENTIFIER PRIMARY KEY )
                    INSERT INTO  #resourceGuids ( ResourceGuid )
                        SELECT   DISTINCT irt.[Guid]
                            FROM dbo.fnListToGuidTableDal2InLine( @resourceGuidList ) t
                            join ItemResourceType irt WITH(nolock) ON irt.[Guid] = t.ui

                    -- Remove the resources from existing scope collections under the 'Default' view
                    DELETE    sm
                        FROM  #resourceGuids r
                        JOIN  ScopeMembership sm
                            ON sm.ResourceGuid = r.ResourceGuid
                        JOIN  ScopeCollection sc 
                            ON sc.ScopeCollectionGuid = sm.ScopeCollectionGuid
                        WHERE sc.ScopeSetGuid = 'D8C07867-F25B-41AF-9C6A-82C1086B2179'
                        AND   sm.ScopeCollectionGuid <> @scopeCollectionGuid

                    -- Place the resource guids into the table if they dont already exist
                    DECLARE @retry   int = 2
                    WHILE ( @retry > 0 )
                    BEGIN
                        -- when we retry, this will be nice date for batch
                        declare @Date datetime = GETDATE()

                        -- sometimes updlock cannot be acquired, 
                        -- so to eliminate PK violations(without using other locks which can affect performance) TRY & CATCH is needed
                        BEGIN TRY
                            MERGE ScopeMembership WITH (readcommittedlock) TARGET
                            USING #resourceGuids SOURCE
                            ON (TARGET.ScopeCollectionGuid = @scopeCollectionGuid AND TARGET.ResourceGuid = SOURCE.ResourceGuid)
                            WHEN NOT MATCHED BY TARGET THEN
                                INSERT (ScopeCollectionGuid, ResourceGuid, CreatedDate) VALUES(@scopeCollectionGuid, SOURCE.ResourceGuid, @Date)
                            ;
                            SET @retry = 0
                        END TRY
                        BEGIN CATCH
                            DECLARE @ErrorNumber int
                            SET     @ErrorNumber = ERROR_NUMBER()
                            IF (@ErrorNumber = 2627)    -- Violation of PRIMARY KEY constraint 'PK_...'
                                SET @retry = @retry - 1
                            ELSE
                                SET @retry = 0
                            
                            IF ( @retry = 0 )
                            BEGIN
                                DECLARE @ErrorMessage  nvarchar(4000),
                                        @ErrorSeverity int,
                                        @ErrorState    int,
                                        @ErrorLine     int
                                SELECT  @ErrorMessage  = N'SMPERROR(%d), Level %d, State %d, Line %d, ' + 'Message: '+ ERROR_MESSAGE(),
                                        @ErrorSeverity = ERROR_SEVERITY(),
                                        @ErrorState    = ERROR_STATE(),
                                        @ErrorLine     = ERROR_LINE()
                                RAISERROR ( @ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine )
                           END
                        END CATCH
                    END

                    DROP TABLE #resourceGuids
                END
                GO