Index Defragmentation

None of the system tables in our databases have a significant number of
pages, and I can't see there would be many scans happening, but in the
interests of completeness should system tables be defragmented if they show
high fragmentation?Hi Ben
I would say yes, they are no different to any other table in that respect
and should be optimized in a similar way. Unfortunately you can't run DBCC
INDEXDEFRAG or DBCC DBREINDEX on system tables, so you will have to hope that
the system has it's own way of keeping these tables in tune.
John
"BenUK" wrote:
> None of the system tables in our databases have a significant number of
> pages, and I can't see there would be many scans happening, but in the
> interests of completeness should system tables be defragmented if they show
> high fragmentation?
>

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

None of the system tables in our databases have a significant number of
pages, and I can't see there would be many scans happening, but in the
interests of completeness should system tables be defragmented if they show
high fragmentation?Hi Ben
I would say yes, they are no different to any other table in that respect
and should be optimized in a similar way. Unfortunately you can't run DBCC
INDEXDEFRAG or DBCC DBREINDEX on system tables, so you will have to hope tha
t
the system has it's own way of keeping these tables in tune.
John
"BenUK" wrote:

> None of the system tables in our databases have a significant number of
> pages, and I can't see there would be many scans happening, but in the
> interests of completeness should system tables be defragmented if they sho
w
> high fragmentation?
>

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