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?

Improve this question
1
  • 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
    Commented May 31, 2018 at 11:19

2 Answers 2

Reset to default
2

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.

Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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