Thursday, May 23, 2013

Fill Factor impact on Sql Server performance

Interestingly, fill factor naming convention itself  saying its an argument, which has been to be fill up when performing certain task  none other than creating or rebuilding indexing.

prior to that other interesting concept is page split in Sql Server.In Sql Server data/rows will be stored in pages capacity range of 8kb. Page split operation happens when more number of rows are getting to be inserted in pages.

Fill Factor indicates the percentage that server has to be filled with data in the index pages creation/rebuild.
It has to be configured under level of Server and Database with the integer value limit to 100.
Fill Factor 0 = 100 in Sql server specifies the maximum amount of space filled by the server.
Fill Factor 70 Specifies only 70% of space has to be used for filling up the data into the pages rest is left for further respective insets and Updates.

Recommendation : Data Reading i.e SELECT always recommends 100/0 fill factor value.
More DML operations then default value should be altered based on the data range modification.
default value is '0'
using the syntax: Alter Index/Create Index for altering the value at Individual index level.
sp_Configure at server level fill factor value modification.

No comments: