SQL Server Performance

sql server not using the index

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Rocky, Nov 13, 2006.

  1. Rocky New Member

    Hi,

    I have exact schema on 2 databases, the table structure, columns and indexes match but one of them has huge data compared to other.

    Now the problem is, for the database with lesser number of records, sql server uses the right index (viewing the execution plan) and does a index seek, where as on the databse which has lot of data for that table, it goes for a scan.

    The index has been created on the field used in where clause of the select query.

    Can you help me understand whats going on here.

    Thanks & Regards,
    Rocky
  2. Adriaan New Member

    Is the second plan showing a table scan, or an index scan?

    Sounds like a statistics issue. Are you using the same filter criteria for both? Try this query on both instances:

    SELECT filter_col, COUNT(*)
    FROM table
    GROUP ON filter_col
    ORDER BY COUNT(*) DESC

    If there are a number of very big counts, and a large number of small counts, and the criteria include (one of) the big count values, then an index seek is pointless.
  3. Rocky New Member

    Hi Adriaan,
    The second one is showing index scan and I am running the exact same query (if thats what you mean by same filter criteria).

    -Rocky


    quote:Originally posted by Adriaan

    Is the second plan showing a table scan, or an index scan?

    Sounds like a statistics issue. Are you using the same filter criteria for both? Try this query on both instances:

    SELECT filter_col, COUNT(*)
    FROM table
    GROUP ON filter_col
    ORDER BY COUNT(*) DESC

    If there are a number of very big counts, and a large number of small counts, and the criteria include (one of) the big count values, then an index seek is pointless.
  4. Adriaan New Member

    Filter, as in: do you have any search criteria in the WHERE clause?
  5. Rocky New Member

    yes i do,
    this is how the query looks,

    select name, address, email, blah, blah, blah
    from table1
    where corp_id = 'blah'


    and the 2nd databsae is not using the index created on corp_id.....
  6. Adriaan New Member

    SELECT corp_id, COUNT(*)
    FROM table
    GROUP BY corp_id
    ORDER BY COUNT(*) DESC

    Does 'blah' have (one of) the highest counts, close to the total number of rows in the table?
  7. Rocky New Member

    Thanks Adriaan,

    I do not have access to the database right now, so can not actually find if the max count for that value for the column is approximately equal to the total number of records.

    so if that is the case then sql server may go for index scan rather than seek, is this inference correct?





    quote:Originally posted by Adriaan

    SELECT corp_id, COUNT(*)
    FROM table
    GROUP BY corp_id
    ORDER BY COUNT(*) DESC

    Does 'blah' have (one of) the highest counts, close to the total number of rows in the table?
  8. Adriaan New Member

    If SQL Server expects that most rows will match your filter criteria, then it might as well read the whole index and skip the entries that it doesn't need.

    Part of the problem might be that SQL Server bases its expectations on statistics, and statistics are not always up to date. Or perhaps there is indeed a very small number of records in the one instance, where the index will fit on one or two pages.
  9. gurucb New Member

    In SQL Server 2000 book mark lookups cost was not computed to proper cost... For example book mark lookup will cause Random IO which is bad for IO. So, in sql server 2005 cost of bookmark is set correctly and you will thus see suprising results... Though this may not be the case here and we may be fetching huge data where sql server concludes doing index scan is better than seek.. Referring blog:
    http://blogs.msdn.com/craigfr


  10. Rocky New Member

    Thanks Adriaan and gurucb

Share This Page