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:

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.

Advertisements

About bobbradley1967

Microsoft IAM MVP and Solutions Architect (MCTS, MCP) - FIM/ILM/MIIS Specialist, with 20 years SQL database ( OLAP) and MS.Net applications development/SI background, in particular on the SharePoint 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 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.

  2. 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

  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

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s