Sql-server – FillFactor: IndexOptimize script by Ola Hallengren

indexola-hallengrensql server

We are using Ola Hallengren's IndexOptimize script for index rebuild on SQL Server 2008 and 2012. These maintenance scripts are very good and have worked for our DBs for months now. However, very recently I noticed that it changed the FillFactor to 90 after it executed index rebuild on the DB even when the FillFactor parameter was NULL.

Prior to executing the script, I checked sys.indexes had FillFactor of 0 for all indexes but I am not sure why the script changed FillFactor to 90 after it completed. This caused a lot of issues on the database.

Do you know if this is the intended behavior in IndexOptimize script where the FillFactor gets changed to 90?

Here is what I executed:

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

Best Answer

Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable.

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

Which means someone:

  1. Modified the stored procedure to use 90 as a default value;
  2. Ran it out-of-band from maintenance; or
  3. Changed the values using a different script or the GUI.

You can script out the stored procedure to see if someone changed the default value,

NUTS

Or you can query the dbo.CommandLog table to see if a REBUILD command was run with a Fill Factor of 90.

SELECT *
FROM dbo.CommandLog AS cl
WHERE cl.CommandType = 'ALTER_INDEX'
AND cl.Command LIKE '%FILLFACTOR%'

If someone changed it by using a different script, or the GUI, that would be much more difficult to track down.

Related Question