index defrag rebuild

Hi,
I have a table that has one clustered index and one non-clustered index
defined. What difference would it be if you do the following actions:
DBCC INDEXDEFRAG
DBCC DBREINDEX
REBUILD INDEX
Also, if the table had 100 rows in the beginning when the indexes were
created. After creating the indexes 100 more rows were inserted into the
table. When would these newly inserted rows gets included in the index?
Thanks in advance.Yes, after you create the index, this index will be updated when records are
inserted or deleted.
If your table only has a few hundred records you do not need to worry about
reindexing. Even if you have big tables you need to check the level of index
fragmentation first. Start by looking at DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats on BOL.
Hope this helps,
Ben Nevarez
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. Thatâ's when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, askâ?¦
> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke :). The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc hasâ?¦
> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
--
Thanks, Liliya
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.|||> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
When they are inserted. Thatâ's when the database will touch the indexes. You
have the clustered index in there. That clustered index is your table however
wired it sounds, it is your physical order and the physical pages can be
organized only one way. BOL for sql 2005 has pretty good description what a
clustered index is. Have questions, askâ?¦
> DBCC INDEXDEFRAG
That is a low priority operation. Pretty much it will reorganize the index
for you, will shuffle pages in place. It will take in consideration the fill
factor of the index and compact the pages, if there are any empty ones as a
result of the compaction, they will be freed (removed that is). This is an
on-line operation and it does not lock other processes and is not locked by
them, does not create long living locks, will skip pages locked by other
processes. What does it do?
DBCC INDEXDEFRAG de-fragments the leaf level of an index so that the
physical order of the pages matches the left-to-right logical order of the
leaf nodes, therefore improving index-scanning performance. When DBCC
INDEXDEFRAG is run, index de-fragmentation occurs serially. This means that
the operation on a single index is performed using a single thread. No
parallelism occurs. Also, operations on multiple indexes from the same DBCC
INDEXDEFRAG statement are performed on one index at a time.
Unlike DBCC DBREINDEX is the real rebuild. It is not an on-line operation
and holds long term locks. DBCC DBREINDEX in other words will and should
conflict with any dml on the same table, DBCC INDEXDEFRAG will not
lock/conflick as it was mentioned earlier.
The chose if -time and the fragmentation level you have. If your
maintenance windows allows and your fragmentation can not be fixed by
INDEXDEFRAG, then rebuild that thing.
Does not matter which sql command is used to de-fragment the index, it is
always fully logged, does not matter what mode is your database in. Therefore
do not run INDEXDEFRAG when your database is active period, all you are going
to get is about 1.4 size of your data in tlogs and you did not de-fragment
your index anyways, because the applications were 'dancing' on it locking
pages, INDEXDEFRAG has skipped those, but it was really busy trying to do
the job you have asked for. BOL says that defragmentation of a very
fragmented index can generate more log than a fully logged index creation.
I've seen that one not once, not a joke :). The defragmentation, is performed
as a series of short transactions, but still tlog gets quite big (esp when
you have fast SAN under the tlogs).
DBREINDEX is not good to run when the database is active, pick your quiet
time if you can. Otherwise you will see spike in locks and dead locks. What
will it do to your system depends how fault tolerant your applications are
and if they can handle dead lock errors (many developers just do not code
such a stuff)
When to choose what:
If the index is interleaved with other indexes on disk, running DBCC
INDEXDEFRAG against that index does not make all leaf pages in the index
contiguous. Therefore if clustering is what you are after, rebuild.
Maintenance plans:
Native Rebuild Index Task uses the ALTER INDEX statement when connected to
SQL Server 2005, and the DBCC DBREINDEX statement when connected to SQL
Server 2000
If you are using 3-rd party backup/maintenance read carefully their doc and
just run good old trace to see what exactly are they calling. The trace will
show you what is it, it may be not what the doc hasâ?¦
> DBCC DBREINDEX
> REBUILD INDEX
On index rebuild BOL has pretty good description.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
Making a decision if you have fragmentation.
DBCC SHOWCONTIG WITH TABLERESULTS
Avg. Page Density (full):
average level how filled the pages are. The value should be compared to
the fill factor setting of the particular index, then you look and decide
whether or not the index is internally fragmented.
Scan Density:
the ratio between the Best Count (what was necessary to read when
scanning all the pages of the index) and Actual Count of extents (what did
it actually read). You want this to be as close to 100 as possible. 75% and
below definitely indicates external fragmentation. It is a fine line to
deside what is fragmented what is not. you may have a system that just
creates such a number because it is it's nature and this is how your data are
insrterd,deleted,updated and you have to live with it.
Logical Scan Fragmentation:
shows the ratio of pages out of logical order. You want this close to 0.
10% + indicates external fragmentation.
I've seen references that "Extent Scan Fragmentation" is not really
important. This is because the algorithm that calculates it does not work for
indexes that span over multiple files. According to some Microsoft sources,
in the next version of SQL Server, DBCC SHOWCONTIG will not even include this
metric.
I did not mess with 2008 to test that one to be honest.
--
Thanks, Liliya
"sharman" wrote:
> Hi,
> I have a table that has one clustered index and one non-clustered index
> defined. What difference would it be if you do the following actions:
> DBCC INDEXDEFRAG
> DBCC DBREINDEX
> REBUILD INDEX
> Also, if the table had 100 rows in the beginning when the indexes were
> created. After creating the indexes 100 more rows were inserted into the
> table. When would these newly inserted rows gets included in the index?
> Thanks in advance.