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

    Unanswered: indexdefrag an index scans

    Hi, Im a developer using sql server 2000 as our database.. We run a query regularly
    select * from aTable where item < 8 and noitems=0
    there is an index on the item column, the table is over 1 million records.
    The query analyser disregards the index when running the query - it performs a clustered index scan, Ive used an index hint but this was even more detramental performance wise, ive looked at the index fragmentation...There was quite a lot of index defragmentation on the table, scan density 13% which went up to 97% when I defragmented the indexes.. I presume which will be an improvement, yet didnt use the index still, was better over an index hint. My question is will the indexdefrag help with the clustered index scan, or is it better to rethink the index on this columns? Any help greatly appreciated, as Im relatively inexperienced in this area.
    Thx
    Eric

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    how about including NOITEMS into this clustered index? this way you'll get a covered index.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am not sure you would get a covering index with select *. I thought a covering index was only if all selected fields, and all queried fields were in the index.

    Creane. I remember you posting a very similar question, so I will give a very similar answer:

    What percentage of the table are you getting back with the query? indexes are ignored by the optimizer if you return more than around 5% of the table.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    including fields in SELECT clause is not required to create a covered index. data distribution needs to be analyzed to properly form a covered index comprised of fields that are present in WHERE, JOIN, GROUP BY, HAVING, and ORDER BY clauses.

  5. #5
    Join Date
    Aug 2003
    Posts
    7
    The percentage of records returned by the query against the total records is roughly about 30% (nearly 1.5. million total records) as opposed to 1/2 million records returned by query..Presumably if the index is not used in such circumstances would it be more efficient to put the 1/2 million records in a dedicated table i.e. rather than scanning a table of 1.5 million records query from a table of a 1/5 million records..?
    Thx again
    Eric

Posting Permissions

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