This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Stored procedure [WfPopulateLocalTables] imposes heavy load on SQL Server

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?

Parents
  • You have some brass tacks my friend.  We had a similar sounding issue, sought - and received advice to alter the cots engineered internal query processes too.  ...and rather than possibly break the product by my own hand - we turned to OneIdentity support for assistance. 

    Your issue sounds very close to what we experienced - for the same reasons, and the product was updated to provide relief in these rare instances using two specific performance flags in the registry.  

    There is a secret-sauce recipie for relief in a OneIDentity produced KA you can search for on this site.  High SQL CPU usage.  If you haven’t found it - or someone else hasn’t chimed in later, I’ll find it in my notes.  If you share the same scenario as mine - maybe this will help. 

  • Thank you for your response, I have tried looking for the KB article but only came across this: https://support.oneidentity.com/active-roles/kb/240234/high-cpu-load-on-sql-server-when-requesting-change-history-information

    I'm not sure how relevant the KB article is to my problem, but the solution isn't very descriptive. I can't really work with this. I'd be grateful if you could share those notes Slight smile

Reply Children
No Data