Maintenance of Indexes and Fulltext-Catalog of FIM SQL databases


This is a follow-up to 2 posts I wrote in the past. To avoid index fragmentation and the issues I had with sets in the past I implemented 2 SQL Jobs to keep them clean.

Speed up FIM 2010 R2 SQL performance by rebuild/reorganize indexes

FIM 2010 R2: SQL timeout on using large sets in other sets

For around a month now the following to SQL jobs work perfectly in my customer’s environment even in production, so I think it seems to be safe to implement this, but you should test them on your own.

By default both SQL jobs run weekly on every sunday.

The first SQL job starts a PowerShell script to maintain the indexes on all tables of the FIMService database. The script works only on indexes with fragmentation higher than 20% and will only rebuild 100 indexes per schedule (You can adjust this in the script).

The PowerShell script is not my work, see author information in the scripts URL.

Make sure you replace the DOMAIN\USER and SERVERNAME placeholder in the script to values that fits to your environment.

USE [msdb]
GO

/****** Object:  Job [Index Rebuild on all FIMService tables]    Script Date: 11/17/2013 14:08:12 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/17/2013 14:08:12 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Index Rebuild on all FIMService tables',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'No description available.',
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'DOMAIN\USER', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Index rebuild step]    Script Date: 11/17/2013 14:08:12 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Index rebuild step',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'PowerShell',
		@command=N'PUSHD SQLSERVER:\SQL\SERVERNAME\DEFAULT

# PowerShell script iterated over tables in database, gathers set of indexes
# then for every index, gathers all partitions and performs RebuildIndex on that partition
# To execute this script:
#      Launch SQL PowerShell ( Start -> Run -> sqlps.exe)
#      in Powershell window CD  SQL\machine_name\instance_name; Example: CD SQL\demo-machine\DEFAULT
#      Copy the following script and paste it in SQL powershell window  to run this script
# http://sethusrinivasan.com/2012/02/14/index-rebuild-on-large-database-sql-agent-powershell-job/

# following variables can be updated
# database Name
$dbName = "FIMService"
# number of indexes to rebuild, script terminates after Rebuilding specified number of indexes
$indexesToProcess = 100
# fragmentation threshold - indexes with fragmentation less than this value will be skipped
$fragmentationThreshold = 20

$processedIndex = 0
$tables = dir Databases\$dbName\Tables
"Listing all tables from Database:" + $dbName

foreach($table in $tables)
{
   "    Listing Indexes for Table:" + $table
   foreach($index in $table.Indexes)
   {
        "    Listing Physical Partitions for Indexes:" + $index
        foreach($partition in $index.PhysicalPartitions)
        {
            $fragInfo = $index.EnumFragmentation([Microsoft.SqlServer.Management.Smo.FragmentationOption]::Sampled,
                                    $partition.PartitionNumber)
            $fragmentation = $fragInfo.Rows.Item(0)["AverageFragmentation"]

            "        Checking fragmentation on " +  $index.Name + " is greater than :" + $fragmentationThreshold
            "        Current Fragmentation:" + $fragmentation
            "        Paritition:" + $partition.PartitionNumber
            if($fragmentation -gt $fragmentationThreshold)
            {
                "        Rebuilding Index: " + $index.Name + " partition:" + $partition.PartitionNumber
                $processedIndex = $processedIndex + 1
                if($index.IsPartitioned -eq $True)
                {
                    $index.Rebuild($partition.PartitionNumber)
                }
                else
                {
                    $index.Rebuild()
                }
            }

            if ( $processedIndex -ge $indexesToProcess)
            {
                break
            }
        }

        if ( $processedIndex -ge $indexesToProcess)
        {
            break
        }
    }

    if ( $processedIndex -ge $indexesToProcess)
    {
        break
    }
}

POPD',
		@database_name=N'master',
		@flags=48
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Index rebuild weekly',
		@enabled=1,
		@freq_type=8,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20120214,
		@active_end_date=99991231,
		@active_start_time=130000,
		@active_end_time=235959,
		@schedule_uid=N'69a997b3-6475-4c18-bd87-9f4cf27e687a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The second SQL job uses the SQL internal function to do a rebuild on the FIMService full text catalog. You can user this SQL script or the build-in wizard to create this job.

If using this script make sure to replace the DOMAIN\USER placeholder to values that fits to your environment.

USE [msdb]
GO

/****** Object:  Job [Start Optimize Catalog Population on FIMService.ftCatalog]    Script Date: 11/17/2013 14:13:07 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Full-Text]    Script Date: 11/17/2013 14:13:07 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Full-Text' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Full-Text'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Start Optimize Catalog Population on FIMService.ftCatalog',
		@enabled=1,
		@notify_level_eventlog=2,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'Scheduled full-text optimize catalog population for full-text catalog ftCatalog in database FIMService. This job was created by the Full-Text Catalog Scheduling dialog or Full-Text Indexing Wizard.',
		@category_name=N'Full-Text',
		@owner_login_name=N'DOMAIN\USER', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Full-Text Indexing]    Script Date: 11/17/2013 14:13:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full-Text Indexing',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=-1,
		@on_fail_action=2,
		@on_fail_step_id=-1,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'USE [FIMService]
ALTER FULLTEXT CATALOG [ftCatalog] REORGANIZE
',
		@database_name=N'master',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly FIM.ftCatalog rebuild',
		@enabled=1,
		@freq_type=8,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=1,
		@active_start_date=20131024,
		@active_end_date=99991231,
		@active_start_time=120000,
		@active_end_time=235959,
		@schedule_uid=N'6ba433a3-79eb-4552-ba0b-5f1cc9d5dc1b'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
Advertisements

About Peter Stapf
Senior Consultant Identity and Access MVP (Enterprise Mobility)

8 Responses to Maintenance of Indexes and Fulltext-Catalog of FIM SQL databases

  1. Ari Friedma says:

    Does this apply to FIM 2010 Not R2 as well ?

  2. Peter Stapf says:

    I am not quite sure, but i think so, because it is more an SQL thing then an FIM issue.
    In addition the changes from FIM 2010 to R2 were not that big from the FIM Service perspective.

    And as I remember the KB articles and documentations from Microsoft about this are also related to FIM 2010 R1

    The above solution is working now without any problems in my environment incl. production but you should always test this in your dev environment if you are having this issue in portal.

  3. Database maintenance is an issue for all applications that write to databases. Writing (inserting, updating, deleting) caused fragmentation. Fragmentation will eventually cause problems. see http://blog.ilmbestpractices.com/2014/10/sql-maintenance-for-fim-and-anything.html

  4. Peter Stapf says:

    Thanks for the Link David, looks like a neat script that saves a lot of time genarating all the maintenance jobs.
    I will give it a try in my local dev environment.

  5. Sheldon.Jaquay says:

    Excellent stuff gentlemen, I’ll also give both a try in my dev. I’ve been having some consistent growth with my tlogs on rebuilding all indexes weekly and this might just slow that growth down, Thanks :).

  6. Russ Lema says:

    David, looks like your page is currently unavailable.
    http://www.ilmbestpractices.com/

  7. Russ, Thanks for the heads up. It is back up. My web hoster had an error. We’ll see if I forgive them.

  8. That could be complicated, but your sewing machine ought to
    have an arm you might use for this specific function.

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