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:
SELECT TOP 20 OBJECT_NAME (ips.[object_id]) AS [Object Name], si.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], CASE 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 WHERE si.object_id = ips.object_id AND si.index_id = ips.index_id AND ips.index_level = 0 AND si.name IS NOT NULL ORDER BY CASE 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 DESC GO
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.
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 http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html 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.
Hi Bob, I’ve written a little WPF C# GUI front end on your PowerShell script. You can ge the source here: https://www.dropbox.com/sh/t47617wbrqx6k3h/24_V7X7e9z
Oops, I mean I converted your sql script to PowerShell and put a GUI on it.
Pingback: FIM Database Index Fragmentation | IDM and Other Stuff