Rebuilding Indexes on the FimSynchronizationService Database

When you change the configuration in the Synchronization Service the database tables gets awful fragmented. This can decrease your performance a great deal.

The Synchronization Service is supposed to defragment it by itself, but does not do a great job.

I do a rebuild on the indexes at least once a day to keep the performance up.

Make sure, that no MA's are running, and that the configuration is not changed while running the script.

PARAM
(
  [String]$fimSyncSqlServer
)

$FIMSQLCONNECTION = New-Object System.Data.SqlClient.SqlConnection("Server="+ $fimSyncSqlServer + ";Database=FIMSynchronizationService;Trusted_Connection=True;")

write-host "Start Rebuilding Indexes"

$FIMSyncTables = "mms_connectorspace","mms_cs_link","mms_csmv_link","mms_extensions","mms_joiner_log","mms_lineage_cross_reference","mms_management_agent","mms_metaverse","mms_metaverse_lineagedate","mms_metaverse_lineageguid","mms_metaverse_multivalue","mms_mv_link","mms_partition","mms_run_history","mms_run_profile","mms_server_configuration","mms_step_history","mms_step_object_details","mms_tracking_entries","mms_tracking_entries_history"

$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $FIMSQLCONNECTION
$command.CommandTimeout = 600

$FIMSQLCONNECTION.Open()
foreach ($table in $FIMSyncTables)
{
  write-host "Rebuilding Index On " $table
  $command.CommandText = "ALTER INDEX All on " + $table + " REBUILD PARTITION = ALL"
  $command.ExecuteNonQuery()
}
$FIMSQLCONNECTION.Close()
write-host "Ended Rebuilding"