Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database for our ticket system in SQL Server express. I was poking around and noticed that a lot of the indexes have a fillfactor set to 20. What is the signifigance of it being set to 20? Why would you set it so low?

share|improve this question
add comment

2 Answers 2

up vote 7 down vote accepted

@ 20 % you're consuming 5 times the disk actually needed for the index. I'm sure there's some business case where that might make sense, but I've never seen it. If you aren't rebuilding these regularly (rebuild returns to original fill factor level, in this case 20%), I'd be interested to see how full the indexes are after a month of operation. General ref: http://msdn.microsoft.com/en-us/library/ms177459.aspx

share|improve this answer
3  
I'm willing to bet someone was confused and was wanting to specify 80% full. –  jl01 Mar 24 '12 at 13:21
1  
Agreed, whoops :) –  Eric Higgins Mar 24 '12 at 20:06
add comment

In SQL Server data is stored in to pages. And each page is of size 8K. And in simple terms fillfactor indicates % fullness of any page when index is created or rebuild operation is done.

Fillfactor plays important role if data in tables is modified very frequently. Having a fillfactor other than 0 or 100 actually leaves some free space in that page and it improves speed of insert operation, by avoiding an effect called "page splits".

But downside of having very small fillfactor is, it slows down read operations. And as @Eric mentioned it is very strange that someone has set it up to 20%. May be you want to change it between 70-90% with some trials.

share|improve this answer
add comment

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.