FILL Factor blurb to decide
As all of us know that while creating Index for a Table there is Option for specifying Fill factor. This option specifies how full SQL should make in the Index Page while creating Index.
Why this option is provided, should it not by default 100 ?
What are the advantages of specifying less than 100 (as option is provided) ?
Read fill factor section from SSP indexes link.
By avoiding page splits you know that your fill factor is doing its job is you do not see these show up in performance monitor output. To know more about page splits:
look into (SQLServer:Access Methods:page splits/sec).
However, if page splits increase significantly over time then you will like have to recreate the index(es) with fillfactor on a regular basis.
IMHO and experience fillfactor to use is directly dependant upon what the table is used for. Tables that have a lot of insert activity would probably benefit from a lower fill factor, tables that have only read activity will benefit from a higher fill factor. The books online topic for Create Index has a good description of pad index.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.