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??
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(*)
group by status
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.