index fragmentation

Hi,
I want to defrag all the indexes in a table. How do I use after DBCC
INDEXDEFRAG?
thanks,There is sample code for that in Books Online. Check under DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"aoxpsql" <anonymous@.discussion.com> wrote in message news:ucfQLjSeEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to defrag all the indexes in a table. How do I use after DBCC
> INDEXDEFRAG?
> thanks,
>

Index Defrags

Say you've got a large table that you're no longer
reindexing but are using instead INDEXDEFRAG. Does
INDEXDEFRAG pad the pages with the FILLFACTOR?
This is important to me because it has a clustered index
so I want to make sure we're not getting page splits.
Also, does INDEXDEFRAG reclaim space that would occur
from any deletes that you've done in a table?
From BooksOnLine under DBCC INDEXDEFAG:
DBCC INDEXDEFRAG also compacts the pages of an index, taking into account
the FILLFACTOR specified when the index was created. Any empty pages created
as a result of this compaction will be removed. For more information about
FILLFACTOR, see CREATE INDEX.
It does this when it can but there is no guarentee the fill factor will be
adjusted on all the pages. It depends on what it has to do, how much
fragmentation etc that exists. A few page splits are not a problem and can
not usually be avoided altogether. If you need a solid fill factor then
REINDEX is the only way to guarantee it on every page. Take a look here for
more details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"BoBl" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6aa01c45566$bc7780b0$a101280a@.phx.gbl...
> Say you've got a large table that you're no longer
> reindexing but are using instead INDEXDEFRAG. Does
> INDEXDEFRAG pad the pages with the FILLFACTOR?
> This is important to me because it has a clustered index
> so I want to make sure we're not getting page splits.
> Also, does INDEXDEFRAG reclaim space that would occur
> from any deletes that you've done in a table?

Index Defrags

Say you've got a large table that you're no longer
reindexing but are using instead INDEXDEFRAG. Does
INDEXDEFRAG pad the pages with the FILLFACTOR?
This is important to me because it has a clustered index
so I want to make sure we're not getting page splits.
Also, does INDEXDEFRAG reclaim space that would occur
from any deletes that you've done in a table?From BooksOnLine under DBCC INDEXDEFAG:
DBCC INDEXDEFRAG also compacts the pages of an index, taking into account
the FILLFACTOR specified when the index was created. Any empty pages created
as a result of this compaction will be removed. For more information about
FILLFACTOR, see CREATE INDEX.
It does this when it can but there is no guarentee the fill factor will be
adjusted on all the pages. It depends on what it has to do, how much
fragmentation etc that exists. A few page splits are not a problem and can
not usually be avoided altogether. If you need a solid fill factor then
REINDEX is the only way to guarantee it on every page. Take a look here for
more details:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"BoBl" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6aa01c45566$bc7780b0$a101280a@.phx
.gbl...
> Say you've got a large table that you're no longer
> reindexing but are using instead INDEXDEFRAG. Does
> INDEXDEFRAG pad the pages with the FILLFACTOR?
> This is important to me because it has a clustered index
> so I want to make sure we're not getting page splits.
> Also, does INDEXDEFRAG reclaim space that would occur
> from any deletes that you've done in a table?

Index Defrags

Say you've got a large table that you're no longer
reindexing but are using instead INDEXDEFRAG. Does
INDEXDEFRAG pad the pages with the FILLFACTOR?
This is important to me because it has a clustered index
so I want to make sure we're not getting page splits.
Also, does INDEXDEFRAG reclaim space that would occur
from any deletes that you've done in a table?From BooksOnLine under DBCC INDEXDEFAG:
DBCC INDEXDEFRAG also compacts the pages of an index, taking into account
the FILLFACTOR specified when the index was created. Any empty pages created
as a result of this compaction will be removed. For more information about
FILLFACTOR, see CREATE INDEX.
It does this when it can but there is no guarentee the fill factor will be
adjusted on all the pages. It depends on what it has to do, how much
fragmentation etc that exists. A few page splits are not a problem and can
not usually be avoided altogether. If you need a solid fill factor then
REINDEX is the only way to guarantee it on every page. Take a look here for
more details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"BoBl" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6aa01c45566$bc7780b0$a101280a@.phx.gbl...
> Say you've got a large table that you're no longer
> reindexing but are using instead INDEXDEFRAG. Does
> INDEXDEFRAG pad the pages with the FILLFACTOR?
> This is important to me because it has a clustered index
> so I want to make sure we're not getting page splits.
> Also, does INDEXDEFRAG reclaim space that would occur
> from any deletes that you've done in a table?

Index defragmentation

Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
database?Hello,
Please take a look into the URL which contains the script.
http://www.databasejournal.com/scripts/article.php/3466971
Thanks
Hari
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:4BCDB5B7-FD22-46B1-A36D-78C860D58A7C@.microsoft.com...
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
> a
> database?|||Rob,
this should work:
EXEC sp_msForEachTable @.COMMAND1= 'DBCC indexdefrag (''dbadata'', "?")'
However I'd recommend a more circumspect approach that takes accountof the
level of fragmentation before deciding what to reindex.
Paul Ibison|||On Mar 1, 9:36 am, Rob <R...@.discussions.microsoft.com> wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
> database?
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html|||Rob wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
> database?
To add to the other replies, you can also look up DBCC SHOWCONTIG
command in BOL - here's an example of a script that can do what you ask
for.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Index defragmentation

Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in a
database?Hello,
Please take a look into the URL which contains the script.
http://www.databasejournal.com/scri...cle.php/3466971
Thanks
Hari
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:4BCDB5B7-FD22-46B1-A36D-78C860D58A7C@.microsoft.com...
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
> a
> database?|||Rob,
this should work:
EXEC sp_msForEachTable @.COMMAND1= 'DBCC indexdefrag (''dbadata'', "?")'
However I'd recommend a more circumspect approach that takes accountof the
level of fragmentation before deciding what to reindex.
Paul Ibison|||On Mar 1, 9:36 am, Rob <R...@.discussions.microsoft.com> wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
a
> database?
http://realsqlguy.blogspot.com/2007...gmentation.html|||Rob wrote:
> Is there a way to run DBCC INDEXDEFRAG for all indexes, on all tables, in
a
> database?
To add to the other replies, you can also look up DBCC SHOWCONTIG
command in BOL - here's an example of a script that can do what you ask
for.
Regards
Steen Schlter Persson
Database Administrator / System Administrator