3

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.

alt text

Community
  • 1
dance2die
  • 1,961
  • 7
  • 31
  • 40

2 Answers2

3

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.

Community
  • 1
SuperCoolMoss
  • 1,252
  • 11
  • 20
  • @SuperCoolMoss: Thanks for pointing me to the related question. How do you record actions to review and see what the appropriate fill factor should be? Do you also change fill factor periodically? I am rebuilding/reorganizing indexes but never thought about changing fill factor values periodically. – dance2die Oct 18 '09 at 21:56
  • I use Michelle Ulford's (AKA SQLFool) stored procedure to do daily index maintenance:http://sqlfool.com/2009/06/index-defrag-script-v30/ This records the actions within a table in my DBA maintenance database. If over a period of time I notice an index is being repeatedly rebuilt because of high fragmentation - I consider lowering the fill factor. – SuperCoolMoss Oct 18 '09 at 22:44
1

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).

mrdenny
  • 27,074
  • 4
  • 40
  • 68