• State Verified Answer
  • Replies 1 reply
  • Subscribers 95 subscribers
  • Views 614 views
  • Users 0 members are here
Options
Related

DynamicGroup calculation time monitoring

denny breuer
over 1 year ago

Hi,

we are running 1IM in Version 8.2.1.

Question, as we have a lot of dynamic groups, is there any chance to get the runtime for each dynamic group? If I run an SQL, i can see it took xxx ms. It would be great, if we can get this value for every dynamic group, so we can go to the most time-consuming ones and try to tune them.

Best, Denny

Parents
  • +1 over 1 year ago

    The dynamic group calculations are done by generated stored procedures.

    One option to get the runtimes is to rely on the statistics from the SQL Server itself.

    Something like this:

    SELECT 
       DG.DisplayName AS DynamicGroup, 
       OBJECT_NAME(object_id) AS Procedurename, 
       CONVERT(INT, 1.0*SUM(deps.total_elapsed_time) / SUM(deps.execution_count) / 1000.0) AS AvgRuntimeMs,
       (SELECT COUNT(*) FROM PersonInBasetree BT WHERE BT.UID_Org = dbo.QBM_FCVObjectkeyToElement('ColumnValue1', dg.ObjectKeyBaseTree)) AS [Number of persons assigned to that org] 
FROM sys.dm_exec_procedure_stats deps
JOIN DynamicGroup dg ON OBJECT_ID(dbo.QER_FCVDynGuidToProcName(dg.uid_DynamicGroup, 'All')) = deps.object_id
WHERE deps.database_id = db_id()
AND dbo.QBM_FCVGUIDToModuleOwner(dg.UID_DynamicGroup) = 'CCC' --only custom defined dynamic group
GROUP BY DG.DisplayName, OBJECT_NAME(object_id), dg.ObjectKeyBaseTree
ORDER BY 3 DESC

    Attention:

    The runtime depends on 2 things:

    1. How many changes are made in the run?
    2. How many objects (person, workdesk, hardware) are already attached to the dynamic role?

    So, please do not try to optimize the runtime down to 2ms for a dynamic role that contains 300k objects.

Reply
  • +1 over 1 year ago

    The dynamic group calculations are done by generated stored procedures.

    One option to get the runtimes is to rely on the statistics from the SQL Server itself.

    Something like this:

    SELECT 
       DG.DisplayName AS DynamicGroup, 
       OBJECT_NAME(object_id) AS Procedurename, 
       CONVERT(INT, 1.0*SUM(deps.total_elapsed_time) / SUM(deps.execution_count) / 1000.0) AS AvgRuntimeMs,
       (SELECT COUNT(*) FROM PersonInBasetree BT WHERE BT.UID_Org = dbo.QBM_FCVObjectkeyToElement('ColumnValue1', dg.ObjectKeyBaseTree)) AS [Number of persons assigned to that org] 
FROM sys.dm_exec_procedure_stats deps
JOIN DynamicGroup dg ON OBJECT_ID(dbo.QER_FCVDynGuidToProcName(dg.uid_DynamicGroup, 'All')) = deps.object_id
WHERE deps.database_id = db_id()
AND dbo.QBM_FCVGUIDToModuleOwner(dg.UID_DynamicGroup) = 'CCC' --only custom defined dynamic group
GROUP BY DG.DisplayName, OBJECT_NAME(object_id), dg.ObjectKeyBaseTree
ORDER BY 3 DESC

    Attention:

    The runtime depends on 2 things:

    1. How many changes are made in the run?
    2. How many objects (person, workdesk, hardware) are already attached to the dynamic role?

    So, please do not try to optimize the runtime down to 2ms for a dynamic role that contains 300k objects.

Children
No Data