DBfile usage: fillfactor

Showing posts with label fillfactor. Show all posts

Sunday, March 25, 2012

DBCC SHOWCONTIG incorrect?

Hi. I have a table that has 58 million rows and is about 24 GB. I ran a DBCC
DBREINDEX which appeared to run fine - new fillfactor showing etc. However,
showcontig still shows the indexes as being badly fragmented. I've run an
update stats with fullscan and update usage which has corrected incorrect
space stats but that's all. I run this process every month and normally
everything works as expected. Any ideas why this time it didn't please?Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
--
Keith Kratochvil
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Thanks. I've already done that and it didn't.
"Keith Kratochvil" wrote:
> Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
> --
> Keith Kratochvil
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Hi Shirley
Can you show us the details from DBCC SHOWCONTIG?
Also, how many indexes are there? Are they all fragmented, clustered and
nonclustered?
How much free space is in the database?
--
HTH
Kalen Delaney, SQL Server MVP
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Hi Kalen, thanks for replying.
There is one clustered IX and 3 NC IX. These are the results
- Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
- Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
- Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
- Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
There is about 30 GB free space in the DB and it can autogrow.
"Kalen Delaney" wrote:
> Hi Shirley
> Can you show us the details from DBCC SHOWCONTIG?
> Also, how many indexes are there? Are they all fragmented, clustered and
> nonclustered?
> How much free space is in the database?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Problem fixed. I ran an UPDATE STATISTICS again but with the FULLSCAN option,
then re-ran the reorg and all looks fine now. Don't know why this just
started to happen but will do the updatestats before the reorg every time now.
"Shirley" wrote:
> Hi Kalen, thanks for replying.
> There is one clustered IX and 3 NC IX. These are the results
> - Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
> - Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
> - Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
> - Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
> There is about 30 GB free space in the DB and it can autogrow.
>
> "Kalen Delaney" wrote:
> > Hi Shirley
> >
> > Can you show us the details from DBCC SHOWCONTIG?
> > Also, how many indexes are there? Are they all fragmented, clustered and
> > nonclustered?
> > How much free space is in the database?
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >
> >
> > "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> > news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > > DBCC
> > > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > > However,
> > > showcontig still shows the indexes as being badly fragmented. I've run an
> > > update stats with fullscan and update usage which has corrected incorrect
> > > space stats but that's all. I run this process every month and normally
> > > everything works as expected. Any ideas why this time it didn't please?
> >
> >
> >

Thursday, March 22, 2012

DBCC REINDEX

Hi:
On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR to
zero by using DBCC REINDEX option, but it is not allowing it to reset it to
default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT zero.
Any suggestions ''?
thanks in advance,
Kamran.Kamran,
0 equates to 100 which is the default and you can't use the value 0 (must be
between 1-99). I doubt you want the fillfactor to be anywhere near zero.
HTH
Jerry
"Kamran" <Kamran@.discussions.microsoft.com> wrote in message
news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> Hi:
> On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> to
> zero by using DBCC REINDEX option, but it is not allowing it to reset it
> to
> default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> zero.
> Any suggestions ''?
> thanks in advance,
> Kamran.|||thanks Jerry..
"Jerry Spivey" wrote:
> Kamran,
> 0 equates to 100 which is the default and you can't use the value 0 (must be
> between 1-99). I doubt you want the fillfactor to be anywhere near zero.
> HTH
> Jerry
> "Kamran" <Kamran@.discussions.microsoft.com> wrote in message
> news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> > Hi:
> >
> > On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> > to
> > zero by using DBCC REINDEX option, but it is not allowing it to reset it
> > to
> > default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> > zero.
> >
> > Any suggestions ''?
> >
> > thanks in advance,
> >
> > Kamran.
>
>

DBCC REINDEX

