Sunday, March 14, 2010

What is fill factor

When you create clustered index, SQL server will create index pages to store the data in order to clustered index. When user will insert new record or update clustered index column data then SQL server will change its index pages data. For example if you will insert new data into table, SQL server will shift its data from one data page to another to make sufficient space to insert new record in correct place. It will decrease your query performance. This also implies with non-clustered index.

Fill factor is used at the time of creating index. Fill factor is percentage value from 0 to 100. Fill factor option will add some spaces in data pages so when user will add new record it will easily manageable for SQL server to insert record in any data page.

Fill factor value 100 means there is no space required in data pages. When your table is read only or you are sure about there is no change in your table in future then you can provide 0 as a fill factor.

Lower fill factor value leaves more spaces in data pages. So whenever user will add new record it will easily manageable for SQL server to make sufficient space to add the record in particular order. It will increase speed of your query.

Fill factor imply only when you will create index. It is not maintained after records are added, updated or deleted.

No comments:

Post a Comment

DotNet Code Guru