Based on learning's from the Pioneer Dogfood environment it is important that we watch the fragmentation of the TFS Integration Platform Migration database indexes, as fragmentation can become excessive, leading to a degradation of performance.
Grant has authored the following SQL job that rebuilds/reorganizes the indexes in his environment. He uses a job that reorganizes / rebuilds the indexes every Sunday night using the helper scripts from Ola: http://ola.hallengren.com/.
EXECUTE dbo.IndexOptimize @Databases = 'TfsMigrationConsolidatedDB',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000
20091009 - Thanks to Ola, we updated @FragmentationHigh_LOB = 'INDEX_REBUILD_ONLINE' to @FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE' as online rebuild is not supported on columns with LOB data types in sql server.
Here’s an example of fragmentation:
When using the TFS Integration Platform, especially for large and ongoing sync scenarios, consider scheduling the above SQL job on a regular basis.
Thanks Grant! This will be added to the TFS Integration Platform – Migration Guidance document as well :)