Defragment system tables indexes
-
Does anyone know, if performance will suffer if system table indexes are fragmented and if so, should we be rebuilding these indexes ?
-
Yes and Yes!
You can either drop and recreate them or use the DBCC INDEXDEFRAG ... which sometimes isn't QUITE as good, but is less invasive!
Cheers
Mark
-
I totally agree. Indexdefrag can be done while users are online. However, it can take a very long time, e.g. a dbcc reindex on 25 GB worth of indexes might take 2 or 3 hours on the right equipment, an indexdefrag might take 8 hours. Also, there are certain constraints if you have constraints or don't have a clustered index (which helps physical ordering). Last, sometimes, but maybe more in the past, totally dropping the index and rebuilding all the indexes and constraints may be the only way out of a problem.
-
I'm afraid there is no answer yet. I'd like to know it too, how to defragment SYSTEM TABLES. Indexdefrag doesn't work with system tables.
Jan
-
I would not think defrag would usually be worthwhile for SYSTEM tables. "25 GB worth of indexes" would not be the SYSTEM tables - sysobjects etc. They are mainly indexed by an ID number, 4 bytes, so even syscolumns might have 1000s of rows, say 4000x(4+4)= 4 pages (?). And thats probably the largest of them.
-
I have database with 1670 sysobjects (tables, views, defaults, fkeys, procedures, triggers). Sysobjects table has 32 pages (about 256 KB). But it has 12 extents. Fragmentatiton is more than 90%. I am sure, it must be good to know, if there is a technique to defragment it. I don't know, how big should be the problem in the future. I want to put bulk of application logic into database procedures. It will add many extra procedures. I'm afraid, I will need something to know about sysobjects defragmentation.
-
Yes you might need to defrag system tables, I'm not sure how much impact there would be of course.
How you approach this is another matter - I'm a little wary of discussing this on forum as it is messing with the system tables. There are a couple of tables whoose indexes you can't defrag except by more devious means!
You might want to check out "The Guru's guide to transact -sql" ( I'm not on commission !! )
You can also cause some system indexes to rebuild by altering the fill factor from 100/0 to 99% for the clustered index, this will rebuild all the indexes.
You could also try adding a clustered index and then removing it.
Hope this helps?
PS. Making any changes, no matter how minor, to a system table is very very very dangerous and you want to think hard and long before doing so. Make sure you have backups etc. If in doubt don't. < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Same question about system tables.
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 54
- Extents Scanned..............................: 13
- Extent Switches..............................: 43
- Avg. Pages per Extent........................: 4.2
- Scan Density [Best Count:Actual Count].......: 15.91% [7:44]
- Logical Scan Fragmentation ..................: 40.74%
- Extent Scan Fragmentation ...................: 84.62%
- Avg. Bytes Free per Page.....................: 4364.6
- Avg. Page Density (full).....................: 46.08%
While 54 pages is not a large amount I don't like finding that high a fragmentation level in sysindexes.
Bill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply