The fill factor option is provided for fine-tuning index data storage and performance. It is available on the CREATE INDEX and ALTER INDEX statements and provide for fine-tuning index data storage and performance. The fill-factor value affects an index when it is created or rebuilt. It determines the percentage of space on each page filled with data. The unused space is reserved for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each page will empty (excluding the root page). As data fills the table, the index keys use this space. The fill-factor value is an integer from 0 to 100 representing the percentage. The server-wide default is 0 which is deemed to be 100.

The best fill-factor depends on the purpose of the database and the type of clustered index If the database is primarily for data retrieval, or the primary entry is sequential, a high fill-factor will pack as much as possible in an index page. If the clusterered index is non-sequential (such as a natural primary entry), then the table is susceptible to page splits, so use a lower page fill-factor and defragment the pages often.

Here is the way to Retrieve Count of Fill-Factor Used in a Database as

Select fill_factor, Count(1) as NoOfIndexes From Sys.Indexes Group by fill_factor

Here are more details and explained article http://www.mssqltips.com/sqlservertip/1940/understanding-sql-server-index-fill-factor-setting/
OR
http://www.sqlskills.com/blogs/kimberly/category/FILLFACTOR.aspx


Advertisements
Comments
  1. i’m glad i could learn so much here in your blog. thanks a lot. lista de email lista de email lista de email lista de email lista de email

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s