A Query That Does A Lot Of Reads, But Plan Is Ok

I'm experiencing a strange behaviour in a specific query in the SQL Server 2008 R2. I've got a query that does 19 million reads and is very time-consuming and when I try to check i

Solution 1:

A lot of things can affect the query this way. The most common is, probably, the parameter sniffing. When the query is executed for the first time, the plan is built using cardinality estimates got with these exact parameter values. So, if the parameter value used in the first run is very selective, optimizer will probably utilise nested loops for joins. So the next time, with another value that affects half the table, this cached plan will be extremely ineffective, because hash or merge join is better in this case.

Outdated distribution statistics can also lead to this kind of behaviour. As well as fragmented indices, too. Probably there are some other possibilities - without seeing the actual execution plan, the guessing can go on forever.

But you may try to add optimize for unknown option to the query and see whether it will help.