pad index and fill factor

We have a new database set up on MS SQL Server 2005. It was noticed that the
pad index has not been set and I have been asked to look into what is a
recommended value.
What is the rule of thumb when setting the pad index and fill factor for
optimum performance?The value for fill factor will depend on what the level of read to write is
upon the relevant index.
For example , if it's going to be a read only then keep the fill factor at 0
as you won't need to allow extra spacing in the pages for new inserts.
On the other hand , if there will be large amounts of inserts on the index ,
then you may want to increase the fill factor above 0 .
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:un3uE4QoGHA.3836@.TK2MSFTNGP04.phx.gbl...
> We have a new database set up on MS SQL Server 2005. It was noticed that
the
> pad index has not been set and I have been asked to look into what is a
> recommended value.
> What is the rule of thumb when setting the pad index and fill factor for
> optimum performance?
>|||Pad index and fill factor are only applied when an index is created,
or rebuilt (DBCC DBREINDEX, ALTER INDEX in 2005). As such they are
not meaningful until you have data in the database. Is your new
database populated yet?
Likewise, you need to plan your schedule for rebuilding the indexes
and fit that into the calculation. If you are rebuilding them weekly
it is probably a mistake to build in enough elbow room for two months
worth of activity. And the reverse would probably be even more
important, not to build in one week of free space if the rebuild
schedule is two months.
The key to setting these well is knowing your data, your tables, and
your indexes. Correct values vary by index, and by how the table is
used. An index on an identity column, where new values are always
added to the end, will not be helped by having free space all through
the index. The same is true of any table where the clustered index
causes inserts to go to the end. Think of an Invoices table,
clustered in InvoiceDate, as an example. A reference table that
changes very slowly might fall in the same category.
On the other hand, an index on the Customer column of the Invoices
table would probably benefit from some free space, as new invoices
will cover the range of customers.
Hope that helps.
Roy Harvey
Beacon Falls, CT
On Thu, 6 Jul 2006 09:08:19 -0600, "Loren Z"
<anonymous@.discussions.microsoft.com> wrote:

>We have a new database set up on MS SQL Server 2005. It was noticed that th
e
>pad index has not been set and I have been asked to look into what is a
>recommended value.
>What is the rule of thumb when setting the pad index and fill factor for
>optimum performance?
>|||One other tidbit to add to these answers about usage.
Just to be clear, "fill factor" represents the percentage to fill the data
page files on index creation or rebuild at the *leaf* layer of the index (if
you have a OLTP system, this is the level were the most expensive
fragmentation occurs).
Pad Index represents the same percentage (in fact, you can't set a different
value for Pad Index than you do for Fill Factor) at the *non-leaf* level of
the index. It's off by default because the cost of fragmentation at that
layer (sometimes referred to as an "unbalanced tree") is usually only
measureable, not visible.
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:un3uE4QoGHA.3836@.TK2MSFTNGP04.phx.gbl...
> We have a new database set up on MS SQL Server 2005. It was noticed that
> the pad index has not been set and I have been asked to look into what is
> a recommended value.
> What is the rule of thumb when setting the pad index and fill factor for
> optimum performance?
>