SQL Server Performance Forum – Threads Archive

sql server not using the index

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

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.

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.

Filter, as in: do you have any search criteria in the WHERE clause?

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

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?

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?

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.

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

Thanks Adriaan and gurucb

]]>