1

I'm reviewing a production database with over 400 tables and 658 indexes. I have determined that only 440 of these indexes are being used. Of these, many have fragmentation approaching 100% so I will be rebuilding and reorganizing them as needs require. Many reports are crawling, etc.

One thing I noticed on querying the system views is that all indexes have a fill factor of 0. So my question is twofold: should I aim for a fill factor of 90% and monitor it, and should I adjust the fill factor BEFORE rebuilding indexes?

Improve this question
5
  • Why do you think the index fill factor is the problem? You mention reports are crawling, have you investigated why? Have a look at the query plans for the reports for some ideas
    – Greg
    Mar 27, 2018 at 20:17
  • I have looked at several reports. The tables being joined all have several indexes which are fragmented from between 97 and 99+%. I also noted that the fill factor is 0 across the entire DB.
    – IrishChieftain
    Mar 27, 2018 at 20:24
  • When was the last time the indexes were rebuilt? More importantly, when was the last time statistics were updated? There are cases when changing fill factor can help, but it is rare.
    – Padwan
    Mar 27, 2018 at 20:33
  • Don't know when last rebuilt but would guess long time ago if at all. Stats last updated about 10 months ago. Page fullness on many of the indexes in the 50%+ range.
    – IrishChieftain
    Mar 27, 2018 at 20:48
  • 1
    Are the indexes sequential (e.g. incremental number, current date)? If so, fill the pages to 100% to get better performance. Maybe rebuild them from time to time will be beneficial. Fragmentation is usually a problem when IO is too slow, the fewer pages you have (i.e. fill factor = 0 or 100) the faster it will be.
    – Greg
    Mar 27, 2018 at 21:04

3 Answers 3

Reset to default
1

If you change the fill factor before then the rebuild will build in space. That is probably what you want to do.

If you only want new insert to have the fill factor then do it after the rebuild.

Improve this answer
3
  • Thanks for that. Could you give me some advice on how to approach choosing a fill factor?
    – IrishChieftain
    Mar 27, 2018 at 18:46
  • It depends on how fast you fragment. I find that even 80% is a huge benefit. 90% is also good.
    – paparazzo
    Mar 27, 2018 at 18:48
  • inserts that create new pages at the end of an index are not affected by the fill factor, as per here. Also, when changing fill factor the index is rebuilt as per Pablo's answer. I would suggest reading through the link provided to get some clarification on fill factor before doing anything.
    – DimUser
    Mar 28, 2018 at 7:06
4

Fill factor will leave free space on your index pages. If you know that updates on index keys will need aditional space, then it may be ok to leave that 10% free. But if there will be no changes on index keys, it is better to have no free space on index (remember that all pages will generate logical reads on querys, so More pages = More logical reads = slower execution).

If you change fill factor, immediatly you will be asked to do rebuild

Improve this answer
1

I realise this question is already answered, but I feel there are a few points needed for clarification.

  1. ask yourself if fragmentation is really causing you an issue? Fragmentation is only really a problem when reading from disk. Cache all your data in memory, and concentrate on statistics maintenance (link). Depending on the size of your database, if you can't cache it all in memory do you have a resource problem (e.g. RAM), as opposed to a fragmentation problem? Could your server be getting a high volume of hard page faults, and you don't even realise it?!
  2. How big are your indexes? If your indexes are less than 8 pages then they will be stored in mixed extents, and no amount of index rebuilding is going to solve fragmentation... move on!
  3. What keys are in your index? If any of the keys use something like 'uniqueidentifier', especially as the first key column, then I'm afraid you are unlikely going to solve fragmentation. Due to the nature of this data type, no sooner than you have rebuilt your index, it will be fragmented again after the first few inserts... that's the nature of the beast unfortunately.
  4. If you decide that fill factor is a route you need to go down, DO NOT set it globally for all indexes. Doing so could actually make performance worse. Fill factor increases the free space on index pages, ergo making the indexes larger in size. For an index that has an incremental key, reducing fill factor from 100 (or 0) will likely hinder performance, depending on the size of the index, because you will be causing SQL Server to read more pages to acquire the same amount of data.

Index tuning is a science, there isn't a "one size fits all" solution, so when you ask "should I aim for a fill factor of 90%", this is a genuine time when "it depends". I already posted this link above, but I really recommend you read it, to understand the impact fill factor has.

There are tools out there to help you on your quest (and I don't mean the Database Tuning Advisor!!!). sp_BlitzIndex is the one that I have stuck to over the years... hence my numerous links to their websites!!! :-)

Improve this answer
2
  • Thanks for the info. I've run sp_BlitzIndex and am still trying to evaluate the results. I watched the training video but it's pretty high level. I'm seeing lots of indexes with zero seeks/scans/lookups/writes - so guessing these can safely be deleted?
    – IrishChieftain
    Mar 28, 2018 at 16:28
  • 1
    That would probably be a fair conclusion, but often it's best to monitor index usage over a sustained period of time. Some will say one week, some may say one month, some may say more. Also, remember that certain index stats are reset after certain activities. Some will be reset after an index rebuild, some will be reset after an instance restart. Bear this in mind when analysing your results. Finally, if unsure whether to drop an index, it is possible to disable an index. This way the metadata is still stored in the database and a rebuild will bring it back online.
    – DimUser
    Mar 29, 2018 at 7:14

Your Answer

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

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