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
>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?
>
>.
>|||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...
> 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
>>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.
>--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
>news:163401c515e7$89283730$a401280a@.phx.gbl...
>> 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
>>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?
>>
>>.
>
>.
>sql

filegroups / reindex

Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk Jan
Does it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel
|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:

> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>
|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel
|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:

> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>
|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel
|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:

> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>

filegroups / reindex

Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:

> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:

> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:

> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>

filegroups / reindex

Hi,
We've installed a db with filegroups for data and indexes.
Each filegroup has two equaly sized files spread over two diskdevices.
When data is loaded it's spread equally over the files, if we do a reindex
over all tables and look (via Tasks/Shrink/Files) at the file useage
it has changed from equally spread 50-50% to 65-35%.
any ideas?
Derk JanDoes it mean that your db has 2 filegroup - one for the data and one
for the indexes. And after you loaded the data in, they were
distributed equally. But after the reindex, the proportion changed and
no longer half half?
Index has two kind - clustered and non-clustered. Clustered is with
tables (data itself stored in an order of the index). Non-Clustered
can be stored somewhere else, which I believe you put it into another
filegroup. The size of non-clustered index varies depends on what
columns you have indexed on. The more columns you index, the bigger
the index.
After re-index, the size will change (unless no modification has been
made). So what you observe is normal to me or I must miss sth from
your post.
Mel|||configuration as as you described it.
In our situation however the data was loaded -not altered- and then
re-indexed and then the proportion is no longer 50-50.
tanx,
Dick
"MSLam" wrote:
> Does it mean that your db has 2 filegroup - one for the data and one
> for the indexes. And after you loaded the data in, they were
> distributed equally. But after the reindex, the proportion changed and
> no longer half half?
> Index has two kind - clustered and non-clustered. Clustered is with
> tables (data itself stored in an order of the index). Non-Clustered
> can be stored somewhere else, which I believe you put it into another
> filegroup. The size of non-clustered index varies depends on what
> columns you have indexed on. The more columns you index, the bigger
> the index.
> After re-index, the size will change (unless no modification has been
> made). So what you observe is normal to me or I must miss sth from
> your post.
> Mel
>|||Dick,
When you said data was loaded? Did you put index on before the data
was loaded? Did you turn it on afterwards? What tool did you use to
load the data in?
Mel|||Hi Lam,
Data is loaded with regular SQL stored-procs.
All tables/index definitions where in place when data was loaded, no changes
in table definitions were made after the load.
After the load the dbcc reindex was executed.
cheers,
Dick
"MSLam" wrote:
> Dick,
> When you said data was loaded? Did you put index on before the data
> was loaded? Did you turn it on afterwards? What tool did you use to
> load the data in?
> Mel
>|||The only thing I could think of is the fillfactor field. The
fillfactor may have be changed during the rebuild?
fillfactor
Is the percentage of space on each index page to be used for storing
data when the index is created. fillfactor replaces the original
fillfactor as the new default for the index and for any other
nonclustered indexes rebuilt because a clustered index is rebuilt. When
fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
when the index was created.
I assume you use DBCC REINDEX.
Mel|||Hi,
Did an additional DBCC DBREINDEX ( 'table_name' [,'index_name' [ ,
fillfactor ] ]) on all tables with same fill factor as the tables were
created with, and still
experiencing and uneven spread of data (300MB files 15%-65% spreading), not
off indexes.
Dick
"MSLam" wrote:
> The only thing I could think of is the fillfactor field. The
> fillfactor may have be changed during the rebuild?
> fillfactor
> Is the percentage of space on each index page to be used for storing
> data when the index is created. fillfactor replaces the original
> fillfactor as the new default for the index and for any other
> nonclustered indexes rebuilt because a clustered index is rebuilt. When
> fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified
> when the index was created.
> I assume you use DBCC REINDEX.
> Mel
>