1. UNIFYNow Forum
  2. Questions
0
Answered

Event Broker Pending Exports

Daniel Walters 12 years ago • updated by anonymous 8 years ago • 3

John Hennessy (DB Guru) from DET has been looking at the SQL CPU load created by Event Broker v 2.2.3 when it polls the FIM database for pending exports. One thing he noted was the SQL query that searches the connector spaces for pending exports. He said it seems inefficient in that it checks every record in the connector space table (which contains all connector spaces) for being the correct MA and a pending export. I think he's saying it would be more efficient to find all the entries for the MA, then check that set for pending exports. I imagine this wouldn't have been too significant in most environments but at DET some of the FIM instances have 10+ MAs with ~500-900 thousand objects in each. There's also some indexing stuff he was talking about. He's getting together some more formal statistics and we (Eddie, Richard and I) told him that the product team would be interested.

There was also some confusion about the frequency of the polling for pending exports. It seems as in the attached screen shot that you have two options.

1. Click 'Autorun on Outgoing Provisioning Pending'
or
2. Set a schedule to run the operation list

Both of these options aren't ideal in the DET environment with 10+ MAs. We don't want to set a scheduled export that runs whether there are pending exports or not and it seems that the Autorun option polls the connector space more frequently than what would be ideal.

There'll be more actual numbers to come.


Event Broker.PNG
Matthew Clark 12 years ago

Marked as resolved and assigned to Daniel for comment

Matthew Clark 12 years ago

Hi Daniel,

The issue of the outgoing pending operation of Event Broker has been the subject of many discussions, especially at DET. I recall similar discussions this time last year when something caused the query execution plan to blow out the time this operation was taking. There were earlier discussions of experimenting with adding indexes to the database around the specific query that was being performed, but I'm not sure if this path was taken any further.

The performance and frequency of the query was something we had feedback on from a number of different people, with a number of different issues. These have been all addressed in the latest release of Event Broker, v3.0.0. Issues around export detection that were addressed by this release include:

  • EB-6 - A migration of this issue from our last project management system when this behaviour was first reported, prompting some significant improvements to the query
  • EB-185 - Dealing with issues around the scheduling of pending exports
  • EB-189 - As above, and allowing for this check to become atomic for each management agent
  • EB-203 - Addressing the behaviour of the detection following export errors
  • EB-209 - Resulting in the culmination of the above in the creation of an Outgoing Pending Operation

The Outgoing Pending Operation is essentially a check operation for exports, and has been found to be much more efficient than the previous mechanism. When coupled with some advanced scheduling features, the changes in v3.0.0 make it much easier to manage Event Broker operations in larger solutions. See EB300:Outgoing Pending Operation for more information.

There will be no further updates to v2.x of Event Broker, and as such I would recommend upgrading to the latest version whenever possible. There are many more issues addressed by this release, see EB300:v2.x to v3.0 User Notes.

In the mean time, I believe a similar issue was encountered with the MIS mangement agent in the old solution - review how this has been configured and see if it is appropriate.

Daniel Walters 12 years ago

OK, thanks for the thorough reply. Here's John's notes on the Event Broker performance - because he went to the trouble and just so there's a record of it.

Unify Event Broker service (Unservice.exe) on MPE FIM SSPR SCO8834APP960 10.220.62.64 poll query against FIMSynchronizationService database as follows:

SELECT MA.ma_name
FROM mms_management_agent MA WITH (NOLOCK)
where MA.MA_ID in (select CS.ma_id
from mms_connectorspace CS WITH (NOLOCK, INDEX(IX_mms_connectorspacepartitexportcurrenunappldepthis_expobjectpobjec))
INNER JOIN mms_partition SB WITH (NOLOCK) ON CS.partition_id = SB.partition_id
where CS.CURRENT_EXPORT_BATCH_NUMBER > SB.last_successful_export_batch_number
and CS.IS_EXPORT_ERROR <> 1)

Above query has an index hint 'IX_mms_connectorspacepartitexportcurrenunappldepthis_expobjectpobjec' which forces mms_connectorspace to use an index without 'ma_id' column in it
resulting in bad performance when connectorspace table has many rows e.g. all DET FIM & ILM instances

If the query was changed to use index hint 'IX_mms_connectorspacema_idpartition_idcurrent_export_batch_number' which includes ma_id (example below), same results are returned using
significantly less SQL CPU, I/O and execute duration:

SELECT MA.ma_name
FROM mms_management_agent MA WITH (NOLOCK)
where MA.MA_ID in (select CS.ma_id
from mms_connectorspace CS WITH (NOLOCK, INDEX(IX_mms_connectorspacema_idpartition_idcurrent_export_batch_number))
INNER JOIN mms_partition SB WITH (NOLOCK) ON CS.partition_id = SB.partition_id
where CS.CURRENT_EXPORT_BATCH_NUMBER > SB.last_successful_export_batch_number
and CS.IS_EXPORT_ERROR <> 1)

Problem first identified Nov 2010 in ILM SPOT, EDUC & TRN environments with mitigation being add 'ma_id' column to IX_mms_connectorspacepartitexportcurrenunappldepthis_expobjectpobjec index
in connectorspace table in each environment (details below).

Above change unify poll SQL query to use index hint 'IX_mms_connectorspacema_idpartition_idcurrent_export_batch_number' gives same performance improvement as add ma_id column without
requiring any change to ILM connectorspace index

Customer support service by UserEcho