Based on learning's from the Pioneer Dogfood environment it is important that we watch the fragmentation of the TFS Integration Platform Migration database indexesimage_thumb3, 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:
clip_image002

Solution

When using the TFS Integration Platform, especially for large and ongoing sync scenarios, consider scheduling the above SQL job on a regular basis.

Closing Note

Thanks Grant! This will be added to the TFS Integration Platform – Migration Guidance document as well :)