This is a follow-up question on Stack Overflow question
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.
