DBCC DBREINDEX and FillFactor

My database has one data file only, and the data file was 900M of data after
I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space so
it does not have to expand in a while; I ran DBCC
DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
clustered index). I was expecting the database to expand 900 X 90% = 90M.
However, when it completed, the data file became 1.9G in total. When I view
the database from EM (taskpad view), looks like 50% free space. Why? Thanks."John" <john@.aicinc.com> wrote in message
news:%23ss5hYFbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> My database has one data file only, and the data file was 900M of data
after
> I ran DBCC SHRINKDATABASE(DBName). But I wanted to give it 10% free space
so
> it does not have to expand in a while; I ran DBCC
> DBREINDEX(TableName,N'',90) on all of my user tables (all tables have
> clustered index). I was expecting the database to expand 900 X 90% = 90M.
> However, when it completed, the data file became 1.9G in total. When I
view
> the database from EM (taskpad view), looks like 50% free space. Why?
Thanks.
>
The SHRINKDATABASE by default will leave 10% of your database as free space.
It also does a few other things similar to a defrag on a hard disk, but for
you database objects.
The DBREINDEX command that you specified tells the indexes to be rebuilt
with a fillfactor of 90%. This means that each index will use 90% of a
data page and leave 10% as free space for future growth. For non-clustered
indexes, this does do a whole lot to your overall database growth. For
clustered indexes however, this forces 10% free space at the leaf level of
the index (which is the actual table itself.).
Every table that you created a primary key on using default settings will
create a clustered index on that primary key column. So in essence, by
setting the fill-factor of those indexes to 90% forces up to a 10% growth in
the size of each table.
I hope this explanation helps.
Rick Sawtell
MCT, MCSD, MCDBA|||Rick,
Sorry but that's a little misleading.

> The SHRINKDATABASE by default will leave 10% of your database as free
space.
> It also does a few other things similar to a defrag on a hard disk, but
for
> you database objects.
Actually a Shrink operation will fragment the objects not defrag. Shrinking
a database is the worst thing to do if your concerned with fragmentation and
the objects being contiguous on disk. Another thing, SHRINKDATABASE will
shrink both the Data files and the Log file to get the target amount and
does not discriminate between how much of each it will shrink . You should
consider using SHRINKFILE if shrinking is absolutely necessary so as you get
the control you want.

> The DBREINDEX command that you specified tells the indexes to be rebuilt
> with a fillfactor of 90%. This means that each index will use 90% of a
> data page and leave 10% as free space for future growth. For
non-clustered
> indexes, this does do a whole lot to your overall database growth.
A non clustered index is simply implemented behind the scenes as a table
with a clustered index using the data from the column(s) in the index
expression with the addition of the Clustered Index key value appended. It
will adhere to the same fill factor and actually requires less space and
trouble to rebuild than a clustered index.

> For
> clustered indexes however, this forces 10% free space at the leaf level of
> the index (which is the actual table itself.).
> Every table that you created a primary key on using default settings will
> create a clustered index on that primary key column. So in essence, by
> setting the fill-factor of those indexes to 90% forces up to a 10% growth
in
> the size of each table.
Any time you rebuild a clustered index you need at least 1.2 times the size
of the table in free space in the database or it will grow. And each
nonclustered index requires similar free space equal to the size of the
index. This is why it is so important to have lots of free space in the
database at all times and especially before reindexing. By reindexing all
the tables in a database with little free space it will force multiple
autogrows and end up with numbers like John reported.
Andrew J. Kelly SQL MVP|||Thanks Andrew. I am still confused why it is doubling the space in my data
file (log file) instead of somewhere around 10% increase. We do have a lot
of indexes on each table.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> space.
> for
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get
> the control you want.
>
> non-clustered
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
>
of[vbcol=seagreen]
will[vbcol=seagreen]
growth[vbcol=seagreen]
> in
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>|||Thanks Andrew. I read your reply again and I think I got it.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
> Rick,
> Sorry but that's a little misleading.
>
> space.
> for
> Actually a Shrink operation will fragment the objects not defrag.
Shrinking
> a database is the worst thing to do if your concerned with fragmentation
and
> the objects being contiguous on disk. Another thing, SHRINKDATABASE will
> shrink both the Data files and the Log file to get the target amount and
> does not discriminate between how much of each it will shrink . You
should
> consider using SHRINKFILE if shrinking is absolutely necessary so as you
get
> the control you want.
>
> non-clustered
> A non clustered index is simply implemented behind the scenes as a table
> with a clustered index using the data from the column(s) in the index
> expression with the addition of the Clustered Index key value appended. It
> will adhere to the same fill factor and actually requires less space and
> trouble to rebuild than a clustered index.
>
of[vbcol=seagreen]
will[vbcol=seagreen]
growth[vbcol=seagreen]
> in
>
> Any time you rebuild a clustered index you need at least 1.2 times the
size
> of the table in free space in the database or it will grow. And each
> nonclustered index requires similar free space equal to the size of the
> index. This is why it is so important to have lots of free space in the
> database at all times and especially before reindexing. By reindexing all
> the tables in a database with little free space it will force multiple
> autogrows and end up with numbers like John reported.
>
> --
> Andrew J. Kelly SQL MVP
>
>|||John,

> Thanks Andrew. I am still confused why it is doubling the space in my data
> file (log file) instead of somewhere around 10% increase. We do have a lot
> of indexes on each table.
OK do you mean data file or Log file? You say both. Data files hold the
actual data and have extensions such as .mdf and .ndf. Log files hold
transaction information and have a .ldf extension. And when you talk about
the database size are you referring to just the data file size or both the
data and Log files? You really need to treat them as two completely
separate things. It is likely your data file autogrew many times during the
reindexing operation and not just once. Each time it autogrows it gets
larger and then it will grow more the next time. 10% free space is usually
no where near the amount of free space required to do a Reindex properly.
Another thing to keep in mind is that the total free space in the database
does not really mean you can use it. For instance if your tables have a lot
of fragmentation or a very low fill factor that free space on those pages is
not useable for anything other than inserts to that particular table. A
reindex in table A can not use the free space from pages on table B and so
on.
Andrew J. Kelly SQL MVP|||If not, please let us know what we can help with.
Andrew J. Kelly SQL MVP
"John" <john@.aicinc.com> wrote in message
news:eny1c3NbEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Thanks Andrew. I read your reply again and I think I got it.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23r2zZAHbEHA.3476@.tk2msftngp13.phx.gbl...
but[vbcol=seagreen]
> Shrinking
> and
will[vbcol=seagreen]
> should
> get
rebuilt[vbcol=seagreen]
a[vbcol=seagreen]
It[vbcol=seagreen]
level[vbcol=seagreen]
> of
> will
by[vbcol=seagreen]
> growth
> size
all[vbcol=seagreen]
>