Hi:
On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR to
zero by using DBCC REINDEX option, but it is not allowing it to reset it to
default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT zero.
Any suggestions ''?
thanks in advance,
Kamran.Kamran,
0 equates to 100 which is the default and you can't use the value 0 (must be
between 1-99). I doubt you want the fillfactor to be anywhere near zero.
HTH
Jerry
"Kamran" <Kamran@.discussions.microsoft.com> wrote in message
news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> Hi:
> On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> to
> zero by using DBCC REINDEX option, but it is not allowing it to reset it
> to
> default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> zero.
> Any suggestions ''?
> thanks in advance,
> Kamran.|||thanks Jerry..
"Jerry Spivey" wrote:

> Kamran,
> 0 equates to 100 which is the default and you can't use the value 0 (must
be
> between 1-99). I doubt you want the fillfactor to be anywhere near zero.
> HTH
> Jerry
> "Kamran" <Kamran@.discussions.microsoft.com> wrote in message
> news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
>
>

Wednesday, March 21, 2012

DBCC REINDEX

Hi:
On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR to
zero by using DBCC REINDEX option, but it is not allowing it to reset it to
default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT zero.
Any suggestions ???
thanks in advance,
Kamran.
Kamran,
0 equates to 100 which is the default and you can't use the value 0 (must be
between 1-99). I doubt you want the fillfactor to be anywhere near zero.
HTH
Jerry
"Kamran" <Kamran@.discussions.microsoft.com> wrote in message
news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
> Hi:
> On SQL Server 2000 (service pack 3), I am trying to reset the FILLFACTOR
> to
> zero by using DBCC REINDEX option, but it is not allowing it to reset it
> to
> default (i.e. zero). I am able to reset it to 1 - 99 though, just NOT
> zero.
> Any suggestions ???
> thanks in advance,
> Kamran.
|||thanks Jerry..
"Jerry Spivey" wrote:

> Kamran,
> 0 equates to 100 which is the default and you can't use the value 0 (must be
> between 1-99). I doubt you want the fillfactor to be anywhere near zero.
> HTH
> Jerry
> "Kamran" <Kamran@.discussions.microsoft.com> wrote in message
> news:F2D018D3-D3E9-4018-876D-6DA701BF2A17@.microsoft.com...
>
>

Saturday, February 25, 2012

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[vbcol=seagreen]
> 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
> 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[vbcol=seagreen]
> 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
> 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...[vbcol=seagreen]
> 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
>

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]
>

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.
>
> > 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 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.
>
> > 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
>
>|||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...
> > 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
> >
> >
> >
>

DBCC DBREINDEX

There is a parameter in DBCC DBREINDEX that allows you to specify the
freespace on leaf level pages (fillfactor). This is only use when the index
is created and when the DBREINDEX command is run.
In SQL2005, there is a new parameter in the create index which is
'PAD_INDEX' this determines the amount of free space on the intermediate
pages of the index. When you run DBREINDEX the fill factor applies to the
leaf levels but does it also apply to the intermediate pages, or is there
another parameter I may be missing here?
Thanks in Advance
Dave
Dave
You are probably talking about ALTER INDEX operation
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON
The percentage of free space that is specified by FILLFACTOR is applied to
the intermediate-level pages of the index. If FILLFACTOR is not specified at
the same time PAD_INDEX is set to ON, the fill factor value stored in
sys.indexes is used.
OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity. This leaves
sufficient space for at least one row of the maximum size that the index can
have, based on the set of keys on the intermediate pages.
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave
|||Hi Uri,
I understand that but my question is does this 'PAD_INDEX' facility also
work with DBREINDEX?
Dave
"Uri Dimant" wrote:

> Dave
> You are probably talking about ALTER INDEX operation
> PAD_INDEX = { ON | OFF }
> Specifies index padding. The default is OFF.
> ON
> The percentage of free space that is specified by FILLFACTOR is applied to
> the intermediate-level pages of the index. If FILLFACTOR is not specified at
> the same time PAD_INDEX is set to ON, the fill factor value stored in
> sys.indexes is used.
> OFF or fillfactor is not specified
> The intermediate-level pages are filled to near capacity. This leaves
> sufficient space for at least one row of the maximum size that the index can
> have, based on the set of keys on the intermediate pages.
>
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>
|||Dave
I have no idea, but what I do know that DBCC DBREINDEX is deprecated
and will be removed in the future
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:5E727125-DDB0-44CD-8287-886D9D1436E2@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
>
> "Uri Dimant" wrote:
|||PAD_INDEX has always been there it is not a 2005 feature. But it does not
take a value for the space. Instead it is either on or off. If it is on it
will inherit the fill factor setting and use it for the intermediate levels
of the index just like the fill factor is for the leaf level. It is not
maintained except for rebuilds and creation.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave
|||Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
change this if required?
Thanks in Advance
Dave
Dave
"Andrew J. Kelly" wrote:

