January 16, 2006 at 11:12 am
dbcc showcontig shows my system tables are very fragmented both internally and externally. Does fragmentation of system tables make sense i.e. are they tables in a strong enough sense that showcontig and other tools and concepts apply to them. If so should\how can I defrag them?
Thanks for any help with this.
Jules
January 17, 2006 at 8:34 am
How about a maintenance plan on the system DBs?
January 17, 2006 at 8:52 am
I wouldn't de-frag/reindex/set any optimizations for SYSTEM databases.
According to books on line:
'DBCC DBREINDEX is not supported for use on system tables. '
Regards,
Satveer.
January 17, 2006 at 9:25 am
Theirs not a lot value in fixing non contiguous allocations for system tables, they are relatively small and are likely memory resident and they are generally key accessed as opposed to table scanned.
One tool available to address System tables is the sp_fixindex stored procedure. See Microsoft article 106122.
Needless to say, test this process and perform backups prior.
January 27, 2006 at 4:58 am
You cannot defrag them.
I've exactly the same issue.
I'm running SAP on my SQL box, having over 200,000 rows in sysobjects table + sysindexes etc.
I have been discussing with Micrososft about that issue and the answer is: No way to defrag thos tables.
There is a way to defrag other system tables by doing a lot of tricks (changing the system status, the name etc) I'm not suggesting it to you bacause you could dammage your database.
And anyhow You cannot do this trick the sysobjects and sysindexes.
You wil have to accept it as it is.
Bye
Gabor
January 27, 2006 at 5:03 am
http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=10071
I posted the same problem on this forumn and made more progresss. I have defragged non clustered index on system tables. See what you think and let me know..
Jules
January 27, 2006 at 7:18 am
Here is what I'm talking about.
And this is a "small" SAP implementation of ours
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 3793
- Extents Scanned..............................: 495
- Extent Switches..............................: 3792
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 12.52% [475:3793]
- Logical Scan Fragmentation ..................: 50.12%
- Extent Scan Fragmentation ...................: 99.19%
- Avg. Bytes Free per Page.....................: 6346.3
- Avg. Page Density (full).....................: 21.59%
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 721
- Extents Scanned..............................: 119
- Extent Switches..............................: 531
- Avg. Pages per Extent........................: 6.1
- Scan Density [Best Count:Actual Count].......: 17.11% [91:532]
- Logical Scan Fragmentation ..................: 28.29%
- Extent Scan Fragmentation ...................: 92.44%
- Avg. Bytes Free per Page.....................: 3178.0
- Avg. Page Density (full).....................: 60.74%
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 3, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 528
- Extents Scanned..............................: 84
- Extent Switches..............................: 527
- Avg. Pages per Extent........................: 6.3
- Scan Density [Best Count:Actual Count].......: 12.50% [66:528]
- Logical Scan Fragmentation ..................: 49.24%
- Extent Scan Fragmentation ...................: 96.43%
- Avg. Bytes Free per Page.....................: 5926.1
- Avg. Page Density (full).....................: 26.78%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Here I have something to do because the clustered index is huge not the nonclustered ones
Bye
Gabor
January 31, 2006 at 6:00 am
You can defrag most system tables and from a purist point of view there can be good reason to do so. Fragmentation is bad so if it can be removed - great. Always difficult to judge exactly how many of these housekeeping jobs affect performance, but from a "Job well done" perspective I always feel happier if I know I've done the indexes, stats, usage, ntfs fragmentation etc. etc. Like having a car serviced is every tiny chnage noticable - probably not - but together ? Well I always think my car feels better after a service!! Same with databases, if you can get rid of system table fragmentation then do it ( subject to the risks etc. etc. ) You need the database in single user mode btw, so it's not an on-line job.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply