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.
ITMS 8.6, 8.7
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.
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