Fill Factor

Isn't it true that Sql Server does not maintain the fill
factor/pad index? i.e. to redo the fill factor, you must
reindex/defrag? If your fill factor is 100%, you're
going to get poor write performance, correct? Could this
cause your Service Times (on an EMC SAN) to significantly
increase to unacceptable levels?
Having a 100% full page does not mean you will have write problems. It
depends on the clustered index and the order in which you insert the data.
But it is likely you will get page splits with a 100% fill factor. This is
not generally recommended on tables that are not static or read only. Each
time you split a page it has to move half the data onto a new page. This
can cause lots of I/O in a busy system.
Andrew J. Kelly SQL MVP
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:05b301c515e1$490caec0$a501280a@.phx.gbl...
> Isn't it true that Sql Server does not maintain the fill
> factor/pad index? i.e. to redo the fill factor, you must
> reindex/defrag? If your fill factor is 100%, you're
> going to get poor write performance, correct? Could this
> cause your Service Times (on an EMC SAN) to significantly
> increase to unacceptable levels?
|||Thx for the info.
I know that Service Time is a good indicator of the disk
struggling. Mb/Sec seems reasonable to our admin but he
was saying that IO's/sec looked high to him. What did he
mean by that? Is an I/O to him the same as "commit work"
to me?

>--Original Message--
>Having a 100% full page does not mean you will have
write problems. It
>depends on the clustered index and the order in which
you insert the data.
>But it is likely you will get page splits with a 100%
fill factor. This is
>not generally recommended on tables that are not static
or read only. Each
>time you split a page it has to move half the data onto
a new page. This
>can cause lots of I/O in a busy system.
>--
>Andrew J. Kelly SQL MVP
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:05b301c515e1$490caec0$a501280a@.phx.gbl...
fill[vbcol=seagreen]
must[vbcol=seagreen]
this[vbcol=seagreen]
significantly
>
>.
>
|||just look at Physical Disk: Current Disk Queue Length That is a good
indicator of problems..
You want this number to be as low as possible (in a perfect world, Zero). IF
the number is pegged, you have serious issues.
Also monitor Physical Disk Split IO/Sec OR SqlServer Access Methods - Page
Splits/sec.
again you want splits to be Zero in a perfect world.
adjust fill factor as needed to reduce Splits.
If the table is really active, you might need to set fill factor to as low
as say "50". I usually start be setting it around 80% and monitor before
going nuts on it.
IF your clustered index is on an identity column or on some other
"Monotonically Increasing Value", then inserts *should* always go to bottom,
so splits may not be an issue.
over time, splits will massively fragement your indexes and performance will
totally tank.
Each table must have a clustered index as well otherwise Defrags are
fruitless.
Monitor fragmentation levels using DBCC ShowContig. The Scan Density Number
should stay ABOVE 80% or you need to consider defragging indexes.
hope this helps
Greg Jackson
PDX, Oregon
"OX6" <anonymous@.discussions.microsoft.com> wrote in message
news:163401c515e7$89283730$a401280a@.phx.gbl...[vbcol=seagreen]
> Thx for the info.
> I know that Service Time is a good indicator of the disk
> struggling. Mb/Sec seems reasonable to our admin but he
> was saying that IO's/sec looked high to him. What did he
> mean by that? Is an I/O to him the same as "commit work"
> to me?
> write problems. It
> you insert the data.
> fill factor. This is
> or read only. Each
> a new page. This
> message
> fill
> must
> this
> significantly
|||Thx.
>--Original Message--
>just look at Physical Disk: Current Disk Queue Length
That is a good
>indicator of problems..
>You want this number to be as low as possible (in a
perfect world, Zero). IF
>the number is pegged, you have serious issues.
>Also monitor Physical Disk Split IO/Sec OR SqlServer
Access Methods - Page
>Splits/sec.
>again you want splits to be Zero in a perfect world.
>adjust fill factor as needed to reduce Splits.
>If the table is really active, you might need to set
fill factor to as low
>as say "50". I usually start be setting it around 80%
and monitor before
>going nuts on it.
>IF your clustered index is on an identity column or on
some other
>"Monotonically Increasing Value", then inserts *should*
always go to bottom,
>so splits may not be an issue.
>over time, splits will massively fragement your indexes
and performance will
>totally tank.
>Each table must have a clustered index as well otherwise
Defrags are
>fruitless.
>
>Monitor fragmentation levels using DBCC ShowContig. The
Scan Density Number
>should stay ABOVE 80% or you need to consider defragging
indexes.
>
>hope this helps
>Greg Jackson
>PDX, Oregon
>
>"OX6" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:163401c515e7$89283730$a401280a@.phx.gbl...
disk[vbcol=seagreen]
he[vbcol=seagreen]
he[vbcol=seagreen]
work"
>
>.
>