Identifying #FIM2010 Database Index Fragmentation

I want to share the following SQL script which I have adapted for FIM from the original here.

If you read the blog post you will understand that both FIM databases meet the criteria the author describes (GUID cluster keys) as a cause for high index fragmentation, leading to poor FIM performance in a variety of ways (even leading to SQL timeouts in extreme cases).  If only the GUID keys were able to be sequential to be able to avoid this problem – but alas they are not.  Hence the need to do something about them – and regularly!

When troubleshooting poor application performance where SQL is involved, my approach (with both FIM services) is to open the following script in SQL Server Management Studio, selecting the FIMService database in the toolbar drop-down list:

 OBJECT_NAME (ips.[object_id]) AS [Object Name], AS [Index Name],
 ROUND (ips.avg_fragmentation_in_percent, 2) AS [Fragmentation],
 ips.page_count AS [Pages],
 ROUND (ips.avg_page_space_used_in_percent, 2) AS [Page Density],
 WHEN ips.avg_page_space_used_in_percent = 0
 THEN ips.page_count * ROUND (ips.avg_fragmentation_in_percent, 2)/100
 ELSE ips.page_count * ROUND (ips.avg_fragmentation_in_percent, 2) / ROUND (ips.avg_page_space_used_in_percent, 2)
 END AS [Weighting]
FROM sys.dm_db_index_physical_stats (DB_ID ('FIMService'), NULL, NULL, NULL, 'DETAILED') ips
--FROM sys.dm_db_index_physical_stats (DB_ID ('FIMSynchronizationService'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
 si.object_id = ips.object_id
 AND si.index_id = ips.index_id
 AND ips.index_level = 0
 WHEN ips.avg_page_space_used_in_percent = 0
 THEN ips.page_count * ROUND (ips.avg_fragmentation_in_percent, 2)/100
 ELSE ips.page_count * ROUND (ips.avg_fragmentation_in_percent, 2) / ROUND (ips.avg_page_space_used_in_percent, 2)

The results I will get back (after about a minute running the first time) will show the tables which I have rated as the most in need of defragmentation (highest weighting) at the top.  I then proceed down the list to simply locate each offending table (e.g. ObjectValueReference is a prime candidate), expand the database table in the Object Explorer treeview, and select the Rebuild All option from the RH mouse menu (at this point I am a tad heavy-handed, and prefer to rebuild ALL of the indexes not just the one that shows the highest weighting).  For particularly large FIM databases it is often best to stop the FIMService first before doing this, but I find I don’t always have to do this.  Once I have completed this exercise I repeat it once or twice until I am satisfied that the remaining fragmentation is acceptable and not likely to cause further problems for now.

I then select the FIMSynchronizationService database in the drop-down list, comment the line referencing FIMService, uncomment the corresponding FIMSynchronizationService line, and repeat the above process for the FIM Sync database (with far less tables here you will find this far quicker).

At some stage I plan to implement something along the lines of the SQL Server Maintenance Solution , but in the meantime I am using this spot fix approach – particularly after the initial system data load on deployment, or after periods of high data volatility such as the beginning of a school year at an education site :).  I like the weighting idea because the generic > 30% fragmentation rule sometimes used (or similar) doesn’t necessarily highlight those fragmented table indexes which are having the greatest performance impact but may be below your threshold.


About bobbradley1967

Microsoft Identity and Access Professional with 2 decades of successful IAM implementations in APAC, specialising in MIM and its predecessors (FIM/ILM/MIIS) and now with SoftwareIDM. A Microsoft IAM MVP prior to that with a background in MS.Net applications development/SI. Now with a particular interest how Identity and HyperSync Panel provide the Identity and Access orchestration presently missing in the Azure Entra Suite to effectively enforce Zero Trust on the M365 platform.
This entry was posted in FIM (ForeFront Identity Manager) 2010, ILM (Identity Lifecycle Manager) 2007 and tagged , , , , , . Bookmark the permalink.

5 Responses to Identifying #FIM2010 Database Index Fragmentation

  1. Bob,
    What you have written about FIM using Cluster GUID keys is accurate for versions of FIM before R2. With R2 most of the major tables were rearchitected to have smallint, int or bigint (as appropriate) with Identity as the clustered index key to avoid this problem. While this helps and follows best design database principles, fragmentation will still occur in these indexes but especially in others.

    I recommend using from SQL MVP Ola Hallengren. It is a free solution that is configurable. By default it only examines indexes with more than 1000 pages. It will rebuild indexes that have more than 30% fragmentation, and reorganize those between 5% and 30%.

    • Thanks David – yes I have seen your recommendations about Ola’s solution before which was why I referenced it in my last paragraph. The intent of this post was to provide a segue to implementing a formal regime by running a simple enough query. Glad to hear that the FIM R2 changes you refer to were made – since I am still mainly working on R1 sites I now feel armed with a bit more ammunition to argue the case for an R2 upgrade – but with the regression testing costs for a custom solution being so high this may be a while coming.

  2. Hi Bob, I’ve written a little WPF C# GUI front end on your PowerShell script. You can ge the source here:

  3. Oops, I mean I converted your sql script to PowerShell and put a GUI on it.

  4. Pingback: FIM Database Index Fragmentation | IDM and Other Stuff

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.