This is a follow-up question on Stack Overflow question
How to find out SQL Server table’s read/write statistics?
The goal is the same as the previous question
The goal here is to find out appropriate fill factor for indexes
According to Mitch Wheat's answer, he suggested that
... ordinary tables use the default 90% fill factor, high insert tables somewhere between 70 - 85% (depending on row size). Read only tables can utilise a fill factor of 100%
Even after finding out index statistics, I am having trouble making sense of the result.
How do you determine whether a table is a high
- insert
- read
- insert/read table?
Do you have to take all stats like leaf_insert_count
, leaf_delete_count
, user_seek/scan/lookup_count
etc into account?
or Would I need to look at only few domains?
I would love to see what other ways there are to determine how to determine fill factor for a particular index.
Query used to get index stats,
select db_name(US.database_id)
, object_name(US.object_id)
, I.name as IndexName
, OS.leaf_allocation_count
, OS.nonleaf_allocation_count
, OS.leaf_page_merge_count
, OS.leaf_insert_count
, OS.leaf_delete_count
, OS.leaf_update_count
, *
from sys.dm_db_index_usage_stats US
join sys.indexes I
on I.object_id = US.object_id
and I.index_id = US.index_id
join sys.dm_db_index_operational_stats(db_id(), null, null, null) OS
on OS.object_id = I.object_id and OS.index_id = I.Index_id
where I.type <> 0 -- not heap
and object_name(US.object_id) not like 'sys%'
order by OS.leaf_allocation_count desc,
OS.nonleaf_allocation_count desc,
OS.leaf_page_merge_count desc,
US.User_updates desc,
US.User_Seeks desc,
US.User_Scans desc,
US.User_Lookups desc
And sample result.
I asked a similar question, but specifically for clustered indexes made up of identity fields. Had good answers:
Correct value for fill factor for clustered indexes with surrogate identity keys
I decided to globally set the fill factor on all tables / indexes to 80% and ones with identity clustered indexes 90%
I remove fragmentation daily- rebuilding indexes with fragmentation >=30% and reorganising indexes >5% and <30%.
I record these actions in a table which I review periodically and use as a guide to upping / lowering fill factors.
Fill factor is something which you have to play with until you get the right setting.
It doesn't matter how high right the table is. 80% may not always be a good number. If the values will always be in order then use 100%. There's no point in leaving free space in the page if you don't need to.
If you've got an index when new values will be all over the place, then you need a higher fill factor. If you are indexing on a GUID (and not creating the GUIDs in order) then a fill factor of 50% may be needed (depending on table insert rate, index rebuild frequency, etc).