defrag system tables indexes

does anyone know how could i defrag system tables indexes.. because dbcc
indexdefrag or rebuild can't be used, thksnikolakg wrote:
> does anyone know how could i defrag system tables indexes.. because dbcc
> indexdefrag or rebuild can't be used, thks
You shouldn't have to mess with the system table indexes. They're small
enough that defragging really isn't going to help them, and they're
static enough that they shouldn't fragment much, if at all, anyway.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i ran dbcc showcontig and the scan density for system tables was too low
12-15%.
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" έγγÏ?αÏ?ε:
> nikolakg wrote:
> > does anyone know how could i defrag system tables indexes.. because dbcc
> > indexdefrag or rebuild can't be used, thks
> You shouldn't have to mess with the system table indexes. They're small
> enough that defragging really isn't going to help them, and they're
> static enough that they shouldn't fragment much, if at all, anyway.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> i ran dbcc showcontig and the scan density for system tables was too low
> 12-15%.
>
How many pages are in the indexes you're looking at?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
syscolumns syscolumns 244 12200 12.810
sysdepends ncsysdepends1 56 2027 13.462
syscolumns ncsyscolumns 93 12200 13.483
sysindexes sysindexes 187 1063 13.793
sysdepends sysdepends 57 2027 14.815
syscomments syscomments 926 1505 16.885
sysobjects sysobjects 27 1680 17.391
sysobjects ncsysobjects 14 1680 20.000
sysproperties sysproperties 6 65 20.000
sysobjects ncsysobjects2 5 1680 25.000
this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
(and some more columns)
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" έγγÏ?αÏ?ε:
> nikolakg wrote:
> > i ran dbcc showcontig and the scan density for system tables was too low
> > 12-15%.
> >
> How many pages are in the indexes you're looking at?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||nikolakg wrote:
> OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> syscolumns syscolumns 244 12200 12.810
> sysdepends ncsysdepends1 56 2027 13.462
> syscolumns ncsyscolumns 93 12200 13.483
> sysindexes sysindexes 187 1063 13.793
> sysdepends sysdepends 57 2027 14.815
> syscomments syscomments 926 1505 16.885
> sysobjects sysobjects 27 1680 17.391
> sysobjects ncsysobjects 14 1680 20.000
> sysproperties sysproperties 6 65 20.000
> sysobjects ncsysobjects2 5 1680 25.000
>
> this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> (and some more columns)
>
Less than 1000 pages, don't worry about them.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||thank you!
Î? Ï?Ï?ήÏ?Ï?ηÏ? "Tracy McKibben" έγγÏ?αÏ?ε:
> nikolakg wrote:
> > OBJECT NAME INDEX NAME PAGES ROWS SCAN DENSITY
> > syscolumns syscolumns 244 12200 12.810
> > sysdepends ncsysdepends1 56 2027 13.462
> > syscolumns ncsyscolumns 93 12200 13.483
> > sysindexes sysindexes 187 1063 13.793
> > sysdepends sysdepends 57 2027 14.815
> > syscomments syscomments 926 1505 16.885
> > sysobjects sysobjects 27 1680 17.391
> > sysobjects ncsysobjects 14 1680 20.000
> > sysproperties sysproperties 6 65 20.000
> > sysobjects ncsysobjects2 5 1680 25.000
> >
> >
> > this is what i get when i run DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> > (and some more columns)
> >
> Less than 1000 pages, don't worry about them.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||We have an issue because text data from sysIndexes is sitting right at the
end of our primary datafile and this stops us truncating the end off the
file.
We have done a load of housekeeping and could reclaim a greate deal of disk
space.
We cannot do a shrink/movc data pages due to log shipping across our WAN.
DBCC extentinfo (Production, sysIndexes, -1)
Paul