Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Scan Density - Small Tables

    I have attached a DBCC ShowContig for a table that has a less than 50% scan density value.

    I vendor in insistent I have run their scripts incorrectly three times. I have run a "dbcc reindex" and even dropped primary key and indexes for a table and the scan density does not change. I have read white papers that state that on small tables you may never get a scan density of 100%. I am using this forum as a yardstick for this question.

    Thanks

    Table: 'ACCESSPROFPERM' (859150106); index ID: 1, database ID: 8
    TABLE level scan performed.
    - Pages Scanned................................: 4
    - Extents Scanned..............................: 2
    - Extent Switches..............................: 1
    - Avg. Pages per Extent........................: 2.0
    - Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 50.00%
    - Avg. Bytes Free per Page.....................: 709.5
    - Avg. Page Density (full).....................: 91.23%

    DBCC SHOWCONTIG scanning 'ACCESSPROFPERM' table...
    Table: 'ACCESSPROFPERM' (859150106); index ID: 2, database ID: 8
    LEAF 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 ..................: 0.00%
    - Extent Scan Fragmentation ...................: 75.00%
    - Avg. Bytes Free per Page.....................: 2013.0
    - Avg. Page Density (full).....................: 75.13%
    DBCC SHOWCONTIG scanning 'ACCESSPROFPERM' table...
    Table: 'ACCESSPROFPERM' (859150106); index ID: 3, database ID: 8
    LEAF level scan performed.
    - Pages Scanned................................: 3
    - Extents Scanned..............................: 3
    - Extent Switches..............................: 2
    - Avg. Pages per Extent........................: 1.0
    - Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 66.67%
    - Avg. Bytes Free per Page.....................: 1723.3
    - Avg. Page Density (full).....................: 78.71%

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    My experience has shown that these tables will not have any better scan density than they have right now. They are too small to have dedicated extents, and live in shared extents. This means they are jumbled up with a bunch of small tables in a SQL Server equivalent of a rooming house. Since the tables are so small to begin with, I would not worry about re-indexing them at all. If the vendor is insistent about getting 100% on all indexes/tables, then tell them to prove it can be done on tables of this size, or to stop wasting your billable hours.

    Can you tell I have had a few run ins with lousy vendors who think they know it all? It is almost fun to deal with them at some points. Especially when they try to salvage what little dignity they have when you prove them absolutely wrong.

  3. #3
    Join Date
    Aug 2004
    Posts
    9

    Thanks

    Thanks for the feedback...I will share with mgmt staff

Posting Permissions

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