If a Management Agent is taking longer to complete its run operation, there are few things to consider. If you have a delay in an export or import operation, there could be a problem with the source system or network. Initial investigations for longer-than-expected synchronizations should determine whether a large number of groups is being synchronized, whether non-indexed attributes are being used as joins, or whether there are SQL Server performance problems.

If the MIM portal takes a long time to respond or times out while performing a "contains" search, it could be because the full-text search catalog needs to be rebuilt. Start SQL Management Studio and click on Databases | FIMService | Storage | Full Text Catalogs. Right-click on ftCatalog and select Rebuild:

If your portal's search performance is slow for "non-contain" searches, then you may need to rebuild the indexes and update the statistics of the service database. The following query can be used to determine the average fragmentation as a percentage:

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'fim.ObjectValueString'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

The official SQL documentation on reorganizing and rebuilding indexes at http://bit.ly/SQLReorgAndRebuildIndexes says to rebuild the index if fragmentation is greater than 30%.

You should also periodically reindex all of the tables in the Synchronization service database. Tim Macaulay, senior support escalation engineer for Microsoft, posted the following SQL script for table reindexing:

USE FIMSynchronizationService 
DECLARE @table_name varchar(1000)
declare c1 cursor for SELECT name 
FROM  sysobjects
WHERE xtype = 'U'
open c1
fetch next from c1 into @table_name
while @@Fetch_Status = 0
begin DBCC DBREINDEX (@table_name,  '')
fetch next from c1 into @table_name
end
close c1
deallocate c1
GO

Note that reindexing the synchronization database while performing synchronization will see performance problems and potential SQL locking problems, so we strongly recommend that you rebuild the index during a maintenance window when no Management Agents are being run.