Recently worked on an issue where the customer had their entire FIM 2010 Solution virtualized using a VMWARE Solution which included a CISCO device. The customer indicated that the problem occurred during the middle of the night.
Review of PSSDIAG Information and SQL Server Profiler Trace Information, we were able to detect the following query which caused the CPU to spike and is experiencing horrible performance.
DELETE FROM [fim].[ObjectValueText] WHERE [ObjectKey] IN ( SELECT [ObjectKey] FROM [fim].[ExpiredObjectKeys] );
There was thought/questions around adding an additional index to resolve the issue. However, adding an index to the backend FIM Synchronization Service Database and/or the FIM Service Database would be considered modifying the backend database, which would cause the database(s) to be in a non-supported state.
We do not recommend adding an index because:
Research displayed that the query is being called from Store Procedure FIM_DeleteExpiredSystemObjects which is called from the FIM_DeleteExpiredSystemObjectsJob SQL Agent Job. By default this is fired during the middle of the night around 3 AM.
Here are a few options that could be utilized to help resolve the above issue.
Modify the Command to include the optional parameter @requestLimit (*NOTE: The default requestLimit is 20000 )
FIM LANDING PAGE: Resource and Troubleshooter Wiki Index Page