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

Tectonics

Giving an answer to your questions will be very hard if you don't provide any code, indexes or the execution plans. Would that be possible? – Stretchy

I have attached execution plans, thanks – Tectonics

It's not using the same indexes: 1 uses IDX_EndDate_Start..., 2 uses EDX_SiteId_EndDate... – Stretchy

You were right, the indexes were different I guess that because the execution tree are different, I have force (with index()) to both select use the same indexes and slow query is now even slower than before, from 12 seconds to more than 15 minutes (I have cancelled...). I have edit my post to add new screenshots – Tectonics

Comment instead of answer because I can't tell if this is the problem because you have not provided the text of your query or the parameter values of the first execution. sp_executesql will cache a plan and that plan will greatly depend on the values of the parameters on first execution. This article may help: sqlskills.com/blogs/kimberly/… – Audiogenic

If you know that hash match provides the better execution speed, you can use the join hint by indicating inner hash join. However, the poor execution speed and it using the wrong indexes probably means that your indexes are sub-optimal. – Clam

Sir, I faced same problem. Did you find solution? – Byng

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

Sienkiewicz