OVERVIEW

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:

1. While adding an index will speed the reads against the backend database, it will slow the writes.
2. Hotfixes Patches, Service Packs, Upgrades could fail, or the Index could be removed during one of these installation processes.
3. Could affect the overall performance of the FIM 2010 / FIM 2010 R2 product.

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.

Resolution

Here are a few options that could be utilized to help resolve the above issue.

1. Move the SQL Server Agent Job FIM_DeleteExpiredSystemObjectsJob Schedule to a slow time ( How to change the scheduled time )
2. The FIM_DeleteExpiredSystemObjects Stored Procedure takes a batch size parameter. You could reduce the size of the batch specified.

How to change the scheduled time

1. Open SQL Server Management Studio and connect to the SQL Server hosting the FIM Service Database
2. Expand SQL Server Agent then Jobs
3. Right mouse click on FIM_DeleteExpiredSystemObjectsJob and select Properties to view the Properties of this particular Job.

1.  Click the Edit button at the bottom of the Job Properties Window
2. You will get the Job Schedule Properties Window

1. We are concerned with the Daily Frequency

1.  Change the Occurs Once at to a different time.
2. Click Ok until you are back out to the SQL Server Management Studio

• How to reduce batch size

  1. Open SQL Server Management Studio and connect to the SQL Server hosting the FIMService Database.
  2. Expand SQL Server Agent and then Jobs
  3. Select FIM_DeleteExpiredSystemObjectsJob
  4. Right click on FIM_DeleteExpiredSystemObjectsJob and select Properties
  5. Select Steps
  6. Select Step #2 which should be FIM_DeleteExpiredSystemObjectsJob
  7. Click the Edit Button to open the Job Step Properties Window
  8. Go to the Command

  

  1.  Modify the Command to include the optional parameter @requestLimit (*NOTE: The default requestLimit is 20000 )

  

  See also

  FIM LANDING PAGE: Resource and Troubleshooter Wiki Index Page