2

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

'
  • Also you probably don't want to use the internal sp_msforeachdb - try using Aaron's (in the previous link) or Brent Ozar's version for true reliability. – LowlyDBA Oct 10 '17 at 14:58
3

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

  • Is this for SharePoint? I was really after some SharePoint 2013 specifics. – Andrew Gaskell Oct 10 '17 at 14:53
  • This applies to any SQL Server Sharepoint backend. – Kris Gruttemeyer Oct 10 '17 at 14:56
  • So you have both of the timer jobs turned off Kris? – Andrew Gaskell Oct 10 '17 at 15:00
  • Correct, I don't let Sharepoint boss my SQL server around. I do the maintenance from SQL Server Agent jobs behind the scenes. – Kris Gruttemeyer Oct 10 '17 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.