Non-optimal execution plan using sp_executesql

Question

I'm having problems with slow performance in a sql select statement with some parameters, for the same query, executing this select using sp_executesql way it takes double time that the inline-way.

The problem is that in sp_execute-way sql server is not using optimal execution plan. Although plans are different, it seems that in both cases indexes of tables are being used correctly. I really don't understand why performance are so different.

My original query is more complex but to try to figure out what's happening I have simplify the original query to a select with 3 tables and 2 joins. The main difference is the use of Hash Match in optimal , I really don't know the meaning of this but is the only difference I can see.

Optimal plan (hash match, over 3 seconds)

Wrong plan (no hash match, same indexes than above, over 12 seconds)

1. I think my problem is not "parameter sniffing", in my case the query is always slow for all distinct parameter values because the execution plan is always incorrect.

2. OPTION (RECOMPILE) doesn't help, sp_executesql keeps going slow and inline-way take more time (because the query always compile the execution plan)

3. Statistics for tables are updated

4. I have to use sp_executesql way because it seems that reporting services encapsulates the select in sp_executesql calls

Does anybody know why sp_executesql generates a different (wrong) execution plan than the inline query?

---

EDIT: Queries wasn't using same indexes I guess that because the execution tree is not the same and sqlserver takes indexes as it pleases, attached you can find new execution plans to force to use the same indexes, performance is now even worst, from 12 seconds to more than 15 minutes (I have cancelled) in slow query. I'm really not interested in run this specific query more speed, as I say this is not the real query I'm dealing with, what I'm trying to figure out is why execution plans are so different between inline-query and sp_executesql-query.

Is there any magic option in sp_executesql that do this works properly? :)

Optimal

Slow

Answer 1

My understanding is that sp_executesql keeps a cached plan after the first execution. Subsequent queries maybe using a bad cached plan. You can use the following command to clear out the ENTIRE SQL Server procedure cache.

    DBCC FREEPROCCACHE

http://msdn.microsoft.com/en-us/library/ms174283.aspx