Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

I asked a similar question, but specifically for clustered indexes made up of identity fields. Had good answers:

http://serverfault.com/questions/21091/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.

share|improve this answer
    
@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. –  Sung Oct 18 '09 at 21:56
    
I use Michelle Ulford's (AKA SQLFool) stored procedure to do daily index maintenance: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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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