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.
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.
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..?