Fill factor

What fill factor value should I set for index on a table that is hit 10,000
times a day? Is there any other tool to help boost performance along with
fill factor?
Gracias
We'll need just a "bit" more information to answer that question. Does this
table have a clustered index? Is it autoincrementing? >Hit? What is the
percentage of reads to writes for the table? Plenty of available disk
space?
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
> What fill factor value should I set for index on a table that is hit
> 10,000
> times a day? Is there any other tool to help boost performance along with
> fill factor?
> Gracias
|||A dbcc showcontig (tableName) is also useful. Could you put that out here as
well.
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>
|||Hi Jerry,
Thanks a lot for responding.
Table has primary key clustered index w/ fill factor 90%, autogrow 10%, 90%
write, plenty of disk space.
LaEsmeralda
"Jerry Spivey" wrote:

> We'll need just a "bit" more information to answer that question. Does this
> table have a clustered index? Is it autoincrementing? >Hit? What is the
> percentage of reads to writes for the table? Plenty of available disk
> space?
> HTH
> Jerry
> "LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
> news:E010274C-673B-4154-96AC-5A0ED6E9DB20@.microsoft.com...
>
>
|||That is still not enough info to make an intelligent decision. The fill
factor is used mainly to limit the number of page splits that occur in an
index. This is influenced by the column(s) in the clustered index (in your
case), the number of inserts or updates to the clustered key and the values
of the column(s) of the clustered key. If the key is an identity and you
never update the row with data that is larger than the existing data you can
get by with a 100% fill factor as each new row is appended to the end of the
page. If the values are random such as last name then you have to account
for how much space should be there in between index rebuilds so that you get
few to no page splits. But a clustered index is not the only factor. Too
many non clustered indexes can be an issue with lots of writes as well.
Please post the DDL for the table including all indexes, constraints etc.
Andrew J. Kelly SQL MVP
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote:
|||I think it still depends. Is this an autoincrementing PK? If so new
records will be appended to the end of the table. If not, new records could
get inserted anywhere. Are there a lot of updates occuring?
As a general rule of thumb I think tables that are predominately read should
have a higher FILLFACTOR to reduce the number of data pages required to
store the data and the amount of physical I/O required to process queries.
A lower FILLFACTOR would apply for a table with a large number of writes.
This approach would help minimize internal and external fragementation but
at the cost of increased disk space to store the data and possibly a reduced
query performance.
HTH
Jerry
"LaEsmeralda" <LaEsmeralda@.discussions.microsoft.com> wrote in message
news:76B385EC-3E89-4234-BA33-B06555E9A643@.microsoft.com...[vbcol=seagreen]
> Hi Jerry,
> Thanks a lot for responding.
> Table has primary key clustered index w/ fill factor 90%, autogrow 10%,
> 90%
> write, plenty of disk space.
> LaEsmeralda
> "Jerry Spivey" wrote: