Dynamics Community Forum Thread Details

As part of CRM maintenance jobs located under ScaleGroupOrganizationMaintenanceJobs table (MSCRM_CONFIG), reindexALL (operationType = 30) should be considered one of the most important jobs to make sure CRM is performing properly.

This job is responsible to handle fragmentation of indexes and perform some actions around statistics.

It is very common customers are not monitoring these jobs or even aware they exist.

If this is not running with success everyday, and depending how much data on a specific table is changed, this might lead to SQL not deciding the best execution plans for the queries and eventually cause SQL timeouts.

There might be scenarios where a customer decided to "disable" reindexALL job, by putting its next run time to for example year 2099. This would make sense if a customer DB is very complex and it was decided to create a SQL custom job to handle fragmentation instead of the OOB job.

Focusing here on ReindexALL only we should always make sure:

The scheduled time is during Dark hours, or when the volume is low. This is important to make sure the job will not cause blocking to user triggered queries and/or make sure other actions around CRM to not affect a correct execution of this job.
No other daily maintenance job is scheduled to run around the same time. OperationType 14 and 15 (DeletionService and IndexManagement) should be scheduled before reindexALL and separated by 20 minutes each.

There will be some occasions where ReindexALL job might timeout, either because another process was running at the same time, or because there was too much data to handle.

The default timeout for the job is 20minutes.

On crm 2016 (v8), we introduced a setting where we can define the timeout of the reindexJob.

use MSCRM_CONFIG
select * from DeploymentProperties where ColumnName like '%ReindexOperationTimeout%'

Any previous version will have this value hardcoded and cannot be changed.

Please be carefull changing this value as it might cause the job to overlap with other important tasks your are doing on your CRM, causing undesired effects.

If used, it should be monitored closely and used just as a one time fix to allow reindexALL to process "accumulated" fragmentation.

The ideal scenario would always be that the OOB schedule completes with success everyday.

Again, if there was a day with a huge amount of data changed, or SQL server was slow/blocking, the job could hit a timeout if the default 20 minutes are not enough.

A method that can be used for all CRM versions is by triggering ReindexALL manually. This can be usefull to manually define a timeout and/or other variables, with the single objective of "catching up" failed executions, so we go then into an healthy state where the daily job runs with success.

CRM organization databases contain a stored procedure called p_ReindexALL.

This stored procedure can be triggered on demand by passing the required parameters.

Depending on the CRM version, these parameters could be less or more, so it would require an evaluation of the stored procedure before running it.

In the context of CRM v9.1, we should pass 14 paramters:

@AllIndexTypes int = 1, --0:Clustered Index only, 1: Clustered and Non Clustered indexes
@MaxRunTime int = NULL, --Maximum allowed running time (in seconds)
@FragRebuildPctHigh int = 30, --Min. Percentage of fragmentation above which indexes are REBUILD unless @DefragType is set to 1.
@FragRebuildPctLow int = 10, --Min. Percentage of fragmentation at which indexes are REORGANIZED unless @DefragType is set to 2.
@MaxFragPctToRebuild int = 100, --Max. Percentage of fragmentation above which indexes won't be rebuild or reorganized; useful for defrag within a defined range first, refer usage below
@DefragType int = 0, --0: default (REORGANIZE fragmentation less than @FragRebuildPctHigh%; REBUILD otherwise)
--1: ONLY perform ALTER INDEX ... REORGANIZE irrespective of fragmentation
--2: ONLY perform ALTER INDEX ... REBUILD irrespective of fragmentation
@MinPages int = 1000, --do not touch tables less than 1000 pages
@Verbose int = 0, --1: Print progress messages and detailed results including additional scan for final fragmentation after reindex completes for reporting old/New ragmentation.
@Online bit = 1, --1: rebuild indexes ONLINE ONLY, if fails due to BLOB, perform REORGANIZE;
--0: rebuild indexes ONLINE first, if fails due to BLOB, perform OFFLINE rebuild
@Maxdop int = 1, --1: default
@SortInTempDB bit = 0, --0: default
@ScanMode int = 0, --0: default (SAMPLE: Query optimizer uses sampled data and computes the sample size by default )
--1 : FULLSCAN
@StatisticsNoRecompute bit = 0, --0: default ( OFF )
@StatsMaintenanceinBG int = 1 --DEPRECATED 1: Only do the stats maintenance in BG no index operations are performed.
)

Bellow are two examples that can be used directly in SSMS:

exec p_ReindexAll 1, NULL, 30, 10, 100, 0, 1000, 1, 1, 1, 0, 0, 0,1

In this example we pass NULL as a second parameter, so we give infinite time for the stored procedure to complete, the only parameter that is really changed here and that is not OOB is the verbose flag set to 1.

exec p_ReindexAll 1, 21600, 30, 10, 100, 0, 1000, 1, 1, 1, 0, 0, 0,1

In that example we pass 21600 as a second parameter so six hours (in seconds), so we are not giving infinite time for the stored procedure to complete as above verbose flag set to 1.

The reason for giving 6 hours is to avoid slow performance the next day, so typically we could start running the ReindexAll job from SSMS for 6 hours. If more than 6 hours are needed, the script will stop and can then be executed the next day to carry on maintaining the database for extra 6 hours. On some occasion it may take 24 hours even more if the database has not been maintained so this may also be done in the weekend when in most situation load is less. It’s a good practice to specify a maximum time between 6 to 8 hours. When using setting the verbose flag the script will also output what it managed to do over the specified period. As always, Reindexing is a costly and can be intrusive operation, as a result some blocking may occur. As a best practice always try to plan during non-peak load hours or dark hours, start small for example 30 minutes so 3600 seconds and then re execute and grow until the next day.

One key advantage of running exceptionally the p_ReindexAll using SSMS is that it avoids SQL timeouts for this specific stored procedure. In many situations this is one of the common issues as well, the ReindexAll applies a timeout (ReindexOperationTimeout) and if it exceeds everything is rolled back.

As a conclusion, the proper workflow to use this procedure would be:

Monitor table ScaleGroupOrganizationMaintenanceJobs and focus on lastruntime and lastresultdata and operationtypes 14 ,15 and 30 (deletionservice, indexmanagement, reindexall)
If there is a failure on ReindexALL where it didn't have enough time to complete, maybe it was a one time problem and monitor again the next day.
If it still fails, evaluate extending the timeout (carefully) or using the manual stored procedure.
Execute until it finishes the manual stored procedure one time and monitor the next day the automated schedule of the job.
If after running the manual procedure, the automated job still does not run with success, either evaluate changing the date of the schedule, extend the timeout on Deployment Properties table, evaluate your SQL health, or if your DB is very complex, evaluate 3rd party defrag tools