SQL Server SharePoint – Ola Hallengren Parameters for Index Rebuild/Reorganize

ola-hallengrensharepointsql server

I plan on using the IndexOptimize script by Ola Hallengren to replace the SharePoint 2013 On Premise Defragmentation Timer Job.

Anyone out there using it for this purpose? What params do you use and how? Here is what I put together to target the databases that the OOOTB Timer job would normally handle:

EXEC sp_msforeachdb 
'if exists(select 1 from [?].sys.objects where name=''proc_DefragmentIndices'')

EXECUTE dbo.IndexOptimize
@Databases = ''?'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

'

Best Answer

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates. Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update. On large tables, that may be a while and not frequent enough.

SQL2014 and lower - Default sample size will not go parallel, only FULLSCAN will

SQL2016 - Default sample size can also go parallel (Parallel Statistics Update)

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages. If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server

Related Question