Performance Improvement for initial SQL MA Run RRS feed

  • General discussion

  • Hello,

    I have two SQL MA in my FIM2010 environment.

    M1 --> brings records from one database (let's call DB1)
    M2 --> export all the records brought by M1 to another database(let's call DB2)

    both databases are having the same table with three columns where first two columns establish Primary Key in the table

    1. Id
    2. AttributeId
    3. AttributeValue

    Because the first two columns ( Id + AttributeId ) is primary key naturally I have set up both of them jointly as AnchorId in both MAs with following MV extension code

    void IMVSynchronization.Provision (MVEntry mventry) { if (mventry.ObjectType.Equals("fimLocation")) ProvisionToSQL(mventry); } private void ProvisionToSQL(MVEntry mventry) { ConnectedMA thisMA = mventry.ConnectedMAs["MA2"]; if (thisMA.Connectors.Count < 1) { CSEntry csentry = thisMA.Connectors.StartNewConnector("fimLocation"); ReferenceValue dn = thisMA.EscapeDNComponent(System.Guid.NewGuid().ToString()); //following two attributes are mapped for export purpose because MA2 configuration prevents

    //to set up export flow because they are AnchorId csentry["Id"].Value = mventry["Id"].Value; csentry["AttributeId"].Value = mventry["AttributeId"].Value; csentry.DN = dn; csentry.CommitNewConnector(); } }

    I have total 1,205,000 objects which takes more than 36 hours for MA2 to export and reimport exported changes and 20 hours for MA1 to perform full import full sync.

    I don't have any requirements to push all the records to FIM portal and I have choosen classical provisioning Vs Declarative to avoid unwanted EREs.

    Anybody has any idea to improve the performance for intial load? I have already set up Deltas for subseuest loads.

    Thanks in advance for your help.


    Tuesday, May 8, 2012 7:21 PM

All replies

  • Have you taken a look at:
    Wednesday, May 9, 2012 5:18 AM
  • How much time does the export actually take? maybe it is the import that takes so much time.

    Furthermore are you using multivalue tables or only single tables, are there any triggers on the table (after update trigger) that can cause slowdown, when exporting is the sql server running high CPU?

    Need realtime FIM synchronization and advanced reporting? check out the new that supports FIM 2010, Omada Identity Manager, SQL, File, AD or Powershell real time synchronization!

    Wednesday, May 9, 2012 5:50 AM
  • Hi Paul, S. Kwan

    Thank you very much for taking your time and replying to my post.

    Actual Export time : 18:29:14 (hours:minutes:seconds)

    Actual Export confirming import time :17:30:52 (hours:minutes:seconds)

    There is no trigger, no MVA. I didn't check CPU usage. Sync rules are very simple and it is one-to-one mapping.

    @S.Kwan I did check the article you mentioned and next time we are planning to apply those best practises.

    I am planning to change the AnchorId adding another column to destination database and run the MA again to see the perfomance. Does anybody has any better idea to improve the performace? Is this much long time expected for such a large intial load?


    Wednesday, May 9, 2012 1:27 PM
  • Not having run a network trace or gotten familiar with SQL Profiler, my impression has always been that the sync engine performs the exports and imports one record at a time, which of course is not the most efficient way to transfer large amounts of data between two other databases.  The times you are experiencing are thus expected based on the way things are. 

    The best suggestions I could give you would be to ensure that the SQL Server instance that is hosting FIM's sync engine database for you has lots of memory (enough to hold the entire database), very fast disk access and is either on the same box as the FIM Sync Service or there is good (Gigabit) network connectivity between the FIM server and your SQL Server.  Ultimately you're fighting the cumulative latency from network round trips, SQL query responses, and disk I/O, all of which are going to happen 1.2 million times or some multiple thereof.

    I'm not sure how these two SQL MAs fit into your overall design, but if all you wanted to do was transfer the data between databases something like SSIS or some other ETL tool might be the better option.

    The good news is you'll be much happier with the relative speed of your deltas once you get to the point of being able to run them.


    Wednesday, May 9, 2012 8:43 PM