> PAD_INDEX has always been there it is not a 2005 feature. But it does not
> take a value for the space. Instead it is either on or off. If it is on it
> will inherit the fill factor setting and use it for the intermediate levels
> of the index just like the fill factor is for the leaf level. It is not
> maintained except for rebuilds and creation.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>
|||It is OFF by default and if you want to change it you need to specify it in
the create or rebuild index commands.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...[vbcol=seagreen]
> Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
> What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
> change this if required?
> Thanks in Advance
> Dave
>
> --
> Dave
>
> "Andrew J. Kelly" wrote:
|||Thanks Andrew
Dave
"Andrew J. Kelly" wrote:

> It is OFF by default and if you want to change it you need to specify it in
> the create or rebuild index commands.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...
>
>

Friday, February 24, 2012

DBCC DBREINDEX

There is a parameter in DBCC DBREINDEX that allows you to specify the
freespace on leaf level pages (fillfactor). This is only use when the index
is created and when the DBREINDEX command is run.
In SQL2005, there is a new parameter in the create index which is
'PAD_INDEX' this determines the amount of free space on the intermediate
pages of the index. When you run DBREINDEX the fill factor applies to the
leaf levels but does it also apply to the intermediate pages, or is there
another parameter I may be missing here?
Thanks in Advance
--
DaveDave
You are probably talking about ALTER INDEX operation
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON
The percentage of free space that is specified by FILLFACTOR is applied to
the intermediate-level pages of the index. If FILLFACTOR is not specified at
the same time PAD_INDEX is set to ON, the fill factor value stored in
sys.indexes is used.
OFF or fillfactor is not specified
The intermediate-level pages are filled to near capacity. This leaves
sufficient space for at least one row of the maximum size that the index can
have, based on the set of keys on the intermediate pages.
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave|||Hi Uri,
I understand that but my question is does this 'PAD_INDEX' facility also
work with DBREINDEX?
--
Dave
"Uri Dimant" wrote:

