Description for fill factor on index rebuild maint plan misleading

Hi.
I found this misleading issue and thought I would share it.
We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
fillfactor of 90%. After the plan ran, the database grew by about 70% and
the fill factor was actually 10%.
We found the following in the maint plan:
'Change free space per page percentage to:' We entered 10%.
In hindsight, it meant FILLFACTOR and should have been 90%.
Do you also find this misleading?
The documentation (BOL) reads:
Change free space per page percentage to
Drop the indexes on the tables in the database and re-create them with a
new, automatically calculated fill factor, thereby reserving the specified
amount of free space on the index pages. The higher the percentage, the more
free space is reserved on the index pages, and the larger the index grows.
Valid values are from 0 through 100.
It says the HIGHER the percentage, the more free space is reserved. This
should read LOWER?
Is this a 'bug' in the documentation?
Could someone please pass this onto the Microsoft guys. Maybe they know
about this already.
Thanks!
WayneWayne
I agree that it is a little bit confusing
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
rebuild indexes is logged operation and needs a free space to rebuild all
indexes
Kalen Delaney said
"The first definition
is correct; fillfactor specifies how full each page should be. 30 means 30%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
I will report this discrepancy in the Books Online definitions. "
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/eda35e4b5bedab51/535a1ef3d33f1d88?lnk=st&q=&rnum=3&hl=en#535a1ef3d33f1d88
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
> Thanks!
> Wayne
>|||"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
>
Anyone can submit doc bugs. The feedback link at the bottom of the BOL
topics will generate an email that automatically creates a doc bug.
David

Description for fill factor on index rebuild maint plan misleading

Hi.
I found this misleading issue and thought I would share it.
We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
fillfactor of 90%. After the plan ran, the database grew by about 70% and
the fill factor was actually 10%.
We found the following in the maint plan:
'Change free space per page percentage to:' We entered 10%.
In hindsight, it meant FILLFACTOR and should have been 90%.
Do you also find this misleading?
The documentation (BOL) reads:
Change free space per page percentage to
Drop the indexes on the tables in the database and re-create them with a
new, automatically calculated fill factor, thereby reserving the specified
amount of free space on the index pages. The higher the percentage, the more
free space is reserved on the index pages, and the larger the index grows.
Valid values are from 0 through 100.
It says the HIGHER the percentage, the more free space is reserved. This
should read LOWER?
Is this a 'bug' in the documentation?
Could someone please pass this onto the Microsoft guys. Maybe they know
about this already.
Thanks!
WayneWayne
I agree that it is a little bit confusing

> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
rebuild indexes is logged operation and needs a free space to rebuild all
indexes
Kalen Delaney said
"The first definition
is correct; fillfactor specifies how full each page should be. 30 means 30%
full, 100 means 100% full. The only special case is 0, which means the leaf
level is full, but there is room for one or two rows per page in the upper
levels of the index tree.
I will report this discrepancy in the Books Online definitions. "
http://groups.google.com/group/micr...33f1d88

"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
> Thanks!
> Wayne
>|||"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:D30307D8-C6C6-4196-AB44-76E2F7958714@.microsoft.com...
> Hi.
> I found this misleading issue and thought I would share it.
> We set up a maint plan on a SQL2005 RTM box to rebuild our indexes with a
> fillfactor of 90%. After the plan ran, the database grew by about 70% and
> the fill factor was actually 10%.
> We found the following in the maint plan:
> 'Change free space per page percentage to:' We entered 10%.
> In hindsight, it meant FILLFACTOR and should have been 90%.
> Do you also find this misleading?
> The documentation (BOL) reads:
>
> Change free space per page percentage to
> Drop the indexes on the tables in the database and re-create them with a
> new, automatically calculated fill factor, thereby reserving the specified
> amount of free space on the index pages. The higher the percentage, the
> more
> free space is reserved on the index pages, and the larger the index grows.
> Valid values are from 0 through 100.
>
> It says the HIGHER the percentage, the more free space is reserved. This
> should read LOWER?
>
> Is this a 'bug' in the documentation?
>
> Could someone please pass this onto the Microsoft guys. Maybe they know
> about this already.
>
Anyone can submit doc bugs. The feedback link at the bottom of the BOL
topics will generate an email that automatically creates a doc bug.
David

Descending Sort on index

I have a unique index based on the following columns:
ProjectID (int)
MaterialCatalogID (int)

Material catalogues are pretty much static but projects are dynamic and
people are most likely to be working on the latest project so would
using a descending sort on the ProjectID in the index gain any
performance?You'll probably have to give some more details of what your data looks
like, what your most frequent queries and data modifications are etc.
But changing the order of the index is probably only really useful when
you have a lot of queries which return results in that particular
order. You could always try it out on a test server, of course.

Simon|||An index can be traversed both ascending and descending. This means that
changing the index order (in the index definition) is never useful for
single column indexes.

If you have compound indexes, then the order can influence performance
of some very specific queries. Generally I would not worry about the
index order.

Gert-Jan

Trevor Best wrote:
> I have a unique index based on the following columns:
> ProjectID (int)
> MaterialCatalogID (int)
> Material catalogues are pretty much static but projects are dynamic and
> people are most likely to be working on the latest project so would
> using a descending sort on the ProjectID in the index gain any
> performance?

Descending keys in MSSQL7

I try the following index in SQL7
create index i1 on tab1 (f1 asc, f2 desc)
and I get an error. If I type the following:
create index i1 on tab1 (f1, f2 desc)
the index is created but the descending index is ignored. Can anyone point me in the right direction ?Features introduced with SQL2K, not supported in version 7,
"Lawrence" <lawrence@.magicsoftware.com> wrote in message
news:BB978427-F24F-4D10-9BA9-EC7D21B89CD9@.microsoft.com...
> I try the following index in SQL7
> create index i1 on tab1 (f1 asc, f2 desc)
> and I get an error. If I type the following:
> create index i1 on tab1 (f1, f2 desc)
> the index is created but the descending index is ignored. Can anyone point
me in the right direction ?