0

I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor. I found a YouTube video Brent Ozar did on defragmenting & fill factor of indexes which was most insightful. All my fill factors are currently 100%, but I know that this isn't good for all my indexes. I found this article/script about determining a better fill factor for certain indexes. If this article is still applicable, this brings me to my question:

I'm using the Ola Hallengren scripts to help keep my indexes tidy, but I don't see a way to leave the current fill factor alone. It will rebuild/etc with either the system setting or the specified one but not the current one if it is different.

Does anyone have suggestions/practices they use if an index needs to be rebuild with a specific/different fill factor?

  • Both answers are somewhat helpful, but let me try to clarify a little: My index reorgs/rebuilds are working well. Everything is fully defragmenting overnight that needs to. My problem now is the fill factor. Let's say I have 5 indexes. All of which are 100% fill factor. Through analysis, I determine that one of the indexes would do better with a 60% fill factor so I change it. The script only lets me specify a mass fill factor or the system one. In this case, neither one is what I want. I want to defragment the indexes but keep whatever their current fill factor happens to be. – thatstevecena May 31 '18 at 11:19
1

I'm not sure why you think those are the only options.

If you look in IndexOptimize, the @FillFactor variable is passed in as NULL by default.

The command that appends Fill Factor looks like this:

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

If you leave it NULL, the index will get rebuilt at its current Fill Factor.

Your only task would be to remove any Fill Factor hints from the Agent jobs, etc.

1

Complementing to Erik's answer, what I currently do is use CommandLog table to find patterns for endless index fragmentation and adjust it.

If you are deploying index maintenance, then you can use dbo.CommandLog to find out what indexes are frequently getting fragmented, the time taken to defragment them and if you join to sys.indexes then you can tie up FILL FACTOR and adjust it.

See my answer for more details.

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.