> Dave
> You are probably talking about ALTER INDEX operation
> PAD_INDEX = { ON | OFF }
> Specifies index padding. The default is OFF.
> ON
> The percentage of free space that is specified by FILLFACTOR is applied
to
> the intermediate-level pages of the index. If FILLFACTOR is not specified
at
> the same time PAD_INDEX is set to ON, the fill factor value stored in
> sys.indexes is used.
> OFF or fillfactor is not specified
> The intermediate-level pages are filled to near capacity. This leaves
> sufficient space for at least one row of the maximum size that the index c
an
> have, based on the set of keys on the intermediate pages.
>
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>|||Dave
I have no idea, but what I do know that DBCC DBREINDEX is deprecated
and will be removed in the future
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:5E727125-DDB0-44CD-8287-886D9D1436E2@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
>
> "Uri Dimant" wrote:
>|||When the documentation does not speak, it is time to experiment...
Based on the repro script (see below) I conclude that (only) if
PAD_INDEX was used upon index creation, then the index intermediate
levels are padded when reindexing.
HTH,
Gert-Jan
create table Test (id char(900) not null)
go
insert into Test values (1)
insert into Test values (2)
insert into Test values (3)
insert into Test select cast(id as int)+3 from Test
insert into Test select cast(id as int)+6 from Test
insert into Test select cast(id as int)+12 from Test
insert into Test select cast(id as int)+24 from Test
insert into Test select cast(id as int)+48 from Test
insert into Test select cast(id as int)+96 from Test
insert into Test select cast(id as int)+192 from Test
insert into Test select cast(id as int)+384 from Test
insert into Test select cast(id as int)+768 from Test
insert into Test select cast(id as int)+1536 from Test
insert into Test select cast(id as int)+3072 from Test
insert into Test select top 3856 cast(id as int)+6144 from Test
go
create unique clustered index IX_Test on Test(id)
go
sp_spaceused test
-- 11528 KB
go
DBCC DBREINDEX(Test,'',50)
go
sp_spaceused test
-- 18312 KB
go
DBCC DBREINDEX(Test,'',10)
go
sp_spaceused test
-- 91464 KB
go
drop index Test.IX_Test
go
create unique clustered index IX_Test on Test(id)
with (PAD_INDEX=ON, FILLFACTOR=50)
go
sp_spaceused test
-- 20040 KB
go
DBCC DBREINDEX(Test,'',100)
go
sp_spaceused test
-- 11528 KB
go
DBCC DBREINDEX(Test,'',50)
go
sp_spaceused test
-- 20040 KB
go
DBCC DBREINDEX(Test,'',10)
go
sp_spaceused test
-- 160072 KB
go
drop table Test
SkyDave wrote:[vbcol=seagreen]
> Hi Uri,
> I understand that but my question is does this 'PAD_INDEX' facility also
> work with DBREINDEX?
> --
> Dave
> "Uri Dimant" wrote:
>|||PAD_INDEX has always been there it is not a 2005 feature. But it does not
take a value for the space. Instead it is either on or off. If it is on it
will inherit the fill factor setting and use it for the intermediate levels
of the index just like the fill factor is for the leaf level. It is not
maintained except for rebuilds and creation.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
> There is a parameter in DBCC DBREINDEX that allows you to specify the
> freespace on leaf level pages (fillfactor). This is only use when the
> index
> is created and when the DBREINDEX command is run.
> In SQL2005, there is a new parameter in the create index which is
> 'PAD_INDEX' this determines the amount of free space on the intermediate
> pages of the index. When you run DBREINDEX the fill factor applies to the
> leaf levels but does it also apply to the intermediate pages, or is there
> another parameter I may be missing here?
> Thanks in Advance
> --
> Dave|||Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
change this if required?
Thanks in Advance
Dave
Dave
"Andrew J. Kelly" wrote:

> PAD_INDEX has always been there it is not a 2005 feature. But it does not
> take a value for the space. Instead it is either on or off. If it is on it
> will inherit the fill factor setting and use it for the intermediate level
s
> of the index just like the fill factor is for the leaf level. It is not
> maintained except for rebuilds and creation.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:EA1273B8-5F6C-4594-8DC9-C9924F240FB9@.microsoft.com...
>
>|||It is OFF by default and if you want to change it you need to specify it in
the create or rebuild index commands.
Andrew J. Kelly SQL MVP
"SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...[vbcol=seagreen]
> Andrew, Gert-Jan , Uri, thanks for your help, one last question on this.
> What's the default for PAD_INDEX when DBCC DBREINDEX is run and how can I
> change this if required?
> Thanks in Advance
> Dave
>
> --
> Dave
>
> "Andrew J. Kelly" wrote:
>|||Thanks Andrew
Dave
"Andrew J. Kelly" wrote:

> It is OFF by default and if you want to change it you need to specify it i
n
> the create or rebuild index commands.
> --
> Andrew J. Kelly SQL MVP
> "SkyDave" <SkyDave@.discussions.microsoft.com> wrote in message
> news:C8ABC2D0-7D5E-4D6C-98F6-6017D3EE54D2@.microsoft.com...
>
>

Sunday, March 25, 2012

DBCC SHOWCONTIG incorrect?

DBCC SHOWCONTIG incorrect?

Thursday, March 22, 2012

DBCC REINDEX

DBCC REINDEX

Wednesday, March 21, 2012

DBCC REINDEX

Saturday, February 25, 2012

DBCC DBREINDEX and FillFactor

DBCC DBREINDEX and FillFactor

DBCC DBREINDEX and FillFactor

DBCC DBREINDEX

Friday, February 24, 2012

DBCC DBREINDEX

DBfile usage

Blog Archive

About Me