2

sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent.

How does this fit in with the fill factor of an index?

For example:

I have index idx_HeaderInfo

I create the index with a fill factor of 80%.

If I then immediately look at avg_page_space_used_in_percent do I see:

  • 100

  • 80

I ask because I need a way of rebuilding or reorganising based on internal fragmentation rather than external and I don't want to create logic to rebuild/reorg if a avg_page_space_used_in_percent < 75 if the fill factor in the first place is 70 for instance.

Improve this question
4
  • You should rebuild or reorganize based on avg_fragmentation_in_percent why are you using other column details
    – Shanky
    Commented Aug 24, 2015 at 13:13
  • avg_fragmentation_in_percent only identifies external fragmentation. I'm after internal fragmentation only. The environment I work in is on a SAN where data is stored across many disks, therefore is always random.
    – Hpk
    Commented Aug 24, 2015 at 13:23
  • It should always be % of page (8K), not % of (8K * fill factor / 100). Did you try it? Seems like a simple test.
    – Aaron Bertrand
    Commented Aug 24, 2015 at 13:30
  • Yeah I realized I could try it out after posted. Will answer my question :)
    – Hpk
    Commented Aug 24, 2015 at 13:32

2 Answers 2

Reset to default
1

avg_page_space_used_in_percent does not care about fill factor.

Therefore if you set an index to have fill factor of 40, your avg_page_space_used_in_percent will straight away be 40.

Improve this answer
0
0

@Hpk, since you have already answered your own question, my answer will provide you some food for thoughts :

I ask because I need a way of rebuilding or reorganizing based on internal fragmentation rather than external and I don't want to create logic to rebuild/reorg if a avg_page_space_used_in_percent < 75 if the fill factor in the first place is 70 for instance.

I believe you are writing your own scripts to detect fragmentation and address it based on some criteria. Remember - there is a cost to reinvent the wheel (courtesy of Aaron Bertrand)

I would suggest you to look into - a more intelligent way of addressing your index fragmentation issues. (hint: SQL Server Index and Statistics Maintenance by Ola hallengren)

The benefit of using Ola's script is that it has logging functionality [logs to dbo.CommandLog table] which over time will become a gold mine and you can get real value out of it.

e.g.

  • If you are deploying index maintenance, then you can use dbo.CommandLog to find out what indexes are frequently getting fragmented often, the time taken to defragment them and if you join to sys.indexes then you can tie up FILL FACTOR and adjust it. Check - monitoring-for-endless-index-defragmenting.
  • Also, if you have DatabaseIntegrityCheck - CHECKDB solution, then you can find out how long it takes to perform CHECKDB.

Also, Brent has an excellent post on - Stop Worrying About SQL Server Fragmentation

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.