Optimizations on System Tables

Hi

I have an issue where my system tables are heavily fragmented. Currently our system creates between 200 and 400 new logins daily (its the method the application handles it security). However we have had execution times on this of between 13ms and 1900ms, very inconsistent.

I have seen in a previous thread http://dbforums.com/arch/70/2003/11/965415
that its Kalen Delaney's opinion that fragmentation doesn't really matter unless you have 'several dozen' pages

Here is an example of our sysxlogins table's report :

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 162
- Extents Scanned.......................: 35
- Extent Switches.......................: 156
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 13.38% [21:157]
- Logical Scan Fragmentation ..............: 49.38%
- Extent Scan Fragmentation ...............: 34.29%
- Avg. Bytes Free per Page................: 5694.6
- Avg. Page Density (full)................: 29.64%

As you can see, its got 162 pages, which probably counts as 'several' dozen.

I would like to know if its possible for us to defrag/reindex this table, as you can see, it has a VERY low Scan Density, and a rather high Fragmentation level.

Opinions, help, undocumented functionality all welcome
TiaanI am facing a same issue.
I have noticed that my system table are extremly fragmented. Take a look. Any suggestions? Or does it not matter that they are like that?

DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 23
- Extents Scanned.......................: 7
- Extent Switches.......................: 21
- Avg. Pages per Extent..................: 3.3
- Scan Density [Best Count:Actual Count]......: 13.64% [3:22]
- Logical Scan Fragmentation ..............: 43.48%
- Extent Scan Fragmentation ...............: 71.43%
- Avg. Bytes Free per Page................: 2744.6
- Avg. Page Density (full)................: 66.09%

DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 25.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3019.0
- Avg. Page Density (full)................: 62.70%

DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 85
- Extents Scanned.......................: 17
- Extent Switches.......................: 81
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 13.41% [11:82]
- Logical Scan Fragmentation ..............: 44.71%
- Extent Scan Fragmentation ...............: 70.59%
- Avg. Bytes Free per Page................: 3348.5
- Avg. Page Density (full)................: 58.63%

DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 159
- Extent Switches.......................: 769
- Avg. Pages per Extent..................: 6.2
- Scan Density [Best Count:Actual Count]......: 15.97% [123:770]
- Logical Scan Fragmentation ..............: 46.24%
- Extent Scan Fragmentation ...............: 18.24%
- Avg. Bytes Free per Page................: 3261.0
- Avg. Page Density (full)................: 59.71%

DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 4
- Extents Scanned.......................: 3
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3771.5
- Avg. Page Density (full)................: 53.40%

DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 29
- Extents Scanned.......................: 8
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 14.81% [4:27]
- Logical Scan Fragmentation ..............: 48.28%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3096.8
- Avg. Page Density (full)................: 61.74%

DBCC SHOWCONTIG scanning 'sysxlogins' table...
Table: 'sysxlogins' (33); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 4
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 2.5
- Scan Density [Best Count:Actual Count]......: 28.57% [2:7]
- Logical Scan Fragmentation ..............: 60.00%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 3522.2
- Avg. Page Density (full)................: 56.48%

DBCC SHOWCONTIG scanning 'sysmessages' table...
Table: 'sysmessages' (36); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 169
- Extents Scanned.......................: 23
- Extent Switches.......................: 26
- Avg. Pages per Extent..................: 7.3
- Scan Density [Best Count:Actual Count]......: 81.48% [22:27]
- Logical Scan Fragmentation ..............: 1.78%
- Extent Scan Fragmentation ...............: 13.04%
- Avg. Bytes Free per Page................: 3926.9
- Avg. Page Density (full)................: 51.48%

DBCC SHOWCONTIG scanning 'syslanguages' table...
Table: 'syslanguages' (44); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.5
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3206.0
- Avg. Page Density (full)................: 60.39%

DBCC SHOWCONTIG scanning 'syscharsets' table...
Table: 'syscharsets' (45); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 33
- Extents Scanned.......................: 6
- Extent Switches.......................: 7
- Avg. Pages per Extent..................: 5.5
- Scan Density [Best Count:Actual Count]......: 62.50% [5:8]
- Logical Scan Fragmentation ..............: 24.24%
- Extent Scan Fragmentation ...............: 16.67%
- Avg. Bytes Free per Page................: 2896.6
- Avg. Page Density (full)................: 64.21%

DBCC SHOWCONTIG scanning 'sysaltfiles' table...
Table: 'sysaltfiles' (94); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 25.00% [1:4]
- Logical Scan Fragmentation ..............: 14.29%
- Extent Scan Fragmentation ...............: 75.00%
- Avg. Bytes Free per Page................: 4283.4
- Avg. Page Density (full)................: 47.08%

DBCC SHOWCONTIG scanning 'spt_values' table...
Table: 'spt_values' (85575343); index ID: 1, database ID: 1
TABLE level scan performed.
- Pages Scanned........................: 7
- Extents Scanned.......................: 4
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.8
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 28.57%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2671.7
- Avg. Page Density (full)................: 66.99%|||You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.