Why does sp_executesql use a different query plan?

My C# application issues a query via Entity Framework which runs quickly in dev and test, but slowly in production.

I thought perhaps a bad query plan had been cached, so I tried DBCC freeproccache and DBCC dropcleanbuffers, but it still ran slowly. (I did this before and after each step of the investigation because I'm paranoid about bad plans being cached.)

I tried refactoring the query (taking it outside of sp_executesql) and it ran quickly.

Then I found an index missing from production, created it, and my app started to work quickly, while the original query in SSMS still ran slowly. It's not clear why the index would have made a difference - it's not helping with the join, and it doesn't cover all the columns required.

Question

What is influencing the choice of plan, such that wrapping it inside sp_executesql seems to be a factor? (And why does the app now perform OK while the same query in SSMS doesn't? It's usually the other way around due to bad cached plans.)

Original query:

```
exec sp_executesql N'SELECT
[Limit1].[C1] AS [C1],
[Limit1].[UnitId] AS [UnitId],
[Limit1].[StreetNumber] AS [StreetNumber],
[Limit1].[StreetAlpha] AS [StreetAlpha],
[Limit1].[Name] AS [Name],
[Limit1].[Name1] AS [Name1],
[Limit1].[Direction] AS [Direction],
[Limit1].[Name2] AS [Name2],
[Limit1].[Name3] AS [Name3],
[Limit1].[CorporateName] AS [CorporateName],
[Limit1].[Surname] AS [Surname],
[Limit1].[FirstNames] AS [FirstNames],
[Limit1].[DIPID] AS [DIPID],
[Limit1].[ValuationReference] AS [ValuationReference]
FROM ( SELECT DISTINCT TOP (51)
[Extent1].[DIPID] AS [DIPID],
[Extent1].[ValuationReference] AS [ValuationReference],
[Extent2].[CorporateName] AS [CorporateName],
[Extent2].[FirstNames] AS [FirstNames],
[Extent2].[Surname] AS [Surname],
[Extent3].[StreetNumber] AS [StreetNumber],
[Extent3].[UnitId] AS [UnitId],
[Extent3].[StreetAlpha] AS [StreetAlpha],
[Extent4].[Name] AS [Name],
[Extent4].[

Community wiki answer:

Parameters and local variables are different beasts.

The plan with parameters is generated (if not already cached) using row count estimates gleaned from the actual values supplied and statistic histograms.

The plan with local variables is generated based on unknown values so the average overall density (all_density) is used to estimate row counts.

The plans may differ due to different row count estimates.

Related: Understanding Performance Mysteries by Erland Sommarskog

StackExchange Database Administrators Q#177326, answer score: 4