Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: low scan density

    Hi were using a query that returns a large result set. the query's where clause includes a column that has an index (non clustered) e.g.
    select * from mytable where status <8 and prmts=0
    (status being the indexed column - i.e. leftmost column within a covering index ).. On development this uses the index. but on production a clustered index scan is performed..does it look like the indexes need to be rebuilt??

    DBCC SHOWCONTIG scanning 'mytable' table...
    Table: 'mytable' (1143675122); index ID: 1, database ID: 6
    TABLE level scan performed.
    - Pages Scanned................................: 22968
    - Extents Scanned..............................: 2896
    - Extent Switches..............................: 20544
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 13.97% [2871:20545]
    - Logical Scan Fragmentation ..................: 44.02%
    - Extent Scan Fragmentation ...................: 60.08%
    - Avg. Bytes Free per Page.....................: 3203.6
    - Avg. Page Density (full).....................: 60.42%
    DBCC SHOWCONTIG scanning 'mytable' table...
    Table: 'mytable' (1143675122); index ID: 2, database ID: 6
    LEAF level scan performed.
    - Pages Scanned................................: 8337
    - Extents Scanned..............................: 1052
    - Extent Switches..............................: 1563
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 66.69% [1043:1564]
    - Logical Scan Fragmentation ..................: 7.81%
    - Extent Scan Fragmentation ...................: 65.68%
    - Avg. Bytes Free per Page.....................: 980.4
    - Avg. Page Density (full).....................: 87.89%
    DBCC SHOWCONTIG scanning 'mytable' table...
    Table: 'mytable' (1143675122); index ID: 3, database ID: 6
    LEAF level scan performed.
    - Pages Scanned................................: 12668
    - Extents Scanned..............................: 1595
    - Extent Switches..............................: 9829
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 16.11% [1584:9830]
    - Logical Scan Fragmentation ..................: 37.06%
    - Extent Scan Fragmentation ...................: 45.89%
    - Avg. Bytes Free per Page.....................: 2991.4
    - Avg. Page Density (full).....................: 63.04%
    DBCC SHOWCONTIG scanning 'mytable' table...
    Table: 'mytable' (1143675122); index ID: 4, database ID: 6
    LEAF level scan performed.
    - Pages Scanned................................: 13537
    - Extents Scanned..............................: 1709
    - Extent Switches..............................: 8791
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 19.26% [1693:8792]
    - Logical Scan Fragmentation ..................: 29.40%
    - Extent Scan Fragmentation ...................: 50.03%
    - Avg. Bytes Free per Page.....................: 3283.1
    - Avg. Page Density (full).....................: 59.44%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It sounds like the data distribution is a little off between the two systems. If you are lucky, you can simply run update statistics (with fullscan) against the table and see if that helps. This should probably be done when few other people are on the system, as it will read the entire table. If it is a big table, you could hurt performance while it is running. If that does not help, then you will have to look at what percentage of the table has status = 8. This query can help:

    select status, count(*)
    from table
    group by status
    compute sum(count(*))

    or

    dbcc show_statistics ('table', 'index')

    If a large portion of the table has status = 8 (more than 10%) then the index will not be used. I think the actual threshold that the optimizer uses is around 4%, but I am not sure of that. Hope this helps.

Posting Permissions

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