Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: 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
    Tiaan

  2. #2
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I 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%

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You need not worry about fragmentation on System tables and use DBCC UPDATEUSAGE in order to correct the inconsistency.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •