Greetings,
We have ActiveRoles deployed in several domains without any problem whatsoever, but ever since upgrading to 7.2 one of our SQL servers has been experiencing a 90% total CPU load. We've been able to pinpoint this to the [WfPopulateLocalTables] stored procedure. One of our SQL admins has been able to reduce the CPU load to a total of 30% by modifying the stored procedure:
-- make sure that WfLocal*** tables contain all required data
ALTER PROCEDURE [dbo].[WfPopulateLocalTables]
AS BEGIN
DECLARE @countLocal INT
DECLARE @countShared INT
-- Operations
-- select @countShared = count(*) from [WfSharedOperations]
-- select @countLocal = count(*) from [WfLocalOperations]
SELECT @countShared = [rows] FROM sysindexes WHERE id = OBJECT_ID('WfSharedOperations') AND indid < 2
SELECT @countLocal = [rows] FROM sysindexes WHERE id = OBJECT_ID('WfLocalOperations') AND indid < 2
IF @countLocal < @countShared
INSERT INTO [WfLocalOperations] WITH (TABLOCKX)([guid], [schema_version]) SELECT [guid], 0 FROM [WfSharedOperations] WITH(TABLOCK)
WHERE NOT EXISTS(SELECT * FROM [WfLocalOperations] WHERE [guid] = [WfSharedOperations].[guid]);
-- Tasks
-- select @countShared = COUNT(*) FROM [WfSharedTasks]
-- select @countLocal = COUNT(*) FROM [WfLocalTasks]
SELECT @countShared = [rows] FROM sysindexes WHERE id = OBJECT_ID('WfSharedTasks') AND indid < 2
SELECT @countLocal = [rows] FROM sysindexes WHERE id = OBJECT_ID('WfLocalTasks') AND indid < 2
IF @countLocal < @countShared
INSERT INTO [WfLocalTasks] WITH (TABLOCKX)([guid], [schema_version]) SELECT [guid], 0 FROM [WfSharedTasks] WITH(TABLOCK)
WHERE NOT EXISTS(SELECT * FROM [WfLocalTasks] WHERE [guid] = [WfSharedTasks].[guid]);
END
Knowing that SysIndexes is a deprecated object, is there anything else OneIdentity (or the community) can suggest to make this more efficient? Our last history DB import was 82 million rows so I'm aware our situation is a bit unusual (or extreme), but up till version 7.2 this didn't matter much. I'm aware that the code of this stored procedure hasn't changed since, so is there any other reason that this is happening?
